SQL Server Performance

Wrong Execution Plan

Discussion in 'Performance Tuning for DBAs' started by ykchakri, Oct 13, 2003.

  1. ykchakri New Member


    I have a fairly large table (28 mi rows) that I'm running the following query on:

    Select col1, col2, col3 from table1
    where col1 = 1234 and col2 = 'abcd'

    It is taking 5 secs to return the results. I've looked at the execution plan. It is using the index on col1 to return the 100 rows that match 1234 and index on col2 to return the 4 mi rows that match 'abcd', then doing merge join between these two results. The merge join is taking 62% and the index seek on col2 is taking 38%.

    But, when I force an index on the same query as below:

    Select col1, col2, col3 from table1 (index(nx_col1))
    where col1 = 1234 and col2 = 'abcd'

    It is returning the results in couple of milli seconds. And the execution plan shows a seek on Index and a bookmark lookup, where the bookmark lookup taking 100% of the time.

    So, my question is why the optimizer is not picking up the second plan (with the bookmark lookup) when it is much faster than the first plan (with the merge join) ?

    Can you please throw some ideas ?

  2. Luis Martin Moderator

    The second option came from Index Tuning?.

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. bradmcgehee New Member

    There are three main reasons this can happen:

    1) Your indexes statistics are outdated and need updated.
    2) The query plan is overly complex, which can cause the optimizer to choose less than optimal plan. I doubt if this is the case, as your query is very simple.
    3) A bug, but this is unlikely if you are using SP3 of SQL Server 2000.

    Try manually updating the statistics for these indexes. If that doesn't fix it, then use a hint as you are doing now.

    Brad M. McGehee, MVP
  4. joechang New Member

    look at the execution plans for the 2 queries, with & w/o the hint

    Select col1, col2, col3 from table1 where col1 = 1234 and col2 = 'abcd'

    Select col1, col2, col3 from table1 (index(nx_col1))
    where col1 = 1234 and col2 = 'abcd'

    presumably the optimizer claims the 1st query is less expensive even though the run test proves otherwise.
    in this case, the problem is because sql server overweights the cost of bookmark lookups and looks for plans that do not use bookmarks for too many rows
  5. ykchakri New Member

    Hi Luis and Brad,

    Thanks for the replies. Yes, the update statistics resolved the issue. I've updated the statistics on the table without specifying a sample, just before the test. But, apparently it didn't help.

    Now, I've updated the statistics on the specific index with FULLSCAN and it worked like a charm. Optimizer is now picking up the second plan.
    Looks like I need to make some changes to my daily update statisitcs job. Right now, to minimize the time taken, I'm updating statistics with 50% sample, but looks like it is not going to be enough.
  6. satya Moderator

    And also suggest to run regular DBREINDEX to take advantage.

    Satya SKJ
  7. bradmcgehee New Member

    At a Microsoft SQL Server performance tuning workshop I just attended, the Microsoft expert suggested that UPDATE STATISTICS be run daily, assuming that your maintenance window is long enough. Of course, you don't need to do this on the same days as you run DBREINDEX, as this command also updates statistics automatically. While I guess the autostats helps a lot, in many cases, it still is not good enough all the time to give the query analyzer enough data to make good execution plans.

    Brad M. McGehee, MVP
  8. ykchakri New Member

    Thanks Brad,

    I don't have any trust in autostats and am running UPDATE STATISTICS twice a day. But, after the above test in question, I've realized that the default sample used by SQL Server or fixed sample of 50% that I was using doesn't seem to be doing any good. It looks like I need to do a FULLSCAN every day. Am I right ?

Share This Page