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
05Jul 2018 by Grant Fritchey 60 Comments

Why Aren’t You Using SSMS 17?

SQL Server, SQL Server 2016, SQL Server 2017
Last fall, Microsoft split the coding and release of SQL Server Management Studio away from any dependency on the server code. With that, they began an aggressive and exciting series of releases with exciting new functionality in each release. However, you don't care. How do I know? I've seen the telemetry that shows that most of you are still using old versions of SSMS. I want to know why. Please, post comments below so I can understand. Why You Should Move to SSMS 17 There are a ton of reasons for you to make the move. The least of which is bug fixes are coming hot and fast. The more exciting things are the ability to compare execution plans or the new Extended Events sessions that look just like those…
Read More
11Jun 2018 by Grant Fritchey 6 Comments

Plan Metrics Without the Plan: Trace Flag 7412

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don't need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan. Trace Flag 7412 Here's how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan…
Read More
04Jun 2018 by Grant Fritchey 5 Comments

Extended Events, the system_health Session, and Waits

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I advocate for, use, document, teach, and just downright love, Extended Events. They are so much better than the old Trace Events (aka, Profiler) that it's sometimes difficult to keep from just gushing. Let's talk about a common situation that you're going to run into on your servers all the time and how you can put Extended Events to work to help you, without actually doing any work at all. What's that? Be lazy and get rewards? Yes. The Extended Events system_health Session On your servers, any of them that are SQL Server 2008 or newer, right now, unless you've performed actions to prevent this, you're running the Extended Events system_health session. It's just happening, currently, on all your servers. Nothing you need to do about it at all. I'll…
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
05Feb 2018 by Grant Fritchey 3 Comments

Wait Statistics on a Query

SQL Server, SQL Server 2016, SQL Server 2017
Wait statistics are a vital part of understanding what is causing your system to run slowly. Capturing them can be done through a variety of mechanisms from sys.dm_os_wait_stats (use this query for that) to sys.dm_db_wait_stats in Azure SQL Database. Those cover the system and the database, however, what about capturing query wait statistics on a specific query? Query Wait Statistics There was a time when this was actually kind of difficult. However, now we have a lot of different tools to capture query wait statistics. First up, and really, one of the best and easiest ways to deal with this, is to use the wait statistics captured by the Query Store. The only drawback to this method is that it is an aggregation of query wait statistics for the given…
Read More
11Dec 2017 by Grant Fritchey 2 Comments

Execution Plan Metrics and Units of Measure

SQL Server, SQL Server 2016, SQL Server 2017
Have you ever looked at the properties inside an execution plan and wondered what the units of measure were? I know I have. I also get the question pretty frequently about all sorts of properties. What does EstimatedAvailableMemoryGrant display it's units in anyway? For that matter, what the heck is an EstimatedAvailableMemoryGrant? Showplan Schema and Units of Measure The answer to those questions and a whole lot more is pretty easy to find. You just have to look to the ShowPlan Schema. Microsoft has all the schema's published, going back to 2005. The next time you're faced with a question such as, what are the units of measure of the MemoryGrantInfo: Or, what the heck is SerialDesiredMemory and why is it exactly matching RequestedMemory, you just have to go and…
Read More