Most of the time, we use wait stats to identify SQL Server's bottleneck and focus on what to tune.
However, let's say you've got a really well-tuned SQL Server where:
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 table are currently cached in memory.
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.
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 perfmon counters or CPU graphs.
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 10 minutes to go achieve that task - and then when they return, they'll sit patiently waiting for their next assignment.
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
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'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…
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: For a few minutes last night the app servers couldn't connect to SQL. Can you check if there was a SQL problem during that time?
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:
[crayon-6a7f82bf97bea321607816/]
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…
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 server is frozen or unresponsive.
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…
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.
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 should you do if it isn’t?
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:
[crayon-6a7f82bf98c80656178774/]
It returns a prioritized list of things that are wrong, broken, or dangerous about your SQL Server. Focus on things in priority 1 through 50 - these are the big, urgent issues that can get you fired, or can cause showstopper problems:
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.
It's this easy to get a cumulative recap of wait stats, file stats, and Perfmon counters:
In the above example, here's what the wait stats columns mean:
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 it I told the recipient, "This is great, but only read it from page X to Y."
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…
