Query plan mismatch in SQL 2000 and 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query plan mismatch in SQL 2000 and 2005

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"
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
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 />
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.
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
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.
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
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.
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".
– 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

]]>