Wait Stats

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

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…
Read More

Wait Stats Should Be Easy By Now

Wait Stats
1 Comment
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…
Read More

SQL Server 2017 CU3 add a new CXCONSUMER wait type

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…
Read More

Network Issue or THREADPOOL waits?

Wait Stats
10 Comments
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:…
Read More

New Wait Stats and DMV Columns in SQL Server 2017

SQL Server 2017, Wait Stats
3 Comments
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…
Read More

What Are Poison Waits?

Wait Stats
5 Comments
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…
Read More

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

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…
Read More

In Review: SQL Server 2005 Waits and Queues

Wait Stats
7 Comments
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…
Read More

When Did My Azure SQL Database Server Restart?

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…
Read More

“It’s Slow” Is Not A Metric

Wait Stats
7 Comments
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…
Read More
Menu