Using query history, cached plans, and runtime data to investigate performance.
200 associated posts60 primary posts
Iceland has some pretty famous waterfalls, from Þórufoss and Skógafoss in Game of Thrones, to Háifoss in Stranger Things, to Dettifoss in Prometheus, and more. However, probably the most-photographed one is Seljalandsfoss, partially because of its easy access from the Ring Road, and partially because of, uh, Justin Bieber's video for I'll Show You. That video is also a pretty good intro to the greatest hits of Iceland's south coast.
In this live stream recording, I'll show you how to use the latest features of sp_BlitzCache to rapidly improve performance on an existing server. I'll show you how to find your server's top bottleneck, find the queries causing that bottleneck, and then demonstrate how to get advice from AI on how to tune them in seconds.
I love me some documentation.
For years, I've pointed folks to the SQL Server 2008 documentation on Forced Parameterization, a really useful tool for reducing plan cache bloat, getting more accurate reusable query plans, and enabling SQL Server 2019 and 2022's Intelligent Query Plan features, many of which rely on the same query text coming in repeatedly over time in order to tune it. That documentation was pretty sparse, though.
In theory, SQL Server performance monitoring is pretty simple:
Review the server's top wait types
Find the queries causing those wait types
Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.)
When you turn on last actual plans in SQL Server 2019 and newer:
[crayon-6a6eb81190b70399630646/]
Let's hang out in my home office in Vegas and go through your top-voted questions from https://pollgab.com/room/brento. The audio's a little echo-y in this one because I just moved my office down to a small first floor guest bedroom with hardwood floors instead of carpet, and I haven't treated the room for noise yet.
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:
It's an overcast afternoon at Atlantis Paradise Island in the Bahamas, so since I can't go into the water, might as well go through your top-voted questions from https://pollgab.com/room/brento.
https://youtu.be/1c0K-WJ-itk
Normally, when SQL Server updates statistics on an object, it invalidates the cached plans that rely on that statistic as well. That's why you'll see recompiles happen after stats updates: SQL Server knows the stats have changed, so it's a good time to build new execution plans based on the changes in the data.
However, updates to system-created stats don't necessarily cause plan recompiles.
Kyoto feels like a timeless, classic version of historical Japan with quiet tree-lined streets, giant temples, and bubbling brooks. It's the exact opposite of last week's experience in noisy Osaka! Last year, I filmed in Kyoto outside a temple, and this year, I'm at another temple just after New Year's, the time when Japanese folks traditionally go to visit their many gorgeous temples.
When there's a performance emergency, a lot of us perform the same manual tasks over and over. We run sp_BlitzWho or sp_WhoIsActive, look for blocking, long-running or out-of-control queries, and kill them. I'm going to build a new sp_kill for the First Responder Kit to make that easier, faster, and safer, and I want your…
First off, I understand if you read the headline and you have a knee-jerk reaction. I totally understand that there are a lot of people out there who hate AI, and believe me, I hate most uses of it. Lots of its advice can be misleading at best, and absolute garbage at worst. However, if…
In retrospect, I should have put on sunscreen - even for just a 20-minute Office Hours session on the beach! Dang, that sun is powerful. Let's go through your top-voted questions from https://pollgab.com/room/brento. This is a 360-degree video, so if you watch it on YouTube, you'll be able to pan the camera around and take a little virtual vacation with me.
When I'm tuning queries, the normal answer is to make the query perform better - either via changing the T-SQL, adding hints, or adding indexes so that the data's better prepared for the query.
However, sometimes when I'm looking at the output of sp_BlitzCache, I scroll across to the Average Rows column and double-check that the query's actually returning a reasonable number of rows out in the wild.
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.
About three years ago, SQL Server 2022 introduced Parameter-Sensitive Plan Optimization (PSPO). At the time, I explained that it didn't work particularly well, and went so far as to pronounce PSPO in a rather unflattering way. I wouldn't suggest that anyone turn it off - it's fine, just fine - but it isn't powerful enough, and poses serious challenges for monitoring and plan cache analysis.
For the last Office Hours of the year, I'm coming to you from Tokyo, the last stop on my 3-week tour of China and Japan. This was a big Epic Life Quest task for me, for sure, something I've wanted to do for decades. Let's bring you along and go through your top-voted questions from https://pollgab.com/room/brento:
I went to LA for the opening week of Somni, a gorgeous restaurant in LA, and took your questions from the rooftop deck of the adorable Petit Ermitage hotel.
https://www.youtube.com/watch?v=0MtdQkAw9a8
I took the Graffiti Gulf 356 out to the Valley of Fire State Park to exercise it, and took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=cr3s9nSagW8
I'm back in the home office to take your database questions from https://pollgab.com/room/brento.
https://youtu.be/H70Iy6bX970?si=6UZJbG89FC6bVni4&t=15
