Cost Threshold for Parallelism

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 ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES  

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 

INSERT INTO master.dbo.PlanCacheForMaxDop

SELECT 

     query_plan AS CompleteQueryPlan,

     n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,

     n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,

     n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,

     n.query('.') AS ParallelSubTreeXML, 

     ecp.usecounts,

     ecp.size_in_bytes 

FROM sys.dm_exec_cached_plans AS ecp

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 BETWEEN 1 AND 5 THEN '1-5'

                 WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN '5-6'

                 WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN '6-7'

                 WHEN StatementSubTreeCost BETWEEN 7 AND 8 THEN '7-8'

                 WHEN StatementSubTreeCost BETWEEN 8 AND 9 THEN '8-9'

                 WHEN StatementSubTreeCost BETWEEN 9 AND 10 THEN '9-10'

                 WHEN StatementSubTreeCost BETWEEN 10 AND 11 THEN '10-11'

                 WHEN StatementSubTreeCost BETWEEN 11 AND 12 THEN '11-12'

                 WHEN StatementSubTreeCost BETWEEN 12 AND 13 THEN '12-13'

                 WHEN StatementSubTreeCost BETWEEN 13 AND 14 THEN '13-14'

                 WHEN StatementSubTreeCost BETWEEN 14 AND 15 THEN '14-15'

                 WHEN StatementSubTreeCost BETWEEN 15 AND 16 THEN '15-16'

                 WHEN StatementSubTreeCost BETWEEN 16 AND 17 THEN '16-17'

                 WHEN StatementSubTreeCost BETWEEN 17 AND 18 THEN '17-18'

                 WHEN StatementSubTreeCost BETWEEN 18 AND 19 THEN '18-19'

                 WHEN StatementSubTreeCost BETWEEN 19 AND 20 THEN '19-20'

                 WHEN StatementSubTreeCost BETWEEN 20 AND 25 THEN '20-25'

                 WHEN StatementSubTreeCost BETWEEN 25 AND 30 THEN '25-30'

                 WHEN StatementSubTreeCost BETWEEN 30 AND 35 THEN '30-35'

                 WHEN StatementSubTreeCost BETWEEN 35 AND 40 THEN '35-40'

                 WHEN StatementSubTreeCost BETWEEN 40 AND 45 THEN '40-45'

                 WHEN StatementSubTreeCost BETWEEN 45 AND 50 THEN '45-50'

                 WHEN StatementSubTreeCost > 50 THEN '>50'

                 ELSE CAST(StatementSubTreeCost AS VARCHAR(100))

            END) AS StatementSubTreeCost ,

        COUNT(*) AS countInstance

FROM    master.dbo.PlanCacheForMaxDop

GROUP BY CASE WHEN StatementSubTreeCost BETWEEN 1 AND 5 THEN 2.5

              WHEN StatementSubTreeCost BETWEEN 5 AND 6 THEN 5.5

              WHEN StatementSubTreeCost BETWEEN 6 AND 7 THEN 6.5

              WHEN StatementSubTreeCost BETWEEN 7 AND 8 THEN 7.5

              WHEN StatementSubTreeCost BETWEEN 8 AND 9 THEN 8.5

              WHEN StatementSubTreeCost BETWEEN 9 AND 10 THEN 9.5

              WHEN StatementSubTreeCost BETWEEN 10 AND 11 THEN 10.5

              WHEN StatementSubTreeCost BETWEEN 11 AND 12 THEN 11.5

              WHEN StatementSubTreeCost BETWEEN 12 AND 13 THEN 12.5

              WHEN StatementSubTreeCost BETWEEN 13 AND 14 THEN 13.5

              WHEN StatementSubTreeCost BETWEEN 14 AND 15 THEN 14.5

              WHEN StatementSubTreeCost BETWEEN 15 AND 16 THEN 15.5

              WHEN StatementSubTreeCost BETWEEN 16 AND 17 THEN 16.5

              WHEN StatementSubTreeCost BETWEEN 17 AND 18 THEN 17.5

              WHEN StatementSubTreeCost BETWEEN 18 AND 19 THEN 18.5

              WHEN StatementSubTreeCost BETWEEN 19 AND 20 THEN 19.5

              WHEN StatementSubTreeCost BETWEEN 10 AND 15 THEN 12.5

              WHEN StatementSubTreeCost BETWEEN 15 AND 20 THEN 17.5

              WHEN StatementSubTreeCost BETWEEN 20 AND 25 THEN 22.5

              WHEN StatementSubTreeCost BETWEEN 25 AND 30 THEN 27.5

              WHEN StatementSubTreeCost BETWEEN 30 AND 35 THEN 32.5

              WHEN StatementSubTreeCost BETWEEN 35 AND 40 THEN 37.5

              WHEN StatementSubTreeCost BETWEEN 40 AND 45 THEN 42.5

              WHEN StatementSubTreeCost BETWEEN 45 AND 50 THEN 47.5

              WHEN StatementSubTreeCost > 50 THEN 100

              ELSE StatementSubTreeCost

         END;

GO

Enjoy!

Chris