Stored Procedure Timing out.. Drop, then Create and it's up again?

Question

I have a web-service that calls a stored procedure from a MS-SQL2005 DB. My Web-Service was timing out on a call to one of the stored procedures I have (this has been in production for a couple of months with no timeouts), so I tried running the query in Query Analyzer which also timed out. I decided to drop and recreate the stored procedure with no changes to the code and it started performing again..

Questions:

Would this typically be an error in the TSQL of my Stored Procedure?

-Or-

Has anyone seen this and found that it is caused by some problem with the compilation of the Stored Procedure?

Also, of course, any other insights on this are welcome as well.

Similar:

• http://stackoverflow.com/questions/1007397/sql-poor-stored-procedure-execution-plan-performance-parameter-sniffing
• http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server

Answer 1

Have you been updating your statistics on the database? This sounds like the original SP was using an out-of-date query plan. sp_recompile might have helped rather than dropping/recreating it.

Answer 2

I think the table the SP is trying to use is locked by some process. Use "exec sp_who" and "exec sp_lock" to find out what is going on to your tables.

Answer 3

If it was working quickly, is (with the passage of several months) no longer working quickly, and the code has not been changed, then it seems likely that the underlying data has changed.

• My first guess would be data growth--so much data has been added over the past few months (or past few hours, ya never know) that the query is now bogging down.
• Alternatively, as CodeByMoonlight implies, the data may have changed so much over time that the original query plan built for the procedure is no longer good (though this assumes that the query plan has not cleared and recompiled at all over a long period of time).
• Similarly Index/Database statistics may also be out of date. Do you have AutoUpdateSatistics on or off for the database?

Again, this might only help if nothing but the data has changed over time.

Answer 4

Parameter sniffing.

Answered a day or 3 ago: "strange SQL server report performance problem related with update statistics"

Answer 5

There are a few things you can do to fix/diagnose this.

1) Update your statistics on a regular/daily basis. SQL generates query plans (think optimizes) bases on your statistics. If they get "stale" your stored procedure might not perform as well as it used to. (especially as your database changes/grows)

2) Look a your stored procedure. Are you using temp tables? Do those temp tables have indexes on them? Most of the time you can find the culprit by looking at the stored procedure (or the tables it uses)

3) Analyze your procedure while it is "hanging" take a look at your query plan. Are there any missing indexes that would help keep your procedure's query plan from going nuts. (Look for things like table scans, and your other most expensive queries)

It is like finding a name in a phone book, sure reading every name is quick if your phone book only consists of 20 or 30 names. Try doing that with a million names, it is not so fast.

Answer 6

This happend to me after moving a few stored procs from development into production, It didn't happen right away, it happened after the production data grew over a couple months time. We had been using Functions to create columns. In some cases there were several function calls for each row. When the data grew so did the function call time.

The original approach was good in a testing environment but failed under a heavy load. Check if there are any Function calls in the proc.