Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Huge difference for same operator in different environment


Huge difference for same operator in different environment

Author
Message
sabyasm
sabyasm
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 524

We have the same query performing differently in DEV and QA environment with 99% identical data. DEV was restored from QA few days back. Here is the interesting facts:

1. Both the query process nearly same amount of data.
2. Query in QA takes 1 hour 15 minutes to complete. In DEV the same takes 20 minutes.
3. Exact same execution plan is generated in both cases in terms of operators. However, the sort component costs 41% in QA whereas it takes only 10% in DEV

execution plan:

The query in question is basically a right join and group by to calculate the sum. I am unable to write the complete query as-is as per the security guideline.

My question: is this normal to have so much difference in one perticular operator cost between two environments? (provided everything else remains same)

I have checked the following:
- Table indexes are same (and ordering same too)
- Statistics are updated


Any pointer would be highly appreciated.

Execution plan (text) in both the environment:



|--Table Insert(OBJECTSad[TMP_SalesWeek_AllRow_MTD]), SETSad[dbo.TMP_SalesWeek_AllRow_MTD].[TimeWeekId] = [DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[TimeWeekId] as [s].[TimeWeekId],[dbo.TMP_SalesWeek_AllR
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Stream Aggregate(GROUP BYSad[s].[TimeWeekId], [s].[SalesWeek_CustomerDwId], [s].[SalesWeek_ProductDwId], [s].[SalesWeekSalesType], [s].[SalesWeek_IntOrgDwId]) DEFINESad[Expr1018]=SUM(isnull([Expr1012],(0.0000))), [Expr1019]=SUM(isnull([Ex
|--Sort(ORDER BYSad[s].[TimeWeekId] ASC, [s].[SalesWeek_CustomerDwId] ASC, [s].[SalesWeek_ProductDwId] ASC, [s].[SalesWeekSalesType] ASC, [s].[SalesWeek_IntOrgDwId] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[s].[TimeWeekId], [s].[SalesWeek_CustomerDwId], [s].[SalesWeek_ProductDwId], [s].[SalesWeekSalesType], [s].[SalesWeek_IntOrgDwId]))
|--Hash Match(Right Outer Join, HASHSad[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId], [Expr1022], [DB_Name].[dbo].[DailySales].[DailySales_ProductDwId], [Expr1025], [Expr1027])=([s].[SalesWeek_CustomerDwI
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId], [Expr1022], [DB_Name].[dbo].[DailySales].[DailySales_ProductDwId], [Expr1025]
| |--Compute Scalar(DEFINESad[Expr1012]=[DB_Name].[dbo].[DailySales].[DailySalesQuantity], [Expr1013]=[DB_Name].[dbo].[DailySales].[DailySalesReturnedGood], [Expr1022]=[DB_Name].[dbo].[DailySales].[S
| |--Clustered Index Scan(OBJECTSad[DB_Name].[dbo].[DailySales].[idx_DailySales_Full]), SEEKSad[PtnId1003] >= RangePartitionNew(CONVERT_IMPLICIT(int,CONVERT(char(8),getdate()-'1900-05-11 00:00:00.000',112),0
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[s].[SalesWeek_CustomerDwId], [Expr1023], [s].[SalesWeek_ProductDwId], [Expr1024], [Expr1026]))
|--Compute Scalar(DEFINESad[Expr1023]=[DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[LastDayWeek] as [s].[LastDayWeek]/(100), [Expr1024]=isnull([DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[
|--Clustered Index Scan(OBJECTSad[DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[idx_c_TMP_SalesWeek_AllRow_FD_LD] AS [s]))






--
Sabya

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
Is the amount of data in the two tables the same (not the amount returned, the amount in the tables)
Are the hardware specs the same?

Can you post the xml plan please? Or at least the full version of the text plan with all the columns?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4466 Visits: 4542
Also I would check that you have the same software installed / services running on both machines. I had a similar issue a few years ago but after investigation found that I had different amounts of memory available to SQL due to different software installed - despite the hardware being the same.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
sabyasm
sabyasm
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 524

Hello Gail,

I have attached the text plan in original post.
Below is the xml plan (from dev).

What I hear from the DBA, that the hardware configuration is pretty much same. The SQL version is same as well : Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)




<?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.2769.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="208738000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="18522.5" StatementText="SELECT s.TimeWeekId,       s.SalesWeek_CustomerDwId,       s.SalesWeek_ProductDwId,       s.SalesWeekSalesType,       s.SalesWeek_IntOrgDwId,       SUM(ISNULL(s2.DailySalesQuantity, 0)) AS SalesWeekSoldQuantity_MTD,       SUM(ISNULL(s2.DailySalesReturnedGood, 0)) AS SalesWeekReturnedGoodQuantity_MTD    INTO dbo.TMP_SalesWeek_Prev3M_AllRow_MTD    FROM (       SELECT *       FROM dbo.DailySales       WHERE DailySales_TimeId >= CONVERT(CHAR(8), (GETDATE() - 130), 112)          AND DailySales_TimeId <= CONVERT(CHAR(8), (GETDATE()), 112)       Wink s2    RIGHT JOIN dbo.TMP_SalesWeek_Prev3M_AllRow_FD_LD s       ON s.SalesWeek_CustomerDwId = s2.DailySales_CustomerDwId          AND s2.DailySales_TimeId / 100 = s.LastDayWeek / 100          AND s.LastDayWeek >= s2.DailySales_TimeId          AND s.SalesWeek_ProductDwId = s2.DailySales_ProductDwId          AND ISNULL(s.SalesWeekSalesType, 'DUMMY') = ISNULL(s2.DailySalesSalesType, 'DUMMY')          AND ISNULL(s.SalesWeek_IntOrgDwId, - 1) = ISNULL(s2.DailySales_IntOrgDwId, - 1)              GROUP BY s.TimeWeekId,       s.SalesWeek_CustomerDwId,       s.SalesWeek_ProductDwId,       s.SalesWeekSalesType,       s.SalesWeek_IntOrgDwId" StatementType="SELECT INTO" QueryHash="0x3AE4D94B1260C683" QueryPlanHash="0x6FD7B5A7C1A9537F">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="160" CompileTime="196" CompileCPU="196" CompileMemory="1704">
<RelOp AvgRowSize="9" EstimateCPU="208.738" EstimateIO="13773.4" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="18522.5">
<OutputList />
<Update DMLRequestSort="false">
<Object Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_MTD]" />
<SetPredicate>
<ScalarOperator ScalarString="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[TimeWeekId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[TimeWeekId] as [s].[TimeWeekId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_CustomerDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_CustomerDwId] as [s].[SalesWeek_CustomerDwId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_ProductDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_ProductDwId] as [s].[SalesWeek_ProductDwId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeekSalesType] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeekSalesType] as [s].[SalesWeekSalesType],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_IntOrgDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_IntOrgDwId] as [s].[SalesWeek_IntOrgDwId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeekSoldQuantity_MTD] = [Expr1018],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeekReturnedGoodQuantity_MTD] = [Expr1019]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="TimeWeekId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeek_CustomerDwId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeek_ProductDwId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeekSalesType" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeek_IntOrgDwId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeekSoldQuantity_MTD" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD]" Column="SalesWeekReturnedGoodQuantity_MTD" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="80" EstimateCPU="20.8738" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="4540.37">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="80" EstimateCPU="82.9366" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Gather Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="4519.5">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<Parallelism>
<RelOp AvgRowSize="80" EstimateCPU="9.56715" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Aggregate" NodeId="3" Parallel="true" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="4436.56">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="SUM(isnull([Expr1012],(0.0000)))">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="SUM(isnull([Expr1013],(0.0000)))">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0.0000)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</GroupBy>
<RelOp AvgRowSize="72" EstimateCPU="607.682" EstimateIO="1194.44" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Sort" NodeId="4" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="4426.99">
<OutputList>
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1013" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</OutputList>
<MemoryFractions Input="0.825497" Output="1" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="72" EstimateCPU="122.825" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Repartition Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2624.87">
<OutputList>
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1013" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</PartitionColumns>
<RelOp AvgRowSize="72" EstimateCPU="306.659" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Right Outer Join" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2502.05">
<OutputList>
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1013" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</OutputList>
<MemoryFractions Input="1" Output="0.174503" />
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
<ColumnReference Column="Expr1022" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Column="Expr1023" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1026" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_CustomerDwId] as [s].[SalesWeek_CustomerDwId]=[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId] AND [Expr1022]=[Expr1023] AND [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[LastDayWeek] as [s].[LastDayWeek]>=[DB_Name].[dbo].[DailySales].[DailySales_TimeId] AND [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_ProductDwId] as [s].[SalesWeek_ProductDwId]=[DB_Name].[dbo].[DailySales].[DailySales_ProductDwId] AND [Expr1024]=[Expr1025] AND [Expr1026]=[Expr1027]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1022" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1023" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1024" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1025" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1026" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1027" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="132" EstimateCPU="3.89227" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4947800" LogicalOp="Repartition Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="370.833">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1022" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
<ColumnReference Column="Expr1022" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</PartitionColumns>
<RelOp AvgRowSize="132" EstimateCPU="0.0206158" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4947800" LogicalOp="Compute Scalar" NodeId="8" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="366.941">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1022" />
<ColumnReference Column="Expr1025" />
<ColumnReference Column="Expr1027" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="[DB_Name].[dbo].[DailySales].[DailySalesQuantity]">
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesQuantity" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="[DB_Name].[dbo].[DailySales].[DailySalesReturnedGood]">
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesReturnedGood" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="[DB_Name].[dbo].[DailySales].[DailySales_TimeId]/(100)">
<Arithmetic Operation="DIV">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="isnull([DB_Name].[dbo].[DailySales].[DailySalesSalesType],N'DUMMY')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesSalesType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'DUMMY'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1027" />
<ScalarOperator ScalarString="isnull([DB_Name].[dbo].[DailySales].[DailySales_IntOrgDwId],(-1))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_IntOrgDwId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(-1)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="74" EstimateCPU="0.227095" EstimateIO="366.512" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4947800" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="true" Partitioned="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="366.739" TableCardinality="42290100">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesQuantity" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesReturnedGood" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_IntOrgDwId" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_CustomerDwId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_ProductDwId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesSalesType" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesQuantity" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySalesReturnedGood" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_IntOrgDwId" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Index="[idx_DailySales_Full]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Column="PtnId1003" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="RangePartitionNew(CONVERT_IMPLICIT(int,CONVERT(char(8),getdate()-'1900-05-11 00:00:00.000',112),0),(1),(20110101),(20110201),(20110301),(20110401),(20110501),(20110601),(20110701),(20110801),(20110901),(20111001),(20111101),(20111201),(20120101),(20120201),(20120301),(20120401),(20120501),(20120601),(20120701),(20120801),(20120901),(20121001),(20121101),(20121201))">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1020">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Convert DataType="char" Length="8" Style="112" Implicit="false">
<ScalarOperator>
<Arithmetic Operation="SUB">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'1900-05-11 00:00:00.000'" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110301)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110401)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110501)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110601)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110701)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110801)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110901)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111001)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120301)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120401)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120501)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120601)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120701)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120801)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120901)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121001)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121201)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Column="PtnId1003" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="RangePartitionNew(CONVERT_IMPLICIT(int,CONVERT(char(8),getdate(),112),0),(1),(20110101),(20110201),(20110301),(20110401),(20110501),(20110601),(20110701),(20110801),(20110901),(20111001),(20111101),(20111201),(20120101),(20120201),(20120301),(20120401),(20120501),(20120601),(20120701),(20120801),(20120901),(20121001),(20121101),(20121201))">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1021">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Convert DataType="char" Length="8" Style="112" Implicit="false">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110301)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110401)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110501)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110601)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110701)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110801)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20110901)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111001)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20111201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120201)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120301)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120401)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120501)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120601)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120701)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120801)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20120901)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121001)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121101)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(20121201)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[DB_Name].[dbo].[DailySales].[DailySales_TimeId]>=CONVERT_IMPLICIT(int,CONVERT(char(8),getdate()-'1900-05-11 00:00:00.000',112),0) AND [DB_Name].[dbo].[DailySales].[DailySales_TimeId]<=CONVERT_IMPLICIT(int,CONVERT(char(8),getdate(),112),0)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="GE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1020">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Convert DataType="char" Length="8" Style="112" Implicit="false">
<ScalarOperator>
<Arithmetic Operation="SUB">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'1900-05-11 00:00:00.000'" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[DailySales]" Column="DailySales_TimeId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1021">
<ScalarOperator>
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Convert DataType="char" Length="8" Style="112" Implicit="false">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="136" EstimateCPU="169.635" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Repartition Streams" NodeId="21" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1824.56">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
<ColumnReference Column="Expr1023" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1026" />
</OutputList>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Column="Expr1023" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1026" />
</PartitionColumns>
<RelOp AvgRowSize="136" EstimateCPU="0.869741" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Compute Scalar" NodeId="22" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1654.92">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
<ColumnReference Column="Expr1023" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1026" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1023" />
<ScalarOperator ScalarString="[DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[LastDayWeek] as [s].[LastDayWeek]/(100)">
<Arithmetic Operation="DIV">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1024" />
<ScalarOperator ScalarString="isnull([DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeekSalesType] as [s].[SalesWeekSalesType],N'DUMMY')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'DUMMY'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1026" />
<ScalarOperator ScalarString="isnull([DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_IntOrgDwId] as [s].[SalesWeek_IntOrgDwId],(-1))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(-1)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="50" EstimateCPU="9.56716" EstimateIO="1644.49" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="208738000" LogicalOp="Clustered Index Scan" NodeId="23" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1654.05" TableCardinality="208738000">
<OutputList>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</OutputList>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="LastDayWeek" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="TimeWeekId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_CustomerDwId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_ProductDwId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeekSalesType" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" Column="SalesWeek_IntOrgDwId" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Index="[idx_c_TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="[s]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
</StreamAggregate>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



--
Sabya

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
And from the other instance?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Are the phyical file locations the same?
TempDB and user DBs on different spindles?
RAID and disks all ok?
Does your QA enviroment have other instances or DBs sharing the resources?



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
dba_pkashyap
dba_pkashyap
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 317
Since you say its during sort, this can be the case of spilling to tempdb as not enough memory was granted.

Check the estimated no. of rows and actual rows in both the plans during sort operation.
Try re-compiling the query again and see or use option(recompile) hint and check.

Pavan.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search