August 3, 2010 at 8:58 am
I've been having problems with a few stored procedures that are in applications I maintain. Basically they are getting a Count(x) from a table with some parameters with ranges from 20,000 to over a million records, but returning 1 line or at the most 5 or 10.
These stored procedures run in about 5-10 seconds in Management Studio, maybe 15 or 20 seconds on the longer ones, whether I am running the query or the stored procedure. And the first time I call them through code, or through Reporting Services, they work fine. But after a while, they will "lock up" to the point where execution never finishes from a remote call. They still work fine from Management Studio, but the code times out, or the report seems to generate forever.
As a temporary fix, it turns out that dropping and re-creating the stored procedure will clear the problem, but it sometimes comes back.
Has anybody run into this? What can I do with these stored procedures?
August 3, 2010 at 9:07 am
Sounds like the execution plan is getting stale, or the way you are sending parameters is not consistent from the app. This probably needs some research or tuning, but a band-aid that might help is to create the SP using WITH RECOMPILE. This will recompile the SP each time it runs. This is why dropping and recreating the proc is helping; you are recompiling the proc each time.
August 3, 2010 at 11:42 am
You need to determine what "locked up" means. It could be that the procedure recompiled with a bad plan and the queries are running slow, or it could be contention. Or heck, it could be something else. I'd suggest a few steps. First, to determine if it is statistics going out of date or parameter sniffing, I'd suggest you get the execution plan for when the query is running slow and compare it to the execution plan for when the query is running fast. If they're different, you're probably looking at something along the lines of statistics, bad plans, recompiles, parameter sniffing, that sort of thing. If they're the same, you need to look at other stuff. Check sys.dm_exec_requests and see if you have blocked processes the next time the query is running slow. That will help you identify if it's contention. You can also see exactly what the query is waiting on using sys.dm_exec_requests if it's not blocked. Again, useful information to determine the root cause of the problem.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply