06Jan 2020 by Grant Fritchey 8 Comments

Execution Plans: First Operator

SQL Server, T-SQL
The first time you see a new execution plan that you're examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator. First Operator The first operator is easily discerned (with an exception). It's the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case: This is regardless of how you capture the execution plan (with an exception). Whether you're looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE: If you get an execution plan plus runtime metrics (previously referred to as an "actual" execution plan), you'll still see…
Read More
19Nov 2018 by Grant Fritchey 1 Comment

Query Store and Plan Cache Plans Compared

SQL Server 2016, SQL Server 2017
Query Store plans and the plans in cache are identical, right? There won't be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up. Differences Between Plans In order to compare the two plans, first, we need a query. Here's a stored procedure that I'm going to use to generate a plan that will be in cache and in the query store: CREATE PROC dbo.ProductTransactionHistoryByReference ( @ReferenceOrderID int ) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID…
Read More
20Aug 2018 by Grant Fritchey 1 Comment

Actual Execution Plan Costs

T-SQL
Why don't "actual execution plans" have "actual execution plan costs"? This is a question and a myth I have to fight against all the time. It's so hard to convince people that all execution plans are estimated plans in the first place (by the way, all execution plans are estimated plans). If we execute a query at the same time we capture a plan, we have enabled SQL Server to also capture run-time metrics with that plan. So we end up with what is known as an actual plan, but it's still just an estimated plan plus those run-time metrics. Execution Plan Costs When you look at a given operator within an estimated plan, it's going to show you four numbers related to cost: Estimated CPU Cost Estimated I/O Cost…
Read More
13Aug 2018 by Grant Fritchey 3 Comments

Measuring Query Execution Time: What Is Most Accurate

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL, Tools
Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. However, all measures are not created equally. In fact, they frequently disagree with one another. Let's take a look at this odd phenomenon. Measuring Query Execution Time Before we get into all the choices and compare them, let's baseline on methodology and a query to use. Not sure why, but many people give me blow back when I say "on average, this query runs in X amount of time." The feedback goes "You can't say that. What if it was just blocking or resources or..." I get it.…
Read More
06Aug 2018 by Grant Fritchey 2 Comments

Analyze Actual Execution Plan

SQL Server
One of the many new sets of functionality introduced in SQL Server Management Studio 17 is the new option "Analyze Actual Execution Plan." If Microsoft continues down this path, there will be a lot of useful functionality at some point. If you haven't yet looked at Analyze Actual Execution Plan, well, read on. Analyze Actual Execution Plan To get to the new functionality, you have to have an Actual Execution Plan open within SSMS 17. After that, it's just a matter of right clicking to bring up the context menu: If you select the menu choice, then a new window opens at the bottom of the execution plan, showing each of the batch statements in one tab, and some interesting stuff in the next tab: You can click that to…
Read More
09Jul 2018 by Grant Fritchey 1 Comment

What Is The Preferred Join Operator in SQL Server?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I schedule many of my blog posts at least 2-3 weeks out, so I have time to adjust them, change, them, insert new things into the schedule, what have you. So, as you read this, I'll have written it at least two weeks ago. As I write this, I'm recovering from a very mild, but irritating, cold. I needed to write a blog post, but my stuffed head wasn't coming up with ideas. So, I half-jokingly asked Twitter for any execution plan questions so I could write something. First response, also a joke, was the question at the title of this post: What is the preferred operator when joining tables: Hash Match, Nested Loops or Merge? While my immediate response to this question is, yes. Meaning, they're all preferred, situationally.…
Read More
07May 2018 by Grant Fritchey 5 Comments

When Simple Parameterization…Isn’t

SQL Server 2017
I'm desperately working to finish up a new version of my book on Execution Plans. We're close, so close. However, you do hit snags. Here's one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it? Simple Parameterization The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this: SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = 42; The resulting execution plan looks like this: The initial, graphical, pointer that we're seeing parameterization…
Read More
16Apr 2018 by Grant Fritchey 4 Comments

Execution Plan Shortcoming in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events, they're just marvelous... until you capture an execution plan. Execution Plans in Extended Events Don't get me wrong. Capturing execution plans with Extended Events is the way to go if you're attempting to automate the process of capturing plans on specific queries on an active system. It's step two that bugs me. So, we capture the plan. Here's an example script. Captures all the various plans and the batch, puts 'em together using causality tracking: CREATE EVENT SESSION ExecutionPlansOnAdventureWorks2014 ON SERVER ADD EVENT sqlserver.query_post_compilation_showplan (WHERE ( sqlserver.database_name = N'AdventureWorks2014')), ADD EVENT sqlserver.query_post_execution_showplan (WHERE…
Read More
12Mar 2018 by Grant Fritchey 6 Comments

Execution Plans and the GDPR

SQL Server, SQL Server 2016, SQL Server 2017
What? Execution plans and the GDPR? Is this it? Have I completely lost it? Well, no, not on this topic, keep reading so I can defend myself. GDPR and Protected Data The core of the GDPR is to ensure the privacy and protection of a "natural person's" information. As such, the GDPR defines what personal data is and what processing means (along with a bunch of additional information). It all comes down to personally identifying (PI) data, how you store it, and how you process it. More importantly, it's about the right for the individual, the natural person, to control their information, up to and including the right to be forgotten by your system. OK. Fine. And execution plans? Execution Plans and PI Data If you look at an execution…
Read More
05Mar 2018 by Grant Fritchey 11 Comments

Every Single Execution Plan is an Estimated Plan

SQL Server, SQL Server 2016, SQL Server 2017
All the execution plans are estimated plans. All of them. There fundamentally isn't any such thing as an "Actual" plan. Where Do You Get Execution Plans? There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store. All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they're all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This…
Read More