SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MAX Degree of Parallelism Option Not working in Resource Governer


MAX Degree of Parallelism Option Not working in Resource Governer

Author
Message
adhiman
adhiman
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 152
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42266 Visits: 19526
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?
Cool
adhiman
adhiman
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 152
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>


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search