Diagnosing instance performance through waits, resources, monitoring, and baselines.
405 associated posts180 primary posts
This week's top-voted questions from https://pollgab.com/room/brento finds us opening ChatGPT multiple times! It's fun to show how I use it, and what it thinks of me.
Prefer listening to this via podcast? Good news! You can now subscribe to Office Hours on Spotify, Apple Podcasts, Podcast Index, Podcast Addict, Podchaser, and anywhere else fine podcasts are sold.
I flew into Wales a couple days early to get used to the time zones, and showed up at the conference center where SQLBits would be located. Let's go through your top-voted questions from https://pollgab.com/room/brento on a very chilly morning:
https://www.youtube.com/watch?v=6XjBwH7NzfA
When you've got questions about architecture, development, or best practices, post 'em at https://pollgab.com/room/brento, and upvote the ones you'd like to see me cover. It's like free bite-sized consulting.
https://www.youtube.com/watch?v=lCbPSYmCTHg
Let's hop right into it. I've got one lab server (named Colorful), so when I open the app, my estate overview isn't particularly interesting - yours will be:
Go into the server's details, and you see the top wait types over time:
When I travel, I try to take y'all with me to really pretty or at least interesting locations. On my Tokyo visit last year, I did Office Hours in Akihabara, the electronics & anime shopping hub. Even though I'm not into anime at all and I don't collect that kind of gear, I wanted to see it because it's famous among geeks.
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.
I drove the Graffiti Gulf out to The Last Spike (video) outside of Las Vegas and took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=XcNJVJ1-XaE
I threw in the towel on my workday at 3PM when everything started going wrong in the Ozar home office, so I switched to answering your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=v7-Bq9qmDX8
In last week's Query Exercise, I challenged you to play some code golf to generate big spills with tiny T-SQL.
Today, I'm going to walk you through my thought process - the initial attempts I tried and failed with, and the discoveries I made along the way, because I think it makes for fun storytelling.
My cruise ship took a detour to Honduras to avoid the hurricane. Let's hang out on the beach and go through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=aD7FnCRzJL8
Short story: what the title says.
This is especially problematic for folks who merge multiple databases onto the same server. All it takes is one badly-behaving application to leave its transactions open, and suddenly it causes the rest of the databases to run TempDB out of space. That app's transaction might not even be changing anything, and it might have never caused problems for that application before - but once it shares a TempDB with other apps, it causes cascading problems.
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
TempDB is one of the banes of my existence.
Anybody, anybody who can query your server can run a denial-of-service attack in a matter of seconds just by filling it up with a simple query:
Max Degrees of Parallelism (MAXDOP) tells the database engine, "If you decide to parallelize a query, go parallel with this many worker threads."
(It's a little more complex than that - there is also a coordinating thread, plus a single plan might have multiple parallel zones that each consume MAXDOP worker threads, but for the sake of this blog post, let's keep it simple.)
In between clients, I hopped onto my Twitch channel to take your top-voted questions from https://pollgab.com/room/brento. If you'd like to get notified whenever I do one of those live streams, you can follow my channel for free and you'll get email notifications automatically. I do 'em whenever I have time, usually about once a week when I'm at home.
SQL Server 2025 introduces a new sys.dm_os_memory_health_history view to make it easier for meatbags like you and robots like Copilot to know if the SQL Server has been under memory pressure recently.
To show how it works, let's run a bunch of simultaneous high-memory-grant queries in the Stack Overflow database:
On the last day of my Caribbean cruise, as we pull into port in Miami, let's go through your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/2tkrh7--2jU
The first few questions from this week's crop at https://pollgab.com/room/brento are very much about what-problem-are-you-trying-to-solve, but they get more complex from there.
https://www.youtube.com/watch?v=YWDUgd_8ZNo
Let's hang out, listen to the peaceful waterfall, and go through your top-voted questions from https://pollgab.com/room/brento. This is kinda the calm before the storm: in another week, I'll be on the road again for a while, hopefully bringing you some fresh Office Hours locations from the Caribbean and Alaska!
https://www.youtube.com/watch?v=hdBa7FKQMWI
I'm on a Med cruise, and I stopped the boat today in Corsica to make sure your questions from https://pollgab.com/room/brento got the answers they deserve. I started by mentioning that I'm using a 360 degree camera, but the file size on that video was so huge that I couldn't upload it from the cruise ship, so you just get the 180 version.
