Collecting and interpreting wait statistics for performance triage.
56 associated posts51 primary posts
This week, Brent, Richie, Erik, and Tara discuss speeding up performance, how to make SSRS highly available, what to do with SSIS packages when making upgrades, Windows Core, and how to hide data from DBAs when they have SA access.
Here’s the video on YouTube:
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:
Have you got a SQL Server that usually performs just fine, but every now and then, everything falls to pieces? Users complain about performance, and then minutes - or hours - later, the problem just mysteriously solves itself?
Here's a few things to look for:
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…
I recently came across a curious case where a SQL Server was suffering a number of long-running queries coming from an application written in Entity Framework. When I measured the average query execution times, I got some unexpected results:
CPU time: 12 milliseconds
Reads: 273
Elapsed time: 6800 milliseconds
When I ask you how fast your car is, there's a two common metrics for answers:
How fast it can go
How much horsepower or torque its engine can deliver
When Extended Events (XE) were released with SQL Server 2008, I was excited - something new to learn! I read Microsoft articles about them, read blog posts about them, and tried to use them. At that time, there was no GUI for XE. I had to create the sessions with T-SQL. That wasn't awful - I…
Recently we got an email asking for help from a DBA who was concerned about a high percentage of the PREEMPTIVE_DEBUG wait on a SQL Server. They were investigating poor performance and had searched the internet high and low and not found information on what this wait means. They were stumped. What to Do When You Find an Unusual…
I love free tools. I also love analyzing SQL Server's wait statistics. But I'm not a fan of Activity Monitor, a free tool in SQL Server Management studio, which helps you look at wait stats.
Activity Monitor just doesn't give you the whole truth.
I sometimes hear database administrators say, "This database must be involved in our performance problem: it has too many connections." Lots of connections might cause a problem for your SQL Server, or it might not. The good news is that there's a way to clearly tell if they're dragging down performance or not. Lots of…
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…
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. Our SQL Server Stand-In: a Class. Pretend that you're a schoolteacher in a classroom with a couple dozen students. You've got a class project that you need to…
Developers frequently make a big mistake: they tune out on discussions of storage performance and IO. Don't fall into this trap! Understanding how storage impacts SQL Server performance helps you tune the right parts of your code and focus on what really matters. In this 30 minute video, Microsoft Certified Master Kendra Little demonstrates why developers need to understand the IO impact on writes and reads in SQL Server. She'll show you how simple configuration changes can sometimes save hundreds of hours of developer time.
Here's what I've been reading lately on the SQL Server front. I'd like to thank all three authors and publishers for providing these books for review. Securing SQL Server by Denny Cherry Paperback - Kindle Denny's a jack of all trades, and a Microsoft Certified Master of SQL Server to boot. I've had the pleasure…
When my Grandpa Ozar wanted to find out if his 10 megabyte 5.25" hard drive was responding fast enough to handle his database loads, he monitored the Perfmon counter Physical Disk: Disk Queue Length. Things have changed since Grandpa's time, though, and now it's time to learn a new performance tuning trick - SQL Server waits.
