Query subtree costs justify parallelism, but it doesn't

Question

Query subtree costs justify parallelism, but it doesn't

Indianrock

SSC-Insane

Points: 20333
More actions
August 8, 2016 at 7:05 am

#315965

After testing I've raised our production cost threshold for parallelism from the default of 5 to 25 as a first step. Sql enterprise 64-bit, 64 logical processors, 512 GB of memory. Looking at one query today where each of the subTrees had cost beyond 25, it appears that Parallel=0 in all cases.
Why would this query not multi-thread? I can post the plan and entire xml if desired.
Line 10: <RelOp NodeId="1" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="1.98089e+006" EstimateIO="0" EstimateCPU="868.732" AvgRowSize="1500" EstimatedTotalSubtreeCost="6184.44" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 77: <RelOp NodeId="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1.65018e+006" EstimateIO="0" EstimateCPU="1.23614" AvgRowSize="27" EstimatedTotalSubtreeCost="360.902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 82: <RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.57528e+006" EstimateIO="0" EstimateCPU="77.7244" AvgRowSize="31" EstimatedTotalSubtreeCost="359.665" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 102: <RelOp NodeId="4" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="2.57528e+006" EstimateIO="0" EstimateCPU="77.7244" AvgRowSize="31" EstimatedTotalSubtreeCost="359.665" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 136: <RelOp NodeId="6" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="2.73976e+006" EstimateIO="0" EstimateCPU="110.543" AvgRowSize="26" EstimatedTotalSubtreeCost="281.941" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 185: <RelOp NodeId="7" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="2.73976e+006" EstimateIO="95.1674" EstimateCPU="9.37599" AvgRowSize="44" EstimatedTotalSubtreeCost="104.543" TableCardinality="1.27035e+008" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 290: <RelOp NodeId="8" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="8.52349e+006" EstimateIO="43.1586" EstimateCPU="9.37599" AvgRowSize="34" EstimatedTotalSubtreeCost="52.5346" TableCardinality="1.27035e+008" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Line 353: <RelOp NodeId="20" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1.27035e+008" EstimateIO="4815.07" EstimateCPU="139.739" AvgRowSize="1500" EstimatedTotalSubtreeCost="4954.81" TableCardinality="1.27035e+008" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
Search "Parallel=" (0 hits in 0 files)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Can't say w/o looking at the actual execution plan... But... If I were to hazard a bling guess, I'd look for scalar functions and/or multi-statement table valued functions.

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

I see now that it is doing a group by Having at the end.

FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID IN (SELECT AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM (SELECT AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.CLIENT_ID = (10) AND AccountOwnershipDocSummary02.STATUS NOT IN ( 'NO_ACCOUNT_PL', 'CLOSED_DE'))) GROUP BY AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID HAVING ((COUNT(*) > (1)))) AS ScalarQueryTable)))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Indianrock (8/8/2016)
I see now that it is doing a group by Having at the end.
FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID IN (SELECT AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM (SELECT AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.CLIENT_ID = (10) AND AccountOwnershipDocSummary02.STATUS NOT IN ( 'NO_ACCOUNT_PL', 'CLOSED_DE'))) GROUP BY AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID HAVING ((COUNT(*) > (1)))) AS ScalarQueryTable)))

Not sure what your point is here 🙂

Can you post the actual execution plan of an affected query as a .sqlplan attachment please? Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

Indianrock

SSC-Insane

Points: 20333

August 8, 2016 at 7:58 am

#1894340

Attached the plan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Indianrock (8/8/2016)
Attached the plan

How long does it take this whopper to execute?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Indianrock SSC-Insane Points: 20333 More actions · Answer 6

This appears to be one that runs in the middle of the night only. 226 seconds last night. Since we're in the process of adjusting cost threshold for parallelism, I'm trying to evaluate what's happening now after the initial bump from 5 to 25. Since our main prod database is mixed oltp/olap, CTFP is now being tested at 40 in QA. Initially it was tested at 25.

I definitely realize not all CX Packet waits are bad, ( we seem to run with cxpacket in the 50% of waits range ) but kept reading that with modern hardware, the default of 5 for CTFP was probably bad.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Indianrock (8/8/2016)
This appears to be one that runs in the middle of the night only. 226 seconds last night. Since we're in the process of adjusting cost threshold for parallelism, I'm trying to evaluate what's happening now after the initial bump from 5 to 25. Since our main prod database is mixed oltp/olap, CTFP is now being tested at 40 in QA. Initially it was tested at 25.
I definitely realize not all CX Packet waits are bad, ( we seem to run with cxpacket in the 50% of waits range ) but kept reading that with modern hardware, the default of 5 for CTFP was probably bad.

I can't see anything in the plan which would inhibit parallelism and this query should be multithreaded, it's shoving a ton of data around.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Indianrock SSC-Insane Points: 20333 More actions · Answer 8

I see two other similar plans in the cache with a high number of executions/uses and quick runtimes. This bad boy has only one use and slow runtime. The slow one is the only one trying to do a group by having count(*)>1

FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID IN (SELECT AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM (SELECT AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID20 AND AccountOwnershipDocSummary02.STATUS NOT IN ( @DerivedTable01_STATUS41, @DerivedTable01_STATUS52))) GROUP BY AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID HAVING ((COUNT(*) > @DerivedTable01_83))) AS ScalarQueryTable)))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Indianrock (8/8/2016)
I see two other similar plans in the cache with a high number of executions/uses and quick runtimes. This bad boy has only one use and slow runtime. The slow one is the only one trying to do a group by having count(*)>1
FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID IN (SELECT AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM (SELECT AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0 FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 WHERE ((AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID20 AND AccountOwnershipDocSummary02.STATUS NOT IN ( @DerivedTable01_STATUS41, @DerivedTable01_STATUS52))) GROUP BY AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID HAVING ((COUNT(*) > @DerivedTable01_83))) AS ScalarQueryTable)))

Applying a little formatting:

FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02

WHERE ((

AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID IN (

SELECT AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0

FROM ( -- ScalarQueryTable

SELECT AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID AS AccountOwnershipDocSummary02_ACCOUNT_MODIFIED_MANUFACTURER_ID0

FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02

WHERE ((

AccountOwnershipDocSummary02.CLIENT_ID = @DerivedTable01_CLIENT_ID20

AND AccountOwnershipDocSummary02.STATUS NOT IN ( @DerivedTable01_STATUS41, @DerivedTable01_STATUS52)

))

GROUP BY AccountOwnershipDocSummary02.ACCOUNT_MODIFIED_MANUFACTURER_ID

HAVING ((COUNT(*) > @DerivedTable01_83))

) AS ScalarQueryTable

) -- in

)) -- where

The heavily nested selects, overlong table and column aliases and lack of formatting indicate that the author of this code was inexperienced at writing TSQL. You've pointed out that the statement is expensive. If that's an issue then consider a rewrite. If it isn't an issue, then format the code to bring it in line with your coding standards including documenting your performance findings, but don't change it, not even the aliases.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Indianrock SSC-Insane Points: 20333 More actions · Answer 10

Almost all of our application code is ORM-generated, originally in C# code. ( entity framework type stuff )

Are you saying the table/column aliases etc actually have a bearing on performance? I'm sure the formatting doesn't.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Indianrock (8/17/2016)
Almost all of our application code is ORM-generated, originally in C# code. ( entity framework type stuff )
Are you saying the table/column aliases etc actually have a bearing on performance? I'm sure the formatting doesn't.

No, not at all. I'm saying the code looks like it was written by a tool.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden