Sql-server – Understanding Execution Plan

execution-plansql server

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

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

Best Answer

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.

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested. The graphical execution plan seems to indicate this is the case -- TOP is the "last" step. But it appears there is more going on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes. An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close(). As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator). This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards. This row-at-a-time model is essential in forming a sound intuition for query execution.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives. The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets. The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed. The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree. The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal. This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans. Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans. More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it. Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best. Which option is appropriate in your case depends on the details of course. The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.

Sql-server – Execution Plan in Microsoft SQL Server

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.

Best Answer

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

Sql-server – Execution Plan in Microsoft SQL Server

Related Question