Execution Plans

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

Using our good old buddy the StackOverflow demo database, let’s look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field. Here’s the actual execution plan: https://www.brentozar.com/pastetheplan/?id=HyuSDxZye Ooo – clustered index scan – that’s bad, right? Not so…

What’s the Difference Between Estimated and Actual Execution Plans?

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

I’m going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName LIKE 'Brent%'; 123 SELECT *FROM dbo.UsersWHERE DisplayName LIKE 'Brent%'; First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Estimated execution plan: https://www.brentozar.com/pastetheplan/?id=H1TeRlZke Click on the…

Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Say you’ve got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you’re getting bad query plans. We’re going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a…

Why Is This Query Sometimes Fast and Sometimes Slow?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

You swear you didn’t change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let’s see how it works. I’m going to use the StackOverflow database – particularly, the Users table that I demo in How to Think Like the…

Silent Demo: The Plan Cache

Last Updated November 16, 2017

Execution Plans, sp_BlitzCache, Videos

Shhh, and pay close attention. In 76 seconds, I’ll teach you something intriguing about the way SQL Server caches execution plans:

PasteThePlan Update: See the Most Recently Pasted Plans

Last Updated February 13, 2017

Execution Plans, PasteThePlan.com

Just another terrifying query PasteThePlan.com lets you paste in execution plans and get a link to share them with other people. In our company chat room, we get an alert whenever a new plan comes in, and I’m not ashamed to admit that we’re geeky enough that we usually jump straight into it and start…

Searching Strings in SQL Server is Expensive

Last Updated June 25, 2018

Execution Plans, Licensing

In the classic spirit of my How to Think Like the Engine class, let’s go take a look at the StackOverflow.Users table and find all the users named Brent. There’s a DisplayName field, and I’m going to be querying that a lot in this blog post, so I’ll go ahead and create an index on…

PasteThePlan.com Winners and Sample Plans

Last Updated April 9, 2017

Execution Plans, PasteThePlan.com

When we announced PasteThePlan last week, we wondered what kinds of plans you folks would paste in. Six of you won free Everything Bundles: Ajay Dwivedi Samuel Jones Stefan Gabriel Stephan Schon Steve Armistead Vesa Juvonen But rather than stepping through their plans (which are awesome too), I’m going to show a few bits and…

Query Tuning Week: How to Run sp_BlitzCache on a Single Query

Last Updated August 29, 2020

Execution Plans, Parameter Sniffing, sp_BlitzCache

The most popular way of using sp_BlitzCache® is to just run it – by default, it shows you the top 10 most CPU-intensive queries that have run on your server recently. Plus, it shows you warnings about each of the queries – if they’re missing indexes, experiencing parameter sniffing issues, running long, running frequently, doing…

Question From Office Hours: SQL Handle vs. Plan Handle

Last Updated February 13, 2017

Execution Plans

Great question! We recently added some columns to sp_BlitzCache to help you remove undesirable plans from the cache. Doing this will force SQL to come up with a new plan, or you know, just re-create the old plan. Because that just happens sometimes. I answered it, but I wasn’t happy with my answer. So here’s…

How to Start Troubleshooting Parameter Sniffing Issues

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

When a query is sometimes fast and sometimes slow, for the same input parameters, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing. After SQL Server starts up and you run a stored procedure, SQL Server builds an execution plan for that proc based on the first set…

Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure

Last Updated April 9, 2017