Huge difference for same operator in different environment

• sabyasm

  SSCarpal Tunnel

  Points: 4324

  More actions

  November 27, 2012 at 2:09 am

  #285211

  [font="Tahoma"]

  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 .[TimeWeekId],[dbo.TMP_SalesWeek_AllR

  |--Top(ROWCOUNT est 0)

  |--Parallelism(Gather Streams)

  |--Stream Aggregate(GROUP BY:(.[TimeWeekId], .[SalesWeek_CustomerDwId], .[SalesWeek_ProductDwId], .[SalesWeekSalesType], .[SalesWeek_IntOrgDwId]) DEFINE:([Expr1018]=SUM(isnull([Expr1012],(0.0000))), [Expr1019]=SUM(isnull([Ex

  |--Sort(ORDER BY:(.[TimeWeekId] ASC, .[SalesWeek_CustomerDwId] ASC, .[SalesWeek_ProductDwId] ASC, .[SalesWeekSalesType] ASC, .[SalesWeek_IntOrgDwId] ASC))

  |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:(.[TimeWeekId], .[SalesWeek_CustomerDwId], .[SalesWeek_ProductDwId], .[SalesWeekSalesType], .[SalesWeek_IntOrgDwId]))

  |--Hash Match(Right Outer Join, HASH:([DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId], [Expr1022], [DB_Name].[dbo].[DailySales].[DailySales_ProductDwId], [Expr1025], [Expr1027])=(.[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]..[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:(.[SalesWeek_CustomerDwId], [Expr1023], .[SalesWeek_ProductDwId], [Expr1024], [Expr1026]))

  |--Compute Scalar(DEFINE:([Expr1023]=[DB_Name].[dbo].[TMP_SalesWeek_AllRow_FD_LD].[LastDayWeek] as .[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 ))

  [/font]

  [font="Comic Sans MS"]--
  Sabya[/font]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 27, 2012 at 2:18 am

  #1562551

  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

  SSCoach

  Points: 19505

  More actions

  November 27, 2012 at 2:25 am

  #1562553

  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 [/url]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

  SSCarpal Tunnel

  Points: 4324

  More actions

  November 27, 2012 at 2:51 am

  #1562564

  [font="Tahoma"]

  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)

  [/font]

  <?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 .[TimeWeekId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_CustomerDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_CustomerDwId] as .[SalesWeek_CustomerDwId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_ProductDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_ProductDwId] as .[SalesWeek_ProductDwId],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeekSalesType] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeekSalesType] as .[SalesWeekSalesType],[dbo.TMP_SalesWeek_Prev3M_AllRow_MTD].[SalesWeek_IntOrgDwId] = [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_IntOrgDwId] as .[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="" 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="" 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="" 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="" 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="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_IntOrgDwId" />

  </OutputList>

  <Parallelism PartitioningType="Hash">

  <PartitionColumns>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Column="Expr1023" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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 .[SalesWeek_CustomerDwId]=[DB_Name].[dbo].[DailySales].[DailySales_CustomerDwId] AND [Expr1022]=[Expr1023] AND [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[LastDayWeek] as .[LastDayWeek]>=[DB_Name].[dbo].[DailySales].[DailySales_TimeId] AND [DB_Name].[dbo].[TMP_SalesWeek_Prev3M_AllRow_FD_LD].[SalesWeek_ProductDwId] as .[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="" 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="" 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="" 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="" Column="LastDayWeek" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Column="Expr1023" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="LastDayWeek" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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 .[LastDayWeek]/(100)">

  <Arithmetic Operation="DIV">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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 .[SalesWeekSalesType],N'DUMMY')">

  <Intrinsic FunctionName="isnull">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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 .[SalesWeek_IntOrgDwId],(-1))">

  <Intrinsic FunctionName="isnull">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="LastDayWeek" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="TimeWeekId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" Column="LastDayWeek" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="TimeWeekId" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_CustomerDwId" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeek_ProductDwId" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" Column="SalesWeekSalesType" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[DB_Name]" Schema="[dbo]" Table="[TMP_SalesWeek_Prev3M_AllRow_FD_LD]" Alias="" 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="" 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>

  [font="Comic Sans MS"]--
  Sabya[/font]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 27, 2012 at 7:17 am

  #1562670

  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

  SSCarpal Tunnel

  Points: 4473

  More actions

  November 27, 2012 at 7:38 am

  #1562684

  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.[/url]

  "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

• dba_pkashyap

  Hall of Fame

  Points: 3336

  More actions

  November 27, 2012 at 7:41 am

  #1562685

  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.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 28, 2012 at 6:51 am

  #1563111

  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 on googles mail service