2

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

4
  • 5
    The cost of the parallel plan is less 5, the cost of the serial plan is probably higher. Jun 8, 2017 at 13:08
  • 1
    You are right!! Cost of serial plan was 6.72. Jun 8, 2017 at 14:10
  • There you go. That's confusing at first for most people. Jun 8, 2017 at 14:16
  • Check this: brentozar.com/archive/2014/11/…
    – CR241
    Jun 8, 2017 at 16:48

1 Answer 1

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.