Have you tried doing a reindex of the entire database? DBCC REINDEX I think. I realize this takes quite a while, but we do this periodically to keep things straightened up.
What we've seen in the past when dealing with SQL Server is the stored procs are using the correct indexes on the develoment server, but upon moving them to the production server they use totally different ones that make absolutely no sense. Looking at the query plan seems to indicate that may be the case. Have you tried hard coding the indexes on the production server to use? It goes something like this. select count (mr.mail_row_id) as rows from t_mail_row mr (INDEX = MY_INDEX) where mr.mail_list_id = 31 and mr.mail_row_id not in ( select distinct mr.mail_row_id from t_mail_row mr, t_mail_row_action mre where mr.mail_list_id = 31 and mre.Mail_List_action_ID = 1 and Left(mre.column2,10) = left(mr.column2,10) and Left(mre.column3,10) = left(mr.column3,10) and Left(mre.column4,10) = left(mr.column4,10)) and mr.column7 <> 'HI' and mr.column7 <> 'MT' and mr.column7 <> 'OR' and mr.column7 <> 'VT' We've found that we've had to do this in order for them to use the same indexes. It's pretty easy to see which indexes to use based on the query plan from the development server. Good luck and let me know how it goes. Chris --- "Dunwiddie, Bruce" <[EMAIL PROTECTED]> wrote: > I'm really kind of stuck on this one and it's a bit > out of my realm, but we > really need to find a solution. Basically, a query > that's been basically > been running fine on the dev server was moved to the > production server along > with a few database changes, renaming of a couple > tables and keys and such, > and now the query doesn't even come back. The normal > run time of the query > should be less than 30 s, and I ran the query in > production, using query > analyzer, and had to stop execution at over 14 m. > I then started breaking the query up into parts to > see if I could find some > problem with the indexes from the move to prod, and > got down to one part > that was running in 12 s, and like 90% of the time > was being spent on a > clusted index seek, which made no sense to me at > all. Out of desperation, I > removed the clustered index, and reran the query, > and this time it took only > 1 s. I then reapplied the clustered index, reran the > query, and it stayed at > 1 s, so I went back to the whole query, and reran > it, and it still didn't > return after several minutes. I then went back to > breaking up the query, and > this is where I get lost, and reran all the seperate > parts of the query, and > the slowest part runs in 3 s. I then compared > execution plans between prod > and dev, and they're considerably different, even > though the indexes all > appear to be the same. Here's the rundown of my > results, and as far as I can > tell, there's something that's happening in sql > server's execution plan that > is making it basically run into a wall. > > Here's the query that is never returning. The > subquery is returning 0 > results in this situation. > > select count (mr.mail_row_id) as rows from > t_mail_row mr where > mr.mail_list_id = 31 and mr.mail_row_id not in ( > select distinct > mr.mail_row_id from t_mail_row mr, t_mail_row_action > mre where > mr.mail_list_id = 31 and mre.Mail_List_action_ID = 1 > and > Left(mre.column2,10) = left(mr.column2,10) and > Left(mre.column3,10) = > left(mr.column3,10) and Left(mre.column4,10) = > left(mr.column4,10)) and > mr.column7 <> 'HI' and mr.column7 <> 'MT' and > mr.column7 <> 'OR' and > mr.column7 <> 'VT' > > this query runs in 3 s > > select distinct mr.mail_row_id from t_mail_row mr, > t_mail_row_action mre > where mr.mail_list_id = 31 and > mre.Mail_List_action_ID = 1 and > Left(mre.column2,10) = left(mr.column2,10) and > Left(mre.column3,10) = > left(mr.column3,10) and Left(mre.column4,10) = > left(mr.column4,10) > > this query runs < 1 s > > select count (mr.mail_row_id) as rows from > t_mail_row mr where > mr.mail_list_id = 31 and mr.mail_row_id not in ( 1 ) > and mr.column7 <> 'HI' > and mr.column7 <> 'MT' and mr.column7 <> 'OR' and > mr.column7 <> 'VT' > > this query runs < 1 s > > select count (mr.mail_row_id) as rows from > t_mail_row mr where > mr.mail_list_id = 31 and mr.column7 <> 'HI' and > mr.column7 <> 'MT' and > mr.column7 <> 'OR' and mr.column7 <> 'VT' > > I even tried added a duplicate row into t_mail row > so that the subquery > returned a result and it still didn't return. I've > tried removing and > reapplying related fk's and pk's, adding extra > indexes, and still nothing. > It's sql server 7. Any suggestions at this point > would be helpful as I'm at > a COMPLETE loss. The indexes all appear fine and > this just flat shouldn't be > happening, so I'm assuming there's some form of > repair that I need to do to > the db to reset this... > __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/[email protected] Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
