Low priority locking wait types

SQL Server 2014 (and Azure SQL Database V12) added some cool new functionality for online index operations to allow you to prevent long-term blocking because of the two blocking locks that online index operations require. At the start of any online index operation, it acquires a S (share) table lock. This lock will be blocked until […]

Identifying queries with SOS_SCHEDULER_YIELD waits

One of the problems with the SOS_SCHEDULER_YIELD wait type is that it’s not really a wait type. When this wait type occurs, it’s because a thread exhausted its 4ms scheduling quantum and voluntarily yielded the CPU, going directly to the bottom of the Runnable Queue for the scheduler, bypassing the Waiter List. A wait has […]

Updated sys.dm_os_waiting_tasks script

Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that ‘text’ on one line does not have delimiters because that messes up the code formatting plugin):

Capturing IO latencies for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I/O latencies that occurred over a period of time. The script does the […]

Capturing wait statistics for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The first one on the list is a simple script to allow you to capture all the waits that occurred over a period of time. The script does the following: Creates two temporary […]

Finding a table name from a page ID

This is a question that I was sent over email that I thought would make a good post, and I’m waiting to do a user group presentation in Australia at 2am, so this will keep me busy :-) Imagine you come to work in the morning and notice that some new rows have been entered into […]

Updated wait stats script for performance and 2014

Very short blog post to let you all know that I’ve updated my wait stats script so that it works on 2014 and also now runs very fast. If you’re using my script, please replace it with the new one. Check it out on the original post: Wait statistics, or please tell me where it hurts. […]

Most common wait stats over 24 hours and changes since 2010

Back in February I kicked off a survey asking you to run code that created a 24-hour snapshot of the most prevalent wait statistics. It’s taken me a few months to provide detailed feedback to everyone who responded and to correlate all the information together. Thanks to everyone who responded! I did this survey because I […]

Causes of IO_COMPLETION and WRITE_COMPLETION SQL Server wait types

In many of the sets of wait statistics I’ve been analyzing, the IO_COMPLETION and WRITE_COMPLETION waits show up (but never as the most prevalent wait type). The official definition of these wait types are: IO_COMPLETION: Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear […]

A cause of high-duration ASYNC_IO_COMPLETION waits

In some of the wait statistics data I’ve been analyzing, some servers have been showing very long ASYNC_IO_COMPLETION waits, which I had a hunch about but wanted proof. The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task is waiting for I/Os to finish.’ Very helpful – NOT! Using the code I blogged yesterday (How […]

How to determine what causes a particular wait type

Wait statistics, as you know, are one of my favorite things to do with SQL Server, along with corruption, the transaction log, and Kimberly (but not necessarily in that order :-) One of the things that really frustrates me about wait statistics is that there is hardly any documentation about what the various wait types […]

What is the most worrying wait type?

Two weeks ago I kicked off a survey that presented a scenario and asked you to vote for the wait type you’d be most worried to see after a code roll-out to production. Here are the results: A very interesting spread of responses – thank you to all who thought about it and responded. Remember, […]

Send me your wait stats and get my advice and 30 days of free Pluralsight in return

[Edit: 3/25/14 No more codes left – thanks for all the data! – please don’t send any more.] Yes, you read that correctly. Call me crazy, but I’ve been wanting to do this for a while. Here’s the deal: You run the code from this post that creates a 24-hour snapshot of the wait stats […]

Most common latch classes and what they mean

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 […]

Survey: most prevalent latch waits (code to run)

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

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

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

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)

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

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

[Last updated: January 5, 2015] 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 […]

How to download a sqlservr.pdb symbol file

I just had to figure out how to do this so I figured a quick blog post is in order to save other people time in future. If you ever need to use windbg to debug a SQL Server crash dump, or you want to capture call stacks using extended events (e.g. when debugging excessive […]

Survey: have you ever used these DMVs?

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

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?

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. […]