Grant Fritchey

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…

10Sep 2018 by Grant Fritchey 1 Comment

Estimated Plans and Forced Plans from Query Store

SQL Server 2016, SQL Server 2017, T-SQL

While all plans are estimated plans, there is still a difference between capturing an estimated plan and looking at a plan from the cache or from query store. Or is there? A question came up during a recent presentation; what happens to capturing an estimated plan when you're forcing plans? Let's find out. The answer is interesting. Estimated Plans Here's my stored procedure that I'll be using with AdventureWorks2017: CREATE OR ALTER 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 WHERE th.ReferenceOrderID = @ReferenceOrderID; END; For reasons I'll explain in a bit, I'm going to free the procedure cache: ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; Then, if I capture an estimated plan for two different values:…

03Sep 2018 by Grant Fritchey 1 Comment

Back to University, Redgate University

Redgate Software

Let's be really clear, Redgate makes ingeniously simple tools. That's a fact. Nothing has changed. However, if you really want to get the full range of capability out of the tools, you need to understand how best to put them to work. Enter Redgate University. Redgate University The concept is very simple, one might even say ingeniously so; have a course on how to get more out of the tool. So, for example, I can demo SQL Clone and SQL Data Masker in about 3 minutes each. You'll understand what they are, how they work and how they'll help you in your company. Done. You can even install them and have them working within your environment within about 15 minutes or so on each. Cool. However, you won't be taking…

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…

21Aug 2018 by Grant Fritchey No Comments

SQL in the City Summit

Redgate Software

This fall, in October, Redgate Software will be hosting three, live, in-person events. These events will take place in New York, London and Chicago. They are on the 12th, 18th, and 26th of October, respectively. You can follow this link to read all the details. I'll be speaking there, along with other Redgaters like Steve Jones and Tom Austin. There will also be other, quite amazing, speakers; Bob Ward of Microsoft, Brian Randell from MCW Technologies, Ronit Reger of Microsoft, Bob Pusateri consultant and Bob Walker of Octopus. We're going to be providing excellent educational content done in the way that only Redgate does it. We'll cover a number of topics, all related to our central theme of Compliant Database DevOps. I want to tell you a little about my…

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…

15Aug 2018 by Grant Fritchey 6 Comments

Rant: There Is No NoSQL Data Storage Engine

Misc

Do I need relational or NoSQL? How does NoSQL help me scale? Will I get paid better with NoSQL or relational? Does NoSQL make my butt look big? OK, maybe I made up that last one. However, the others are all variations of questions I've been seeing a lot of recently. I don't know if I'm just noticing them a lot more or there are more of them, but I'm sick of seeing NoSQL on my computer screen. I love the DB-Engines Ranking page. We can argue over their methods and whether or not Vertica should be ranked above Filemaker all we want. However, what we have is both a very thorough collection of all the different data management systems out there and a great set of definitions for the…

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.…

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…

03Aug 2018 by Grant Fritchey No Comments

Youtube Channel Update: 3 August 2018

Misc

I've posted a number of new videos to the Youtube channel that might be of interest if you're a data professional. First up, I've started migrating my Database Fundamentals posts over to Youtube as SQL Server Fundamentals. The first one is available: [embedyt] https://www.youtube.com/watch?v=NHkzj9ZRhbk[/embedyt] Speaking of DBAs, the job is not going away, but it is changing. Find out how to survive the change: [embedyt] https://www.youtube.com/watch?v=loUwmKQ_Eg4[/embedyt] Would you like to know how the Query Store works? Here's a getting started video: [embedyt] https://www.youtube.com/watch?v=XK8rBO9R43c[/embedyt] I also have a good video on how to combine capturing query metrics along with wait statistics using Extended Events: [embedyt]https://www.youtube.com/watch?v=nYyTSxry03A&t=97s[/embedyt] Wait until you see what Trace Flag 7412 can do for you. It's very cool. [embedyt]https://www.youtube.com/watch?v=ei2zJwZyRic&t=79s[/embedyt] Finally, if you are interested in attending one of my…