Menu
I have a web service that's been running fine without modification for a couple of years now. Suddenly today it decides that it would not like to function, and throws a SQL timeout:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Interesting to note that this web service lives on the same server as the database, and also that if I pull the query out of a SQL trace and run it in management studio, it returns in under a second. But it's timing out after exactly 30 seconds when called from the web service without fail. I'm using the Enterprise Library to connect to the database, so I can't imagine that randomly started failing.
I'm not quite sure what could suddenly make it stop working. I've recycled the app pool it's in, and even restarted the SQL process that I saw it was using. Same behavior. Any way I can troubleshoot this?
UPDATE: Mitch nailed it. As soon as I added "WITH RECOMPILE" before the "AS" keyword in the sproc definition, it came back to life. Bravo!
766
If you encounter a connection-timeout error, follow the steps: Increase the connection-timeout parameter. If you use an application to connect to SQL Server, increase the relevant connection-timeout parameter values and check whether the connection eventually succeeds.
SQL Server will typically show you connection timeouts or operation (query) timeouts. These values are set by the client connecting to the SQL Server. An operation timeout occurs when a command takes too long to complete, and the client raises an error.
High load on tables that manage the content can result in timeout exceptions. Troubleshooting this will require reproducing the problem to capture information about where the database lock is raised which causes the query to timeout.
This type of timeout can have four causes: There's a deadlock somewhere with a SQL table on the database. The database's statistics and/or query plan cache are incorrect. The query is too complex and needs to be tuned.
The symptoms you describe are 99.9% certain of being due to an incorrectly cached query plan.
Please see these answers:
Big difference in execution time of stored proc between Managment Studio and TableAdapter
Rule of thumb on when to use WITH RECOMPILE option
which include the advice to rebuild indexes and ensure statistics are up to date as a starting point.
Do you have a regular index maintenance job scheduled and enabled?
The canonical reference is: Slow in the Application, Fast in SSMS?
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
