In Recovery...

Azure Data Studio, which used to be called SQL Operations Studio, has a bunch of extensions available, including one called Server Reports from Microsoft. Last year they took my wait stats query (from here) and made it into a report as part of Server Reports extension. In this quick post I’ll show you how to install […]

I’m a little bit overdue to blog about this new wait type, but I wanted to wait until SQL Server 2016 SP2 was released for a bit and people started seeing this new wait type. History Back in September 2016 I created a Connect item (3102145) to split the CXPACKET wait into benign and actionable […]

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101. Wait statistics analysis is one of my […]

A few months ago while I was teaching wait statistics, I was asked whether there’s any expected differences with waits stats when SQL Server is running in a virtual machine. My answer was yes – there’s a possibility of seeing longer wait times if something prevents the VM from running, as the wait times are […]

A few years ago I realized that there was a huge gap in knowledge in the SQL Server community – what do all the various wait types mean? – so I started a labor-of-love project to document all wait types and latch classes that have existed from SQL Server 2005 onward. In May 2016, I released […]

In Monday’s newsletter I discussed an email question I’d been sent and promised to blog about it. The question was “how can latch waits for ACCESS_METHODS_DATASET_PARENT occur when the instance MAXDOP is set to 1?” This shouldn’t happen, as that latch is only used for coordination between threads performing a parallel table/index scan. However, just […]

Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all. It happens when a thread is […]

This is a quick post to blog a script that allows spinlock statistics to be captured for a defined period of time (as I need to reference it in my next post). Enjoy! Example output (trimmed to fit here):

It’s finally ready! For the last two years, I’ve been working on-and-off on a new community resource. It was postponed during 2015 while I mentored 50+ people, but this year I’ve had a bunch of time to work on it. I present to the community a comprehensive library of all wait types and latch classes that […]

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I’ve updated my waiting tasks script to pull in the dop field from sys.dm_exec_query_memory_grants. I’ve also added […]

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] 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 […]

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) 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 […]

Note: the latest version of this script, with additions is here. 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 […]

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

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] (Script last updated June 13, 2018) 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 […]

(Check out my Pluralsight online training course: SQL Server: Detecting and Correcting Database Corruption.) 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 […]

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

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] 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 […]

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) 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 […]

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] 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 […]

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] 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 […]

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) 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 […]

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

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) 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 […]

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

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

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

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

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

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

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) 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 […]

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

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

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) [Last updated: February 13, 2019] 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 […]

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