Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Huge difference for same operator in different environment Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 2:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518

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(OBJECT[TMP_SalesWeek_AllRow_MTD]), SET[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 BY[s].[TimeWeekId], [s].[SalesWeek_CustomerDwId], [s].[SalesWeek_ProductDwId], [s].[SalesWeekSalesType], [s].[SalesWeek_IntOrgDwId]) DEFINE[Expr1018]=SUM(isnull([Expr1012],(0.0000))), [Expr1019]=SUM(isnull([Ex
|--Sort(ORDER BY[s].[TimeWeekId] ASC, [s].[SalesWeek_CustomerDwId] ASC, [s].[SalesWeek_ProductDwId] ASC, [s].[SalesWeekSalesType] ASC, [s].[SalesWeek_IntOrgDwId] ASC))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[s].[TimeWeekId], [s].[SalesWeek_CustomerDwId], [s].[SalesWeek_ProductDwId], [s].[SalesWeekSalesType], [s].[SalesWeek_IntOrgDwId]))
|--Hash Match(Right Outer Join, HASH[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId], [Expr1022], [DB_Name].[dbo].[DailySales].[DailySales_ProductDwId], [Expr1025], [Expr1027])=([s].[SalesWeek_CustomerDwI
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId], [Expr1022], [DB_Name].[dbo].[DailySales].[DailySales_ProductDwId], [Expr1025]
| |--Compute Scalar(DEFINE[Expr1012]=[DB_Name].[dbo].[DailySales].[DailySalesQuantity], [Expr1013]=[DB_Name].[dbo].[DailySales].[DailySalesReturnedGood], [Expr1022]=[DB_Name].[dbo].[DailySales].[S
| |--Clustered Index Scan(OBJECT[DB_Name].[dbo].[DailySales].[idx_DailySales_Full]), SEEK[PtnId1003] >= RangePartitionNew(CONVERT_IMPLICIT(int,CONVERT(char(8),getdate()-'1900-05-11 00:00:00.000',112),0
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[s].[SalesWeek_CustomerDwId], [Expr1023], [s].[SalesWeek_ProductDwId], [Expr1024], [Expr1026]))
|--Compute Scalar(DEFINE[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(OBJECT[DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[idx_c_TMP_SalesWeek_AllRow_FD_LD] AS [s]))






--
Sabya
Post #1389017
Posted Tuesday, November 27, 2012 2:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1389019
Posted Tuesday, November 27, 2012 2:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:30 AM
Points: 3,004, Visits: 3,180
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
Post #1389021
Posted Tuesday, November 27, 2012 2:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:30 AM
Points: 346, Visits: 518

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) ) 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
Post #1389036
Posted Tuesday, November 27, 2012 7:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
And from the other instance?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1389180
Posted Tuesday, November 27, 2012 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1389199
Posted Tuesday, November 27, 2012 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 6, 2012 2:49 PM
Points: 140, Visits: 310
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.
Post #1389200
Posted Wednesday, November 28, 2012 6:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 4,320, Visits: 6,113
We could use the other server plan (as Gail asked for already).

Without that I am going to guess:

a) production memory is being used much more heavily and their is either a memory grant wait for the large sort and/or the sort is being spilled out of memory down to tempdb, which is causing signficant slowness

or

b) there is blocking in production keeping the query in a waiting state for some resource lock

or

c) waiting is occurring for some reason (MANY possiblitiies here such as pageiolatch (i.e. slow IO), CPU, etc)

or

d) much physical IO is necessary on prod than dev due to concurrent workload on prod

Get a professional tuner to remote into your system and this can probably be figured out in a matter of minutes.



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1389736
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse