Execution Plans

First Responder Kit, Memory Grants, sp_BlitzCache, sp_BlitzFirst

Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…

An Introduction to Query Memory

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server

Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we’re going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.) Using the freely available StackOverflow database, let’s start with a simple query…

Can Adding an Index Make SQL Server 2016…Worse?

Last Updated April 9, 2017

Using the StackOverflow database, let’s check out Krock’s query. He’s a competitive fella, and he’s looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does. I’m going to simplify the query a little here: Transact-SQL SELECT me.Id, u.Id FROM dbo.Users me JOIN dbo.Users u on…

Breaking News: Query Store in All Editions of SQL Server 2016

Last Updated February 13, 2017

Execution Plans, Query Store (QDS), SQL Server

Bob Ward talking Query Store at SQL Intersection Onstage at SQL Intersections in Orlando this morning, Bob Ward announced that Query Store will be available in all editions of SQL Server 2016. This is awesome, because Query Store is a fantastic flight data recorder for your query execution plans. It’ll help you troubleshoot parameter sniffing…

Stats Week: Do Query Predicates Affect Histogram Step Creation?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

Auto Create Statistics is your friend It’s not perfect, but 99% of the time I’d rather have imperfect statistics than no statistics. This question struck me as interesting, because the optimizer will totally sniff parameters to compile an initial plan. If you don’t have index statistics, or system statistics already on a column in a…

Improved diagnostics for query execution plans that involve residual predicate pushdown

Last Updated February 13, 2017

I love stuff like this! Even though it’s not on my list of dream features, it’s pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown…

SQL Server 2012 SP3 Adds Memory Grant and Performance Features

Last Updated January 8, 2019

Memory Grants, SQL Server

New ways to measure your queries SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features. Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the…

The case for Query Store in tempdb

Last Updated December 4, 2020

Execution Plans, Query Store (QDS), TempDB

Query Store is so cool Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI. “Try looking into that place where you dare not look! You’ll find me there, staring out at you!”…

Enabling Query Store in Azure SQL Database

Last Updated February 13, 2017

Jeremiah Peschka

Execution Plans, Microsoft Azure, Query Store (QDS)

Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft’s words, “The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review”. It’s like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…

Why is This Partitioned Query Slower?

Last Updated June 11, 2019

Execution Plans, Partitioning

It’s tempting to think that table partitioning will improve query performance. After all, it’s an Enterprise Edition feature– it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…

Logical Query Processing

Last Updated March 12, 2018

You can’t do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn’t think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here’s a sample query that takes advantage of…

When does a Query Get Trivial Optimization?

Last Updated August 15, 2024

Execution Plans, Parameter Sniffing

We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here’s a little glimpse into what we talked about. For really simple queries, SQL Server can use “trivial optimization”. If there’s a very limited number of ways to run the query, why do a…

Announcing Live Query Execution Plans

Last Updated February 13, 2017

Behold! Live query execution plans This is new in the preview of SQL Server Management Studio 2016, available for download now. It even works when you’re connected to SQL Server 2014 SP1 – but not 2012, because it relies on DMVs that were only shipped with 2014 SP1. It’s so cute to watch it working…

How many CPUs is my parallel query using in SQL Server?

Last Updated May 4, 2018

Execution Plans

Parallelism can be confusing. A single query can have multiple operators that run at the same time. Each of these operators may decide to use multiple threads. You set SQL Server’s “max degree of parallelism” to control the number of processors that can be used, but it’s not immediately obvious what this means. Does this setting…

SQL Server Query Store

Last Updated December 19, 2017

#SQLPass, Execution Plans, Query Store (QDS), SQL Server

Execution Plan Hell I spend most of my day tuning SQL Server to make it go faster. I’m usually called in after the fact, when the app has become intolerably slow. One of the first things I ask is, “What’s changed?” Nobody ever knows with any sense of accuracy. I understand – until now, SQL Server hasn’t…

Are SQL Server Functions Dragging Your Query Down?

Last Updated February 14, 2017

Jes Schultz

Execution Plans, SQL Server, T-SQL

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you…

Comparing Estimated and Actual Execution Plans in SQL Server

Last Updated May 12, 2019

Jes Schultz

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will…

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

Last Updated May 26, 2020