SQL Server Performance

Buffers+Caches+Stats = Wrong Exec. Plans

Discussion in 'Performance Tuning for DBAs' started by hominamad, Mar 9, 2004.

  1. hominamad Member

    Hi All,

    We have the following Query:

    dbo.vSecurityActive SS
    INNER JOIN dbo.RateReset RR ON SS.SecID = RR.SecID
    INNER JOIN dbo.Position P ON P.SecID = SS.SecID
    LEFT OUTER JOIN dbo.AuctionTrade A ON
    A.SecID = SS.SecID
    AND A.InvestorID = P.InvestorID
    AND A.ResetDate = RR.ResetDate
    AND A.TradeTypeCode <> 'B'
    AND A.TradeStatusCode <> 'C'
    SS.RateSource = 'A'
    AND P.CurPos = CAST(1 AS BIT)
    AND P.Qty <> 0
    AND RR.ResetStatusCode IN ('P', 'Z')
    AND RR.ResetDate = '3/9/2004'
    AND SS.ResetDeadline = '12:30'
    MAX(P.Qty) > SUM(ISNULL(A.Qty, 0))

    This query is run many times throughout the day. Most of the time it will return a large number of records. Throughout the day, that number gets smaller and smaller due to business processing. Eventually it returns none. The strange thing is, the fewer records it returns, the longer the execution time becomes.

    We examined the execution plan. We noticed that somehow, an Index Seek is being perfromed on 2 million+ rows on an index in the AuctionTrade table. There are less than 500k rows in this table. After running Update Statistcs, the execution plan changes, and this Index Seek is gone, and the query takes 1 second (still correctly returning no rows). We then INSERT a bunch more rows to the AuctionTrade table as a test, and run DBCC DROPCLEANBUFFERS. This causes the "bad" execution plan to come back again.

    What we want to find out is:
    1. Is this query coded inefficiently, in a way that could cause this behavior?
    2. What could be causing the Execution plan to change this way?
    3. Where does that 2 million+ index seek come from?

    I don't know if I've included enough information. I may be able to post the two different execution plans if it would help. Thanks in advance!

  2. satya Moderator

  3. hominamad Member

    Thanks Satya. I read those before. Actually, I think what I need is an article about how the actual execution plans are created - based on statistics, etc. - lower level stuff. I found a few online but they were extremely low level. Interesting, but maybe beyond the scope of what I need to know for right now. I need to get to the bottom of why the same query, on the same data, is producing two different plans, one good, and one horrible. I know it has to do with statistics and buffers, because playing with UPDATE_STATISTICS and DROPCLEANBUFFERS is what causes the plans to go back and forth.


    quote:Originally posted by satya


    These 2 links will help you to understand more about Execution plans.


    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. satya Moderator

Share This Page