Performance Tuning
Query Exercise Answers: Why Are These 3 Estimates So Wrong?
This week's Query Exercise challenged you to figure out why these 3 estimates went so badly:
[crayon-6a6f0c28f0af5590604375/]
Indexing and Statistics
Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
Performance Tuning
Query Exercise: Why Are These 3 Estimates So Wrong?
Our prior Query Exercise introduced SQL Server's 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that's really overkill for the simple problem we were facing in that initial challenge.
[Video] Office Hours Q&A: Abrupt Exit Edition
Last week, we had an abrupt intro, and this week, we have an abrupt ending when my camera overheats, hahaha:
https://www.youtube.com/watch?v=pQOk2N3ljCc
[Video] Office Hours: Abrupt Intro Edition
Today's Office Hours had a ton of technical difficulties because it was my first live stream on both Twitch and TikTok. The first few minutes were cut off, and you can't see the questions onscreen. Nonetheless, there are good answers in here, so let's do it:
https://www.youtube.com/watch?v=Nv0Wnli6kaE
Performance Tuning
Query Exercise Answers: Solving the 201 Buckets Problem
In this week's Query Exercise challenge, I explained SQL Server's 201 buckets problem. SQL Server's statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates.
In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is in outliers ~202-300:
Performance Tuning
Query Exercise: Solving The 201 Buckets Problem
When you run a query, SQL Server needs to estimate the number of matching rows it'll find - so that it can decide which indexes to use, whether to go parallel, how much memory to grant, and more.
For example, take any Stack Overflow database, and let's say I have an index on Location, and I want to find the top-ranking users in Lithuania:
Performance Tuning
Query Exercise Answer: What Makes SELECT TOP 1 or SELECT MAX Different?
This Query Exercise was very different: I didn't ask you to solve a particular problem. I pointed out that I've heard advice that SELECT MAX is faster than SELECT TOP 1, and that's not quite true. I asked you to find factors that would cause these two queries to get different execution plans: [crayon-6a6f0c290808e203360714/] In the…
[Video] Office Hours with a Special Guest: Count Distinct
I was busy, so I asked a friend to fill in for me and answer your top-voted questions from https://pollgab.com/room/brento. He did a pretty good job:
https://youtu.be/oaRIxcf8sKE
Performance Tuning
Query Exercise: What Makes SELECT TOP 1 or SELECT MAX Different?
These two queries both get the same answer from the Stack Overflow database:
[crayon-6a6f0c2908357499008264/]
[Video] Office Hours in Cabo San Lucas, Mexico
What a comfy morning to sit down with a breakfast margarita and tackle your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=E97eDEDLQU8
[Video] Office Hours in a Mexican Hot Tub
While taking a dip in Cabo, I went through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=OaHImrIMM6Y
[Video] Office Hours in My Backyard
On a pleasantly mild morning, I sat down on the patio and took your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=4chYaiYUaSk
[Video] Office Hours in Bulgaria
I went through your top-voted questions from https://pollgab.com/room/brento while in front of the National Gallery for Foreign Art in Sofia.
https://www.youtube.com/watch?v=12E_6VWKb_4
[Video] Office Hours in Sofia, Bulgaria
I was honored to speak at the Present to Succeed conference in Sofia, Bulgaria, run by a former SQL Server MCM. Sofia is a beautiful city, and the gorgeous Patriarchal Cathedral of St. Alexander Nevsky (Wikipedia) was close to my hotel, so I dragged my tripod over there for an Office Hours session.
https://youtu.be/xn2afGkPcmo
T-SQL & Development
Does Bit Column Order Matter in Tables?
At the PGConf.dev, where Postgres developers get together and strategize the work they wanna do for the next version, I attended a session where Matthias van de Meent talked about changing the way Postgres stores columns. As of right now (Postgres 17), columns are aligned in 8-bit intervals, so if you create a table with alternating columns:
[Video] Office Hours: Ask Me Anything About Azure and Microsoft Databases
Back at home in the office, time to settle in with a nice caffeine-free Diet Coke and go through your top-voted questions from https://pollgab.com/room/brento. Why caffeine-free? Because I slug multiple coffees first thing in the morning when I wake up (usually around 3am-4am), and by the time I stream with y'all, I don't need any more go juice.
[Video] Database Answers in Hong Kong
As my time in Hong Kong came to an end, I sat inside on a foggy morning and hit your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=jylCG2l6YSA
[Video] Office Hours: Just Three Questions
Sometimes y'all post questions at https://pollgab.com/room/brento that require extended answers. Today, I'm hitting just 3 questions:
https://youtu.be/xYZDYuCmqJ8
[Video] Office Hours: The Long One
You posted a lot of great questions at https://pollgab.com/room/brento and I spent almost an hour covering these:
https://youtu.be/hBoAhZd6M4s
[Video] Office Hours in Snowy Telluride, Colorado
On a beautiful sunny afternoon in the mountains, I went through your top-voted questions from https://pollgab.com/room/brento. Audio is a little rough on this one because I used a new wireless microphone and forgot to put the wind shield on it. If you'd like to watch the spatial video version on an Apple Vision Pro or…
