The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism.
The default is set to 5, which is a really low setting. Although there is no right and wrong number, I’d recommend starting with this setting at 50 or so and tuning up or down as appropriate.
The following script returns a list of parallel query plans and their subtree costs which will help you decide an appropriate number.
SQL Server ignores the cost threshold for parallelism value under the following conditions:
- Your computer has only one processor.
- Only a single CPU is available to SQL Server because of the affinity mask configuration option.
- The max degree of parallelism option is set to 1.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), core AS ( SELECT eqp.query_plan AS [QueryPlan], ecp.plan_handle [PlanHandle], q.[Text] AS [Statement], n.value('(@StatementOptmLevel)', 'VARCHAR(25)') AS OptimizationLevel , ISNULL(CAST(n.value('(@StatementSubTreeCost)', 'VARCHAR(128)') as float),0) AS SubTreeCost , ecp.usecounts [UseCounts], ecp.size_in_bytes [SizeInBytes] FROM sys.dm_exec_cached_plans AS ecp CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q CROSS APPLY query_plan.nodes ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn ( n ) ) SELECT TOP 100 QueryPlan, PlanHandle, [Statement], OptimizationLevel, SubTreeCost, UseCounts, SubTreeCost * UseCounts [GrossCost], SizeInBytes FROM core ORDER BY --GrossCost DESC SubTreeCost DESC
Fore more details: Cost Threshold for Parallelism Option