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.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="86.0046" StatementText="SELECT 
	L.LoanID	
FROM dbo.Company C, dbo.Loan L 
	JOIN dbo.Status S on L.LoanID = S.LoanID
	JOIN dbo.Participation P on L.LoanID = P.LoanID
	JOIN dbo.Delinquent D on L.LoanID = D.LoanID
	JOIN dbo.Property Pr on Pr.Loanid = L.Loanid
	JOIN dbo.MailingAddress ma ON ma.LoanID = L.LoanID	
	LEFT JOIN dbo.BorrowerPhonePivot bp ON bp.loanid = l.loanid
WHERE s.primstat=1 AND DATEADD(d,16,L.DueDate) <= C.TransPostDate

" StatementType="SELECT" QueryHash="0x97D0BAB9E77D370A" QueryPlanHash="0x8C74B48C742E3DD2">
- <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="4" MemoryGrant="96320" CachedPlanSize="264" CompileTime="2466" CompileCPU="2346" CompileMemory="25496">
- <RelOp AvgRowSize="21" EstimateCPU="0.0285026" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="86.0046">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10363" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Parallelism>
- <RelOp AvgRowSize="21" EstimateCPU="3.91524E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="1" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="85.9761">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="2641" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="2604" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2567" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2551" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </OuterReferences>
- <RelOp AvgRowSize="21" EstimateCPU="2.45657" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="2" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="28.7971">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="0.292352" Output="0.270831" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2604" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2641" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2551" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2567" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Participation].[LoanID] as [P].[LoanID]=[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="33" EstimateCPU="2.56964" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="99916.6" LogicalOp="Bitmap Create" NodeId="3" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="24.1148">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2604" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2641" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2551" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2567" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Bitmap>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bitmap1069" />
- </DefinedValue>
- </DefinedValues>
- <HashKeys>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </HashKeys>
- <RelOp AvgRowSize="33" EstimateCPU="2.56964" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="99916.6" LogicalOp="Inner Join" NodeId="4" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="24.1148">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="0.300678" Output="0.707648" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2604" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2641" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2551" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2567" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]=[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="33" EstimateCPU="3.44356" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="102677" LogicalOp="Bitmap Create" NodeId="5" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="19.6874">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2603" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2639" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2548" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2562" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Bitmap>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bitmap1068" />
- </DefinedValue>
- </DefinedValues>
- <HashKeys>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeys>
- <RelOp AvgRowSize="33" EstimateCPU="3.44356" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="102677" LogicalOp="Inner Join" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="19.6874">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="0.490748" Output="0.699322" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="2639" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="2603" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2548" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2562" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID] AND [Expr1056]<=[Service_Prod].[dbo].[Company].[TransPostDate] as [C].[TransPostDate]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="LE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Expr1056" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="29" EstimateCPU="3.5305" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="170588" LogicalOp="Bitmap Create" NodeId="7" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="13.028">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Bitmap>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bitmap1067" />
- </DefinedValue>
- </DefinedValues>
- <HashKeys>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeys>
- <RelOp AvgRowSize="29" EstimateCPU="3.5305" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="170588" LogicalOp="Inner Join" NodeId="8" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13.028">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="0.494552" Output="0.509252" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Delinquent].[LoanID] as [D].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="29" EstimateCPU="3.57416" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="171887" LogicalOp="Bitmap Create" NodeId="9" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="7.32783">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Bitmap>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bitmap1066" />
- </DefinedValue>
- </DefinedValues>
- <HashKeys>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeys>
- <RelOp AvgRowSize="29" EstimateCPU="3.57416" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="171887" LogicalOp="Inner Join" NodeId="10" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.32783">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="1" Output="0.505448" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Status].[LoanID] as [S].[LoanID]=[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="29" EstimateCPU="0.403317" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="175568" LogicalOp="Bitmap Create" NodeId="11" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="1.57615">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Bitmap>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Bitmap1065" />
- </DefinedValue>
- </DefinedValues>
- <HashKeys>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </HashKeys>
- <RelOp AvgRowSize="29" EstimateCPU="0.403317" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="175568" LogicalOp="Repartition Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.57615">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="44476" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44413" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="44745" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="29" EstimateCPU="0.366938" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="175568" LogicalOp="Inner Join" NodeId="13" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.17283">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="178572" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <RelOp AvgRowSize="15" EstimateCPU="0.0285021" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="14" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0317852">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="RoundRobin">
- <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="15" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" Column="TransPostDate" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Alias="[C]" IndexKind="Heap" />
- </TableScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- <RelOp AvgRowSize="21" EstimateCPU="0.193282" EstimateIO="0.580829" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="175568" LogicalOp="Index Seek" NodeId="16" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.774111" TableCardinality="439764">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="178572" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- <RunTimeCountersPerThread Thread="4" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Index="[IDX_Status_PrimStat_INCLoanID]" Alias="[S]" TableReferenceId="1" IndexKind="NonClustered" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[S]" Column="PrimStat" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Bitmap>
- </RelOp>
- <RelOp AvgRowSize="21" EstimateCPU="0.740375" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="440142" LogicalOp="Repartition Streams" NodeId="17" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2.17751">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="44938" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="45463" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44875" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="45232" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="21" EstimateCPU="0.242157" EstimateIO="1.19498" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="440142" LogicalOp="Index Scan" NodeId="18" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.43713" TableCardinality="440142">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="2" ActualRows="52977" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="42989" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="43214" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="41328" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Index="[aaaaaProperty_PK]" Alias="[Pr]" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="PROBE([Bitmap1065],[Service_Prod].[dbo].[Property].[LoanID] as [Pr].[LoanID])">
- <Intrinsic FunctionName="PROBE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bitmap1065" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Property]" Alias="[Pr]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- </Bitmap>
- </RelOp>
- <RelOp AvgRowSize="21" EstimateCPU="0.7395" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439601" LogicalOp="Repartition Streams" NodeId="20" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2.16967">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="44936" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="45463" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="45229" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44873" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="21" EstimateCPU="0.241859" EstimateIO="1.18831" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439601" LogicalOp="Index Scan" NodeId="21" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.43017" TableCardinality="439601">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="2" ActualRows="42403" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="41548" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="51965" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="44585" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Index="[aaaaaDelinquent_PK]" Alias="[D]" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="PROBE([Bitmap1066],[Service_Prod].[dbo].[Delinquent].[LoanID] as [D].[LoanID])">
- <Intrinsic FunctionName="PROBE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bitmap1066" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Delinquent]" Alias="[D]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- </Bitmap>
- </RelOp>
- <RelOp AvgRowSize="29" EstimateCPU="0.0219877" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439754" LogicalOp="Compute Scalar" NodeId="23" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3.21586">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Column="Expr1056" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1056" />
- <ScalarOperator ScalarString="dateadd(day,(16),[Service_Prod].[dbo].[Loan].[DueDate] as [L].[DueDate])">
- <Intrinsic FunctionName="dateadd">
- <ScalarOperator>
- <Const ConstValue="(4)" />
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(16)" />
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="DueDate" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="29" EstimateCPU="0.96732" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439754" LogicalOp="Repartition Streams" NodeId="24" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="3.19387">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="DueDate" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="45462" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="44936" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="45229" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="44873" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="29" EstimateCPU="0.241943" EstimateIO="1.98461" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439754" LogicalOp="Index Scan" NodeId="25" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.22655" TableCardinality="439754">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="DueDate" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="4" ActualRows="47138" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="41997" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="46918" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="44447" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="DueDate" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Index="[_dta_index_Loan_43_K1_23]" Alias="[L]" TableReferenceId="1" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="PROBE([Bitmap1067],[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID])">
- <Intrinsic FunctionName="PROBE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bitmap1067" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </Hash>
- </RelOp>
- </Bitmap>
- </RelOp>
- <RelOp AvgRowSize="21" EstimateCPU="0.739993" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439906" LogicalOp="Repartition Streams" NodeId="30" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.85774">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="2714" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="2667" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2599" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2624" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="21" EstimateCPU="0.242027" EstimateIO="0.875718" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439906" LogicalOp="Index Scan" NodeId="31" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.11774" TableCardinality="439906">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="2" ActualRows="2361" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2300" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2746" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="3197" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Index="[StatusMailingAddress]" Alias="[ma]" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="PROBE([Bitmap1068],[Service_Prod].[dbo].[MailingAddress].[LoanID] as [ma].[LoanID])">
- <Intrinsic FunctionName="PROBE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bitmap1068" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[MailingAddress]" Alias="[ma]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- </Bitmap>
- </RelOp>
- <RelOp AvgRowSize="20" EstimateCPU="0.725522" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="440352" LogicalOp="Repartition Streams" NodeId="33" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2.22573">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2666" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2711" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2596" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2619" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Parallelism PartitioningType="Hash">
- <PartitionColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </PartitionColumns>
- <RelOp AvgRowSize="20" EstimateCPU="0.242272" EstimateIO="1.25794" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="440352" LogicalOp="Index Scan" NodeId="34" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.50021" TableCardinality="440352">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="2" ActualRows="2408" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2954" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="1" ActualRows="2662" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2568" ActualEndOfScans="1" ActualExecutions="1" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Index="[Reference17]" Alias="[P]" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="PROBE([Bitmap1069],[Service_Prod].[dbo].[Participation].[LoanID] as [P].[LoanID])">
- <Intrinsic FunctionName="PROBE">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Bitmap1069" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Participation]" Alias="[P]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Intrinsic>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- </Parallelism>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="9" EstimateCPU="2.06065E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.87332" LogicalOp="Aggregate" NodeId="36" Parallel="true" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="57.179">
- <OutputList />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2538" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2577" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2488" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2477" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <StreamAggregate>
- <DefinedValues />
- <GroupBy>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </GroupBy>
- <RelOp AvgRowSize="21" EstimateCPU="0.207265" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.87332" LogicalOp="Filter" NodeId="37" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="57.179">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="5289" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="5390" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="2" ActualRows="5156" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="4" ActualRows="5201" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Filter StartupExpression="false">
- <RelOp AvgRowSize="21" EstimateCPU="14.3582" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="431801" LogicalOp="Inner Join" NodeId="38" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="56.9718">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </OutputList>
- <MemoryFractions Input="0.729169" Output="0.729169" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="3" ActualRows="1208254859" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="1" ActualRows="1191327396" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="2" ActualRows="1174399933" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="4" ActualRows="1167079949" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="BorrowerID" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[BorrowerPhone].[LoanID] as [bp].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]=[Service_Prod].[dbo].[BorrowerPhone].[AssmRecCounter] as [bp].[AssmRecCounter] AND [Service_Prod].[dbo].[Borrower].[BorrowerID] as [b].[BorrowerID]=[Service_Prod].[dbo].[BorrowerPhone].[BorrowerID] as [bp].[BorrowerID]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="AssmRecCounter" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="BorrowerID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="35" EstimateCPU="1.49241" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="258435" LogicalOp="Inner Join" NodeId="39" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="35.0748">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </OutputList>
- <MemoryFractions Input="0" Output="0" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="716047920" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="726222180" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="2" ActualRows="705873660" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="4" ActualRows="701473980" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Column="CompanyKey" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="CompanyKey" />
- </HashKeysProbe>
- <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="40" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Column="CompanyKey" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2604" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2641" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2551" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2567" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Column="CompanyKey" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Company]" Index="[aaaaaCompany_PK]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="36" EstimateCPU="1.58243" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="268776" LogicalOp="Inner Join" NodeId="41" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="33.5791">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="CompanyKey" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="716047920" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="726222180" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="701473980" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="705873660" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[s]" Column="LoanID" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Status].[LoanID] as [s].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[s]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="22" EstimateCPU="0.483897" EstimateIO="2.53942" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="175568" LogicalOp="Index Scan" NodeId="42" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="3.02332" TableCardinality="439764">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[s]" Column="LoanID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="465001488" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="471608652" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="455537172" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="458394324" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[s]" Column="LoanID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Index="[IDX_Status_LoanIDPrimStat_INCVariousColumns]" Alias="[s]" TableReferenceId="2" IndexKind="NonClustered" />
- <Predicate>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Status].[PrimStat] as [s].[PrimStat]=(1)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Status]" Alias="[s]" Column="PrimStat" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="36" EstimateCPU="2.4135" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="562505" LogicalOp="Inner Join" NodeId="43" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="28.7623">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="CompanyKey" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="1770420540" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="1795576285" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="1734386635" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="1745264795" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Borrower].[LoanID] as [b].[LoanID] AND [Service_Prod].[dbo].[Loan].[AssmRecCounter] as [l].[AssmRecCounter]=[Service_Prod].[dbo].[Borrower].[AssmRecCounter] as [b].[AssmRecCounter]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="AssmRecCounter" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="23" EstimateCPU="0.483886" EstimateIO="23.132" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="439754" LogicalOp="Clustered Index Scan" NodeId="44" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="23.6159" TableCardinality="439754">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="CompanyKey" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="1145119416" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="1161390314" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="1121812454" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="1128848518" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="AssmRecCounter" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="CompanyKey" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Index="[PK_Loan]" Alias="[l]" TableReferenceId="2" IndexKind="Clustered" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="23" EstimateCPU="0.748242" EstimateIO="1.98461" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="680077" LogicalOp="Index Scan" NodeId="45" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.73285" TableCardinality="680077">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="1770933528" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="1796096562" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="4" ActualRows="1734889182" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="2" ActualRows="1745770494" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="LoanID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="AssmRecCounter" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Alias="[b]" Column="BorrowerID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[Borrower]" Index="[aaaaaBorrower_PK]" Alias="[b]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- </Merge>
- </RelOp>
- </Merge>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="23" EstimateCPU="1.17784" EstimateIO="6.3609" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1070620" LogicalOp="Index Seek" NodeId="49" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="7.53874" TableCardinality="1071390">
- <OutputList>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="LoanID" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="AssmRecCounter" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="BorrowerID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="1" ActualRows="2787907500" ActualEndOfScans="2604" ActualExecutions="2604" />
- <RunTimeCountersPerThread Thread="3" ActualRows="2827520625" ActualEndOfScans="2641" ActualExecutions="2641" />
- <RunTimeCountersPerThread Thread="2" ActualRows="2748294375" ActualEndOfScans="2567" ActualExecutions="2567" />
- <RunTimeCountersPerThread Thread="4" ActualRows="2731164375" ActualEndOfScans="2551" ActualExecutions="2551" />
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="LoanID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="AssmRecCounter" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="BorrowerID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Index="[_dta_index_BorrowerPhone_43_K5_K12_K2_K1_K3_K11_K15_6_10_14]" Alias="[bp]" IndexKind="NonClustered" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="PhoneType" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="ForeignPhone" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="PhoneType" />
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[BorrowerPhone]" Alias="[bp]" Column="ForeignPhone" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </Hash>
- </RelOp>
- <Predicate>
- <ScalarOperator ScalarString="[Service_Prod].[dbo].[Loan].[LoanID] as [l].[LoanID]=[Service_Prod].[dbo].[Loan].[LoanID] as [L].[LoanID]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[l]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Service_Prod]" Schema="[dbo]" Table="[Loan]" Alias="[L]" Column="LoanID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </Filter>
- </RelOp>
- </StreamAggregate>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Parallelism>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement