Signs Your SQL Server Execution Plan Is Lying To You

Polygraph

Unless you’re looking at an actual execution plan, one must never ever ever ever ever believe what one is seeing.

What one is seeing is a series of estimates that may have very little to do with reality. I don’t only mean within the execution plan; I also mean the runtime issues a query may encounter.

With all that in mind, here are some of the many ways a query plan can hide work:

Pants On Fire:

• Low CPU, Long Duration: Scalar Valued Functions, Table Variables, Row Goals
• Low Cost, High CPU: Blocking, Triggers
• Spools: Index, Table
• Parallelism: Exchange Spills, Repartition Streams
• Dynamic SQL and Sub Procs
• Many to Many Merge Joins
• Nested Loops/Lookups High Executions
• Memory Grants/Sort and Hash Spills
• Operator Costs
• Batch Costs
• Branch usage/Startup Expressions
• Cached Temp Tables

Over the next few weeks, I’m going to show you how all of these things can be dreadfully wrong. The links will come alive here over the next few weeks, but you can see the full playlist here.

Anyway.

Thanks for reading!

Video Summary

In this video, I’m Erik Darling from Erik Darling Data, and I’m excited to share my insights on performance tuning queries in SQL Server. As someone who has been diving deep into the intricacies of query optimization, I realized there are some tricky aspects that weren’t as clear when I first started. This series aims to explore these complexities, particularly focusing on how actual execution plans can differ from cached plans in the plan cache or Query Store. I’ll also delve into why queries with low costs but high CPU usage can be misleading and discuss what happens when stored procedures call dynamic SQL or other stored procedures. Additionally, we’ll examine the nuances of parallelism and merge joins, as well as some parameter-related issues that aren’t exactly about sniffing but still significantly impact performance tuning. Stay tuned for more insights as this list grows over time, and who knows—maybe Microsoft will surprise us with a solution to these challenges!

Full Transcript

Hello, Erik Darling here with Erik Darling Data, almost on the verge of celebrating my one year anniversary. I’m going to be recording a series of videos because I think this is something that I wish I understood better when I was first getting into performance tuning queries. And I think there’s some really tricky and interesting things that I’m going to be recording. stuff that happens between like differences between actual execution plans and the cache plans that you might look at in the plan cache or in query store. And the ways that the cache plans can be very, very misleading. So I plan on also having all of these demos as a talk. So I’m recording these videos sort of as supplementary material to that too, in case anyone wants to go and revisit. anything. And this is just sort of the introductory video where I’m going to talk through the stuff that I’m going to record over the course of today. And this might change over time. This list will probably get a little bit longer. Hopefully, maybe have some new stuff added to it. But just like I got drunk on a Sunday and this is what I put together. I mean, it’s what day is today? No, this is now Thursday.

So I’ve been I’ve been working on this. And I look forward to giving my liver a break. So what I’m going to talk through what can stuff that can be misleading. When queries have a very low cost, but use a lot of CPU and the things that can cause those when see when queries have low CPU but high duration spools and how those can be crappy and misleading. Weird stuff that can go on with parallel What happens to a store procedure when you use it to call dynamic SQL or other store procedures? When merge joins have the many to many flags that is true. And then some stuff sort of I don’t want to call it parameter sniffing. I just want to call it parameter related because not all of it is exactly parameter sniffy stuff. Some of it is like if you like have if logic or something like that in your query that you use to like branch stuff off or like make some decisions about what exactly what you want to call it. You’re going to do inside of a store procedure when it runs. So I’m looking forward to sharing all this stuff with you. I’m also looking forward to this list growing as I don’t know as the time as time goes on or I don’t know maybe Microsoft will magically make it so that we don’t have to worry about this stuff anymore. That would be nice too. I’m going to go make some phone calls about that. Anyway, I will see you in the next video where we will talk about. Well, we will start with getting these liars to expose themselves. Anyway, see you there.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.