Sql-server – SQL Server using Parallel Plan for Cost less than 5

parallelismsql serversql-server-2008-r2

I have a pivot query that uses a parallel plan even though the cost is less than 5.

The Cost Threshold for Parallelism is set to default of 5, and MAXDOP is 0.

SELECT Column1, Column2 Column3, Column4 AS Column5, Column6 Column7, Column8 Column9, Column10 Column11
        FROM (
              SELECT Column12,
                        Column13,
                        Column1
                FROM Database1.Schema1.Object1
             ) Object2 PIVOT( SUM(Column13) FOR Column12 IN (Column2, Column4, Column6, Column8, Column10) ) AS pvtScore;

Following screenshot shows the estimated subtree cost as 1.39.

I am wondering why the parallel plan was used when the cost threshold is at 5 for parallelism to kick in.

NUTS

Version: SQL Server 2008 R2

Best Answer

This can be confusing when you're first looking at parallel plans. The thing to keep in mind is that the cost based optimizer picks what it estimates will be the cheaper query to execute. It's not always right, but hey, it's not rocket science.

As a simple example, here are two count queries. One is allowed to go parallel, the other is forced to run serially.

SELECT COUNT(*)
FROM dbo.Posts AS p;

SELECT COUNT(*)
FROM dbo.Posts AS p
OPTION(MAXDOP 1);

Their query plans have the information we need:

NUTS

NUTS

The serial plan costs 121 query bucks, and the parallel plan costs 69.

Related Question