Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 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.
You've heard that page splits are bad, and they're an indication that your table design is making your storage work too hard. You've heard that the right answer to fix it is adjusting fill factor lower, or doing regular index maintenance.
Before you watch the below animation, you'll wanna get up to speed with how index seeks work. Then, let's explain page splits with an animation:
In my free How to Think Like the Engine class, I gloss over the entire concept of B-trees and just hand-wavily say "SQL Server can seek to the page that has the row you're looking for." That's always kinda bothered me, so I finally gave in to temptation and whipped up an animated example of a clustered index on the Users table:
I was in Rome to attend a teaching conference, and my AirBnB had the most beautiful view overlooking Rome and Vatican City. While having my morning coffee, I went through your top-voted questions from https://pollgab.com/room/brento - and I didn't go through many questions because some of them required really long answers, and I had a great time with those.
After the PASS Data Community Summit in Frankfurt, Germany, I headed back to Iceland and spent a few more days gallivanting around the country in a Tesla Model Y camper. (Well, "camper" is a glamorous word for it: CampEasy.com puts a mattress in the back. But still.) Today, I'm at Djúpalónssandur, a marvelous black pebble…
I work with databases for a living.
I teach people, and I help them via consulting.
In this 76-minute session, I'll show you how to use the latest features of sp_BlitzIndex to rapidly improve performance on an existing database. I'll show you how to figure out quickly if you've got too many indexes or not enough, and then demonstrate how to get advice from AI on how to consolidate them in seconds.
Our cruise ship pulled into port in Victoria, Canada, so before we head out to the Butchart Gardens, let's go through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=MDTuQFiXo2s
At least once a month, I get this question from a client:
We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.
Let's go through a LOT of your top-voted questions from https://pollgab.com/room/brento on a VERY early Saturday morning.
https://www.youtube.com/watch?v=V7lGcm8CyEM
The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don't already exist), and uses those two statistics together - but they're not really correlated.
For the longer answer, let's take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:
Well, maybe mountain is a bit of a strong word, but it's one of the highest elevation home sites in Las Vegas, with beautiful views over the valley, the Strip, the airport, and the surrounding mountains. Let's go through your top-voted questions from https://pollgab.com/room/brento while taking in the view - and you can move the camera around, since this is an Insta360 video.
My tripod is probably never gonna recover from the salt water, but the water was so nice that I couldn't resist. This is a 360 video, so you can grab the screen and move it around to see Magens Bay in St Thomas, US Virgin Islands, as I go through your top-voted questions from https://pollgab.com/room/brento.
The official Azure SQL Dev's Corner blog recently wrote about how to enable soft deletes in Azure SQL using row-level security, and it's a nice, clean, short tutorial. I like posts like that because the feature is pretty cool and accomplishes a real business goal. It's always tough deciding where to draw the line on how much to include in a blog post, so I forgive them for not including one vital caveat with this feature.
Sometimes I really hate my job. Forever now, FOREVER, it's been a standard thing where I can say, "When you're measuring storage performance during index and query tuning, you should always use logical reads, not physical reads, because logical reads are repeatable, and physical reads aren't. Physical reads can change based on what's in cache,…
Yes, I'm back on a cruise ship with another 360 degree video. Lest you think I'm being wildly irresponsible (or responsible perhaps?) with your consulting and training money, be aware that this particular cruise was free thanks to the fine folks in the casino department at Norwegian Cruise Lines. In between beaches and blackjack, let's go through your top-voted questions from https://pollgab.com/room/brento.
Want to ask your own questions or pick the ones I discuss? Head over to https://pollgab.com/room/brento to get involved. In the meantime, let's hang out in the backyard, where Beni makes a special guest appearance.
https://youtu.be/aMMBj4owm3A
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.
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
