Parallelism, where to start? Well let’s start right at the beginning shall we with the question, what is parallelism? Simply put it is a feature of SQL Server which allows queries to utilize more threads in order to complete quicker. There are several ways to influence SQL Servers decision whether to use parallelism or not and if so how “much” which is referred to as the degree of parallelism.
Cost Threshold For Parallelism
This is a server wide setting which indicates what the minimum query cost has to be before Parallelism is considered by the optimizer (yes UK readers I spelt optimizer with a Z not an S).
Maximum Degree Of Parallelism
This is a server wide setting which indicates the maximum number of processors a query run in parallel can use.
OPTION (MAXDOP X)
This is a query hint which contrary to some beliefs will override the above two settings by reducing the degree of parallelism for queries greater than the cost threshold, but will not make a too small query run in parallel (Thanks to Brent Ozar for pointing this out).
So what settings should you be using, well as always “it depends”, sorry but this really isn’t a one size fits all solution. Servers will have different workloads, number of cores, some will have hard NUMA some will have Soft NUMA some older servers won’t have NUMA at all. What you really need to do is understand the impact parallelism has on your environment, today I will look specifically at the sp_configure option ‘cost threshold for parallelism‘.
SQL Server exposes an increasing wealth of information via DMV’s that allow us to make more informed decisions; one idea that I loved was using these DMV’s specifically sys.dm_exec_cached_plans and sys.dm_exec_query_plan to return information from the Plan Cache for queries run in parallel. This idea I took from Jonathan Kehayias and adapted to group the exposed information by StatementSubTreeCost. This will give you the spread of query costs which you can use to make an educated decision as to if the default cost threshold for parallelism value of 5 is in fact the best for your environment.
/*
-----------------------------------------------------------------
Cost Threshold For Parallelism
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set Database Context
USE master;
GO
-- Create table
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE [object_id] =OBJECT_ID('dbo.PlanCacheForMaxDop')
AND [type] = 'U' )
CREATE TABLE master.dbo.PlanCacheForMaxDop
(
CompleteQueryPlan XML ,
StatementText VARCHAR(4000) ,
StatementOptimizationLevel VARCHAR(25) ,
StatementSubTreeCost FLOAT ,
ParallelSubTreeXML XML ,
UseCounts INT,
PlanSizeInBytes INT
);
ELSE
-- If table exists truncate it before population
TRUNCATE TABLE master.dbo.PlanCacheForMaxDop;
GO
-- Collect parallel plan information
SET TRANSACTION ISOLATIONLEVEL READ UNCOMMITTED;
WITHXMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT
query_plan ASCompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') ASStatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') ASStatementSubTreeCost,
n.query('.') ASParallelSubTreeXML,
ecp.usecounts,
ecp.size_in_bytes
FROM sys.dm_exec_cached_plans ASecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;
GO
-- Return parallel plan information
SELECT CompleteQueryPlan ,
StatementText ,
StatementOptimizationLevel ,
StatementSubTreeCost ,
ParallelSubTreeXML ,
UseCounts ,
PlanSizeInBytes
FROM master.dbo.PlanCacheForMaxDop;
GO
-- Return grouped parallel plan information
SELECT MAX(CASE WHEN StatementSubTreeCost BETWEEN1 AND 5 THEN '1-5'
WHENStatementSubTreeCost BETWEEN 5 AND 6 THEN '5-6'
WHENStatementSubTreeCost BETWEEN 6 AND 7 THEN '6-7'
WHENStatementSubTreeCost BETWEEN 7 AND 8 THEN '7-8'
WHENStatementSubTreeCost BETWEEN 8 AND 9 THEN '8-9'
WHENStatementSubTreeCost BETWEEN 9 AND 10 THEN '9-10'
WHENStatementSubTreeCost BETWEEN 10 AND 11 THEN '10-11'
WHENStatementSubTreeCost BETWEEN 11 AND 12 THEN '11-12'
WHENStatementSubTreeCost BETWEEN 12 AND 13 THEN '12-13'
WHENStatementSubTreeCost BETWEEN 13 AND 14 THEN '13-14'
WHENStatementSubTreeCost BETWEEN 14 AND 15 THEN '14-15'
WHENStatementSubTreeCost BETWEEN 15 AND 16 THEN '15-16'
WHENStatementSubTreeCost BETWEEN 16 AND 17 THEN '16-17'
WHENStatementSubTreeCost BETWEEN 17 AND 18 THEN '17-18'
WHENStatementSubTreeCost BETWEEN 18 AND 19 THEN '18-19'
WHENStatementSubTreeCost BETWEEN 19 AND 20 THEN '19-20'
WHENStatementSubTreeCost BETWEEN 20 AND 25 THEN '20-25'
WHENStatementSubTreeCost BETWEEN 25 AND 30 THEN '25-30'
WHENStatementSubTreeCost BETWEEN 30 AND 35 THEN '30-35'
WHENStatementSubTreeCost BETWEEN 35 AND 40 THEN '35-40'
WHENStatementSubTreeCost BETWEEN 40 AND 45 THEN '40-45'
WHENStatementSubTreeCost BETWEEN 45 AND 50 THEN '45-50'
WHENStatementSubTreeCost > 50 THEN '>50'
ELSECAST(StatementSubTreeCost AS VARCHAR(100))
END) ASStatementSubTreeCost ,
COUNT(*) AS countInstance
FROM master.dbo.PlanCacheForMaxDop
GROUP BY CASE WHEN StatementSubTreeCost BETWEEN1 AND 5 THEN2.5
WHENStatementSubTreeCost BETWEEN 5 AND 6 THEN 5.5
WHENStatementSubTreeCost BETWEEN 6 AND 7 THEN 6.5
WHENStatementSubTreeCost BETWEEN 7 AND 8 THEN 7.5
WHENStatementSubTreeCost BETWEEN 8 AND 9 THEN 8.5
WHENStatementSubTreeCost BETWEEN 9 AND 10 THEN 9.5
WHENStatementSubTreeCost BETWEEN 10 AND 11 THEN 10.5
WHENStatementSubTreeCost BETWEEN 11 AND 12 THEN 11.5
WHENStatementSubTreeCost BETWEEN 12 AND 13 THEN 12.5
WHENStatementSubTreeCost BETWEEN 13 AND 14 THEN 13.5
WHENStatementSubTreeCost BETWEEN 14 AND 15 THEN 14.5
WHENStatementSubTreeCost BETWEEN 15 AND 16 THEN 15.5
WHENStatementSubTreeCost BETWEEN 16 AND 17 THEN 16.5
WHENStatementSubTreeCost BETWEEN 17 AND 18 THEN 17.5
WHENStatementSubTreeCost BETWEEN 18 AND 19 THEN 18.5
WHENStatementSubTreeCost BETWEEN 19 AND 20 THEN 19.5
WHENStatementSubTreeCost BETWEEN 10 AND 15 THEN 12.5
WHENStatementSubTreeCost BETWEEN 15 AND 20 THEN 17.5
WHENStatementSubTreeCost BETWEEN 20 AND 25 THEN 22.5
WHENStatementSubTreeCost BETWEEN 25 AND 30 THEN 27.5
WHENStatementSubTreeCost BETWEEN 30 AND 35 THEN 32.5
WHENStatementSubTreeCost BETWEEN 35 AND 40 THEN 37.5
WHENStatementSubTreeCost BETWEEN 40 AND 45 THEN 42.5
WHENStatementSubTreeCost BETWEEN 45 AND 50 THEN 47.5
WHENStatementSubTreeCost > 50 THEN 100
ELSEStatementSubTreeCost
END;
GO
Enjoy!
Chris
