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.0" Build="9.00.4226.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="2" StatementEstRows="172.741" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="23.9038" StatementText="SELECT attx.intAttributeID,attx.txtAttributeName,attx.txtAttributeLabel,attx.txtType,attx.txtEntity FROM
 AST_tblAttributes attx WHERE attx.intAttributeID 
 IN
 (SELECT Distinct([TargetAttributeID]) FROM
 (SELECT att1.intAttributeID as [TargetAttributeID]
 FROM AST_tblAttributes att1
 INNER JOIN
 AST_lnkProfileDemandAttributes pda
 ON pda.intAttributeID=att1.intAttributeID AND pda.intProfileID = @intProfileID
 union all
 SELECT ca2.intAttributeID as [TargetAttributeID] FROM
 AST_lnkCapturePolicyAttributes ca2
 INNER JOIN
 AST_lnkEmployeeCapture ec2 ON ec2.intAdminCaptureID = ca2.intAdminCaptureID AND ec2.intTeamID = 57
 WHERE ec2.dteCreatedDate >= @cutoffdate) x)" StatementType="SELECT">
- <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
- <QueryPlan DegreeOfParallelism="0" MemoryGrant="439" CachedPlanSize="39">
- <RelOp AvgRowSize="84" EstimateCPU="0.00643028" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="129.217" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="23.9038">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Merge ManyToMany="false">
- <InnerSideJoinColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Column="Union1010" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_tblAttributes].[intAttributeID] as [attx].[intAttributeID]=[Union1010]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="15" EstimateCPU="0.00151382" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="129.217" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="23.8924">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Column="Union1010" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="15" EstimateCPU="0.494257" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="129.217" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="23.8796">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <MemoryFractions Input="0" Output="0" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Column="Union1010" />
- </HashKeysBuild>
- <BuildResidual>
- <ScalarOperator ScalarString="[Union1010] = [Union1010]">
- <Compare CompareOp="IS">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Column="Union1010" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </BuildResidual>
- <RelOp AvgRowSize="15" EstimateCPU="0.0075198" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="75198" LogicalOp="Concatenation" NodeId="4" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="23.3853">
- <OutputList>
- <ColumnReference Column="Union1010" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="9080" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Concat>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Union1010" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="15" EstimateCPU="0.000180724" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43.2353" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0197208">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OuterReferences>
- <RelOp AvgRowSize="15" EstimateCPU="0.000204559" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="43.2353" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00332956">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Index="[PK_AST_lnkProfileDemandAttributes]" Alias="[pda]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intProfileID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[@intProfileID]">
- <Identifier>
- <ColumnReference Column="@intProfileID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="42.1146" EstimateRewinds="0.120737" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0162105">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Index="[PK_AST_tblAttributes]" Alias="[att1]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[att1]" Column="intAttributeID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkProfileDemandAttributes].[intAttributeID] as [pda].[intAttributeID]">
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkProfileDemandAttributes]" Alias="[pda]" Column="intAttributeID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.314147" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="75154.7" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="23.3581">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="9080" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <NestedLoops Optimized="true" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- <ColumnReference Column="Expr1011" />
- </OuterReferences>
- <RelOp AvgRowSize="23" EstimateCPU="0.189446" EstimateIO="0.665347" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9416.59" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.854794">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="3193" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Index="[idxAST_lnkEmployeeCapturedteCreatedDate]" Alias="[ec2]" />
- <SeekPredicates>
- <SeekPredicate>
- <StartRange ScanType="GE">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="dteCreatedDate" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[@cutoffdate]">
- <Identifier>
- <ColumnReference Column="@cutoffdate" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </StartRange>
- </SeekPredicate>
- </SeekPredicates>
- <Predicate>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkEmployeeCapture].[intTeamID] as [ec2].[intTeamID]=(57)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intTeamID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(57)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="15" EstimateCPU="0.000165779" EstimateIO="0.003125" EstimateRebinds="9415.55" EstimateRewinds="0.042674" EstimateRows="7.9811" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="22.1065">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="9080" ActualEndOfScans="3193" ActualExecutions="3193" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAttributeID" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Index="[PK_AST_lnkCapturePolicyAttributes]" Alias="[ca2]" />
- <SeekPredicates>
- <SeekPredicate>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkCapturePolicyAttributes]" Alias="[ca2]" Column="intAdminCaptureID" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[CCMI].[dbo].[AST_lnkEmployeeCapture].[intAdminCaptureID] as [ec2].[intAdminCaptureID]">
- <Identifier>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_lnkEmployeeCapture]" Alias="[ec2]" Column="intAdminCaptureID" />
- </Identifier>
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekPredicate>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Concat>
- </RelOp>
- </Hash>
- </RelOp>
- </Sort>
- </RelOp>
- <RelOp AvgRowSize="84" EstimateCPU="0.000443" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="260" LogicalOp="Clustered Index Scan" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00504948">
- <OutputList>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="71" ActualEndOfScans="0" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="intAttributeID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeName" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtAttributeLabel" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtType" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Alias="[attx]" Column="txtEntity" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[CCMI]" Schema="[dbo]" Table="[AST_tblAttributes]" Index="[PK_AST_tblAttributes]" Alias="[attx]" />
- </IndexScan>
- </RelOp>
- </Merge>
- </RelOp>
- <ParameterList>
- <ColumnReference Column="@cutoffdate" ParameterRuntimeValue="'2012-04-01 00:00:00.000'" />
- <ColumnReference Column="@intProfileID" ParameterRuntimeValue="(16)" />
- </ParameterList>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement