Execution Plans

Of Windowing Functions And Where Clauses

March 2, 2018

Execution Plans, SQL Server

Seen One, Seen’em All This isn’t about indexing! I promise. In fact, I’ve dropped all my indexes for this. It’s just that, well, I’ve seen this happen with two clients in a row, so maybe it’s time to blog about it. If you run this query, you get a fairly obvious plan. Transact-SQL WITH nonsense…

Joe Sack query bucks

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

February 28, 2018

Adaptive Joins, Execution Plans

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

February 23, 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?

February 16, 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

February 15, 2018

Execution Plans, Indexing, SQL Server

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 and “the use of indexes”

February 1, 2018

Execution Plans, Indexing, SQL Server

Wordy Bird 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. Which Indexes? In the SUPERUSER database (yeah, I know, I’m cheating on Stack Overflow), all the tables have a PK/CX on an Id column, which is…

How Much Can One Row Change A Plan, Part 4

January 19, 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

January 18, 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!…

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

January 12, 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

January 11, 2018

Execution Plans, 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 adds CXCONSUMER waits, doesn’t mention it

January 10, 2018

Execution Plans, SQL Server 2017, Wait Stats

The revolution will not be documented At the PASS 2017, Pedro Lopes (don’t call him low-pez) from Microsoft mentioned that a new parallelism wait was getting added soon. That wait, if you’re too darn tootin’ lazy to click, is called CXCONSUMER. According to Pedro’s slide, but not the ENTIRELY MISSING DOCUMENTATION, this wait is the…

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

January 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

December 13, 2017

Execution Plans

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? --…

Pocket Square

sp_BlitzQueryStore: A Gentle Introduction

December 7, 2017

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

dba, development, performance tuning, sql server

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

November 9, 2017

Execution Plans

dba, development, performance tuning, sql server

>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

November 7, 2017

Execution Plans

dba, development, performance tuning, sql server

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

November 3, 2017

Execution Plans

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

October 31, 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.

October 31, 2017

Execution Plans

When you look at a query plan, SQL Server shows a tooltip with an Estimated Subtree Cost: Estimated Subtree Cost Now I can run all the bad queries I want! A long time ago in a galaxy far, far away, it meant the number of seconds it would take to run on one guy’s Dell…

Adaptive Blog Posts

September 29, 2017

Adaptive Joins, Execution Plans

dba, development, performance tuning, sql server

It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn’t supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…

Menu