SQL Server Timeout troubleshooting

Question

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: Please note that you should not add WITH RECOMPILE unless its a last ditch thing. Using RECOMPILE FOR is much more targeted — Mitch Wheat
– Mitch Wheat, Commented Mar 5, 2009 at 0:16

Community · Accepted Answer · 2017-05-23 12:18:07Z

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?

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Mar 5, 2009 at 0:08

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Greg Dean · Accepted Answer · 2009-03-05 00:10:23Z

1

Rebuild any relevant indexes.

answered Mar 5, 2009 at 0:10

Greg Dean

30.3k15 gold badges70 silver badges79 bronze badges

Comments

asdvoin · Accepted Answer · 2009-03-05 00:11:24Z

1

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

answered Mar 5, 2009 at 0:11

asdvoin

Collectives™ on Stack Overflow

SQL Server Timeout troubleshooting

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related