7

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!

Chris
  • 4,030
  • 4
  • 47
  • 76
  • @Chris: Please note that you should not add WITH RECOMPILE unless its a last ditch thing. Using RECOMPILE FOR is much more targeted – Mitch Wheat Mar 05 '09 at 00:16

3 Answers3

4

The symptoms you describe are 99.9% certain of being due to an incorrectly cached query plan.

Please see these answers:

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?

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1

Rebuild any relevant indexes.

Greg Dean
  • 29,221
  • 14
  • 67
  • 78
1

Update statistics, check set options on query in profiler. SSMS might be using different connection set options.