January 19, 2012 at 12:08 pm
Hi all,
I could use some advice on a few poorly performing queries.
Unfortunately they are Microsoft SharePoint stored procedures so I can't change the queries themselves.
However, based on the performance stats I'm not sure the queries are the issue.
The same query can have wildly different "Reads" and "Duration" according to SQL profiler.
Sometimes a query might take 1 second to run and display 4000 reads.
Other times the exact same query will take 6 seconds and show 50k reads.
Can anyone explain this erratic behavior?
Could it be a resource issue on the server as opposed to a problem with the queries?
Thanks in advance,
Ben
January 19, 2012 at 12:40 pm
Without more information I'm just guessing. It could be that different parameters for these queries results in simply different result sets. If it's accessing more data (the increased number of pages) then it's more subject to contention for resources, blocking, etc. Check for blocks, validate the information being moved by the fast & slow versions of the query. Or, assuming identical parameters, you might be seeing bad parameter sniffing. Take a look at the execution plans for hte fast & slow version. If they're different and you can use the compile time vs. runtime values of the parameters and the data underlying those parameters to identify data skew, it could be bad parameter sniffing.
Either way, with it being sharepoint, there's little you can do about it. Make sure you've got the latest install in place. Validate with Microsoft that the queries don't have an index published (MS sometimes puts useful indexes up on their web site). Make sure you have good index and statistic maintenance in place and that they're running successfully.
After that... if you've identified a clear bottleneck your choices are simple. Create your own indexes and/or put some plan guides in place to provide hinting OR contact Microsoft support. Maybe do both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2012 at 3:27 pm
Many thanks for taking the time to reply.
I am - as it happens - currently reading your book on performance tuning as I was looking for some inspiration :-).
I think I will raise this with Microsoft, and pursue a plan to add more memory to the SQL server in question.
Ben
January 19, 2012 at 6:39 pm
Good luck. The current version of the book doesn't emphasize wait stats nearly enough, so keep an eye on those. I hope the book proves helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply