Execution Plans

Adaptive Joins, SQL Server 2017

I don’t like it unless it’s brand new When new features drop, not everyone has time to jump on top of them and start looking at stuff. That’s what consultants with nothing better to do are for. I’ve been excited about this feature since talking to The Honorable Joseph Q. Sack, Esq. about it at…

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…

Status Update On Query Plan Hash Bug

Last Updated April 21, 2017

Execution Plans

Not long ago I posted about a bug

Look Ma, Adaptive Joins

Last Updated May 12, 2017

Execution Plans, SQL Server 2017

This probably won’t seem like a big deal soon But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is. I hope Joe Sack has strong ribs. And here’s the tool tip info! Dynamic Duo This…

Is it ever worth adding indexes to table variables?

Last Updated April 16, 2017

Execution Plans, Indexing

Disclaimer I found this totally by accident, and it even surprised me. You can probably guess the TL;DR on this is yes, huh? Dude, where’s my table variable? I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had…

Replacing ISNULL In A WHERE Clause

Last Updated April 7, 2017

Execution Plans, T-SQL

I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…

Does a TempDB spill mean statistics are out of date?

Last Updated April 11, 2017

Execution Plans

When you see a yellow bang in an execution plan, and you hover over it to discover that SQL Server spilled out to TempDB, your first reaction is to wail in pain and rend your clothes. Crying over spilled memory After that, though, it seems like people always ask the same question: “Why doesn’t updating…

Decrypting Insert Query Plans

Last Updated March 25, 2017

We’ve all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there’s not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…

Crappy Missing Index Requests

Last Updated November 16, 2017

Execution Plans, Indexing, sp_BlitzIndex

When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…

When THREADPOOL Waits Lead To RESOURCE_SEMAPHORE Waits

Last Updated January 8, 2019

Memory Grants, Wait Stats

Your server is underpowered That’s an understatement. Your server sucks. It has four cores in a single socket, data outpaces RAM by a country mile, the disks have whiskers, and the network card still has a phone jack in it. Alright, so maybe it’s not that bad, but it’s bad enough that you run into…

Using Trace Flag 2453 to Improve Table Variable Performance

Last Updated March 2, 2018

Tara Kizer

Execution Plans, SQL Server, TempDB

I recently saw a server with trace flag 2453 configured. I hadn’t come across this trace flag before, so I did a little research. Microsoft says it allows “a table variable to trigger recompile when enough number of rows are changed”. This can lead to a more efficient execution plan. Trace flag 2453 is available…

Memory Grants and Data Size

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

What does memory do? In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along…

Some Plans Are Wider Than Others

Last Updated February 13, 2017

Using the publicly available Stack Overflow database (the 100GB 2016/03 version), here’s an execution plan for an update statement: The Narrow Plan (click to see PasteThePlan details) It’s a pretty simple query – it just scans the Posts table looking for any post with PostTypeId = 8, and then updates a single field for the first 250…

Indexing Temp Tables

Last Updated February 13, 2017

Execution Plans, Indexing, SQL Server, TempDB

People often don’t give this thought Which is a shame, because I see people sticking fairly large amount of data into temp tables. On the rare occurrence that I do see them indexed, it’s a nonclustered index on a column or two. The optimzer promptly ignores this index while you select 10 columns and join…

Things I Would Love to Put In sp_BlitzCache

Last Updated April 21, 2017

Execution Plans, First Responder Kit, SQL Server

This is my favorite script to work on Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier. Along the way, I see a lot of things that I would love to warn people…

Date Math In The WHERE Clause

Last Updated February 13, 2017

Oh, THAT SARGability I realize that not everyone has a Full Metal Pocket Protector, and that we can’t all spend our days plunging the depths of query and index tuning to eek out every CPU cycle and I/O operation from our servers. I mean, I don’t even do that. Most of the time I’m just…

Spills SQL Server Doesn’t Warn You About

Last Updated February 13, 2017

Don’t make me spill Table variables get a lot of bad press, and they deserve it. They are to query performance what the TSA is to air travel. No one’s sure what they’re doing, but they’ve been taking forever to do it. One particular thing that irks me about them (table variables, now) is that…

SQL Server 2016 SP1 Bug with Graphical Execution Plans

Last Updated February 13, 2017

Early adopters: if you’re rolling on SQL Server 2016 SP1, you may have noticed that when you click on query plans in sp_BlitzCache or sp_WhoIsActive, they sometimes render as ugly XML rather than the beautiful graphical showplan. Here’s a 3-minute video showing the bug: To work around it, save the XML plan as a file…

Index Scans: Not Nearly As Bad As You Might Think

Last Updated February 13, 2017