Cost threshold for parallelism? What should the number be?

Before we get started, Let’s start with a description from (msdn)

“Use the cost threshold for parallelism option to specify the threshold at which Microsoft 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 cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Only set cost threshold for parallelism on symmetric multiprocessors.”

The next question is, What do I set my cost threshold for parallelism? Also as Kendra Little  calls it “query bucks” . I hate to re invite the wheel, However I found a great blog post from Jared Karney  that has an excellent script that will help you decide what your cost threshold for parallelism is. Below is also the query that Jared Karney wrote.

 

CREATE TABLE #SubtreeCost(StatementSubtreeCost DECIMAL(18,2));
;WITH XMLNAMESPACES
INSERT INTO #SubtreeCost
SELECT
    CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,2))
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;
SELECT StatementSubtreeCost
FROM #SubtreeCost
ORDER BY 1;
SELECT AVG(StatementSubtreeCost) AS AverageSubtreeCost
FROM #SubtreeCost;
SELECT
    ((SELECT TOP 1 StatementSubtreeCost
    FROM
        (
        SELECT TOP 50 PERCENT StatementSubtreeCost
        FROM #SubtreeCost
        ORDER BY StatementSubtreeCost ASC
        ) AS A
    ORDER BY StatementSubtreeCost DESC
    )
    +
    (SELECT TOP 1 StatementSubtreeCost
    FROM
        (
        SELECT TOP 50 PERCENT StatementSubtreeCost
        FROM #SubtreeCost
        ORDER BY StatementSubtreeCost DESC
        ) AS A
    ORDER BY StatementSubtreeCost ASC))
    /2 AS MEDIAN;
SELECT TOP 1 StatementSubtreeCost AS MODE
FROM   #SubtreeCost
GROUP  BY StatementSubtreeCost
ORDER  BY COUNT(1) DESC;
DROP TABLE #SubtreeCost;
You can run this on production it will not cause blocking or deadlocking, However on a busy system it can take up to 15-20 minutes to run.  This query will look at your cached query plans, cached query text, the amount of memory taken by the cached plans, and the reuse count of the cached plans, returns the Showplan XML format  and then aggregates  Average, median and Mode of the Subtree Cost. If you look below in my production server below you will see the results.
As the great Brent Ozar says “Always Test!”. You can start with 50? But the query above will you give some guidance.
Now you decide to change the value you have two options (TSQL or GUI)
TSQL:

sp_configure ‘show advanced options’, 1;

GO

reconfigure;

GO

sp_configure ‘cost threshold for parallelism’, 50;– Insert new value here

GO

reconfigure;

GO

 

GUI