25Mar 2019 by Grant Fritchey 1 Comment

Can You Force A Parallel Plan in Query Store?

SQL Server 2016, SQL Server 2017
I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven't a clue. The trick I think is going to be in setting up the test. Let's try it out. Reliably Getting a Parallel Plan Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I've decided to cheat. I'll take advantage of a little functionality that ensures I see a parallel plan when I want to. Here's my code: DBCC TRACEON(8649); GO SELECT soh.OrderDate, soh.ShipDate, sod.OrderQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID = 43705; GO DBCC TRACEOFF(8649); Traceflag 8649 will force all plans to go parallel by effectively making the Cost Threshold for…
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