Execution Plans

How Much Can One Column Change A Query Plan? Part 2

Last Updated September 8, 2017

What happened in Part 1? Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn’t have a single foreign key in it, anywhere. If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley. 2. Duplicated rows Not necessarily duplicated completely,…

How Much Can One Column Change A Query Plan? Part 1

Last Updated September 3, 2017

Erik Darling

Execution Plans, SQL Server

Beyond Key Lookups I’m going to show you two queries, and two query plans. The two queries are only different by one selected column, but the two query plans are wildly different. Unless I’ve talked to you in the last few days, these may surprise you as well. Here’s the first one. Transact-SQL DECLARE @DisplayName…

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Last Updated May 26, 2020

Erik Darling

Columnstore Indexes, Development, Execution Plans, Indexing, Parameter Sniffing, SQL Server, SQL Server 2017

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…

Five Mistakes Performance Tuners Make

Last Updated July 27, 2017

Erik Darling

Development, Execution Plans, Indexing, SQL Server

There’s no Top in the title And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could…

Locking When There’s Nothing To Lock

Last Updated July 27, 2017

Erik Darling

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels

Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…

sp_BlitzCache: Eventual Compatibility With Azure

Last Updated November 16, 2017

Erik Darling

Execution Plans, Microsoft Azure, sp_BlitzCache

Better than nothing Yesterday it was announced that global temp tables were available in public preview for Azure. That means that unmodified versions of sp_BlitzCache (which uses a global temp table to hold a whole bunch of information) will be compatible up there. This is a nice addition for Azure, and an even nicer addition…

SQL 2016 SP1 Shows You Wait Stats in Execution Plans. Or Does It? [UPDATED]

Last Updated October 5, 2017

Brent Ozar

Execution Plans, Wait Stats

SQL Server 2016 Service Pack 1 brought us performance tuners all kinds of shiny goodies – like the ability to see wait stats inside a query’s actual execution plan. I was really excited when this first came out, but I keep seeing some real oddities. Let’s keep this one really simple: I’m using the Stack…

A Better Way To Select Star

Last Updated November 20, 2018

Erik Darling

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Erik Darling

Execution Plans, Indexing, SQL Server

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

Introducing sp_BlitzQueryStore

Last Updated November 16, 2017

Erik Darling

Execution Plans, First Responder Kit, Query Store (QDS), sp_BlitzQueryStore

Oh, so you’re the one While SQL Server 2016 adoption is still pretty low, and 2017 still quite isn’t out yet, that’s no reason to not invest a few days of dev time into a stored procedure that is only usable by the smallest portion of SQL Server users. Right? Look, someone around here has…

Why Your Biggest Query Plans Don’t Show Up in Some DMVs

Last Updated May 26, 2017

Brent Ozar

Bad Idea Jeans, Execution Plans

SQL Server has three ways to get execution plans from the plan cache: sys.dm_exec_query_plan – around since SQL Server 2005 sys.dm_exec_text_query_plan – added in 2005 SP2 sys.query_store_plan – new in SQL Server 2016 And there’s an important problem with the first one. To show it, let’s take one of my many bad ideas, building queries that take 12+…

Logical Errors And Query Performance

Last Updated May 26, 2017

Erik Darling

Execution Plans

Is that a nice way of saying typo? People will often tell you to clearly alias your tables, and they’re right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can…

What Do You Want to See in a Free Query Store Tool?

Last Updated May 19, 2017

Erik Darling

Execution Plans, Monitoring, Query Store (QDS), SQL Server

I love Query Plans No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah. It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make…

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated May 19, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react. It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry. So, here goes nothin’! Cross Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s…

How Scalar Functions Can Stop You From Getting Adaptive Joins

Last Updated May 1, 2021

Erik Darling

Adaptive Joins, Execution Plans, SQL Server 2017

If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days. If you are, and you’re worried about them crapping on Adaptive Joins, follow along. The big question I had is if various uses of scalar valued functions would inhibit…

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, Execution Plans, SQL Server 2017

But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.…

SQL Server 2017: Interleaved Execution for MSTVFs

Last Updated May 15, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may surprise you One important current limitation (May-ish of 2017)…

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated May 13, 2017

Erik Darling

Execution Plans, SQL Server, SQL Server 2017

Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. That’s the title of the blog…

SQL Server 2017: Statistics Information Comes To Query Plans

Last Updated May 15, 2017

Erik Darling

Execution Plans, SQL Server 2017, Statistics

Warm fuzzies I think this is how my mom would have felt if I went to college. You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance. Things like statistics, and cardinality estimation. And, sure, having the right indexes…

Adaptive Joins And SARGability

Last Updated May 12, 2017

Erik Darling

Adaptive Joins, SQL Server 2017

There’s a famous saying Non-SARGable predicates don’t get missing index requests. And that’s true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week? You betcha!© One Sided I’m going to paste in some queries, each with something non-SARGable. There are two tables involved: Users and Posts. Only…

Execution Plans

Save your cart?