Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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.3128.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="9283.13" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="40.0382" StatementText="select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED
from
(
 select
 payment.CLIENT_GUID, payment.PMT_AMOUNT, hist.DATE_COMPLETED, ROW_NUMBER() over (partition by payment.client_guid order by payment.deposit_date desc, payment.create_date_time desc) rn
 from 
 trnPMT payment WITH (NOLOCK)
 inner join trnHistory hist WITH (NOLOCK) on payment.TRANS_GUID = hist.TRANS_GUID
 Where 
 payment.REVISED = 0 and
 payment.mpmt_guid <> '00000000-0000-0000-0000-000000000000'
) pmt
where pmt.rn = 1" StatementType="SELECT" QueryHash="0x513938492847C5F0" QueryPlanHash="0xE144581CC8230C60" 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="1" MemoryGrant="104296" CachedPlanSize="56" CompileTime="3" CompileCPU="3" CompileMemory="456">
- <MissingIndexes>
- <MissingIndexGroup Impact="14.929">
- <MissingIndex Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]">
- <ColumnGroup Usage="EQUALITY">
- <Column Name="[REVISED]" ColumnId="2" />
- </ColumnGroup>
- <ColumnGroup Usage="INEQUALITY">
- <Column Name="[MPMT_GUID]" ColumnId="10" />
- </ColumnGroup>
- <ColumnGroup Usage="INCLUDE">
- <Column Name="[TRANS_GUID]" ColumnId="4" />
- <Column Name="[CLIENT_GUID]" ColumnId="7" />
- <Column Name="[DEPOSIT_DATE]" ColumnId="8" />
- <Column Name="[PMT_AMOUNT]" ColumnId="13" />
- <Column Name="[CREATE_DATE_TIME]" ColumnId="19" />
- </ColumnGroup>
- </MissingIndex>
- </MissingIndexGroup>
- </MissingIndexes>
- <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="104296" RequiredMemory="1536" DesiredMemory="104296" RequestedMemory="104296" GrantWaitTime="0" GrantedMemory="104296" MaxUsedMemory="45792" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="830838" EstimatedPagesCached="207709" EstimatedAvailableDegreeOfParallelism="2" />
- <RelOp AvgRowSize="39" EstimateCPU="0.0936484" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9283.13" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="40.0382">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="14059" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="47" EstimateCPU="0.0156081" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="195101" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="39.9446">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- <ColumnReference Column="Expr1004" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="230881" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <SequenceProject>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1004" />
- <ScalarOperator ScalarString="row_number">
- <Sequence FunctionName="row_number" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="47" EstimateCPU="0.00390202" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="195101" LogicalOp="Segment" NodeId="2" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="39.929">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- <ColumnReference Column="Segment1005" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="230881" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Segment>
- <GroupBy>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- </GroupBy>
- <SegmentColumn>
- <ColumnReference Column="Segment1005" />
- </SegmentColumn>
- <RelOp AvgRowSize="55" EstimateCPU="15.7377" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="195101" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="39.9251">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- </OutputList>
- <Warnings>
- <SpillToTempDb SpillLevel="1" />
- </Warnings>
- <MemoryFractions Input="0.220553" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="230881" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- </OrderByColumn>
- <OrderByColumn Ascending="false">
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- </OrderByColumn>
- <OrderByColumn Ascending="false">
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="55" EstimateCPU="14.8114" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="195101" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="24.1761">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- </OutputList>
- <MemoryFractions Input="1" Output="0.779447" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="230881" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="TRANS_GUID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="TRANS_GUID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[TestDb].[dbo].[trnHistory].[TRANS_GUID] as [hist].[TRANS_GUID]=[TestDb].[dbo].[trnPMT].[TRANS_GUID] as [payment].[TRANS_GUID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="TRANS_GUID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="TRANS_GUID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="80" EstimateCPU="0.27306" EstimateIO="6.56609" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="231168" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="6.83915" TableCardinality="248094">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="TRANS_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="230881" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="TRANS_GUID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CLIENT_GUID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="DEPOSIT_DATE" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="PMT_AMOUNT" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="CREATE_DATE_TIME" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Index="[imp_clpk_trnPMT]" Alias="[payment]" IndexKind="Clustered" />
- <Predicate>
- <ScalarOperator ScalarString="[TestDb].[dbo].[trnPMT].[REVISED] as [payment].[REVISED]=(0) AND [TestDb].[dbo].[trnPMT].[MPMT_GUID] as [payment].[MPMT_GUID]<>{guid'00000000-0000-0000-0000-000000000000'}">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="REVISED" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="NE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnPMT]" Alias="[payment]" Column="MPMT_GUID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="{guid'00000000-0000-0000-0000-000000000000'}" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="31" EstimateCPU="0.553754" EstimateIO="1.7535" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="503270" LogicalOp="Index Scan" NodeId="6" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.30725" TableCardinality="503270">
- <OutputList>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="TRANS_GUID" />
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="503270" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="TRANS_GUID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Alias="[hist]" Column="DATE_COMPLETED" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[TestDb]" Schema="[dbo]" Table="[trnHistory]" Index="[IX_trnHistory_DATE_COMPLETED_TRANS_GUID_TRANS_NUMBER]" Alias="[hist]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- </Hash>
- </RelOp>
- </Sort>
- </RelOp>
- </Segment>
- </RelOp>
- </SequenceProject>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[Expr1004]=(1)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1004" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement