22Jan 2019 by Grant Fritchey 2 Comments

Critiquing Grant Fritchey: Circa 2008

DevOps, SQL Server
For several years, I ran a regular feature on this blog, Speaker of the Month. I attend a lot of events, so I have the opportunity to hear a lot of people talk about various topics. I decided, as an attempt to help out, to call out individuals that I thought had given a great presentation. There was no other reward beyond my attempts to promote others. In addition to that promotion and praise, I did offer criticism as well. It was never intended to be hurtful or in any way negative. I was hoping to point out people that I thought were great at presenting and provide a tip or two to make them even better. Not everyone liked it. In fact, a few people were decidedly, animatedly, against…
Read More
21Jan 2019 by Grant Fritchey 1 Comment

Get Your Learn On

PASS, SQL Server
There is one truth that I can say about technology with an absolute certainty: It's going to change. Get your learn on! Technology is going to be shifting under your feet, constantly. Even if all you ever do is work with SQL Server, on premises, on hardware, without VMs, containers, clouds or any of that foofaraw, things are going to change. Dealing With Change What's the best way to deal with change? Get your learn on! Learning all the new stuff is absolutely necessary. It's unavoidable. Even if you're not running SQL Server 2019 in production today (and very few of you should be as I write this because it's still in preview) because you're still on SQL Server 2005 (and, by the way, support ended in April of 2016,…
Read More
29Oct 2018 by Grant Fritchey 14 Comments

Explicitly Drop Temporary Tables Or Wait For Cleanup?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn't remember the specifics, but I said it actually didn't matter. However, that answer has bugged me, so I set up a quick test. Explicitly Drop Temporary Tables We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn't: CREATE PROC dbo.BOMDrop AS CREATE TABLE #BOMData (BOMLevel SMALLINT, PerAssemblyQty DECIMAL(8, 2), ComponentName VARCHAR(50), ProductAssemblyName VARCHAR(50), UnitMeasure VARCHAR(50)); INSERT #BOMData (BOMLevel, PerAssemblyQty, ComponentName, ProductAssemblyName, UnitMeasure) SELECT bom.BOMLevel, bom.PerAssemblyQty, c.Name, pa.Name, um.Name FROM Production.BillOfMaterials AS bom JOIN Production.Product AS pa ON pa.ProductID = bom.ProductAssemblyID JOIN Production.Product AS c ON c.ProductID =…
Read More
22Oct 2018 by Grant Fritchey 6 Comments

Why Did a Plan Get Removed From Cache?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I'm going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let's talk about how these work. Removed From Cache Just so we can see ALL the activity, I'm creating an Extended Events session that captures a little more than just the two events: CREATE EVENT SESSION PlanCacheRemoval ON SERVER ADD EVENT sqlserver.query_cache_removal_statistics (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_completed (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_starting (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_hit (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_insert (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT…
Read More
15Oct 2018 by Grant Fritchey 9 Comments

Using Extended Events to Capture Implicit Conversions

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
Using the appropriate data type to avoid conversions or implicit conversions is a fundamental approach to good T-SQL coding practices. Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a way to see if you are experiencing implicit conversions? Actually, yeah, it's right there in Extended Events. plan_affecting_convert Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here's one example of how you might capture implicit…
Read More
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