What if we invited you to spend a week in a Dell Resource Center, experimenting with SQL Server performance on almost unlimited hardware resources like 40-core processors, up to a terabyte of RAM and all solid-state storage? You’d come out of that a pretty well-rounded DBA, wouldn’t you?
That’s what our Dell DBA Days were for. We invited the Brent Ozar Unlimited team of DBA rock stars into our Resource Center lab. They built a data center and pushed the hardware, storage gear and Microsoft SQL Server as fast and furiously as they could, then created webcasts for you around the lessons they learned. The result was a string of chart-topping, live, fun, zany and highly informative webinars for Quest DBA Days:
From those, we asked Brent to put together a webcast called Quest DBA Days: The Brent Ozar Unlimited Show Greatest Hits. (Title tells all.)
We did it to save you time. After all, if you were a Clash fan, you might carve out 100 minutes to listen to “The Story of the Clash, Vol. 1” but not an entire Saturday to listen to everything from “London Calling” to “Sound System.” So, to whet your appetite for the webcast (and save you even more time), I’ll summarize the greatest hits of Brent’s Greatest Hits in this and my next blog post.
How much easier could I make it for you? I do this because I care.
In all of the webcasts, Brent and his team tried different ways of using wait stats to remove bottlenecks and find out how to maximize SQL Server database performance. The point of departure is to look at how queries are processed in SQL Server, starting with a simple SELECT statement.
Brent breaks down SQL Server infrastructure by the four main resources that queries affect: network (where users are), memory, CPU and storage (or persistence layer), as shown in this diagram:
Any bottleneck occurs somewhere among these. To understand why, Brent explains that there are three ways in which SQL Server schedules CPU:
- What’s running now
An ordinary SELECT statement lands on the CPU. SQL Server then tries to pull the results out of memory. If it needs to go to storage, it pulls parts of the table through memory, figuring out which rows you need and performing SORTs and JOINs. If it can’t cache all the results in RAM, it uses TempDB, a scratch space in storage, to hold them. When all of the query result set is ready, then SQL Server pushes it over the network to users.
DUI operations (deletes, updates and inserts) are different. SQL Server has to pull into memory not only the data being queried but also related data that enforces foreign key integrity, checks constraints, triggers other tables, grabs index pages and locks the rows in use so they cannot be changed when the query is processing. All of that involves looking through memory, fetching from disk and writing changes to the log file.
- What’s waiting (queue)
The result is a lot of back and forth (and red arrows). With only one query, there’s no waiting for resources, but with multiple simultaneous queries, operations can spend a lot of time waiting for network, CPU, memory and storage. To reduce the delay, SQL Server uses wait stats to keep track of which queries are waiting and what they’re waiting for. As soon as one query needs to wait on something, it goes to the back of the line so that the next query in line can run.
The busier your SQL Server is, the more waits you have piled up in any given second on the clock.
There’s also a concept of runnable operations, between running and waiting. Say that Query A has everything it needs in memory, but Query B is using CPU right now. Query A is runnable, but it needs more CPU power, so it’s waiting for Query B to get off of a CPU scheduler.
With those three concepts in place, Brent talks about the old ways of tuning SQL Server performance. You can measure all the choke points like CPU percentage busy, average disk queue length, page life expectancy and memory usage. You can compare your metrics against Microsoft’s guidelines. You can improve anything that seems to be running slowly.
But the new way is to ask SQL Server, “What are you waiting on when you run queries?” If it’s CPU, then you need to figure out which queries are using the most cycles and tune them. If it’s for results to return from storage, then it’s time to either increase memory, upgrade to faster storage or tune queries and indexes so that they don’t need to pull so much data from disk.
So, how do you figure out what your SQL Server has been waiting on and for how long? Tune in again for my next post to find out. (Hint: Free tool coming.)
Meanwhile, listen to our webcast, Quest DBA Days: The Brent Ozar Unlimited Show Greatest Hits. Brent designed it for database administrators who need to get up to speed fast on recent updates, but don't have the time or the resources to lab test out everything with their own servers.