Back in May I kicked off a survey about prevalent latches on SQL Server instances across the world (see here). It's taken me quite a while to get around to collating and blogging about the results, but here they are at last! I got information back for almost 600 servers, and if you remember, I [...]
Paul S. Randal
In Recovery...
Most common latch classes and what they mean
Posted on: August 21, 2012 12:24 pm
Survey: most prevalent latch waits (code to run)
Posted on: May 10, 2012 2:26 am
I first started blogging about latches and some of the deeper parts of SQL Server internals last year (see Advanced performance troubleshooting: waits, latches, spinlocks) and now I'd like to pick up that thread (no scheduling pun intended :-)) and blog some more about some of the common latches that could be a performance bottleneck. [...]
Capturing wait stats for a single operation
Posted on: July 11, 2011 9:00 am
This is a performance tuning post that's been on my to-do list for quite a while. Wait stats analysis is a great way of looking at the symptoms of performance problems (see my Wait Stats category for more posts on this) but using the sys.dm_os_wait_stats DMV shows everything that's happening on a server. If you [...]
New whitepapers on latches and spinlocks published
Posted on: July 1, 2011 8:13 am
Over the last few months I've been blogging occasionally about some pretty deep performance tuning topics, namely latches and spinlocks (see my blog categories Wait Stats, Latches, and Spinlocks). Ewan Fairweather and Mike Ruthruff of the SQLCAT team have written some really excellent whitepapers on interpreting and dealing with latch and spinlock issues, which I [...]
MAXDOP configuration survey results
Posted on: June 23, 2011 3:50 am
A month ago I kicked off a survey about MAXDOP setting – see here for the survey. I received results for 700 servers around the world! Here they are: The X-axis format is X-Y-Z, where X = number of cores, Y = number of NUMA nodes, Z = MAXDOP setting. I didn't differentiate between [...]
Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)
Posted on: May 24, 2011 12:25 pm
It's been a long time since the last blog post on SSD benchmarking - I've been busy! I'm starting up my benchmarking activities again and hope to post more frequently. You can see the whole progression of benchmarking posts here. You can see my benchmarking hardware setup here, with the addition of the Fusion-io ioDrive Duo [...]
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock
Posted on: April 26, 2011 11:41 am
Continuing my series on advanced performance troubleshooting – see these two posts for the scripts I'll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I'd like to show you an example of SOS_SCHEDULER_YIELD waits occurring and [...]
Advanced SQL Server performance tuning
Posted on: April 13, 2011 7:32 am
It’s all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what’s going on with SQL Server than any of these tools can go. Or you have to call Customer Support or Premier Support so *they* can dive in deeper. Typically you or they are going to [...]
Survey: have you ever used these DMVs?
Posted on: March 24, 2011 8:57 am
In this week's survey I've got four mini-surveys for you, all to do with in-depth performance analysis. I'd like to know whether you've ever used each of four DMVs that look progressively more deeply into the workings of the database engine. I'll report on the results in a week or two and start blogging about [...]
Wait statistics, or please tell me where it hurts
Posted on: December 9, 2010 3:18 pm
How many times have you walked up to a SQL Server that has a performance problem and wondered where to start looking? One of the most under-utilized performance troubleshooting methodologies in the SQL Server world is one called “waits and queues” (also known simply as “wait stats”). The basic premise is that SQL Server is permanently [...]
Survey: what is the highest wait on your system?
Posted on: October 25, 2010 9:21 am
I've recently been creating some content about wait stats analysis and I think it would be really interesting to see what kind of waits people are seeing out there in the wild. Hopefully it'll also introduce a bunch of people to the waits-and-queues performance troubleshooting methodology and how it can be really useful to them. [...]
