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. I was always surprised that wait stats weren’t a part of that, especially…
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…
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: sp_Blitz It returns a prioritized list of things that are wrong, broken, or dangerous about your SQL Server. Focus on things in priority…
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…
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: Sample Ended – when it ran Hours Sample – the number of hours your SQL Server instance has been up wait_type – the name of the wait stat Wait…
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…
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…
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…
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. Here’s a 15-minute video explaining it, or you can keep scrolling down to read: Our SQL Server Stand-In: a Class. Pretend that you’re a schoolteacher in a classroom…
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.
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.
