Product has been added to your cart.

Execution Plans

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

Last Updated May 26, 2017

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

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

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

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

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

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

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

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

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

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…

Adaptive Joins And Local Variables

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality Estimator math here, we’re just going to look at Adaptive Join plan choice…

Anatomy Of An Adaptive Join

Last Updated May 12, 2017

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

Execution Plans, SQL Server

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…