How SQL Server compiles, optimizes, and executes individual queries.
573 associated posts292 primary posts
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…
Nah, not necessarily. SQL Server's query optimizer behavior keeps changing with every freakin' version. Let's illustrate it with a simple query against the Stack Overflow Users table:
[crayon-6a706329dee7e710340014/]
When I was testing SQL Server 2025's new ability to limit TempDB usage with Resource Governor, I wrote a few fun diabolical demos. One of them was to generate giant spills to TempDB, quickly.
When you're looking at an actual (not estimated) query plan, and you see a yellow exclamation point on a sort operator, that means:
I love SQL Server, and I'm excited for the release of 2025. I think the query processing keeps getting better, which means your apps are gonna go faster, with less code changes. The AI stuff is a little fluffy, and I don't think time will be kind to it, but I totally understand why Microsoft…
SQL Server 2025 and .NET 10 bring several new improvements to storing JSON natively in the database and querying it quickly. On the SQL Server 2025 side, the two big ones are the new native JSON indexes and the new JSON_CONTAINS function. Let's see their improvements in action. On the .NET 10 side, EF 10…
In the Query Plan Pop Quiz, questions 2 and 3 asked you about what the sizes of arrows on query plans meant. The good news is that almost all of you got Question 2 right, but the bad news is that the vast majority of you got Question 3 completely incorrect, and the saddest part of that is that you've been using that inaccurate knowledge to guide your query tuning - and wasting your time.
In last week's Query Plans Pop Quiz, the first question was, someone hands you these two queries and you get their estimated plans to decide which query to tune. Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool. The question was, which query should you focus on tuning?…
SQL Server Management Studio 22 Preview 3 is out, and it brings with it a new Query Hint Recommendation tool. Start by highlighting the query you want to test, then click Tools, Query Hint Recommendation Tool. It slides out a new pane on the right hand side: The maximum tuning time defaults to 300 seconds,…
I'm back at home in Vegas, taking your top-voted questions from https://pollgab.com/room/brento on a hillside enjoying the fall desert weather.
https://www.youtube.com/watch?v=dXwD-nJ2K3c
Question 1: Pick the Problematic Plan: someone hands you a pair of queries, and you get the estimated query plans. (Perhaps you get the estimated plans from SSMS, or from sp_BlitzCache, or from your monitoring tool.) Which one of these two should you focus on tuning first, Query 1 or Query 2?
Question 2: on an estimated plan, what does the thickness of the colored arrow represent?
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.
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.
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
When I'm on the road, I try to take you along with me to glamorous or scenic locations to give you a change of pace. However, last week in London was a bit of a whirlwind between SQLBits and restaurants, so at the last minute, I recorded Office Hours in my hotel room!
https://www.youtube.com/watch?v=t8lURbMoKzk
Last week, I asked y'all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:
A few thoughts:
I use AI tools constantly in my work, so when the new SSMS v21 came out with Copilot support, I was excited to try it. The short story is that the user interface is fine, and it doesn't feel buggy, but the advice is simply terrible compared to other LLMs due to the prompt Copilot is using. You're better off using tools like ChatGPT, Gemini, and Claude directly for now.
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.
I strapped on my Apple Vision Pro, updated its OS, and recaptured my persona with the latest updates, and... it's still pretty weird. Let's go through your top-voted questions from https://pollgab.com/room/brento and have a laugh at my hot dog fingers.
https://www.youtube.com/watch?v=tLAEqItBnuI
For yeeeeeears, when I've explained execution plans, part of my explanation has included the instructions, "Read the plan from right to left, top to bottom, looking for the place where the estimates vs actuals are suddenly way off." Here's an example:
Things seem to be going okay on the query plan until you hit the key lookup, which brought back 13 rows of an estimated 19,452. That would appear to be a pretty doggone bad estimate.
