MAX Degree of Parallelism Option Not working in Resource Governer

• adhiman

  SSC Veteran

  Points: 282

  More actions

  May 29, 2014 at 12:43 pm

  #293832

  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

  SSC Guru

  Points: 182879

  More actions

  May 29, 2014 at 12:51 pm

  #1717523

  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?

  😎

• adhiman

  SSC Veteran

  Points: 282

  More actions

  May 29, 2014 at 1:00 pm

  #1717529

  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>