Post reply

SQL Server 2008 optimizer

  • November 9, 2010 at 5:59 pm

    #228689

    I'm somewhat stumped on how SQL Server 2008 optimizer works. Below I have 2 queries (Original and Tweaked) and the Original takes 15-16 seconds to execute whereas the Tweaked version takes less than a second. Both queries retrieve the exact same results, however, the Original query seems to be doing a full index scan where the Tweaked query will correctly perform an index seek. The PERF_SUMMARY table is approximately 54 million rows, attached are the execution plans for both queries:

    --Original

    SELECT e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE, e51.STATUS_FLAG, e51.DICTIONARY_ID

    FROM PERFORM.DBO.PERF_SUMMARY e51

    WHERE ( ( ( ( ( ( ( ( e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'}

    AND ( e51.ENTITY_ID IN ('1000')))

    OR

    ( ( (e51.END_EFFECTIVE_DATE > {d '2010-01-30'}

    AND e51.END_EFFECTIVE_DATE <={d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) ) )

    AND e51.PERF_FREQ_CODE = 'D' )

    OR

    ( ( ( (e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) )

    AND e51.PERF_FREQ_CODE = 'M' ) )

    AND e51.DICTIONARY_ID = 3 ) )

    AND e51.SRC_INTFC_INST = 4 ) )

    AND e51.PERF_SUMMARY_TYPE = 'P' ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC

    --Tweaked

    SELECT e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE, e51.STATUS_FLAG, e51.DICTIONARY_ID

    FROM PERFORM.DBO.PERF_SUMMARY e51

    WHERE ( ( ( ( ( (( e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'} )

    AND ( e51.ENTITY_ID IN ('1000'))

    AND e51.PERF_FREQ_CODE = 'D' )

    OR

    ( ( ( (e51.END_EFFECTIVE_DATE > {d '2010-01-30'}

    AND e51.END_EFFECTIVE_DATE <={d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) )

    AND e51.PERF_FREQ_CODE = 'D' )

    OR

    ( ( ( (e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) )

    AND e51.PERF_FREQ_CODE = 'M' ) )

    AND e51.DICTIONARY_ID = 3 ) )

    AND e51.SRC_INTFC_INST = 4 ) )

    AND e51.PERF_SUMMARY_TYPE = 'P' ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC

  • November 9, 2010 at 6:39 pm

    #1248645

    It's either late, or I'm blind. Where's the tweak?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 9, 2010 at 6:50 pm

    #1248647

    There is a additional "AND e51.PERF_FREQ_CODE = 'D' )" on the first condition in the tweaked query:

    --Original

    SELECT e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE, e51.STATUS_FLAG, e51.DICTIONARY_ID

    FROM PERFORM.DBO.PERF_SUMMARY e51

    WHERE ( ( ( ( ( ( ( ( e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'}

    AND ( e51.ENTITY_ID IN ('1000')))

    OR

    ( ( (e51.END_EFFECTIVE_DATE > {d '2010-01-30'}

    AND e51.END_EFFECTIVE_DATE <={d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) ) )

    AND e51.PERF_FREQ_CODE = 'D' )

    OR

    --Tweaked

    SELECT e51.ENTITY_ID, e51.PERF_FREQ_CODE, e51.END_EFFECTIVE_DATE, e51.STATUS_FLAG, e51.DICTIONARY_ID

    FROM PERFORM.DBO.PERF_SUMMARY e51

    WHERE ( ( ( ( ( (( e51.END_EFFECTIVE_DATE = {d '2010-01-30'}

    OR e51.END_EFFECTIVE_DATE = {d '2010-02-28'} )

    AND ( e51.ENTITY_ID IN ('1000'))

    AND e51.PERF_FREQ_CODE = 'D' ) <========================================================

    OR

    ( ( ( (e51.END_EFFECTIVE_DATE > {d '2010-01-30'}

    AND e51.END_EFFECTIVE_DATE <={d '2010-02-28'})

    AND ( e51.ENTITY_ID IN ('1000') ) ) )

    AND e51.PERF_FREQ_CODE = 'D' )

    OR

  • November 9, 2010 at 7:45 pm

    #1248653

    The higher selectivity probably lets it seek instead of scan an index. Would have to see the DDL and the comparable sqlplans to tell for sure.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 9, 2010 at 7:56 pm

    #1248657

    I have attached the sqlplans on the first post.

  • November 10, 2010 at 11:16 am

    #1249072

    jfong-786685 (11/9/2010)

    I have attached the sqlplans on the first post.

    Attached plans are 0 bytes each. No data in them, just the file names. Please double check?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • November 10, 2010 at 11:26 am

    #1249077

    Check it again, there should be valid plans now.

  • November 10, 2010 at 11:59 am

    #1249097

    Alright,

    For starters, these are estimated plans. Everything here is a guess by the optimizer. By preference, you always want to work with actuals.

    That said.

    The original plan is doing a non-clustered index scan first on Perf_Sum_Logic_Key index. The second plan is doing a 3way seek on the same non-clustered index. This is then contatonated (think union) with the result set. This is entirely about selectivity of that one index. The optimizer feels its faster to scan then run the index multiple times.

    Both versions are estimating the same number of rows, however.

    There's not really a lot that can be done here without a detailed analysis of the data itself. The difference in your two where clauses is a logic change in the requirements. While it may not affect your data, from an external perspective, those are two uniquely different queries.

    As a side note, you're also missing statistics it would like on Entity_ID and Dictionary_ID, and fixing this may help you out. This can be cured with minimal pain via:

    CREATE STATISTICS stat_Perf_Summary_Entity_ID ON dbo.Perf_Summary (Entity_ID) WITH FULLSCAN

    CREATE STATISTICS stat_Perf_Summary_Dictionary_ID ON dbo.Perf_Summary (Dictionary_ID) WITH FULLSCAN

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply