Bad Query Plan

Last Post 25 Oct 2011 03:21 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
rerichards
New Member
New Member

--
25 Oct 2011 01:54 PM
Running SQL Server 2008 Enterprise.

I have a statement similar to the following which is generating a bad query plan:

SET ROWCOUNT 1
SELECT @LastWidgetAmount = Pay.WidgetAmount,
             @LastWidgetDate = Pay.WidgetDate
FROM dbo.Widgets Pay
  INNER JOIN dbo.WidgetDetails PD
      ON PD.WidgetFID = Pay.Widget_UID
      AND PD.WidgetKey = Pay.WidgetKey
WHERE Pay.PartyFID = @PartyFID
    AND Pay.WidgetSource = 1 
    AND Pay.WidgetKey = @WidgetKey
    AND PD.SpaceFID IS NULL
    AND PD.Freight = 0
    AND PD.WidgetKey = @WidgetKey
    AND WidgetDate = ( SELECT MAX(WidgetyDate) AS MaxWidgetDate 
                                     FROM dbo.Widgets Pay
                                          INNER JOIN dbo.WidgetDetails PD
                                              ON PD.WidgetFID = Pay.Widget_UID
                                              AND PD.WidgetKey = Pay.WidgetKey
                                     WHERE Pay.PartyFID = @PartyFID
                                         AND Pay.WidgetSource = 1
                                         AND Pay.WidgetKey = @WidgetKey
                                         AND PD.SpaceFID IS NULL
                                         AND PD.Freight = 0
                                         AND PD.WidgetKey = @WidgetKey )
SET ROWCOUNT 0


So the query plan has two branches, one branch for the outer query and one branch to obtain the MaxWidgetDate.

Each branch uses Index Seeks on both tables, which appear to be optimal, and in each instance the index seeks combine into a Nested Loop.

What is bad, is that the Outer Input in each branch leading into the Nested Loop is table WidgetDetails, and the following is from the popup on the Index Seek node for WidgetDetails:

Actual Number of Rows: 175375
Estimated Number of Rows: 1

The Inner Input (the index seek on table WidgetPayments) has the following from the popup on the Index Seek node:

Actual Number of Rows: 1
Estimated Number of Rows: 1

It is the same for both statement branches.

NOTE: Statistics on both tables have been updated with FULLSCAN. The commands DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS were both run before query execution.

Now, I realize this query can be re-written, perhaps using a join with a derived table instead of a Subquery, or a CTE, or running the Subquery first and storing the result in a variable that is then used in the main query.

But, that is not the issue. I am wondering if anybody has seen this behavior before, and why it is acting the way it does? Is it because of the Subquery rather than a join? Does it have to do with the SET ROWCOUNT?

I would just like some suggestions (not on a rewrite, but I guess that would be OK), but why the query might be behaving in this manner?


gunneyk
New Member
New Member

--
25 Oct 2011 03:21 PM
OK a couple of things happening here. First off I would definately get rid of the SET ROWCOUNT if for nothing else than it is being depreciated. But I would recommend you replace it with a TOP 1 instead. The optimizer can recognize TOP commands and act appropriately. The other is that you appear to be using variables in the WHERE clause. For instance the @WidgetKey and @PartyFID. The optimizer cannot read the values of the variables when it creates the plan so it takes some quesses. IN this case it looks like it guessed a much higher number of rows matching than there are. If you used parameters to a stored proc it would read the actual value passed the first time you ran it and got the actual stats for that value. I think the TOP 1 would solve it all anyway but if not try using a hint such as OPTIMIZE FOR @WidgetKey = xxx. Use a value that you know only has 1 row if that is the plan you want it to produce.


Acceptable Use Policy
---