Hi thereI have a query and then I ran Execution Plan. Before I am running tuning the indexing etc etc as per Database Engine Tuning Advisor, I write down the COST OF LAST SELECT statement (ON THE LEFT). Then I do the tuning etc etc and write down the COST again. What I found that the COST is higher though but on screen is abit faster ?!!? I am looking at right one though by looking at last select statement as the total time execution?I am appreciated your comment.Here's the execution plan:<?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.3077.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple> <StoredProc ProcName="brp_TPXSRCST01_ServiceByCostReport"> <Statements> <StmtSimple StatementCompId="3" StatementId="2" StatementText="-- =============================================#xD;#xA;-- Author: <Author,,Name>#xD;#xA;-- Create date: <Create Date,,>#xD;#xA;-- Description: <Description,,>#xD;#xA;-- =============================================#xD;#xA;CREATE PROCEDURE [dbo].[brp_TPXSRCST01_ServiceByCostReport]#xD;#xA;(#xD;#xA; @TenantID INT,#xD;#xA; @BatchID INT,#xD;#xA; @OrganisationNodeID INT#xD;#xA;)#xD;#xA;AS#xD;#xA;BEGIN#xD;#xA; SET NOCOUNT ON;#xD;#xA;#xD;#xA;" StatementType="SET ON/OFF" /> <StmtSimple StatementCompId="4" StatementEstRows="157.589" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="0.27331" StatementText=" SELECT a.APartyNo, #xD;#xA; SUM(a.CostNetExGST * (c.SplitPercentage / 100)) AS SplitCostNetExGST#xD;#xA; FROM#xD;#xA;#xD;#xA; (SELECT BatchID, APartyNo, CostNetExGST#xD;#xA; FROM prf_BatchItems#xD;#xA; WHERE batchID = @BatchID AND #xD;#xA; Code1 = 'DS' AND #xD;#xA; IsActive = 1 ) a #xD;#xA; INNER JOIN brp_Services b ON a.APartyNo = b.ServiceNo AND a.BatchID = b.BatchID#xD;#xA; INNER JOIN brp_ServiceChartAccounts c ON b.ServiceID = c.ServiceID #xD;#xA; INNER JOIN brp_ChartAccounts d ON c.ChartAccountID = d.ChartAccountID#xD;#xA; INNER JOIN brp_ChartAccountOrganisationNodes e ON d.ChartAccountID = e.ChartAccountID#xD;#xA; WHERE b.IsActive = 1 AND#xD;#xA; b.TenantID = @TenantID AND#xD;#xA; c.IsActive = 1 AND#xD;#xA; c.TenantID = @TenantID AND#xD;#xA; d.IsActive = 1 AND#xD;#xA; d.TenantID = @TenantID AND#xD;#xA; e.IsActive = 1 AND#xD;#xA; e.TenantID = @TenantID#xD;#xA; GROUP BY a.APartyNo#xD;#xA; ORDER BY SplitCostNetExGST DESC#xD;#xA;#xD;" 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 CachedPlanSize="71" CompileTime="161" CompileCPU="32" CompileMemory="992"> <RelOp AvgRowSize="37" EstimateCPU="0.00189467" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="157.589" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.27331"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Column="Expr1012" /> </OutputList> <MemoryFractions Input="0.285714" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1012" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="37" EstimateCPU="0.000440427" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="157.589" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.260154"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Column="Expr1012" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1012" /> <ScalarOperator ScalarString="CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1020" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1021" /> </Identifier> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="37" EstimateCPU="0.000440427" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="157.589" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.260154"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Column="Expr1020" /> <ColumnReference Column="Expr1021" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1020" /> <ScalarOperator ScalarString="COUNT_BIG([Expr1017]*[Expr1018])"> <Aggregate AggType="COUNT_BIG" Distinct="false"> <ScalarOperator> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1017" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1021" /> <ScalarOperator ScalarString="SUM([Expr1017]*[Expr1018])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1017" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> </GroupBy> <RelOp AvgRowSize="34" EstimateCPU="0.00878352" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="602.721" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.259713"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Column="Expr1017" /> <ColumnReference Column="Expr1018" /> </OutputList> <MemoryFractions Input="1" Output="0.714286" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="34" EstimateCPU="0.000110902" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="602.721" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.239669"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Column="Expr1017" /> <ColumnReference Column="Expr1018" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1017" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(decimal(19,4),[EUCTelcoDB_DEV_2].[dbo].[prf_BatchItems].[CostNetExGST],0)"> <Convert DataType="decimal" Precision="19" Scale="4" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="CostNetExGST" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="33" EstimateCPU="0.00251937" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1109.02" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.239558"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="CostNetExGST" /> <ColumnReference Column="Expr1018" /> </OutputList> <NestedLoops Optimized="false" WithUnorderedPrefetch="true"> <OuterReferences> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceNo" /> <ColumnReference Column="Expr1019" /> </OuterReferences> <RelOp AvgRowSize="25" EstimateCPU="0.000272397" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="65.1667" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0547277"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceNo" /> <ColumnReference Column="Expr1018" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> </OuterReferences> <RelOp AvgRowSize="20" EstimateCPU="0.0203934" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="65.1667" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.0347774"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> <ColumnReference Column="Expr1018" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ChartAccountID" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ServiceChartAccounts].[ChartAccountID] as [c].[ChartAccountID]=[EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccountOrganisationNodes].[ChartAccountID] as [e].[ChartAccountID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ChartAccountID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00658204"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </OuterReferences> <RelOp AvgRowSize="24" EstimateCPU="0.0001636" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032886"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </DefinedValue> </DefinedValues> <Object Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Index="[PK_brp_ChartAccountOrganisationNodes]" Alias="[e]" /> <Predicate> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccountOrganisationNodes].[TenantID] as [e].[TenantID]=CONVERT_IMPLICIT(bigint,[@TenantID],0) AND [EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccountOrganisationNodes].[IsActive] as [e].[IsActive]=(1)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="TenantID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="ConstExpr1016"> <ScalarOperator> <Convert DataType="bigint" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@TenantID" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </ColumnReference> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="IsActive" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="11" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="TenantID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="IsActive" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="TenantID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="IsActive" /> </DefinedValue> </DefinedValues> <Object Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Index="[PK_adt_ChartAccounts]" Alias="[d]" /> <SeekPredicates> <SeekPredicate> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="ChartAccountID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccountOrganisationNodes].[ChartAccountID] as [e].[ChartAccountID]"> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccountOrganisationNodes]" Alias="[e]" Column="ChartAccountID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccounts].[TenantID] as [d].[TenantID]=CONVERT_IMPLICIT(bigint,[@TenantID],0) AND [EUCTelcoDB_DEV_2].[dbo].[brp_ChartAccounts].[IsActive] as [d].[IsActive]=(1)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="TenantID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="ConstExpr1015"> <ScalarOperator> <Convert DataType="bigint" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@TenantID" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </ColumnReference> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ChartAccounts]" Alias="[d]" Column="IsActive" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="37" EstimateCPU="3.91E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="391" LogicalOp="Compute Scalar" NodeId="12" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0074549"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ChartAccountID" /> <ColumnReference Column="Expr1018" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1018" /> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ServiceChartAccounts].[SplitPercentage] as [c].[SplitPercentage]/(100.)"> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="SplitPercentage" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(100.)" /> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="37" EstimateCPU="0.0005871" EstimateIO="0.0068287" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="391" LogicalOp="Clustered Index Scan" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0074158"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ChartAccountID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="SplitPercentage" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ChartAccountID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="SplitPercentage" /> </DefinedValue> </DefinedValues> <Object Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Index="[PK_adt_ServiceNoCASplits]" Alias="[c]" /> <Predicate> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ServiceChartAccounts].[TenantID] as [c].[TenantID]=CONVERT_IMPLICIT(bigint,[@TenantID],0) AND [EUCTelcoDB_DEV_2].[dbo].[brp_ServiceChartAccounts].[IsActive] as [c].[IsActive]=(1)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="TenantID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="ConstExpr1014"> <ScalarOperator> <Convert DataType="bigint" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@TenantID" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </ColumnReference> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="IsActive" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="20" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="64.1667" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="18" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0196779"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceNo" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceNo" /> </DefinedValue> </DefinedValues> <Object Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Index="[IDX_brp_Services]" Alias="[b]" /> <SeekPredicates> <SeekPredicate> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="TenantID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="BatchID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="IsActive" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@TenantID],0)"> <Identifier> <ColumnReference Column="ConstExpr1013"> <ScalarOperator> <Convert DataType="bigint" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="@TenantID" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </ColumnReference> </Identifier> </ScalarOperator> <ScalarOperator ScalarString="[@BatchID]"> <Identifier> <ColumnReference Column="@BatchID" /> </Identifier> </ScalarOperator> <ScalarOperator ScalarString="(1)"> <Const ConstValue="(1)" /> </ScalarOperator> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_ServiceChartAccounts].[ServiceID] as [c].[ServiceID]"> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_ServiceChartAccounts]" Alias="[c]" Column="ServiceID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="28" EstimateCPU="0.000167174" EstimateIO="0.003125" EstimateRebinds="64.1667" EstimateRewinds="0" EstimateRows="9.24892" LogicalOp="Index Seek" NodeId="19" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.182311"> <OutputList> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="CostNetExGST" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="CostNetExGST" /> </DefinedValue> </DefinedValues> <Object Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Index="[IDX_prf_BatchItems]" /> <SeekPredicates> <SeekPredicate> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="BatchID" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="Code1" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="IsActive" /> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[prf_BatchItems]" Column="APartyNo" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[@BatchID]"> <Identifier> <ColumnReference Column="@BatchID" /> </Identifier> </ScalarOperator> <ScalarOperator ScalarString="'DS'"> <Const ConstValue="'DS'" /> </ScalarOperator> <ScalarOperator ScalarString="(1)"> <Const ConstValue="(1)" /> </ScalarOperator> <ScalarOperator ScalarString="[EUCTelcoDB_DEV_2].[dbo].[brp_Services].[ServiceNo] as [b].[ServiceNo]"> <Identifier> <ColumnReference Database="[EUCTelcoDB_DEV_2]" Schema="[dbo]" Table="[brp_Services]" Alias="[b]" Column="ServiceNo" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </ComputeScalar> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </Sort> </RelOp> <ParameterList> <ColumnReference Column="@TenantID" ParameterCompiledValue="(2)" /> <ColumnReference Column="@BatchID" ParameterCompiledValue="(50)" /> </ParameterList> </QueryPlan> </StmtSimple> </Statements> </StoredProc> </StmtSimple> </Statements> </Batch> </BatchSequence></ShowPlanXML>
Understanding of Execution Plan
Posted - 2009-05-27 : 03:52:18
