22Aug 2017 by Grant Fritchey 4 Comments

Runtime Metrics In Execution Plans

SQL Server 2016
Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you're using SQL Server 2016 SP1 or better in combination with SQL Server Management Studio 2017. When you capture an actual plan (using any method), you get the query execution time on the server as well as wait statistics and I/O for the query. Fundamentally, this changes how we can go about query tuning. Runtime Metrics To see these runtime metrics in action, let's start with a query: SELECT p.LastName, pp.PhoneNumber, pnt.Name FROM Person.Person AS p JOIN Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID JOIN Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID WHERE pnt.PhoneNumberTypeID = 3; We'll run this query and capture the Actual Execution Plan using SSMS 2017. The changes…
Read More
15Aug 2017 by Grant Fritchey 11 Comments

Bad Parameter Sniffing Decision Flow Chart

SQL Server, SQL Server 2016
Lots of people are confused by how to deal with bad parameter sniffing when it occurs. In an effort to help with this, I'm going to try to make a decision flow chart to walk you through the process. This is a rough, quite rough, first draft. I would love to hear any input. For this draft, I won't address the things I think I've left out. I want to see what you think of the decision flow and what you think might need to be included. Click on it to embiggen. Thanks to the attendees at my SQLSaturday Louisville pre-con for the great questions and the inspiration to get this done. Thank you in advance for any and all feedback.
Read More
09Aug 2017 by Grant Fritchey No Comments

IT/Dev Connections 2017

Database Lifecycle Management, DevOps, SQL Server 2016
I'm very honored to be able to announce that I am going to be speaking at IT/Dev Connections in San Francisco. I'm not just speaking there, I'm presenting an all day seminar on the tools needed for query tuning. The title does say SQL Server 2016, but most of the tools I'll cover can be used be used from SQL Server 2012 to SQL Server 2017. I'll also throw in a few SQL Server 2017 tools just to spice things up. If you're looking for a lot of information about how to get your query tuning done, I'm here to help. I'm also going to be talking about two other favorite topics of mine, DevOps and Monitoring. Please check it out and join me at this event.
Read More
01Aug 2017 by Grant Fritchey 16 Comments

There Is a Magic Button, a Rant

SQL Server, SQL Server 2016, T-SQL
OK guys. I think it's way past time. A bunch of us have been keeping a secret from the rest of you. We know something that you don't. I don't think we should hide this secret from the world any more. Illuminati? Incompetents. Free Masons? I am one, so I already know all those secrets. Bilderbergers, Cthulhu Cultists, MKUltra, New World Order, Rotarians? All of these are nothing compared to the vast conspiracy that I'm about to reveal. We need to just unveil the magic "Run Really Fast" button. We've been keeping that sucker a secret forever. It's been tough. Every so often some unauthorized person almost finds it or a "query tuning expert" (as if that was a real thing) tries to reveal it. But we've kept it secret…
Read More
24Apr 2017 by Grant Fritchey No Comments

Query Hash Values, Plan Guides and the Query Store

SQL Server 2016
I was eating dinner with Hugo Kornelis and we started talking about query hash values. You know, like everyone does at dinner. As we talked about it, I suddenly thought about both Plan Guides and the Query Store. I wondered what happened to the query hash values in that case? Thus are blog posts born. Query Hash and Plan Guides The behavior of the query hash itself is fairly straight forward. The text of the query is run through a hashing algorithm within SQL Server and a value comes out, so these two queries: SELECT * FROM Sales.SalesOrderDetail AS sod JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID; SELECT * FROM Sales.SalesOrderDetail AS sod JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID OPTION (FORCE ORDER); Result in two different query…
Read More
13Mar 2017 by Grant Fritchey 9 Comments

Why You Should Change the Cost Threshold for Parallelism

SQL Server, SQL Server 2016
I've written several times about the Cost Threshold for Parallelism and it's relationship to your execution plans, how to determine your execution plan cost, and even how to decide what value to set your Cost Threshold to. What I haven't explicitly addressed in extremely clear terms is why you should adjust your Cost Threshold for Parallelism. There are two reasons to modify this value. Cost Threshold for Parallelism Default Value The primary reason to change the Cost Threshold for Parallelism is because the default value is not a good choice for the vast majority of systems. The default value is 5. This means that when a query has an estimated cost greater than 5, it may get a parallel execution plan. Microsoft set the default value for the Cost Threshold…
Read More
28Feb 2017 by Grant Fritchey 19 Comments

Determining the Cost Threshold for Parallelism

SQL Server, SQL Server 2016
In the previous post, I showed how you can get full listings of your execution plan costs. Knowing what the values you're dealing with for the estimated costs on your execution plans can help you determine what the Cost Threshold on your system should be. However, we don't want to just take the average and use that. You need to understand the data you're looking at. Let's explore this just a little using R. Mean, Median, Range and Standard Deviation I've used the queries in the previous blog post to generate a full listing of costs for my plans. With that, I can start to query the information. Here's how I could use R to begin to explore the data: library("RODBC", lib.loc="~/R/win-library/3.2") query <- "SELECT * FROM dbo.QueryCost;" dbhandle <-…
Read More
20Feb 2017 by Grant Fritchey 12 Comments

Estimated Costs of All Queries

SQL Server, SQL Server 2016
One question constantly comes up; What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to? What Do Your Plans Cost? I have a question right back at you. What do your plans currently cost? Let's say, for argument's sake, that all your plans have an estimated cost (and all plan costs are estimates, let's please keep that in mind, even on Actual plans) value of 3 or less. Do you need to adjust the cost threshold in this case? Probably not. But the key is, how do you look at the costs for your plans? Unfortunately, there isn't a property in a DMV that shows this value. Instead,…
Read More
07Feb 2017 by Grant Fritchey 4 Comments

Does Query Store Pre-Allocate Space

SQL Server 2016, T-SQL
I love the questions I get while I'm presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I'm not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn't going to be pre-allocated in any way. The space will just get used as and when it's needed, just like any other system table. However, don't take my word for it, let's prove that. The Test Testing whether or not enabling Query Store is straight forward. Here's a query that should give us information rather quickly: CREATE DATABASE QSTest; GO USE…
Read More