Why You Should Change the Cost Threshold for Parallelism

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, 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

Determining the Cost Threshold for Parallelism

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, 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

Estimated Costs of All Queries

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, 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

Does Query Store Pre-Allocate Space

SQL Server 2016, TSQL
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

Data About Execution Plans

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I've talked about the data available there and how important it is in this older post. Checking out the properties of a plan you're working on is a fundamental part of tuning that plan. What happens when you don't know which plan you should be working on? What do you do, for example, if you want to see all the plans that are currently using ARITHABORT=FALSE or some other plan affecting setting? The "easy" answer to this question is to run an XQuery against the XML of the query plan itself. You can identify these properties and retrieve the appropriate values from within the plan. However, XQuery consumes quite a…
Read More

Query Optimizer and Data Definition Language Queries

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
Data Definition Language queries don't go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement. I'm working with a team of people to completely rewrite the SQL Server Execution Plans book. We'll probably be published in April or May. It's going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let's answer the question about Data Definition Language. First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build…
Read More

Database Configuration

Azure, SQL Server 2016
It's amazing just how much the landscape changed with the release of SQL Server 2016 SP1. For example, I just found out that you can disable parameter sniffing at the database level using the database configuration. Not only does this work for SQL Server 2016 SP1, but it's enabled for Azure SQL Database. How Database Configuration Works The syntax is very simple and documented here. So, if I want to disable parameter sniffing for a single database, I can do this: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; That's it. Done. It works from within the database and doesn't require rebooting or anything else. Changing this setting does flush the cache of all the execution plans for that database. No other actions are necessary. You can control parameter sniffing at the…
Read More

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

Azure, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled? This is my favorite kind of question because the answer is simple: I don't know. Parameter Sniffing For those who don't know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely. Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots…
Read More

Presentations for SQL Server Beginners

Azure, PASS, PowerShell, Professional Development, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
Read More

Database Clone

SQL Server 2014, SQL Server 2016, TSQL
There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It's moving all the data, not just once, but twice. You move the data when you export it and you move it again when import it (same thing with backup and restore). That makes these methods slow for larger databases. How can you create a database clone without moving the data multiple times? Don't Move the Data At All New with SQL Server 2016, expanded in SP1, and added to SQL Server 2014 SP2 is a new command, DBCC CLONEDATABASE. This is like a dream come true. The use is extremely…
Read More