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
04Dec 2017 by Grant Fritchey 12 Comments

Why I Use AdventureWorks for Demos

SQL Server, SQL Server 2016, SQL Server 2017
I know that when some people see AdventureWorks, their vision turns all red around the edges, their blood pressure spikes and they begin to foam at the mouth. I do understand. AdventureWorks, Microsoft's very old, near ubiquitous, sample database suffers from a lot off issues. What's The Matter With AdventureWorks? Let's start off with the general design of the schemas. It's broken up in odd ways. Production has a TransactionHistory and a TransactionHistoryArchive instead of Sales? That doesn't make any sense. What the heck is up with the general database design anyway? I mean, pick on one example. A person can have more than one phone number. Fine. But, what about people who share phone numbers like that ancient land line in my house that is only EVER used by…
Read More
27Nov 2017 by Grant Fritchey 6 Comments

Database Fundamentals #15: Modifying Data With T-SQL

Database Fundamentals, SQL Server, SQL Server 2016, SQL Server 2017
The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to use the GUI to get that done, it's not a very efficient mechanism. T-SQL is efficient. UPDATE The command for updating information in your tables is UPDATE. This command doesn’t work the same way as the INSERT statement. Instead of listing all the columns that are required, meaning columns that don’t allow for NULL values, you can pick and choose the individual columns that you want to update. The operation over-writes the information that was stored in the column with new information. In addition to defining the table and columns you want to update, you have to tell SQL Server which rows you’re interested in updating. This introduces the WHERE…
Read More
20Nov 2017 by Grant Fritchey 3 Comments

Get That Profiler Feel in Extended Events

SQL Server, SQL Server 2016, SQL Server 2017
I know. You love Profiler. I hear you. You're wrong, but that's OK. Kidding... mostly. Unfortunately though, I think a lot of what passes for issues and problems with Extended Events is actually a lack of knowledge about how they work. Let's take an example and run with it. No Grid in Extended Events One of the pushbacks I hear about using Extended Events is that the Live Data GUI just doesn't have that neat Profiler grid output. Instead you see a list of events in the top pane and then you have to look at the details in the bottom pane. It looks like this out of the gate: You're right. That's a royal pain. That's it. Toss Extended Events. Back to Profiler. Well, hang on a second. Let's…
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
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
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