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
01Jun 2015 by Grant Fritchey 4 Comments

Parallelism and Columnstore Indexes

T-SQL
Columnstore indexes are fascinating and really cool. Unfortunately, they're adding an interesting new wrinkle to an old problem. What's the Cost Threshold for Parallelism set to on your server? If you just said "The whatsis of whositz?" then the value is 5. The cost threshold is the point at which the estimated cost of an execution plan goes from definitely serial to possibly parallel. This default was set for SQL Server 2000 and hasn't been changed since. I've long argued, loudly, that it's too low. I've suggested changing it to a much higher value. My advice has gone from 35 to 50 and several places in between. You could just look at the median or the mode of costs on your system and use the higher of those values as…
Read More
05Aug 2013 by Grant Fritchey 19 Comments

Be Cautious Offering Guidance

Azure, SQL Server, T-SQL
Guidance is hard. Seriously, you'd think it would be easy. You'd think you say things like, don't shrink your database, most tables should have a clustered index, never go against a Sicilian when death is on the line, don't mix sharks and tornados, and that would be it. You'd be done. But it's not that easy. Even worse, it's SHOCKINGLY easy to get stuff wrong. An example. I was looking at information over at Microsoft Developers Network (MSDN) in the SQL Server Books Online. I was reading through information about wait types when I found this little beauty: Occurs when trying to synchronize the query processor exchange iterator. Know what that is? Yeah, OK, a few of you who memorize wait stats do (shut up Tom), but most of us…
Read More