Collecting and interpreting wait statistics for performance triage.
56 associated posts51 primary posts
The Dotonbori area of Osaka is like a video game version of Japan: wild, over-the-top neon signs, awesome street food, cool street markets, Kabuki theater on a revolving stage, and more. It's very touristy, but as touristy places go, it's fantastic. Last year when I was here, I filmed Office Hours at the river, but this time around let's go into one of the side streets where the food vendors gather.
Let's hang out in the backyard - as recently seen on Zillow Gone Wild - and take your top-voted questions from https://PollGab.com/room/brento.
https://www.youtube.com/watch?v=iKd30ILb7ys
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:
Meet my latest toy, a 1991 Honda Beat! I introduce it, then take your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=gkeEKyzc01M
I've already explained that no, it doesn't make your database server more reliable - and in fact, it's the exact opposite. But what about performance? The answer is going to depend on your hardware and workload, but let's work through an example. I'll take the first lab workload from the Mastering Server Tuning class and…
When I went through the top-voted questions at https://pollgab.com/room/brento, I didn't have all the answers today:
https://www.youtube.com/watch?v=g6fqxTPnDd0
I went through your top-voted questions from https://pollgab.com/room/brento before heading out to PGConf.dev in Vancouver.
https://youtu.be/sd6yO-nHDx0
Most of the https://pollgab.com/room/brento questions from this episode had really short and sweet answers. Let's take a break for the holidays and knock 'em out:
https://youtu.be/nhB-dT8_VTg
Thankfully, we didn't hit any icebergs or glacier droppings, so I put my comfiest coat on to answer your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=ZIUbZvtPsrc
When your SQL Server's top wait stat is ASYNC_NETWORK_IO, that indicates SQL Server is waiting on the app to ask for the next part of the result set.
This is a real pain in the rear to troubleshoot because often these waits happen for really short duration each time - think milliseconds, not seconds. It's an issue of death by a thousand cuts.
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.
Some questions that come in at https://pollgab.com/room/brento have such straightforward answers that I don't need to cover them on video. It's time for a rapid-fire speed round:
Q: Uncle Buck: Is there a good way to tell if a given include column for a NC index is being used at all?
On a very chilly morning, I went through your highly-upvoted questions from here and talked through 'em:
https://www.youtube.com/watch?v=lpklTAJdrBE
I took a break from playing with glacier leftovers at Diamond Beach to answer the questions you upvoted at https://pollgab.com/room/brento/.
https://www.youtube.com/watch?v=K4l522V4h08
Erika and I are on the road in Iceland, roaming around the countryside, and I'm taking you with us. This session was shot in Siglufjörður, a beautiful little oceanfront fishing village in the north.
Yes, I'm on the airport runway. No, seriously. Here's what it looks like from the air in the winter.
Page Life Expectancy is a Perfmon counter that's supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead.
Paul Randal has blogged this a few times, and rather than rehash 'em, I'd rather point you to a couple of his roundups:
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.
This week, Brent, Erik, and Richie discuss Microsoft cumulative updates, AlwaysOn Encrypted, query tuning, poison waits, the DBA career, CXPACKET waits, THREADPOOL issues, reporting services, and more.
Here’s the video on YouTube:
Deus Redux
A while back I wrote about the Perils of VSS Snaps.
After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I'd have to look out for.
