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.6020.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="7943430000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="10209.5" StatementText="SELECT a.wf_state, 
 b.oid, 
 p.wf_user_id, 
 p.error_no, 
 1 item_followup, 
 p.element_type, 
 0 selected, 
 p.version_no, 
 p.p_description, 
 p.s_description, 
 p.proc_node_id, 
 p.step_node_id, 
 p.distr_type, 
 f.project vH8UEDV7G4L8LF69S9MDU361STQ, 
 d.reg_period vFMW6V8C6WMRLM8Q5E7AE9HFH6G, 
 d.resource_id v6NJXKK021W7LFC7QVQ7XGXKLF8, 
 d.last_update vQRKHFH40C1WLX78GLXPP10CAM4, 
 a.used_hrs vG3K0QF8FT6U4KA2QTCBXVGS0A8 
FROM awfenquiry p, 
 atstsehourdet a, 
 atstsegldetail c, 
 atstseheader d, 
 atschargecode e, 
 atsproject f, 
 atstsehourdetmap b 
WHERE 1 = 1 
 AND a.tsehourdet_id = b.tsehourdet_id 
 AND a.tsegldetail_id = c.tsegldetail_id 
 AND c.tseheader_id = d.tseheader_id 
 AND c.chargecode_id = e.chargecode_id 
 AND c.client = e.client 
 AND e.client = f.client 
 AND e.project = f.project 
 AND ( a.wf_state = N'U' 
 OR p.distr_type = N'U' ) 
 AND p.element_type = N'TS' 
 AND a.client = N'GB' 
 AND b.oid = p.oid " StatementType="SELECT" QueryHash="0x293DBB020F2F53F" QueryPlanHash="0x24C4E378B5CBFE0A" 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="1211704" CachedPlanSize="192" CompileTime="825" CompileCPU="408" CompileMemory="5544">
- <MemoryGrantInfo SerialRequiredMemory="3200" SerialDesiredMemory="1211704" RequiredMemory="3200" DesiredMemory="1211704" RequestedMemory="1211704" GrantWaitTime="0" GrantedMemory="1211704" MaxUsedMemory="752984" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="367001" EstimatedPagesCached="91750" EstimatedAvailableDegreeOfParallelism="2" />
- <RelOp AvgRowSize="672" EstimateCPU="794.343" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="7943430000" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="10209.5">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="error_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="p_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="proc_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="s_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="step_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="version_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="wf_user_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- <ColumnReference Column="Expr1020" />
- <ColumnReference Column="Expr1021" />
- </OutputList>
- <ComputeScalar>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Column="Expr1020" />
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Column="Expr1021" />
- <ScalarOperator ScalarString="(0)">
- <Const ConstValue="(0)" />
- </ScalarOperator>
- </DefinedValue>
- </DefinedValues>
- <RelOp AvgRowSize="663" EstimateCPU="8744.46" EstimateIO="10.3258" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="7943430000" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="9415.19">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="error_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="p_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="proc_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="s_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="step_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="version_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="wf_user_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="167" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Merge ManyToMany="true">
- <InnerSideJoinColumns>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </InnerSideJoinColumns>
- <OuterSideJoinColumns>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- </OuterSideJoinColumns>
- <Residual>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsehourdetmap].[oid] as [b].[oid]=[finance].[dbo].[awfenquiry].[oid] as [p].[oid] AND ([finance].[dbo].[atstsehourdet].[wf_state] as [a].[wf_state]=N'U' OR [finance].[dbo].[awfenquiry].[distr_type] as [p].[distr_type]=N'U')">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Logical Operation="OR">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'U'" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'U'" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Residual>
- <RelOp AvgRowSize="594" EstimateCPU="2.3095" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="33473.8" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3.70741">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="error_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="p_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="proc_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="s_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="step_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="version_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="wf_user_id" />
- </OutputList>
- <MemoryFractions Input="1" Output="0.0332047" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="33556" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="594" EstimateCPU="0.0571656" EstimateIO="1.30461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="33473.8" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="1.36177" TableCardinality="51826">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="error_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="p_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="proc_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="s_description" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="step_node_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="version_no" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="wf_user_id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="33556" ActualRowsRead="51826" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="distr_type" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="error_no" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="oid" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="p_description" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="proc_node_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="s_description" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="step_node_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="version_no" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="wf_user_id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" IndexKind="Heap" />
- <Predicate>
- <ScalarOperator ScalarString="[finance].[dbo].[awfenquiry].[element_type] as [p].[element_type]=N'TS'">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[awfenquiry]" Alias="[p]" Column="element_type" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'TS'" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- </Sort>
- </RelOp>
- <RelOp AvgRowSize="94" EstimateCPU="237.753" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441100" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="656.692">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </OutputList>
- <Warnings>
- <SpillToTempDb SpillLevel="1" SpilledThreadCount="1" />
- <SortSpillDetails GrantedMemoryKb="400288" UsedMemoryKb="400288" WritesToTempDb="27330" ReadsFromTempDb="27330" />
- </Warnings>
- <MemoryFractions Input="0.330802" Output="0.966795" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2732803" ActualEndOfScans="0" ActualExecutions="1" />
- </RunTimeInformation>
- <Sort Distinct="false">
- <OrderBy>
- <OrderByColumn Ascending="true">
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </OrderByColumn>
- </OrderBy>
- <RelOp AvgRowSize="94" EstimateCPU="192.947" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2441100" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="418.928">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </OutputList>
- <MemoryFractions Input="0.658945" Output="0.635993" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2732909" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsehourdet_id" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="tsehourdet_id" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsehourdet].[tsehourdet_id] as [a].[tsehourdet_id]=[finance].[dbo].[atstsehourdetmap].[tsehourdet_id] as [b].[tsehourdet_id]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsehourdet_id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="tsehourdet_id" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="86" EstimateCPU="86.0682" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2449840" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="215.386">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsehourdet_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </OutputList>
- <MemoryFractions Input="0.734133" Output="0.307851" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2735465" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsegldetail_id" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsehourdet].[tsegldetail_id] as [a].[tsegldetail_id]=[finance].[dbo].[atstsegldetail].[tsegldetail_id] as [c].[tsegldetail_id]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsegldetail_id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="69" EstimateCPU="22.8907" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1098290" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="69.5577">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </OutputList>
- <MemoryFractions Input="0.775277" Output="0.195649" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1221685" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="tseheader_id" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tseheader_id" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsegldetail].[tseheader_id] as [c].[tseheader_id]=[finance].[dbo].[atstseheader].[tseheader_id] as [d].[tseheader_id]">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tseheader_id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="tseheader_id" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="56" EstimateCPU="0.361861" EstimateIO="2.89868" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="328822" LogicalOp="Table Scan" NodeId="8" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="3.26054" TableCardinality="328822">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="tseheader_id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="328822" ActualRowsRead="328822" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="last_update" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="reg_period" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="resource_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" Column="tseheader_id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atstseheader]" Alias="[d]" IndexKind="Heap" />
- </TableScan>
- </RelOp>
- <RelOp AvgRowSize="38" EstimateCPU="10.3528" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1128680" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="43.4065">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tseheader_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </OutputList>
- <MemoryFractions Input="0.146669" Output="0.0370133" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1221685" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="chargecode_id" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="chargecode_id" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsegldetail].[client] as [c].[client]=[finance].[dbo].[atsproject].[client] as [f].[client] AND [finance].[dbo].[atstsegldetail].[chargecode_id] as [c].[chargecode_id]=[finance].[dbo].[atschargecode].[chargecode_id] as [e].[chargecode_id]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="client" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="chargecode_id" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="chargecode_id" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="32" EstimateCPU="1.07856" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="21606.6" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.49001">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="chargecode_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </OutputList>
- <MemoryFractions Input="0.191518" Output="0.0448497" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="23980" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="project" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[finance].[dbo].[atschargecode].[client] as [e].[client]=[finance].[dbo].[atsproject].[client] as [f].[client] AND [finance].[dbo].[atschargecode].[project] as [e].[project]=[finance].[dbo].[atsproject].[project] as [f].[project]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="client" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="project" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="28" EstimateCPU="0.0201429" EstimateIO="0.0623843" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="18169" LogicalOp="Index Scan" NodeId="11" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0825272" TableCardinality="18169">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="18169" ActualRowsRead="18169" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="client" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atsproject]" Alias="[f]" Column="project" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atsproject]" Index="[aiatsproject1]" Alias="[f]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="32" EstimateCPU="0.0265383" EstimateIO="0.302384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="23983" LogicalOp="Table Scan" NodeId="12" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.328923" TableCardinality="23983">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="chargecode_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="project" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="23983" ActualRowsRead="23983" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="chargecode_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="client" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" Column="project" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atschargecode]" Alias="[e]" IndexKind="Heap" />
- </TableScan>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="35" EstimateCPU="1.34575" EstimateIO="30.2179" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1223270" LogicalOp="Table Scan" NodeId="14" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="31.5637" TableCardinality="1223270">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="chargecode_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="client" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tseheader_id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="1223270" ActualRowsRead="1223270" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="chargecode_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="client" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tsegldetail_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" Column="tseheader_id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atstsegldetail]" Alias="[c]" IndexKind="Heap" />
- </TableScan>
- </RelOp>
- </Hash>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="48" EstimateCPU="3.0175" EstimateIO="55.4261" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2742920" LogicalOp="Table Scan" NodeId="17" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="58.4436" TableCardinality="2743040">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsegldetail_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsehourdet_id" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2742921" ActualRowsRead="2743043" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsegldetail_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="tsehourdet_id" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="used_hrs" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="wf_state" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" IndexKind="Heap" />
- <Predicate>
- <ScalarOperator ScalarString="[finance].[dbo].[atstsehourdet].[client] as [a].[client]=N'GB'">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdet]" Alias="[a]" Column="client" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'GB'" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="31" EstimateCPU="3.02364" EstimateIO="7.57127" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2748620" LogicalOp="Index Scan" NodeId="19" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="10.5949" TableCardinality="2748620">
- <OutputList>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="tsehourdet_id" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="2748620" ActualRowsRead="2748620" ActualEndOfScans="1" ActualExecutions="1" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="oid" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Alias="[b]" Column="tsehourdet_id" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[finance]" Schema="[dbo]" Table="[atstsehourdetmap]" Index="[aiatstsehourdetmap1]" Alias="[b]" IndexKind="NonClustered" />
- </IndexScan>
- </RelOp>
- </Hash>
- </RelOp>
- </Sort>
- </RelOp>
- </Merge>
- </RelOp>
- </ComputeScalar>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement