Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MAX Degree of Parallelism Option Not working in Resource Governer Expand / Collapse
Author
Message
Posted Thursday, May 29, 2014 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:48 AM
Points: 11, Visits: 106
I am running a query on SQL 2012 Server with the Resource governer setup for my account to have Max DOP option set to 1.
The query still runs in about 1 minute and the execution plan still considers parallelism.
When I explicitly mention the OPTION (MAXDOP 1) , the query runs in 6 seconds.

Can you please help in understanding why this might be happening?
How can i tell by querying DMV's whether my query is using parallelism or not?
I know there was some bug in 2008 regarding this but i believe it is fixed in SQL 2012.
Post #1575782
Posted Thursday, May 29, 2014 12:51 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 2,393, Visits: 6,630
adhiman (5/29/2014)
I am running a query on SQL 2012 Server with the Resource governer setup for my account to have Max DOP option set to 1.
The query still runs in about 1 minute and the execution plan still considers parallelism.
When I explicitly mention the OPTION (MAXDOP 1) , the query runs in 6 seconds.

Can you please help in understanding why this might be happening?
How can i tell by querying DMV's whether my query is using parallelism or not?
I know there was some bug in 2008 regarding this but i believe it is fixed in SQL 2012.

That is interesting, care to share the two execution plans?
Post #1575787
Posted Thursday, May 29, 2014 1:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:48 AM
Points: 11, Visits: 106
I have pasted the XML of the Execution plans :
This one is without explicitly mentioning the option maxdop 1

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2900.56" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="812050" StatementText="SELECT DISTINCT T10.[casetag_id] FROM [wincase].[test_result] INNER JOIN [wincase].[test_case] ON ( [test_result].[case_id] = [test_case].[id] ) INNER JOIN [wincase].[test_result] T3 ON ( [test_case].[id] = T3.[case_id] ) INNER JOIN [wincase].[test_case] T4 ON ( T3.[case_id] = T4.[id] ) INNER JOIN [wincase].[test_case_tags] ON ( T4.[id] = [test_case_tags].[case_id] ) INNER JOIN [wincase].[test_execution] ON ( [test_result].[execution_id] = [test_execution].[id] ) INNER JOIN [wincase].[test_job] ON ( [test_execution].[job_id] = [test_job].[id] ) LEFT OUTER JOIN [wincase].[test_case_tags] T10 ON ( [test_case].[id] = T10.[case_id] ) WHERE ([test_case_tags].[casetag_id] = 1 AND [test_job].[build_id] IN (93033, 93510, 93543, 95326, 95485)) --OPTION (MAXDOP 1) " StatementType="SELECT" QueryHash="0x374B2A5DE42608C7" QueryPlanHash="0xB23F8B2EFCEBC15D" RetrievedFromCache="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" MemoryGrant="39352" CachedPlanSize="160" CompileTime="166" CompileCPU="166" CompileMemory="4816">
<ThreadStat Branches="6" />
<MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="39352" RequiredMemory="5632" DesiredMemory="39352" RequestedMemory="39352" GrantWaitTime="0" GrantedMemory="39352" MaxUsedMemory="8880" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="503278" EstimatedPagesCached="754918" EstimatedAvailableDegreeOfParallelism="4" />
<RelOp AvgRowSize="11" EstimateCPU="0.0298586" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2900.56" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="812050">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="11" EstimateCPU="0.0499815" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2900.56" LogicalOp="Aggregate" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="812050">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<MemoryFractions Input="0.0128114" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id] = [CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="11" EstimateCPU="0.0354505" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="11602.2" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="812050">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</PartitionColumns>
<RelOp AvgRowSize="11" EstimateCPU="762104" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="11602.2" LogicalOp="Partial Aggregate" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="812050">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id] = [CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="11" EstimateCPU="41412.2" EstimateIO="0.300448" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="184096000000" LogicalOp="Left Outer Join" NodeId="4" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="49946.3">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="16393816" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CASE].[wincase].[test_result].[case_id]=[CASE].[wincase].[test_case_tags].[case_id] as [T10].[case_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="15" EstimateCPU="8419.29" EstimateIO="0.375884" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="26532800000" LogicalOp="Inner Join" NodeId="5" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="8532.23">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="8196908" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[CASE].[wincase].[test_result].[case_id]=[CASE].[wincase].[test_result].[case_id] as [T3].[case_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="11" EstimateCPU="36.239" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6467250" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="57.7725">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</PartitionColumns>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="11" EstimateCPU="1.77853" EstimateIO="19.755" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6467250" LogicalOp="Index Scan" NodeId="7" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="21.5335" TableCardinality="6467250">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6211786" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_result]" Index="[test_result_72319564]" Alias="[T3]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="19" EstimateCPU="2.12875" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="110709" LogicalOp="Sort" NodeId="9" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="54.7968">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.237248" Output="0.237248" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="19" EstimateCPU="0.478326" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="110709" LogicalOp="Inner Join" NodeId="10" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="52.6653">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.566829" Output="0.430605" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.0796767" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="85428" LogicalOp="Repartition Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.45004">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</PartitionColumns>
<RelOp AvgRowSize="11" EstimateCPU="0.023532" EstimateIO="1.34683" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="85428" LogicalOp="Index Scan" NodeId="12" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.37036" TableCardinality="85428">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="85428" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case]" Index="[test_case_name]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.314364" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="110709" LogicalOp="Inner Join" NodeId="13" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="50.7369">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.379448" Output="0.288256" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.0777582" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82225.5" LogicalOp="Bitmap Create" NodeId="14" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="0.722072">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<Bitmap>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Opt_Bitmap1023" />
</DefinedValue>
</DefinedValues>
<HashKeys>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeys>
<RelOp AvgRowSize="11" EstimateCPU="0.0777582" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82225.5" LogicalOp="Repartition Streams" NodeId="15" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.722072">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</PartitionColumns>
<RelOp AvgRowSize="15" EstimateCPU="0.0431271" EstimateIO="0.582384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82225.5" LogicalOp="Index Scan" NodeId="16" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.625511" TableCardinality="156683">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="80881" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Index="[test_case_tags_case_id_22782392_uniq]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[CASE].[wincase].[test_case_tags].[casetag_id]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Bitmap>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0316147" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5199.21" LogicalOp="Repartition Streams" NodeId="17" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="49.7004">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</PartitionColumns>
<RelOp AvgRowSize="11" EstimateCPU="0.0287577" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5199.21" LogicalOp="Inner Join" NodeId="18" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="49.6688">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.0409116" Output="0.0310795" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="139" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.0287576" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="430.053" LogicalOp="Bitmap Create" NodeId="19" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="0.0987816">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<Bitmap>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Opt_Bitmap1022" />
</DefinedValue>
</DefinedValues>
<HashKeys>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</HashKeys>
<RelOp AvgRowSize="11" EstimateCPU="0.0287576" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="430.053" LogicalOp="Repartition Streams" NodeId="20" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0987816">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</PartitionColumns>
<RelOp AvgRowSize="11" EstimateCPU="0.000449406" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="430.053" LogicalOp="Inner Join" NodeId="21" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0700239">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.0285231" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="12.3385" LogicalOp="Distribute Streams" NodeId="22" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0318187">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</OutputList>
<Parallelism PartitioningType="RoundRobin">
<RelOp AvgRowSize="11" EstimateCPU="0.000170572" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="12.3385" LogicalOp="Index Seek" NodeId="23" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329557" TableCardinality="125801">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_job]" Index="[test_job_f1f69fd]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93033)">
<Const ConstValue="(93033)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93510)">
<Const ConstValue="(93510)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93543)">
<Const ConstValue="(93543)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(95326)">
<Const ConstValue="(95326)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(95485)">
<Const ConstValue="(95485)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.00019534" EstimateIO="0.003125" EstimateRebinds="11.3385" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="34.8545" LogicalOp="Index Seek" NodeId="24" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0377558" TableCardinality="490828">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="11" ActualExecutions="11" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Index="[test_execution_751f44ae]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="job_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CASE].[wincase].[test_job].[id]">
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</Bitmap>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0326012" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5199.21" LogicalOp="Repartition Streams" NodeId="25" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="49.5413">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</PartitionColumns>
<Predicate>
<ScalarOperator ScalarString="PROBE([Opt_Bitmap1023],[CASE].[wincase].[test_result].[case_id])">
<Intrinsic FunctionName="PROBE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Opt_Bitmap1023" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="15" EstimateCPU="0.00592146" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="5666.47" LogicalOp="Inner Join" NodeId="27" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="49.508">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6467245" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="id" />
<ColumnReference Column="Expr1031" />
</OuterReferences>
<RelOp AvgRowSize="15" EstimateCPU="1.77853" EstimateIO="28.9335" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6467250" LogicalOp="Index Scan" NodeId="30" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="30.712" TableCardinality="6467250">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6467245" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_result]" Index="[test_result_2f4a622d]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="PROBE([Opt_Bitmap1022],[CASE].[wincase].[test_result].[execution_id],N'[IN ROW]')">
<Intrinsic FunctionName="PROBE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Opt_Bitmap1022" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'[IN ROW]'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="5665.47" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="33" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="18.014" TableCardinality="6467250">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6467245" ActualEndOfScans="0" ActualExecutions="6467245" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_result]" Index="[PK__test_res__3213E83F10CB707D]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CASE].[wincase].[test_result].[id]">
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.935509" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="156683" LogicalOp="Repartition Streams" NodeId="47" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.56102">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</PartitionColumns>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="0.0431271" EstimateIO="0.582384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="156683" LogicalOp="Index Scan" NodeId="48" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.625511" TableCardinality="156683">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="136330" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Index="[test_case_tags_case_id_22782392_uniq]" Alias="[T10]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Parallelism>
</RelOp>
</Merge>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>


This is the one with Explicitly mentioning the option maxdop 1
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2900.56" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1478020" StatementText="SELECT DISTINCT T10.[casetag_id] FROM [wincase].[test_result] INNER JOIN [wincase].[test_case] ON ( [test_result].[case_id] = [test_case].[id] ) INNER JOIN [wincase].[test_result] T3 ON ( [test_case].[id] = T3.[case_id] ) INNER JOIN [wincase].[test_case] T4 ON ( T3.[case_id] = T4.[id] ) INNER JOIN [wincase].[test_case_tags] ON ( T4.[id] = [test_case_tags].[case_id] ) INNER JOIN [wincase].[test_execution] ON ( [test_result].[execution_id] = [test_execution].[id] ) INNER JOIN [wincase].[test_job] ON ( [test_execution].[job_id] = [test_job].[id] ) LEFT OUTER JOIN [wincase].[test_case_tags] T10 ON ( [test_case].[id] = T10.[case_id] ) WHERE ([test_case_tags].[casetag_id] = 1 AND [test_job].[build_id] IN (93033, 93510, 93543, 95326, 95485)) OPTION (MAXDOP 1) " StatementType="SELECT" QueryHash="0x700F27DD3531CA79" QueryPlanHash="0x19BDB868DC39C253" RetrievedFromCache="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1146544" CachedPlanSize="144" CompileTime="124" CompileCPU="124" CompileMemory="4512">
<MemoryGrantInfo SerialRequiredMemory="5120" SerialDesiredMemory="1146544" RequiredMemory="5120" DesiredMemory="1146544" RequestedMemory="1146544" GrantWaitTime="0" GrantedMemory="1146544" MaxUsedMemory="18272" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="503278" EstimatedPagesCached="754918" EstimatedAvailableDegreeOfParallelism="1" />
<RelOp AvgRowSize="11" EstimateCPU="1158420" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2900.56" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1478020">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<MemoryFractions Input="0.000378475" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id] = [CASE].[wincase].[test_case_tags].[casetag_id] as [T10].[casetag_id]">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="11" EstimateCPU="289981" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="184096000000" LogicalOp="Right Outer Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="319598">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<MemoryFractions Input="0.924403" Output="0.948982" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="16393816" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</HashKeysProbe>
<RelOp AvgRowSize="15" EstimateCPU="0.172508" EstimateIO="0.582384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="156683" LogicalOp="Index Scan" NodeId="2" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.754893" TableCardinality="156683">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="156683" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="case_id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Alias="[T10]" Column="casetag_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Index="[test_case_tags_case_id_22782392_uniq]" Alias="[T10]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="29486.3" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="26532800000" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="29617.1">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</OutputList>
<MemoryFractions Input="0.0493279" Output="0.0506394" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="8196908" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</HashKeysProbe>
<RelOp AvgRowSize="15" EstimateCPU="1.44197" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="110709" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="103.973">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.0366084" Output="0.012721" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.0941278" EstimateIO="1.34683" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="85428" LogicalOp="Index Scan" NodeId="5" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.44096" TableCardinality="85428">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="85428" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case]" Index="[test_case_name]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="1.46816" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="110709" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="101.09">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.0369513" Output="0.0128401" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</HashKeysProbe>
<RelOp AvgRowSize="15" EstimateCPU="0.172508" EstimateIO="0.582384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82225.5" LogicalOp="Index Scan" NodeId="7" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.754893" TableCardinality="156683">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="80881" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Index="[test_case_tags_case_id_22782392_uniq]" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[CASE].[wincase].[test_case_tags].[casetag_id]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_case_tags]" Column="casetag_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="29.704" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="123258" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="98.7915">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<MemoryFractions Input="0.00203716" Output="0.000707888" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="139" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</HashKeysProbe>
<RelOp AvgRowSize="11" EstimateCPU="0.00179762" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="430.053" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.042849">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</OuterReferences>
<RelOp AvgRowSize="11" EstimateCPU="0.000170572" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="12.3385" LogicalOp="Index Seek" NodeId="10" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329557" TableCardinality="125801">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_job]" Index="[test_job_f1f69fd]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93033)">
<Const ConstValue="(93033)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93510)">
<Const ConstValue="(93510)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(93543)">
<Const ConstValue="(93543)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(95326)">
<Const ConstValue="(95326)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="build_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(95485)">
<Const ConstValue="(95485)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.00019534" EstimateIO="0.003125" EstimateRebinds="11.3385" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="34.8545" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0377558" TableCardinality="490828">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="11" ActualEndOfScans="11" ActualExecutions="11" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Index="[test_execution_751f44ae]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_execution]" Column="job_id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[CASE].[wincase].[test_job].[id]">
<Identifier>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_job]" Column="id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="7.11413" EstimateIO="61.9305" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6467250" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="69.0447" TableCardinality="6467250">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6467245" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="execution_id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_result]" Index="[PK__test_res__3213E83F10CB707D]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="7.11413" EstimateIO="19.755" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6467250" LogicalOp="Index Scan" NodeId="16" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="26.8691" TableCardinality="6467250">
<OutputList>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="6467245" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[CASE]" Schema="[wincase]" Table="[test_result]" Alias="[T3]" Column="case_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[CASE]" Schema="[wincase]" Table="[test_result]" Index="[test_result_72319564]" Alias="[T3]" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>


Post #1575793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse