Wait Stats

Execution Plans, SQL Server 2017, Wait Stats

The revolution will not be documented At the PASS 2017, Pedro Lopes (don’t call him low-pez) from Microsoft mentioned that a new parallelism wait was getting added soon. That wait, if you’re too darn tootin’ lazy to click, is called CXCONSUMER. According to Pedro’s slide, but not the ENTIRELY MISSING DOCUMENTATION, this wait is the…

Network Issue or THREADPOOL waits?

January 3, 2018

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

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

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?

August 25, 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]

July 18, 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

April 22, 2017

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

dba, development, performance tuning, sql server

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

March 9, 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

March 3, 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

February 16, 2017

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

July 25, 2016

SQL Server, Wait Stats

No Comments

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

October 22, 2015

Jeremiah Peschka

Back in November of 2006, Microsoft released “SQL Server 2005 Waits and Queues” as a best practices article. Many people in the SQL Server community have referenced this article as a great place to get started with understanding waits in SQL Server. Heck, I’ve recommend it on a regular basis. The last time I recommended…

When Did My Azure SQL Database Server Restart?

August 13, 2015

Microsoft Azure, SQL Server, Wait Stats

In Azure SQL Database, no one can hear you scream query common system objects that tell you when a restart happened. You don’t get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases. The closest I’ve been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with…

“It’s Slow” Is Not A Metric

February 11, 2015

Jes Schultz Borland

When I start a SQL Server Critical Care with a client, the first thing I ask is, “What is your #1 pain point? Where does it hurt?” The answer I hear most often is, “The server is too slow”. I’m a runner. If I was able to run a 5K race at an 8:30 per…

T-SQL Tuesday: Stop Tuning with Wait Stats Percentages

March 11, 2014

SQL Server, Wait Stats

For this month’s T-SQL Tuesday topic, Michael J. Swart told us to argue against a popular opinion, and man, is that right up my alley. I’ve told you to stop worrying about index fragmentation, stop backing up your logs once an hour, and to stop running $14k of licensing on $3k of hardware. You’re probably checking…

What is the CXPACKET Wait Type, and How Do You Reduce It?

August 27, 2013

SQL Server, Wait Stats

109

When you query sys.dm_os_wait_stats, or check your waits with sp_Blitz® or sp_BlitzFirst®, one of your biggest wait types is probably CXPACKET. Let’s talk about what’s really going on. Here’s a 15-minute video explaining it, or you can keep scrolling down to read: Our SQL Server Stand-In: a Class. Who wants to help me scan the sales…

Overdriving Your Headlights

September 25, 2009

Monitoring, Wait Stats

The strength of your car's headlights, the speed of your reactions, and the power of your brakes determine how fast you can drive safely at night. Likewise, in SQL Server, we need to see down the road before disaster strikes, and react as quickly as possible.

The Ozar Family Tradition of Performance Monitoring

July 23, 2009