Reading operators, properties, plan shape, and actual-versus-estimated behavior.
92 associated posts86 primary posts
When there are IF branches or conditional logic, "Edit Query Text" only shows you the branches that actually executed. It's easy to miss whole swaths of code that didn't happen to execute one particular time.
To demo it, let's create a simple stored procedure with an IF branch that sometimes executes, and sometimes doesn't:
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?…
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?
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
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
Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results:
[crayon-6a6ecdefdbb0b449814326/]
Their actual execution plans are wildly different:
They both use the index to retrieve their data, sure, but:
Y'all were on fire in this one! You posted great questions at https://pollgab.com/room/brento and I worked through 'em:
https://youtu.be/JDTS5KnqewA
Or perhaps another way to phrase that is, Microsoft is up to something with parallelism. I don't know how long it's been this way - could even be since the launch of Azure SQL DB Serverless - but I just now noticed it while helping a client with a slow query. When you view an…
Post your Azure SQL DB and SQL Server questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss. In this episode, I'm overlooking Breiðamerkursandur, Diamond Beach, one of the most Instagrammed places in Iceland. Here's a shot from a prior trip when I climbed up on one of the icebergs:
Post your SQL Server and Azure SQL DB questions at https://pollgab.com/room/brento and upvote the ones you'd like to hear me discuss.
https://youtu.be/hZSavloVmuw
Not all of the questions y'all post at https://pollgab.com/room/brento require long-winded responses.
Brandon: Do you seen a rise in json queries to address impedance mismatch between data/objects? I struggled with EF to produce a query that was neither simple nor complicated against a properly designed db (according to 2 experts). Dropped EF; used json query in stored proc; it was magic.
Y'all post questions at https://pollgab.com/room/brento, upvote the ones you'd like to see me cover, and then I talk through 'em.
https://youtu.be/aEe-uz2Ikvc
A reader posted a question for Office Hours: Hi Brent, What is your take on Hugo Kornelis's explanation of execution plan naming. As her his explanation, estimated exec plan is simply an execution plan whereas actual execution plan = execution plan+run-time stats. Do you agree that the naming is flawed and confusing? - Yourbiggestfan I…
Until 2022, when a query couldn't go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan.
But starting with SQL Server 2022, even when I'm running under older compatibility levels:
[crayon-6a6ecdefdc66a733788308/]
The execution plan gives me way more details:
When you see "index seek" on an execution plan, that doesn't mean SQL Server is jumping to exactly the row you're looking for. It only means that SQL Server is seeking on the first column of the index.
This is especially misleading on indexes where the first column isn't very selective.
Ah, sunset - time to kick back on the balcony, watch the ocean, and discuss SQL Server. You post questions at https://pollgab.com/room/brento/, upvote the ones you'd like to see me answer, and I discuss 'em:
https://www.youtube.com/watch?v=Vze_ClkDnhE
Got SQL Server questions? Post 'em at https://pollgab.com/room/brento and upvote the questions you'd like to see me discuss.
https://www.youtube.com/watch?v=hmmaAwf4r4k
When you execute a query, the database server has to figure out things like:
Which table to process first
Which index to use on that table
Whether to seek on that index or scan it
Which table to process next
How to join the data between those two tables
When to sort the data
When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on?
For starters, do NOT believe the percentages you see in execution plans.
