Skip to content

Instantly share code, notes, and snippets.

@manseuk

manseuk/Query execution plan 2

Created August 1, 2016 10:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save manseuk/5ff86ad9d5f4e75eb40b3689063a8274 to your computer and use it in GitHub Desktop.
Save manseuk/5ff86ad9d5f4e75eb40b3689063a8274 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Query execution plan 2
<?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="12.0.2569.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0531493" StatementText="UPDATE [workflow_request] set [current_state_id] = @1,[status_id] = @2,[updated_by_id] = @3,[updated] = getdate() WHERE [id]=@4" StatementType="UPDATE" QueryHash="0xC1FA92AB094E0CDC" QueryPlanHash="0x6F2483D76A5DCB0F" RetrievedFromCache="true">
<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="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="64" CompileTime="16" CompileCPU="11" CompileMemory="672">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="206704" EstimatedPagesCached="51676" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="1.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Assert" NodeId="1" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0531493">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0531491">
<OutputList>
<ColumnReference Column="Expr1019" />
<ColumnReference Column="Pass1020" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Pass1022" />
<ColumnReference Column="Expr1023" />
<ColumnReference Column="Pass1024" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1023" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Pass1024" />
</DefinedValue>
</DefinedValues>
<PassThru>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[updated_by_id] IS NULL">
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</PassThru>
<OuterReferences>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1023" />
</ProbeColumn>
<RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0498619">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
<ColumnReference Column="Expr1019" />
<ColumnReference Column="Pass1020" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Pass1022" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1021" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Pass1022" />
</DefinedValue>
</DefinedValues>
<PassThru>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[status_id] IS NULL">
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</PassThru>
<OuterReferences>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1021" />
</ProbeColumn>
<RelOp AvgRowSize="16" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0465746">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
<ColumnReference Column="Expr1019" />
<ColumnReference Column="Pass1020" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1019" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Pass1020" />
</DefinedValue>
</DefinedValues>
<PassThru>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[current_state_id] IS NULL">
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</PassThru>
<OuterReferences>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
</OuterReferences>
<ProbeColumn>
<ColumnReference Column="Expr1019" />
</ProbeColumn>
<RelOp AvgRowSize="19" EstimateCPU="4E-06" EstimateIO="0.04" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Update" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0432873">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Index="[PK__workflow__3213E83F10C4CD86]" IndexKind="Clustered" Storage="RowStore" />
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Index="[IDX_39DB192F98A046EB]" IndexKind="NonClustered" Storage="RowStore" />
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Index="[IDX_39DB192F6BF700BD]" IndexKind="NonClustered" Storage="RowStore" />
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Index="[IDX_39DB192F896DBBDE]" IndexKind="NonClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[current_state_id] = [@1],[simprov4].[dbo].[workflow_request].[status_id] = [@2],[simprov4].[dbo].[workflow_request].[updated_by_id] = [@3],[simprov4].[dbo].[workflow_request].[updated] = RaiseIfNullUpdate([Expr1002])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@2" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@3" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="31" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032833">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1016" />
<ColumnReference Column="Expr1017" />
<ColumnReference Column="Expr1018" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="[Expr1016]">
<Identifier>
<ColumnReference Column="Expr1016" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="[Expr1017]">
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="[Expr1018]">
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="31" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032833">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1016" />
<ColumnReference Column="Expr1017" />
<ColumnReference Column="Expr1018" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(6),getdate(),0)">
<Identifier>
<ColumnReference Column="ConstExpr1015">
<ScalarOperator>
<Convert DataType="datetime2" Scale="6" Style="0" Implicit="true">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="CASE WHEN CASE WHEN [simprov4].[dbo].[workflow_request].[current_state_id] = [@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="CASE WHEN CASE WHEN [simprov4].[dbo].[workflow_request].[status_id] = [@2] THEN (1) ELSE (0) END THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@2" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="CASE WHEN CASE WHEN [simprov4].[dbo].[workflow_request].[updated_by_id] = [@3] THEN (1) ELSE (0) END THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@3" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="8" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
<OutputList>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</DefinedValue>
</DefinedValues>
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Index="[PK__workflow__3213E83F10C4CD86]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@4],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@4" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="27" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="623">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_state]" Index="[PK__workflow__3213E83F2C989420]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_state]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[current_state_id]">
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="current_state_id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="17">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[simprov4]" Schema="[dbo]" Table="[workflow_request_status]" Index="[PK__workflow__3213E83F160E7295]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request_status]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[status_id]">
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="status_id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="29" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues />
<Object Database="[simprov4]" Schema="[dbo]" Table="[core_user]" Index="[PK__core_use__3213E83F49ECA6DF]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[core_user]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[simprov4].[dbo].[workflow_request].[updated_by_id]">
<Identifier>
<ColumnReference Database="[simprov4]" Schema="[dbo]" Table="[workflow_request]" Column="updated_by_id" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN NOT [Pass1020] AND [Expr1019] IS NULL THEN (0) ELSE CASE WHEN NOT [Pass1022] AND [Expr1021] IS NULL THEN (1) ELSE CASE WHEN NOT [Pass1024] AND [Expr1023] IS NULL THEN (2) ELSE NULL END END END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Pass1020" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Pass1022" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1021" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Logical Operation="NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Pass1024" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="IS NULL">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1023" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
<ParameterList>
<ColumnReference Column="@3" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@2" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@1" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@4" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment