0

We are having an issue with SQL Server 2008 R2 64 responding to stored procedure call. About every 2 weeks or so, the database stops responding to stored procedures called from an ADO connection/Command set (4.0 framework). We have been working on this for several months now, with little improvement.

System changes:

We upgraded an existing vendor product from SQL Server 2005 to SQL Server 2008 R2 via their upgrade method. The database instance moved from a 32-bit Windows 2003 Server to 64-bit Windows 2008 Server.

The pattern of failure:

The application is run throughout the day, executed by different users via Citrix without issue. Every few weeks, the application stops responding around the same time frame. Once the database stops responding to the hosted instance of the application, any execution of the procedure from the application hangs (installed on CITRIX server, installed on varied physical systems, or debugging in VStudio 2010). After an hour of checking logs, server status, SQL Monitoring tools, tracing the repeated execution attempts, the server decides to respond to the application without intervention.

Strange thing is, when the server is not responding to ADO.Net calls, we execute the stored procedure from SQL Server Management Studio and receive results in 1 to 2 seconds. We are using the same login to access SQL Server Management Studio, and executing the stored procedure with the same parameters.

Looking at the connection string passed to the ADO connection, I don’t see anything unusual:

connectionString="Data Source=myserver\myinstance;Initial Catalog=databaseName;Persist Security Info=True;User ID=xxxxx;Password=yyyyy;Connect Timeout=45"

Tried so far:

  • Added extra 2gb of RAM to the OS: no change
  • Added extra tempdb file, expanded size of tempdb log file from 1 to 5gb: reduced the issue from weekly to every 2nd or 3rd week.
  • Installed SQL Server 2008 R2 SP3: no change.

The black cloud:

To me, the repeating time pattern of failure implies an issue at the database host (server or resource), but the DBAs do not see load or resource issue. If it were purely a host issue, why does it respond to SQL Server Management calls, and not ADO.NET calls?

The last occurrence lasted over two hours, and was resolved after rebooting the database server. Not a great fallback, but desperate times and all…..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cclarke
  • 86
  • 1
  • 7
  • Update: after further research, it looks like the SQL Server 2008 R2 named instance is dropping the TCP/IP connections intermittently. This is an known issue [SQL Server 2008 R2 TCP timeout expired problem](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a86506a0-b3c4-4cbb-b904-702a7296374e/sql-server-2008-r2-tcp-timeout-expired-problem?forum=sqldataaccess). SQL Server Management Studio is connecting with Named Pipes, allowing me to run the stored procedure when the app is failing. I'm changing the ADO connection to use named pipes, and running through the QA cycle. – cclarke May 04 '15 at 17:55

1 Answers1

0

Updating the ADO.NET connection to use named pipes has resolved the issue for our application. Prefixing the database name with "np:" has the connection using named pipes.

connectionString="Data Source=np:myserver\myinstance;Initial Catalog=databaseName;Persist Security Info=True;User ID=xxxxx;Password=yyyyy;Connect Timeout=45"

The issue returned on 5/14. This query timeout posting gave us hints how to force SQL Management Studio to behave like the ADO.NET connection and allowed us to recognize this is a "parameter sniffing" issue. We have applied changes to disable the parameter sniffing within the stored procedure.

Community
  • 1
  • 1
cclarke
  • 86
  • 1
  • 7