3

I am really having hard time understanding the below execution plan.

Query 1: (Simple Select Query)

Record count of ##SALES_UNIT_RESULT is 168. In first execution plan it is fine.

;WITH MONTHLY_PER_BUSINESS
     AS (SELECT ITEM_MASTER_SID,
                P.PERIOD_SID,
                Q_PERIOD_SID = Min(P.PERIOD_SID)
                                 OVER(
                                   PARTITION BY ITEM_MASTER_SID, P.YEAR, P.QUARTER),
                ACT_MONTHLY_PER_BUSINESS=FORECAST_GTS_SALES / Sum(FORECAST_GTS_SALES)
                                                                OVER(
                                                                  PARTITION BY ITEM_MASTER_SID, P.[QUARTER], P.[YEAR]),
                FORE_MONTHLY_PER_BUSINESS=ACTUAL_GTS_SALES / Sum(ACTUAL_GTS_SALES)
                                                               OVER(
                                                                 PARTITION BY ITEM_MASTER_SID, P.[QUARTER], P.[YEAR]),
                CALC_WAC_PRICE
         FROM   ##GTS_WAC GW
                INNER JOIN PERIOD P
                        ON GW.PERIOD_SID = P.PERIOD_SID)
SELECT SR.PROJECTION_DETAILS_SID,
       PROJECTED_SALES = CASE
                           WHEN BASED_ON = 'SALES' THEN SR.PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS)
                           ELSE PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS) * CALC_WAC_PRICE
                         END,
       PROJECTED_UNITS = CASE
                           WHEN BASED_ON = 'SALES' THEN ( SR.PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS) ) / NULLIF(CALC_WAC_PRICE, 0)
                           ELSE PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS)
                         END,
       MPB.PERIOD_SID
FROM   MONTHLY_PER_BUSINESS MPB
       INNER JOIN ##SALES_UNIT_RESULT SR
               ON SR.ITEM_MASTER_SID = MPB.ITEM_MASTER_SID
                  AND SR.PERIOD_SID = MPB.Q_PERIOD_SID 

enter image description here

Query 2:(Update query with same above Select query)

Here if you see the from ##SALES_UNIT_RESULT table 169344 are coming out for next step.Why when Update is introduced 168 records are increased to 169344. Can anyone tell me what's going on?

;WITH MONTHLY_PER_BUSINESS
     AS (SELECT ITEM_MASTER_SID,
                P.PERIOD_SID,
                Q_PERIOD_SID = Min(P.PERIOD_SID)
                                 OVER(
                                   PARTITION BY ITEM_MASTER_SID, P.YEAR, P.QUARTER),
                ACT_MONTHLY_PER_BUSINESS=FORECAST_GTS_SALES / Sum(FORECAST_GTS_SALES)
                                                                OVER(
                                                                  PARTITION BY ITEM_MASTER_SID, P.[QUARTER], P.[YEAR]),
                FORE_MONTHLY_PER_BUSINESS=ACTUAL_GTS_SALES / Sum(ACTUAL_GTS_SALES)
                                                               OVER(
                                                                 PARTITION BY ITEM_MASTER_SID, P.[QUARTER], P.[YEAR]),
                CALC_WAC_PRICE
         FROM   ##GTS_WAC GW
                INNER JOIN PERIOD P
                        ON GW.PERIOD_SID = P.PERIOD_SID)
UPDATE NSP
SET    NSP.PROJECTION_SALES = A.PROJECTED_SALES,
       NSP.PROJECTION_UNITS = A.PROJECTED_UNITS
FROM   ST_NM_SALES_PROJECTION NSP
       INNER JOIN (SELECT SR.PROJECTION_DETAILS_SID,
                          PROJECTED_SALES = CASE
                                              WHEN BASED_ON = 'SALES' THEN SR.PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS)
                                              ELSE PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS) * CALC_WAC_PRICE
                                            END,
                          PROJECTED_UNITS = CASE
                                              WHEN BASED_ON = 'SALES' THEN ( SR.PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS) ) / NULLIF(CALC_WAC_PRICE, 0)
                                              ELSE PROJECTED_SALES_UNITS * COALESCE(MPB.FORE_MONTHLY_PER_BUSINESS, MPB.ACT_MONTHLY_PER_BUSINESS)
                                            END,
                          MPB.PERIOD_SID
                   FROM   MONTHLY_PER_BUSINESS MPB
                          INNER JOIN ##SALES_UNIT_RESULT SR
                                  ON SR.ITEM_MASTER_SID = MPB.ITEM_MASTER_SID
                                     AND SR.PERIOD_SID = MPB.Q_PERIOD_SID) A
               ON NSP.PROJECTION_DETAILS_SID = A.PROJECTION_DETAILS_SID
                  AND NSP.PERIOD_SID = A.PERIOD_SID
WHERE  [USER_ID] = 12
       AND SESSION_ID = 12 

enter image description here

7
  • Consider creating a clustered index (unique if applicable) on the temp table columns specified in JOIN clauses. This will provide the query optimizer with useful information to generate an optimal plan as well as provide more efficient data access.
    – Dan Guzman
    Oct 28 '15 at 12:18
  • @DanGuzman - Yes i can create a clustered Index on ##SALES_UNIT_RESULT table but why the first query is fine and second query record count is increased from 160 to 1693441 Oct 28 '15 at 12:20
  • @DanGuzman - After creating clustered index on ##SALES_UNIT_RESULT (PROJECTION_DETAILS_SID,PERIOD_SID) . Records count reduced to 504. It worked dude. Can you tell me why is that Oct 28 '15 at 12:24
  • 4
    The first query plan is doing a HASH JOIN, where it scans both inputs once and joins them. The second query plan is doing a LOOP JOIN where it scans the ##SALES_UNIT_RESULT table for each row in the top branch of your plan (1008 rows). If you hover the Table Scan icon, you'll see a 'Number of Executions' value around 1008. 1008*160 gives you the ballpark for 160k records. Adding an index allows you to seek for a value 160 times, instead of scan the entire table, hence the lower record count. Oct 28 '15 at 13:16
  • 2
    Is there a reason you are using global temp tables all over the place instead of standard temp tables?
    – Sean Lange
    Oct 28 '15 at 13:54
1

The count is not distinct rows - so if the table is scanned many times each row will be counted many time. In this case the nested loops join with the scan is reading all 168 rows once for each of the 1008 rows coming out of the other leg which explains the 169344.

I'm guessing that none of the temporary tables have any indexes which would explain the planner resorting to multiple scans, so creating appropriate ones could make a massive difference to the resulting query plans.

-3

You can hit the button of "Display Estimated Execution Plan", It would show in green line if there is any missing indexes that can help executing you statement better and faster. if any thing seemed normal and now green line showed up you can use the following statement to identify the missing indexes"

SELECT 
    *
FROM 
    sys.dm_db_missing_index_group_stats AS igs
        JOIN 
    sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
        JOIN 
    sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle

try to narrow the search using "Where database_id=#" and create your index manually.

Hope i helped.

2
  • Not every time the EP will show index tips. Sometimes it will display an index that will not help at all.
    – Racer SQL
    Oct 29 '15 at 11:05
  • And those missing index suggestions are just that, suggestions. You have to test them. Further, querying the missing index suggestions doesn't tell you which query they're for. Was it a query run once by a junior dba that will never run again, so creating an index is a waste of time? No way to know. This is dangerous advice. Mar 27 '20 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.