Execution Plans

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

Not Exactly The Catchiest Name There’s a TL;DR here, in case you don’t feel like reading the whole darn thing. Batch mode memory grant feedback works with stored procedures It takes several runs to adjust upwards to a final number It seems to only adjust downwards once (in this case by about 40%) This isn’t…

Computed Columns and Cardinality Estimates

Last Updated February 12, 2018

Development, Execution Plans, SQL Server

:thinking_face: When most people think about computed columns, they don’t think about cardinality estimates. Heck, I’m not sure most people think about cardinality estimates. At all. Ever. One of the few people who has ever responded to my emails does, and I didn’t even have to threaten him. Do you think about computed columns? If…

Then Why Doesn’t SQL Always Seek?

Last Updated February 6, 2018

Execution Plans, Indexing, SQL Server

Scan The Man There seems to be a perpetual battle waged against the Index Scan. At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…

Memory Grants: SQL Server’s Other Public Toilet

Last Updated May 26, 2020

Execution Plans, Indexing, Memory Grants, Parameter Sniffing

Sharing Is Caring When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants. The problem becomes itself when queries start to over and under estimate their practical needs. Second Hand Emotion Queries ask for memory to do stuff. Memory is a shared resource. What kind of stuff? Well, usually…

Functions Can Still Use Indexes, Kinda.

Last Updated May 1, 2021

Execution Plans, Indexing, SQL Server

This sentence gets repeated a lot. You know the one: “Functions prevent the use of indexes.” Well, knowing you, it’s probably just your indexes. I’ve seen your indexes. Functions can use indexes, but even so, that doesn’t mean that they’re going to perform as well as queries without functions. Which Indexes? In the SUPERUSER database (yeah, I know, I’m…

How Much Can One Row Change A Plan, Part 4

Last Updated January 17, 2018

Execution Plans, SQL Server

Monte Carlo Or Bust In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans. To figure some stuff out about why the plans changed, let’s focus on the Nested Loops Join plan. Why? Because when I force the Hash Join plan, the results are no different. I get…

How Much Can One Row Change A Plan, Part 3

Last Updated January 17, 2018

Execution Plans, SQL Server

All The Rage We’ve asked how much one column and even one row can change a plan before. The doctor says I’m not allowed to read blog comments anymore on account of my blood pressure, but the AI I wrote in PowerShell assures me they were well-received. So here we go again, ONCE MORE INTO THE FRAY!…

“But It Worked in Development!” – 3 Hard Performance Problems

Last Updated January 8, 2019

Development, Memory Grants

You’ve been performance tuning queries and indexes for a few years, but lately, you’ve been running into problems you can’t explain. Could it be RESOURCE_SEMAPHORE, THREADPOOL, or lock escalation? These problems only pop up under heavy load or concurrency, so they’re very hard to detect in a development environment. In a very fast-paced session, I’ll…

SQL Server 2017 CU3 adds optimizer row goal information in query plans

Last Updated March 22, 2018

Execution Plans, SQL Server 2017

Row Whats? Like it or not, the optimizer has row goals. We’re not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I’ll be adding it to the FRK in an upcoming release. I am going to give you a round up…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

SQL Server 2017 CU3 add a new CXCONSUMER wait type

Last Updated August 9, 2020

Execution Plans, SQL Server 2017, Wait Stats

SQL Server 2017’s new CXCONSUMER wait type was originally announced by Microsoft’s Pedro Lopes, and now it’s out. Here’s what it means for performance tuners. According to Pedro, this wait is the “safe” type of parallelism wait, as opposed to the CXPACKET wait type, which means work isn’t evenly balanced across all of our cores. Pedro blogged…

SQL Server 2017 CU3 adds execution statistics for scalar-valued, user-defined functions

Last Updated December 9, 2018

Execution Plans, SQL Server 2017

Update This seems to finally be fixed as of CU12 for SQL Server 2017. If you’re not already patched up, head over to the link to get there. Estimated Plans Need Not Apply This is, of course, only available in actual plans. All together now: If a query uses one or more user-defined scalar functions…

Query Plan Oddities: Two Identical Missing Indexes

Last Updated December 9, 2017

As I’ve been building labs for my Mastering Query Tuning class, I’ve been spelunking through data.stackexchange.com. It’s where anybody can write queries against the Stack Overflow databases, and share them with friends. For example, Daniel Vandersluis wrote a query to see how many edits he has: Transact-SQL -- How many edits do I have? --…

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 2017

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…

Implied Predicate and Partition Elimination

Last Updated December 4, 2017

>implying Way back when, I posted about turning the Votes table in the Stack Overflow database into a Partitioned View. While working on related demos recently, I came across something kind of cool. It works for both partitioned tables and views, assuming you’ve done some things right. In this example, both versions of the table…

Partitioned Views, Aggregates, and Cool Query Plans

Last Updated December 4, 2017

The Max for the Minimum Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this. There are actually quite a few similarities between the way a partitioned table and…

Microsoft’s Query Tuning Announcements from #PASSsummit

Last Updated November 2, 2017

Microsoft’s Joe Sack & Pedro Lopes held a forward-looking session for performance tuners at the PASS Summit and dropped some awesome bombshells. Pedro’s Big Deal: there’s a new CXPACKET wait in town: CXCONSUMER. In the past, when queries went parallel, we couldn’t differentiate harmless waits incurred by the consumer thread (coordinator, or teacher from my…

How to Get Live Query Plans with sp_BlitzWho

Last Updated November 18, 2017

Execution Plans, First Responder Kit, sp_BlitzWho, SQL Server 2016

sp_BlitzWho is our open source replacement for sp_who and sp_who2. It has all kinds of really neat-o outputs like how many degrees of parallelism the query is using, how much memory it’s been granted, how long it’s been waiting for memory grants, and much more. If you’re on SQL Server 2016 SP1 or newer, it…

What Is Estimated Subtree Cost? Query Bucks. No, Really.

Last Updated June 3, 2022