December 10, 2009 at 2:51 am
I have a SQL 2008 stored procedure to generate a report. It does complex multi-table aggregation over more than 20 million records. The procedure and the indexes it uses have been tuned so it just takes a couple of seconds to run. The procedure returns a small result set (less that 100 rows).
The procedure gets called from an ASP.net application running on on IIS7.
At certain times of day, the procedure takes over 30 seconds to run from the IIS application (so the app displays a timeout error to the user). This slow performance lasts for several hours. However, during this time, the procedure still runs fast when called from SQL Studio.
My question is: Why would a stored procedure take a massively different amount of time to execute, depending on where it's called from?
Thanks in advance
Stuart
December 10, 2009 at 4:11 am
More than likely this is a parameter sniffing issue.
Try this link
December 10, 2009 at 10:08 am
Dave, thanks for the info about parameter sniffing, I hadn't considered that. But I don't think that's the cause. In my test above, I was careful to use exactly the same parameters.
December 11, 2009 at 6:52 am
Sounds like the problem is more likely IIS server performance than SQL Server performance.
I'd also recommend checking the PerfMon counters under SQL Server:Locks object like Lock Waits/Sec, Lock Requests/Sec, and Lock Wait Time to see if you are having more locking and blocking during that time. If not you could likely eliminate the SQL Server as the problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply