January 14, 2014 at 1:10 pm
My boss came to me today about a slow running report. Profiling the report, I found the SPROC that was running slow. According to SQL Profiler, it was taking 10 minutes to complete. Upon its completion, the report rendered immediately.
I then went to SSMS, tore the SPROC apart SELECT by SELECT, ran each part, looked at the Execution Plans (which all looked good, Clustered Index Seeks, no Key lookups etc), I checked for Cardinality issues etc. I then ran the SPROC entirely via SSMS and it finishes in under 30 secs again the Execution Plan looks good.
The SPROC does not use any linked servers, is not querying our cloud for any information. The front end app is an internal app, so it "should" not being doing anything to crazy. I have should in quotes, because I am by no means a web developer.
I investigated the indexes, looked for any missing indexes via DMV's, checked the statistics on the tables involved and all looks good.
During both executions via both the front end web app and SSMS, I was looking at Activity Monitor, hoping to see a spike in CPU, Database I/O and blocking; but again, nothing.
Can anyone think of anything I am missing to check and or what could be causing the slowness via the front end web app?
Any and all suggestions/help will be greatly appreciated!!
January 14, 2014 at 1:15 pm
Have you investigated parameter sniffing? The symptoms you describe sound like that might be the issue.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2014 at 1:18 pm
Sean, I have not but, I will investigate it and reply back.
Thanks for the reply...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply