Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?xml version="1.0" encoding="utf-16"?>
- <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.3000.0">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementText="UPDATE EFT.AssetState
 SET stateID = st.nextStateID
 FROM #Event AS e
 INNER LOOP JOIN
 EFT.AssetState AS ast
 ON ast.assetID = e.assetID
 INNER JOIN
 Transition AS st
 ON st.stateID = ast.stateID
 AND st.eventID = e.eventID
 AND st.MachineID = ast.machineID;
 --
 " StatementId="194" StatementCompId="167" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="3047.26" StatementEstRows="4.29222e+006" StatementOptmLevel="FULL" QueryHash="0x3521FA14FC357BAD" QueryPlanHash="0x82DFFFA1F09E7FD9">
- <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
- <QueryPlan DegreeOfParallelism="4" MemoryGrant="1057800" CachedPlanSize="152" CompileTime="16" CompileCPU="16" CompileMemory="864">
- <ThreadStat Branches="3" UsedThreads="12">
- <ThreadReservation NodeId="0" ReservedThreads="12" />
- </ThreadStat>
- <MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="3563440" RequiredMemory="9440" DesiredMemory="3570848" RequestedMemory="1057800" GrantWaitTime="0" GrantedMemory="1057800" MaxUsedMemory="1041648" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209703" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" />
- <RelOp NodeId="1" PhysicalOp="Sequence" LogicalOp="Sequence" EstimateRows="4.29222e+006" EstimateIO="0" EstimateCPU="8.58445" AvgRowSize="9" EstimatedTotalSubtreeCost="3047.26" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sequence>
- <RelOp NodeId="2" PhysicalOp="Index Update" LogicalOp="Update" EstimateRows="4.29222e+006" EstimateIO="143.484" EstimateCPU="4.29222" AvgRowSize="9" EstimatedTotalSubtreeCost="1542.63" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update WithOrderedPrefetch="1" DMLRequestSort="1">
- <Object Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Index="[IX_AssetState_assetID_stateID]" IndexKind="NonClustered" />
- <SetPredicate>
- <ScalarOperator ScalarString="[assetID1017] = [MerchantReporting].[EFT].[AssetState].[assetID],[busDate1018] = [MerchantReporting].[EFT].[AssetState].[busDate],[machineID1019] = [MerchantReporting].[EFT].[AssetState].[machineID],[stateID1020] = [MerchantReporting].[EFT].[AssetState].[stateID]">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Column="assetID1017" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="busDate1018" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="machineID1019" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="stateID1020" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <ActionColumn>
- <ColumnReference Column="Act1016" />
- </ActionColumn>
- <RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="4.29222e+006" EstimateIO="342.815" EstimateCPU="144.909" AvgRowSize="31" EstimatedTotalSubtreeCost="1394.85" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="0">
- <OrderBy>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Column="Act1016" />
- </OrderByColumn>
- </OrderBy>
- <RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="4.29222e+006" EstimateIO="0" EstimateCPU="1.81227" AvgRowSize="31" EstimatedTotalSubtreeCost="907.126" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Filter StartupExpression="0">
- <RelOp NodeId="6" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="4.76914e+006" EstimateIO="0.00656561" EstimateCPU="0.858902" AvgRowSize="32" EstimatedTotalSubtreeCost="905.314" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2128572" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool>
- <RelOp NodeId="7" PhysicalOp="Split" LogicalOp="Split" EstimateRows="4.76914e+006" EstimateIO="0" EstimateCPU="3.57685" AvgRowSize="32" EstimatedTotalSubtreeCost="1808.04" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2128572" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Split>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Act1016" />
- </DefinedValue>
- </DefinedValues>
- <ActionColumn>
- <ColumnReference Column="Act1016" />
- </ActionColumn>
- <RelOp NodeId="8" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="2.38457e+006" EstimateIO="57.7547" EstimateCPU="2.38457" AvgRowSize="32" EstimatedTotalSubtreeCost="1804.46" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="stateID_OLD" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1064286" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update DMLRequestSort="1">
- <Object Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Index="[PK_AssetState]" IndexKind="Clustered" />
- <SetPredicate>
- <ScalarOperator ScalarString="[MerchantReporting].[EFT].[AssetState].[stateID] = [MerchantReporting].[dbo].[Transition].[nextStateID] as [st].[nextStateID]">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.38457e+006" EstimateIO="0" EstimateCPU="0.238457" AvgRowSize="28" EstimatedTotalSubtreeCost="1744.32" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1064286" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1012" />
- <ScalarOperator ScalarString="[Expr1012]">
- <Identifier>
- <ColumnReference Column="Expr1012" />
- </Identifier>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1013" />
- <ScalarOperator ScalarString="[Expr1013]">
- <Identifier>
- <ColumnReference Column="Expr1013" />
- </Identifier>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.38457e+006" EstimateIO="0" EstimateCPU="0.238457" AvgRowSize="28" EstimatedTotalSubtreeCost="1744.32" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1012" />
- <ScalarOperator ScalarString="CASE WHEN [Expr1010] THEN (1) ELSE (0) END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1010" />
- </Identifier>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1013" />
- <ScalarOperator ScalarString="CASE WHEN [Expr1010] THEN (1) ELSE (0) END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1010" />
- </Identifier>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp NodeId="11" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="2.38457e+006" EstimateIO="0" EstimateCPU="17.8871" AvgRowSize="28" EstimatedTotalSubtreeCost="1744.08" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ColumnReference Column="Expr1010" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1064286" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Parallelism>
- <OrderBy>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp NodeId="12" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="2.38457e+006" EstimateIO="0.00563063" EstimateCPU="115.938" AvgRowSize="28" EstimatedTotalSubtreeCost="1726.2" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ColumnReference Column="Expr1010" />
- </OutputList>
- <MemoryFractions Input="0.42082" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="266421" Batches="0" ActualExecutionMode="Row" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="266139" Batches="0" ActualExecutionMode="Row" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="265775" Batches="0" ActualExecutionMode="Row" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="265951" Batches="0" ActualExecutionMode="Row" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRebinds="0" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Sort Distinct="0">
- <OrderBy>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </OrderByColumn>
- </OrderBy>
- <RelOp NodeId="13" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.38457e+006" EstimateIO="0" EstimateCPU="0.119228" AvgRowSize="28" EstimatedTotalSubtreeCost="1610.25" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ColumnReference Column="Expr1010" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1010" />
- <ScalarOperator ScalarString="CASE WHEN [MerchantReporting].[EFT].[AssetState].[stateID] as [ast].[stateID] = [MerchantReporting].[dbo].[Transition].[nextStateID] as [st].[nextStateID] THEN (1) ELSE (0) END">
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="BINARY IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp NodeId="14" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="2.38457e+006" EstimateIO="0" EstimateCPU="134.242" AvgRowSize="31" EstimatedTotalSubtreeCost="1610.13" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </OutputList>
- <MemoryFractions Input="0.0766756" Output="0.57918" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="4" ActualRows="266139" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="266421" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="265775" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="265951" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ScalarOperator ScalarString="ANY([MerchantReporting].[EFT].[AssetState].[stateID] as [ast].[stateID])">
- <Aggregate Distinct="0" AggType="ANY">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- <ScalarOperator ScalarString="ANY([MerchantReporting].[dbo].[Transition].[nextStateID] as [st].[nextStateID])">
- <Aggregate Distinct="0" AggType="ANY">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </Identifier>
- </ScalarOperator>
- </Aggregate>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <HashKeysBuild>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </HashKeysBuild>
- <BuildResidual>
- <ScalarOperator ScalarString="[MerchantReporting].[EFT].[AssetState].[assetID] as [ast].[assetID] = [MerchantReporting].[EFT].[AssetState].[assetID] as [ast].[assetID] AND [MerchantReporting].[EFT].[AssetState].[busDate] as [ast].[busDate] = [MerchantReporting].[EFT].[AssetState].[busDate] as [ast].[busDate] AND [MerchantReporting].[EFT].[AssetState].[machineID] as [ast].[machineID] = [MerchantReporting].[EFT].[AssetState].[machineID] as [ast].[machineID]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </BuildResidual>
- <RelOp NodeId="15" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="2.8616e+007" EstimateIO="0" EstimateCPU="91.7036" AvgRowSize="31" EstimatedTotalSubtreeCost="1475.89" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="4" ActualRows="266159" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="265971" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="266451" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="265795" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </PartitionColumns>
- <RelOp NodeId="16" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="2.8616e+007" EstimateIO="340.375" EstimateCPU="492.156" AvgRowSize="31" EstimatedTotalSubtreeCost="1384.19" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </OutputList>
- <Warnings>
- <SpillToTempDb SpillLevel="1" />
- </Warnings>
- <MemoryFractions Input="1" Output="0.923324" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="196370" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="177368" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="354762" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="335876" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[MerchantReporting].[dbo].[Transition].[stateID] as [st].[stateID]=[MerchantReporting].[EFT].[AssetState].[stateID] as [ast].[stateID] AND [MerchantReporting].[dbo].[Transition].[eventID] as [st].[eventID]=#Event.[eventID] as [e].[eventID] AND [MerchantReporting].[dbo].[Transition].[machineID] as [st].[machineID]=[MerchantReporting].[EFT].[AssetState].[machineID] as [ast].[machineID]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp NodeId="17" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="2.8616e+007" EstimateIO="0" EstimateCPU="88.8706" AvgRowSize="31" EstimatedTotalSubtreeCost="551.613" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="5970497" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="8375207" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="6937105" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="6389797" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </PartitionColumns>
- <RelOp NodeId="18" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.8616e+007" EstimateIO="0" EstimateCPU="59.8075" AvgRowSize="31" EstimatedTotalSubtreeCost="462.742" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="7262827" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="5710263" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="7166978" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="7532538" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <NestedLoops Optimized="0" WithUnorderedPrefetch="1">
- <OuterReferences>
- <ColumnReference Table="#Event" Alias="[e]" Column="assetID" />
- <ColumnReference Column="Expr1028" />
- </OuterReferences>
- <RelOp NodeId="20" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2.12866e+006" EstimateIO="6.31127" EstimateCPU="1.17084" AvgRowSize="15" EstimatedTotalSubtreeCost="7.48212" TableCardinality="2.12866e+006" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Table="#Event" Alias="[e]" Column="assetID" />
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="558679" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="439251" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="551306" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="579426" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Table="#Event" Alias="[e]" Column="assetID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Table="#Event" Alias="[e]" Column="eventID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[tempdb]" Schema="[dbo]" Table="[#Event]" Index="[IX_Ev_assetID]" Alias="[e]" />
- </IndexScan>
- </RelOp>
- <RelOp NodeId="21" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="13.4432" EstimateIO="0.003125" EstimateCPU="0.000171788" AvgRowSize="27" EstimatedTotalSubtreeCost="395.453" TableCardinality="2.6582e+006" Parallel="1" EstimateRebinds="177380" EstimateRewinds="1.95128e+006" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="7262827" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="558679" ActualExecutions="558679" />
- <RunTimeCountersPerThread Thread="4" ActualRows="5710263" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="439251" ActualExecutions="439251" />
- <RunTimeCountersPerThread Thread="2" ActualRows="7532538" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="579426" ActualExecutions="579426" />
- <RunTimeCountersPerThread Thread="3" ActualRows="7166978" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="551306" ActualExecutions="551306" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="busDate" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="machineID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="stateID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Index="[PK_AssetState]" Alias="[ast]" IndexKind="Clustered" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Alias="[ast]" Column="assetID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="#Event.[assetID] as [e].[assetID]">
- <Identifier>
- <ColumnReference Table="#Event" Alias="[e]" Column="assetID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Parallelism>
- </RelOp>
- <RelOp NodeId="22" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="1214" EstimateIO="0" EstimateCPU="0.0316408" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0436601" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="305" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="318" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="307" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="284" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- </PartitionColumns>
- <RelOp NodeId="23" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1214" EstimateIO="0.0112731" EstimateCPU="0.0007462" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0120193" TableCardinality="1214" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="324" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="293" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="323" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="274" Batches="0" ActualExecutionMode="Row" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="machineID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="eventID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="stateID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Alias="[st]" Column="nextStateID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[MerchantReporting]" Schema="[dbo]" Table="[Transition]" Index="[UK_Transition_machineID_nextStateID_eventID]" Alias="[st]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Sort>
- </RelOp>
- </Parallelism>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Update>
- </RelOp>
- </Split>
- </RelOp>
- </Spool>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="NOT [Expr1012]">
- <Logical Operation="NOT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1012" />
- </Identifier>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </Sort>
- </RelOp>
- </Update>
- </RelOp>
- <RelOp NodeId="44" PhysicalOp="Index Update" LogicalOp="Update" EstimateRows="4.29222e+006" EstimateIO="96.9078" EstimateCPU="4.29222" AvgRowSize="9" EstimatedTotalSubtreeCost="1496.05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Update WithOrderedPrefetch="1" DMLRequestSort="1">
- <Object Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Index="[IX_AssetState_machineID_stateID_assetID]" IndexKind="NonClustered" />
- <SetPredicate>
- <ScalarOperator ScalarString="[assetID1021] = [MerchantReporting].[EFT].[AssetState].[assetID],[busDate1022] = [MerchantReporting].[EFT].[AssetState].[busDate],[machineID1023] = [MerchantReporting].[EFT].[AssetState].[machineID],[stateID1024] = [MerchantReporting].[EFT].[AssetState].[stateID]">
- <ScalarExpressionList>
- <ScalarOperator>
- <MultipleAssign>
- <Assign>
- <ColumnReference Column="assetID1021" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="busDate1022" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="machineID1023" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- <Assign>
- <ColumnReference Column="stateID1024" />
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- </Identifier>
- </ScalarOperator>
- </Assign>
- </MultipleAssign>
- </ScalarOperator>
- </ScalarExpressionList>
- </ScalarOperator>
- </SetPredicate>
- <ActionColumn>
- <ColumnReference Column="Act1016" />
- </ActionColumn>
- <RelOp NodeId="46" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="4.29222e+006" EstimateIO="342.815" EstimateCPU="144.909" AvgRowSize="31" EstimatedTotalSubtreeCost="1394.85" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="0">
- <OrderBy>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- </OrderByColumn>
- <OrderByColumn Ascending="1">
- <ColumnReference Column="Act1016" />
- </OrderByColumn>
- </OrderBy>
- <RelOp NodeId="47" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="4.29222e+006" EstimateIO="0" EstimateCPU="1.81227" AvgRowSize="31" EstimatedTotalSubtreeCost="907.126" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="747348" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Filter StartupExpression="0">
- <RelOp NodeId="48" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="4.76914e+006" EstimateIO="0.00656561" EstimateCPU="0.858902" AvgRowSize="32" EstimatedTotalSubtreeCost="905.314" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
- <OutputList>
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="assetID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="busDate" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="machineID" />
- <ColumnReference Database="[MerchantReporting]" Schema="[EFT]" Table="[AssetState]" Column="stateID" />
- <ColumnReference Column="Expr1012" />
- <ColumnReference Column="Expr1013" />
- <ColumnReference Column="Act1016" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2128572" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Spool PrimaryNodeId="6" />
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="NOT [Expr1013]">
- <Logical Operation="NOT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1013" />
- </Identifier>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </Sort>
- </RelOp>
- </Update>
- </RelOp>
- </Sequence>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement