SQL Server Performance

Query plan mismatch in SQL 2000 and 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by chetanjain04, May 16, 2007.

  1. chetanjain04 Member

    Hi,

    This is a strange behaviour I am seeing in SQL 2005. Foll. query was executed in SQL 2000 which does a index seek on the NC index on table TableB. The same query however does a Index Scan on SQL 2005.

    SELECT C_F_COD_ACCT_NO,
    N_F_COD_TXN_MNEMONIC,

    CASE WHEN N_F_COD_TXN_MNEMONIC IN
    (SELECT V_M_PARAM_KEY_VALUE FROM TableB WHERE V_M_PARAM_KEY='CASA_TXN_CODE' AND V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_AMT_DEP_LC' AND V_M_PARAM_KEY_STATUS='Y') THEN SUM(ISNULL(N_F_AMT_TXN,0)) END AS AMT_DEP_LC,

    CASE WHEN N_F_COD_TXN_MNEMONIC IN
    (SELECT V_M_PARAM_KEY_VALUE FROM TableB WHERE V_M_PARAM_KEY='CASA_TXN_CODE' AND V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_AMT_WITHD_LC' AND V_M_PARAM_KEY_STATUS='Y') THEN SUM(ISNULL(N_F_AMT_TXN,0)) END AS AMT_WTHD_LC,

    CASE WHEN N_F_COD_TXN_MNEMONIC IN
    (SELECT V_M_PARAM_KEY_VALUE FROM TableB WHERE V_M_PARAM_KEY='CASA_TXN_CODE' AND V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_CASH_DEP_LC' AND V_M_PARAM_KEY_STATUS='Y') THEN SUM(ISNULL(N_F_AMT_TXN,0)) END AS TOT_CSH_DPST_LC,

    CASE WHEN N_F_COD_TXN_MNEMONIC IN
    (SELECT V_M_PARAM_KEY_VALUE FROM TableB WHERE V_M_PARAM_KEY='CASA_TXN_CODE' AND V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_CASH_WITHD_LC' AND V_M_PARAM_KEY_STATUS='Y') THEN SUM(ISNULL(N_F_AMT_TXN,0)) END AS TOT_CSH_WTHD_AMT_LC

    FROM TableA
    GROUP BY C_F_COD_ACCT_NO,N_F_COD_TXN_MNEMONIC

    The table TableB has about 2000 rows and two indexes on foll. columns:
    CL index : V_M_PARAM_KEY, V_M_PARAM_KEY_CODE
    NC index : V_M_PARAM_KEY, V_M_PARAM_KEY_CODE, V_M_PARAM_KEY_VALUE, V_M_PARAM_KEY_STATUS

    The table TableA has 37301282 rows and foll columns have indexes:
    CL index: c_f_cod_acct_no
    NC index: c_f_cod_acct_no, n_f_cod_txn_mnemonic

    I have verified the indexes, table structures and table data are all same. Have tried drop and recreate index, update stats and update usage. However, I am still seeing a index scan in SQL 2k5. Can anybody throw some suggestions.?


    Thanks

    Chetan
    "Calm seas can never make skillful sailors"
  2. bradmcgehee New Member

    If you have done everything you mentioned above, and everything is identical except for the version of SQL Server, then you may have run into one of the differences between the 2000 and 2005 query optimizers. They are different and can produce different results, although not common. Assuming all is identical, then use a hint to resolve the issue (at least for now). It is possible that a new SP could change the future behaviour of the optimizer. Because of this, use hints sparingly, and also reevaluate the need for them periodically.

    --------------------------------
    Brad M. McGehee, SQL Server MVP
    http://www.sqlbrad.com
  3. Luis Martin Moderator

    Could you try this<img src='/community/emoticons/emotion-7.gif' alt=':?' /><br /><br />Run DTA with that query in sql2000 and check if any indexes are recommended.<br /><br />Personally I did that many time and find better suggestion than ITW.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  4. satya Moderator

    Fyi, DTA would work if SQL 2k instance is patched upto latest Service pack which is SP4 +.

    Also take help fromhttp://msmvps.com/blogs/ssqa/archiv...that-a-query-could-benefit-from-an-index.aspx in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. chetanjain04 Member

    While running DTA on sql 2000 db I am getting error:

    "tuninig this edition of SQL 2005 is not permitted"

    I also tried dbcc flushprocindb() but still I am seeing a NC index scan in 2005.


    Best Regards,

    Chetan
  6. satya Moderator

    What is the key time in completing the SP as compared to SQL 2000 and 2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. chetanjain04 Member

    Dear All

    The SP takes 12:41 mins in SQL 2000 but in SQL 2005 it takes 6:08 minutes. The reason for posting this was that there is a plan mismatch which is causing the following error on 2005 but not in 2000:

    "Error: Conversion failed when converting the varchar value 'BUSINESS DIRECT' to data type smallint."

    I discovered that the N_F_COD_TXN_MNEMONIC is a smallint type which contains only integer values in all rows. While V_M_PARAM_KEY_VALUE is a varchar type which contains numbers as well as character data in all rows.

    Example,
    v_m_param_key_valueN_F_COD_TXN_MNEMONIC
    29445001
    29481201
    29521201
    65081201
    BUSINESS DIRECT1201
    CAPS DIRECT2201
    D1765 - DIRECT ASSOCIATES2201

    And since it is performing a NC index seek in 2000, there is no error in 2000 but the error occurs in 2005 since it does a NC index scan on the same index.

    Is this any patch or hotfix related issue?

    Thanks

    Chetan
  8. satya Moderator

    Whats the problem in using NC index scan if the performance is better than previous version?
    Have you recompiled & reindexes necessary SPS, triggers & indexes

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. chetanjain04 Member

    I have to use convert for making the query run sucessfully in 2005.

    CASE WHEN cast(N_F_COD_TXN_MNEMONIC as varchar(10)) IN (SELECT V_M_PARAM_KEY_VALUE FROM CAR_PARAMETER WHERE V_M_PARAM_KEY='CASA_TXN_CODE' AND V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_AMT_DEP_LC' AND V_M_PARAM_KEY_STATUS='Y') THEN SUM(ISNULL(N_F_AMT_TXN,0)) END AS AMT_DEP_LC,

    SQL server tries to typecast the varchar to smallint inside the scan and some rows are not containing integer data. Due to this, I am getting the error mentioned in prev. post.

    I would like to know what is best approach to avoid this error. Whether do some design change and convert the integer (N_F_COD_TXN_MNEMONIC) to varchar type in the table itself.

    or

    use cast or convert in the query as done above.

    or any other suggestions?



    Best Regards,

    Chetan
    "Calm seas can never make skillful sailors".
  10. alzdba Member

    - What is the sp-level of your sql2000 ? SP4 also suffers more on implicit conversions.

    - Did you try :
    SELECT A.C_F_COD_ACCT_NO,
    A.N_F_COD_TXN_MNEMONIC,

    CASE WHEN B.V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_AMT_DEP_LC'
    THEN SUM(ISNULL(N_F_AMT_TXN,0))
    END AS AMT_DEP_LC,

    CASE WHEN B.V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_AMT_WITHD_LC'
    THEN SUM(ISNULL(N_F_AMT_TXN,0))
    END AS AMT_WTHD_LC,

    CASE WHEN B.V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_CASH_DEP_LC'
    THEN SUM(ISNULL(N_F_AMT_TXN,0))
    END AS TOT_CSH_DPST_LC,

    CASE WHEN B.V_M_PARAM_KEY_CODE='COD_TXN_MNEMONIC_CASH_WITHD_LC'
    THEN SUM(ISNULL(N_F_AMT_TXN,0))
    END AS TOT_CSH_WTHD_AMT_LC

    FROM TableA A
    left join TableB B
    on B.V_M_PARAM_KEY='CASA_TXN_CODE'
    AND B.V_M_PARAM_KEY_STATUS='Y'
    AND B.V_M_PARAM_KEY_CODE in ('COD_TXN_MNEMONIC_AMT_DEP_LC', 'COD_TXN_MNEMONIC_AMT_WITHD_LC', 'COD_TXN_MNEMONIC_CASH_DEP_LC', 'COD_TXN_MNEMONIC_CASH_WITHD_LC')
    -- this will give an error if there is a non-numeric value !
    and A.N_F_COD_TXN_MNEMONIC = convert(smallint, B.V_M_PARAM_KEY_VALUE))

    GROUP BY A.C_F_COD_ACCT_NO, A.N_F_COD_TXN_MNEMONIC

    order by A.C_F_COD_ACCT_NO, A.N_F_COD_TXN_MNEMONIC

Share This Page