Execution Plans

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 May 26, 2020

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

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

Execution Plans, Parameter Sniffing, SQL Server

What could go wrong? Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. “Bad plans” can…

Five Reasons a Production DBA Should Learn to Read Execution Plans

Last Updated August 7, 2019

Jes Schultz

While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?” YES! Here’s why. Performance is important. A production DBA’s main focus should be the safety and security of the…

Finding One Problem Query With Extended Events

Last Updated February 13, 2017

Jeremiah Peschka

It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen? Server Side Trace You could set up a…

Exploring the Magic of the Plan Cache

Last Updated February 13, 2017

Jeremiah Peschka

The plan cache holds a lot of secrets about what’s going on inside SQL Server. In the First Responder Kit we shared one of our plan cache scripts to find the top resource consuming queries. That query works well, but over time we’ve added some additional functionality to the query. I figured it was time to share the…

RECOMPILE Hints and Execution Plan Caching

Last Updated April 2, 2021

Execution Plans, Parameter Sniffing

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

Query Plans: Memory Grants and High Row Estimates

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…

Introducing the SQL Server Plan Cache (and a Better sp_Blitz®)

Last Updated February 13, 2017

Execution Plans, SQL Server, Videos

When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…

The Art of the Execution Plan

Last Updated June 18, 2020

There’s beauty everywhere – even in SQL Server. I was just staring at this plan when I was struck by its beauty: Stored Procedure Part 1 Oh, sure, it’s ugly, and I recoiled in horror at first, but when you get over that reaction, there’s an amazing amount of stuff there. Someone – probably many…

Simon Sabin’s talk on query performance

Last Updated March 10, 2021

At #SQLbits, Simon Sabin (Twitter) talked about car crash queries: queries that suddenly have bad performance out of nowhere, yet if you copy/paste them into SQL Server Management Studio, they run quickly. The culprit is often parameter sniffing: the engine looks at the query and builds a plan, but if the parameters in the WHERE…

Book Review: SQL Server 2008 Query Performance Tuning Distilled

Last Updated February 13, 2017

Book Reviews, Execution Plans

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book. Several times in the last two weeks, I reached for this book first.

Getting Help With A Slow Query

Last Updated October 21, 2017