So far in the class, I’ve made a lot of assumptions that what’s onscreen is the truth. However, that’s because we’ve been dealing with relatively straightforward queries. Before I let you loose in the real world, I need to point out that a lot of what you see in complex query plans is actually garbage….
When you see a yellow bang warnings on sort, hash match, or adaptive join operators in an execution plan, that’s a sign that TempDB was invited to the party. When SQL Server runs out of memory, the data spills over into TempDB. Let’s see how it happens, and what we can do about it. Demo…
When you look at the arrows on an actual plan, what do the arrow sizes mean? When you see the list of wait stats, they’re accurate, right? Buckle up and prepare to be disappointed – a lot of what you see in a query plan just flat out isn’t true. Here is the demo script:…
Now let’s step through an execution plan in minute details to see where the estimates from each operator come from, and how they can go wildly wrong. If you want to follow along in SSMS, here’s the query: Transact-SQL SELECT Location, COUNT(*) FROM dbo.Users GROUP BY Location ORDER BY COUNT(*) DESC OPTION (MAXDOP 1); 12345…
Before we start tuning queries, you’ll learn the steps SQL Server takes to build its execution plans, the terminology for parts of a plan, and see what’s missing from execution plans.
