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

Help Me, Help You, Deliver DevOps

DevOps
I believe in DevOps. Actually, that's a pretty horrible way to put it. It's not about belief, like keeping Tinkerbell alive. I have successfully worked within an environment that implemented a DevOps approach to development, deployment and maintenance. I also provide classes and consulting on how to approach DevOps from the Ops perspective as well as writing books on the topic. Because I've seen the DevOps approach work, and work well, despite the fact that my principal job description is in the Ops side of DevOps, I am a very strong and passionate advocate for DevOps. But! DevOps Despite the fact that I absolutely support the concepts of DevOps, moving development & deployment into the production space, and moving operations into better support of the development space, I frequently find…
Read More

SQL Clone and a Little Competition

Database Lifecycle Management, DevOps, Red Gate Software
Redgate has released a new product, SQL Clone. I've already talked about how excited I am about it. Now, it's your turn to get excited. Our developers evidently had too much time on their hands after building SQL Clone. They created a skill in Alexa that lets you clone a database using voice commands. In fact, I was recently visiting Redgate HQ and was able to try it out and you can watch here. [embed]https://www.youtube.com/watch?v=1zI2Oj9EZM8&feature=youtu.be[/embed] Now, in celebration of the launch of SQL Clone, we want to enable you to do some voice commands of your own. We're giving away five Amazon Echo Dots and one copy of SQL Clone to an overall winner. To enter, go into the comments and let us know what skill you would like to build for Alexa…
Read More

Random Blog Post Challenge

Misc
OK, for no particular reason at all or maybe because I had too much food at lunch or just because Jason Hall gave me the idea, here's a blog post challenge for anyone who wants to take it up. Write a technical blog post that incorporates this image: Link back to here and I'll put together an aggregation and assessment of all the posts. Why this image? It's Tom Baker as Dr. Who. That's reason enough. This is just having a little fun. No ulterior motives, requirements, etc. Let's play!
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

SQL Clone

Tools
Today is the launch of SQL Clone, a great new tool that helps you quickly and easily provision SQL Server databases for development and testing. Oh god, that sounds like marketing speak. To heck with that. Let me tell you why I'm so excited about SQL Clone and why I think you're going to be excited too. Once Upon a Time... Almost two years ago one of the developers here at Redgate called me over. He wanted to show off this neat trick he'd figured out. What I saw was a good-sized database, about 200gb, created on his local instance of SQL Server in about 10 seconds. Now, that's fast. Further, he showed me the files and disk space on his machine, and it was only taking up a few…
Read More

I Don’t Understand

Professional Development
I suspect this may make a few people angry, but I felt the need to share. I was recently asked to go through my contacts and put together calls with some technologists for a series of discussions. OK. I thought about what we'd need, compared it to a mental list of people and what I know about their work in my head and started sending emails. I got in touch with people. Everyone was interested in helping out. It was a win. Later... Well, I talked yesterday about reading that blog post by Susan J. Fowler and the confusion it caused. I just don't understand it. Further, I don't understand it on two levels. Level 1 Acknowledging that I'm the most evil, privileged individual on the planet, cis-gendered white male,…
Read More

Confusion of An Old Technologist

Professional Development
As someone who still actively pursues a career in technology, I'm a little on the older side. I got started in tech when a lot of the people I know were still in elementary school. So please understand that when I write the rest of this little... rant. I read the blog post by Susan J. Fowler the day it was posted. I'm not sure where I found it. It's been running around and around in my head ever since, so I'm finally going to write a couple of posts on this. Let's Start With Age There's at least an implication that with age comes wisdom. Well, I'm living proof that's total BS. However, I will agree that with age comes perspective. The modern age has its problems. I've had…
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

Export All Plans From Cache to a .SQLPLAN File

PowerShell
I was asked if there was an easy way to export all the plans from cache to a .SQLPLAN file. My immediate answer was, "I'm sure there's a PowerShell script out there somewhere." However, rather than do a Gingle search, I figured I'd quickly knock up an example. The Script I've gone minimal on the script. I'm creating a connection to the local instance, defining a command, and returning the data into a data set. From there, since the data set consists of a single column, I'm walking through them all to export out to a file: $Query = 'SELECT deqp.query_plan FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp WHERE deqp.query_plan IS NOT NULL;' $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = 'Server=ServerX\DOJO;Database=master;trusted_connection=true' $PlanQuery = new-object System.Data.SqlClient.SqlCommand $PlanQuery.CommandText = $Query $PlanQuery.Connection…
Read More