Execution Plans

Memory Grants, SQL Server

This trace flag is documented in KB #2413549, which says, “Using large amounts of memory can result in an inefficient plan in SQL Server.” The details are a little light, so let’s run a quick experiment with: SQL Server 2017 CU 8 (14.0.3029.16) VM with 4 cores, 32GB RAM, max memory set to 28GB Stack Overflow…

How to Check for Non-Existence of Rows

August 9, 2018

Execution Plans, T-SQL

You’re writing a query, and you wanna check to see if rows exist in a table. I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are: In dbo.Users, the Id field uniquely identifies a user. In dbo.Comments, there’s a UserId field…

Why Does My Select Query Have An Assert?

August 8, 2018

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

July 31, 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

July 27, 2018

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…

CXCONSUMER Is Harmless? Not So Fast, Tiger.

July 24, 2018

Execution Plans, SQL Server

In Theory 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. You may also stumble upon this issue accidentally if your query happens to capture populations like that by accident. For our purposes, let’s look at a query that…

A Quirk In Parallel Hash Join Plans

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?

July 10, 2018

#tsql2sday, t-sql tuesday

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

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

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

June 7, 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?

May 30, 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

May 29, 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

May 24, 2018

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

May 22, 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

May 9, 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

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!

May 3, 2018