Collecting and interpreting wait statistics for performance triage.
56 associated posts51 primary posts
Let's say you've got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn't balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let's write…
Say you've got PAGELATCH_UP or PAGELATCH_EX waits, and you're wondering what might be causing 'em.
I'm going to keep the brutally simple approach of building a stored procedure to simply dump hard-coded values into a table at high speed. I don't want to select from other tables since they might introduce some other types of waits, especially when I'm demoing concurrency quickly.
This week, Brent, Tara, and Richie discuss relaying SQL Server performance updates to upper management, differential backups in AlwaysOn Availability environment, replication, Troubleshooting THREADPOOL Waits, an acceptable amount of preemptive waits, Postgres, and the technologies Richie is working with these days.
Here’s the video on YouTube:
This week, Brent, Erik, Tara, and Richie are back in action again to and are discussing sys.dm_db usage stats, Availability Group troubleshooting, ASYNC Network IO issue, reasons for using a contained database, whether you need to add more CPUs if facing THREADPOOL (or is it Deadpool?) waits, auto-tiering tempdb, and query tuning.
Here’s the video on YouTube:
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…
You've been performance tuning queries and indexes for a few years, but lately, you've been running into problems you can't explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they're very hard to detect in a development environment. In a very fast-paced session, I'll…
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…
This week Brent, Erik, and Richie discuss whether you need to know T-SQL to be a Senior DBA, if you should use SQL Server 2017 for production, monitoring tools, C# vs Powershell, IO_COMPLETION waits, DBA jobs, and SQL Operations Studio.
Here’s the video on YouTube:
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-6a6ee2c5b98e2142867550/]
Your client or boss just said, "Find out why this SQL Server is slow." You've already done a free SQL Server health check, and now you wanna investigate deeper.
Step 1: Download & run sp_BlitzFirst.
Start with wait stats, which tell you what the SQL Server has been waiting on since the service was last started, or since someone cleared wait stats with DBCC SQLPERF, whichever is more recent.
StackExchange users often ask, "Why is my server slow?" Here's a few tips to get better, faster answers. Server information You may have already included server information in your question, but it may not be sufficient. Run sp_Blitz to output a lot of server information fast. [crayon-6a6ee2c5ba0a5427876195/] Click on the XML mumbo jumbo and post the…
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.
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-6a6ee2c5ba977382547487/]
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.
This week, Brent, Erik, Tara, and Richie discuss sp_Blitz capabilities, what's best to use for resolving SQL performance issues, CDC, tools for capturing wait stats, their favorite monitoring tools, temp tables vs table variables, partitioning with constraints, and whether you should get any certifications.
Here’s the video on YouTube:
This week, Brent and Richie discuss their hardware setups, lazy spools, SQL 2016 Service Pack 1, Always On Availability Groups, Database IDs, GroupBy.org, SQL on Linux, and new features they would like to see in the new version of SQL.
Here’s the video on YouTube:
I like science fiction Not enough to go to conventions, or memorize every line, but I have some opinions on things. Dune is probably my favorite series of stories in the genre. Yes, I think Paul would beat Luke in a fight. Sorry, Star Wars people. He's just kind of a wimp. Throw grenade, digress One…
