Wait Stats

It seems obvious: add some memory, and SQL Server should be able to cache data, thereby making queries run faster … right? Well, if you can’t cache everything you need for a query, you might be surprised to find that SQL Server may still read the entire table up from disk, regardless of what parts of the…

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

Last Updated April 9, 2022

Locking, Blocking, and Isolation Levels, Wait Stats

You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it. It might be overzealous index rebuilds. Let’s demo why by starting a new query in the Stack…

Free Webcast Today: How to Measure Your SQL Server

Last Updated April 28, 2020

If you get into a car, how do you know if the car is fast or not? You hold down the gas pedal, and you time how long it takes before you’re breakin’ the law. Now what about SQL Server: how do you know if yours is fast…or a clunker? Database performance tuners need to…

DBA Training Plan 15: What’s Your SQL Server Waiting On?

Last Updated August 30, 2019

DBA Training Plan, Wait Stats

You’re here, dear reader, because you weren’t “classically trained” as a database administrator. You didn’t graduate from the University of Sciencing Computerses with a Master’s of Transactional Processing. You probably got your start as a developer or sysadmin, and gradually fumbled your way here. You’re used to monitoring stuff from the OUTSIDE using things like…

Your SQL Server is Bored: What Low Wait Times Mean

Last Updated July 10, 2019

Let’s say you have an assistant. (I know, unlikely, but bear with me.) And say you give your assistant a task – hey, go fetch me a coffee. Your assistant would nod obediently, go head out to the neighborhood coffee shop, get your preferred Americano, and bring it back to you. It might take them…

Wait Stats Should Be Easy By Now

Last Updated July 20, 2018

Why Is My Query… We’ve all started a question with a close approximation of those words. No matter how you finish that sentence, there’s some basic information that you need to collect to figure it out, like: Query plan Wait stats Other server activity Those are a good place to start. It’s easy enough to…

Building SQL ConstantCare®: Why We Avoided Graphs

Last Updated April 23, 2018

SQL ConstantCare, Wait Stats

When we started building SQL ConstantCare®, one of my early decisions was to focus on a text-based interface. I wanted to just simply tell you what I’d do in your shoes. If users wanted to see more evidence behind the recommendations, I’d show it – but otherwise, I’d hold off and let them ask. The experience has…

SQL Server 2017 CU3 add a new CXCONSUMER wait type

Last Updated August 9, 2020

Execution Plans, SQL Server 2017, Wait Stats

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…

Network Issue or THREADPOOL waits?

Last Updated December 23, 2017

Tara Kizer

I’ve had a handful of clients that were experiencing significant THREADPOOL waits. This isn’t a wait type that I had any knowledge of prior to joining Brent Ozar Unlimited. Looking back, I am pretty sure that a few servers that I’ve been responsible for over the years were experiencing it based on the symptoms. WebOps:…

How to Log Wait Stats to Table with sp_BlitzFirst

Last Updated October 13, 2017

First Responder Kit, Wait Stats

When you’re analyzing SQL Server performance, one of the best places to start is wait stats. What’s your SQL Server waiting on? sp_BlitzFirst makes it really easy to trend this data over time for free with all currently supported versions of SQL Server (2008+). Just set up an Agent job to run this every 15 minutes:…

New Wait Stats and DMV Columns in SQL Server 2017

Last Updated October 2, 2017

SQL Server 2017, Wait Stats

SQL Server 2017 is officially out, and here’s what I’ve found so far. New Wait Stats in 2017 BACKUP_INMEM_DIFFLIST_READ_ACCESS BACKUP_INMEM_DIFFLIST_WRITE_ACCESS BLOB_CONTAINER_TABLE COLUMNSTORE_MIGRATION_BACKGROUND_TASK EXTERNAL_SCRIPT_NETWORK_IO FOREIGN_REDO GHOSTCLEANUP_UPDATE_STATS GLOBAL_QUERY_CANCEL GLOBAL_QUERY_CONSUMER GLOBAL_QUERY_PRODUCER HADR_THROTTLE_LOG_RATE_LOG_SIZE HADR_THROTTLE_LOG_RATE_SEEDING HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE MEMORY_GRANT_UPDATE MIGRATIONBUFFER PREEMPTIVE_OS_BCRYPTIMPORTKEY PREEMPTIVE_OS_NCRYPTIMPORTKEY PREEMPTIVE_XE_PROXY_ADDSESSION PREEMPTIVE_XE_PROXY_PROCESSBUFFER PREEMPTIVE_XE_PROXY_REMOVESESSION PREEMPTIVE_XE_PROXY_SESSIONCOMMIT PWAIT_AUDIT_CLOSE_EXPIRED_LOGS_MGR_RWLOCK PWAIT_AUDIT_SESSIONS_LOCK PWAIT_EXTERNAL_SCRIPT_LIBMGMT_DIR_PERMS PWAIT_PREEMPTIVE_APP_USAGE_TIMER PWAIT_PREEMPTIVE_XE_STUB_LISTENER PWAIT_SBS_FILE_OPERATION QDS_HOST_INIT RBIO_WAIT_VLF REMOTE_BLOCK_IO RESTORE_MSDA_THREAD_BARRIER SBS_DISPATCH SBS_RECEIVE_TRANSPORT SBS_TRANSPORT SECURITY_CNG_PROVIDER_MUTEX SLEEP_SETUP SNI_WRITE_ASYNC…

What Are Poison Waits?

Last Updated August 19, 2017

Most of the time, SQL Server performance tuning starts with your top wait stats. Run sp_BlitzFirst @SinceStartup = 1, look at your top couple of wait types, and that’s where to focus your efforts. However, even small amounts of certain wait types can indicate big problems. When these waits strike, it can feel like the…

SQL 2016 SP1 Shows You Wait Stats in Execution Plans. Or Does It? [UPDATED]

Last Updated October 5, 2017

Execution Plans, Wait Stats

SQL Server 2016 Service Pack 1 brought us performance tuners all kinds of shiny goodies – like the ability to see wait stats inside a query’s actual execution plan. I was really excited when this first came out, but I keep seeing some real oddities. Let’s keep this one really simple: I’m using the Stack…

SQL Server 2017: query_store_wait_stats

Last Updated May 12, 2017

Query Store (QDS), SQL Server 2017, Wait Stats

The missing link Back when the Query Store first got announced, I was amazed. The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool. I was always surprised that wait stats weren’t a part of that, especially…

New White Paper: SQL Server Performance Tuning in Google Compute Engine

Last Updated March 17, 2017

Google Compute Engine, Hardware, Wait Stats

You’re a database administrator, Windows admin, or developer. You might even be a marmot. You’re building your first SQL Servers in Google Compute Engine, and you’re stuck at the create instance screen. How many CPUs should you use? How much memory? How are you supposed to configure storage? Will it be fast enough, and what…

4 Free Things to Do with a Slow SQL Server

Last Updated February 28, 2017

Processes and Practices, Wait Stats

Step 1. Run sp_Blitz and look for urgent warnings. sp_Blitz is one of the free scripts in our First Responder Kit. Just open the sp_Blitz.sql, run that to create the stored procedure, and type: Transact-SQL sp_Blitz 1 sp_Blitz It returns a prioritized list of things that are wrong, broken, or dangerous about your SQL Server. Focus on…

When THREADPOOL Waits Lead To RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Memory Grants, Wait Stats

Your server is underpowered That’s an understatement. Your server sucks. It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it. Alright, so maybe it’s not that bad, but it’s bad enough that you run into…

sp_BlitzFirst @SinceStartup = 1 Shows You Wait Stats Since, Uh, Startup

Last Updated February 13, 2017

SQL Server, Wait Stats

It’s this easy to get a cumulative recap of wait stats, file stats, and Perfmon counters: sp_BlitzFirst @SinceStartup = 1 In the above example, here’s what the wait stats columns mean: Sample Ended – when it ran Hours Sample – the number of hours your SQL Server instance has been up wait_type – the name of…

In Review: SQL Server 2005 Waits and Queues

Last Updated February 13, 2017

Jeremiah Peschka