Execution Plans

You And Ert This is a quick post because it came up with a client. I like having stuff to point people to — that’s sort of like automation, right? Anyway! Lots of plans have Assert operators in them. But they’re usually performing modifications. Assert operators are used to check Foreign Key and Check Constraint…

A Query That Should Be Contradicted

Last Updated July 20, 2018

Innocent Enough I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate. What I ended up writing as an example was this query: Transact-SQL SELECT v.PostId, SUM(v.UserId) AS whatever FROM dbo.Votes AS v WHERE v.UserId IS NULL GROUP BY v.PostId HAVING…

[Video] “Actual” Execution Plans Lie for Branching Queries

Last Updated July 20, 2018

Brent Ozar

In which I show why you can’t trust “actual” execution plans for branching stored procedures, and you’re better off using sp_BlitzCache to figure out what branches you need to tune: Demo code: Transact-SQL CREATE TABLE dbo.TableA (ID INT); CREATE TABLE dbo.TableB (ID INT); CREATE TABLE dbo.TableC (ID INT); CREATE TABLE dbo.TableD (ID INT); CREATE TABLE…

Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger.

Last Updated August 9, 2020

Execution Plans, SQL Server

Let’s say you’ve got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn’t balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let’s write…

A Quirk In Parallel Hash Join Plans

Last Updated July 18, 2018

Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database. Transact-SQL SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON…

#TSQL2sday: How Much Plan Cache History Do You Have?

Last Updated April 21, 2020

Brent Ozar

For this month’s T-SQL Tuesday, Bert Wagner asked, “What code have you written that you would hate to live without?” I bet you think I’m going to say sp_Blitz, and I would indeed hate to live without that, but you already know about that one, dear reader. Lemme show you something else cool. Before I…

A Surprising Simplification Limitation

Last Updated June 9, 2018

When It Comes To Simplification Rob Farley has my favorite material on it. There’s an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That’s why I’d expect a query like this to throw away the join: Transact-SQL SELECT COUNT(u.Id) FROM dbo.Users AS u JOIN dbo.Users AS…

Indexing Strategies For Cross Apply

Last Updated June 8, 2018

Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they’re not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it’d be easy to come up with an ideal index for this one thing. Transact-SQL SELECT u.Id,…

How Table Variables Mess With Parallelism

Last Updated May 30, 2018

Execution Plans, SQL Server

This Is A Parallel Query Hi There This Is A Temp Table Transact-SQL CREATE TABLE #Posts (OwnerUserId INT); 1 CREATE TABLE #Posts (OwnerUserId INT); This Is A Parallel Insert Into A Temp Table Sort of asterisk (The Insert isn’t parallel, but the part of the plan leading up to it is) This Is A Table…

How Many Kinds Of Joins Are There?

Last Updated May 26, 2018

Adaptive Joins, Execution Plans

Quite So There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins. If you look closely at each of those joins, you’ll see all sorts of interesting things pop up. Hopefully none of them will be Sorts. NOT EXISTS Nested Loops has:…

Behind Every Trivial Plan Is A Good Demo

Last Updated May 26, 2018

A While Back I started this Q&A on Stack Exchange about ISNULL. It’s one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable. Also, I’m saving up points for a Stack Overflow Swiss Army Knife. That’s what you…

A Strange Place For A Memory Grant

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

If You Hang Around Fetish Bars Long Enough… You learn things about memory grants in execution plans. Stuff like the things that usually need memory Hash Join Hash Match Sort Parallelism Exchanges But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons. Let’s loosen the clamps and look at it. Keys…

Trivial Plans, Simple Parameterization and Check Constraints

Last Updated May 17, 2018

Trivial Plans Are Weird They’re the optimizer’s “Yes, Dear”. The thing is, sometimes “Yes, Dear” can miss some important details. Cost based decisions about operators Potential index improvements Contradiction detection for check constraints Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan. Which makes sense. Simple parameterization is considered…

When Query Plans Lie Part 2

Last Updated May 7, 2018

Execution Plans, Indexing, SQL Server

Getting Weirder In Part 1, we looked at how query plans can tell us little lies. Now we’re going to look at how those little lies can turn into bigger lies. Adding An Index Right now, this is our query: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…

When Query Plans Lie Part 1

Last Updated May 7, 2018

Freaking Out Brent Is a full time job. Some days I can get him to hit the desk drawer tequila, and other days I crash and burn. This day was so monumental I almost hired someone to do an action movie narration of this blog post. Let’s Start Easy Over in Stack Overflow, we’re going…

Hey, That’s Not My Sort!

Last Updated April 28, 2018

Development, Memory Grants

Understand Your Plan Mr. Optimizer does the rock n roll hoochie koo with Mrs. Optimizer, c. 1953. When reading query plans, you may sometimes see a sort when you didn’t explicitly ask for data to be sorted. Sometimes they show up to support order-preserving operators like stream aggregates, merge joins, or segments. Other times, they…

Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Tara Kizer

I’ve already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they’re useful to developers. I get it. But I’m focused on performance. A quick recap of what I don’t like about ORMs from that other blog post: Implicit conversions due to nvarchar variables vs varchar columns…

Index Key Column Order And Supporting Sorts

Last Updated April 12, 2018

Whatever Man When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of…

Parking Garages And Predicates

Last Updated April 12, 2018