24Sep 2018 by Grant Fritchey 14 Comments

Extended Events and Stored Procedure Parameter Values

SQL Server, SQL Server 2016, SQL Server 2017
One complaint I've received frequently is that you can't see stored procedure parameter values in Extended Events. That is patently not true. However, it does depend on where and how you capture the events and which stored procedure parameter values you're going for. I think this is a holdover from 2008 when Extended Events... well, let's be kind and say... didn't work well. Now, they do. Let's explore this a little. Capturing Stored Procedure Executions As with most things, there's more than one way to capture stored procedure execution in Extended Events. First up, it depends entirely on how they're called and on your intentions when you capture the information. Here are the three methods I know to capture just the completion metrics on stored procedure calls: rpc_completed sql_batch_completed module_end…
Read More
18Sep 2018 by Grant Fritchey 9 Comments

Announcing the 5th Edition of SQL Server 2017 Query Performance Tuning

SQL Server, SQL Server 2016, SQL Server 2017
I am quite excited to announce that the latest, most up to date, and by far the largest, copy of my book on query tuning is now available. 900 pages of information on how to improve the performance on your Azure SQL Database and SQL Server instances has been published. You can get your hands on the digital copies now and the print copies will be available shortly. Follow this link to Amazon to get your copy. Special, public, and heart-felt thanks to my technical editor, my hero, Joseph Sack. He's worked with me through four editions of this monster and the book wouldn't be what it is without him. If you'd like to learn some of the material in the book, I'll be presenting an all-day seminar two more…
Read More
12Sep 2018 by Grant Fritchey 30 Comments

3rd Edition, SQL Server Execution Plans, a Story

SQL Server, SQL Server 2016, SQL Server 2017
Four years ago, after a bunch of dithering and some negotiations with Tony Davis, my editor, I started to update my book, SQL Server Execution Plans. We managed to convince Hugo Kornelis to be the tech editor. I started to do the real writing in early 2015. I was most of the way through a first draft and no one liked it. Tony was unhappy. Hugo was unhappy. I was unhappy. I was just trying to update the existing book, SQL Server Execution Plans. It wasn't working. We all came to the conclusion that the old book was wrong. Not simply in a technical sense, although there was a lot of that, but in a structural sense. So we started rearranging things. SQL Server 2014 came out, but I was…
Read More
27Aug 2018 by Grant Fritchey 4 Comments

Why I Love Extended Events, Reason 394: Customize Live Data

Azure, SQL Server, SQL Server 2016, SQL Server 2017
There are so many reasons to love Extended Events that it can really be overwhelming, but, one more reason that I love them are the ability to customize Live Data. More importantly, you can share that customization with others or move it between machines. Let's explore this. Customize Live Data If you open up an Extended Events file, or connect to a running Session, you're looking at the Live Data window and it looks something like this: I won't lie, that's a pretty ugly UI. At the top are all the events you've captured. At the bottom are the event fields. If you want to look at the information in different events, you click on the event, then down at the fields... blech! However, I have some options. Let's right…
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
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