March 17, 2014 at 1:31 pm
I'm working on resolving performance issues with some of our databases. I've noticed a lot of red flags when running OS_WAIT_STATS; high wait time for LATCH_EX, CXPACKET, PAGEIOLATCH, etc. I'm trying to determine some good steps to resolve these issues and I have a good starting off point through my own knowledge and some books as well. However, I'm trying to determine the root cause of a lot of these problems and while I can start working on these issues as I have determined some problematic queries, I'm hoping to find some other options that will help me correct these issues. Is there a good article, program, resource, etc that someone can recommend? One specific issue is determining the reason for a high Buffer Latches and correlating it back to a set of queries or a server problem.
March 18, 2014 at 4:22 am
If you capture query metrics and the wait statistics from those queries at the same time, you can then correlate from wait statistics directly to queries. But, that's got to be in place first. If all you have is wait statistics and general query metrics (reads, writes, cpu and execution time), then what you have to do is determine what type of wait you're dealing with, page latch is pretty much I/O, and then look to the queries to see which ones have the most reads & writes. The correlation is less direct, but fairly close.
Here's a good article from Jonathan Kehayias to get you started. This document from Microsoft is still one of the best references on waits and queues out there even if it is from 2005. I talk about waits in my book on query tuning.
"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
March 18, 2014 at 8:03 am
Thanks Grant.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply