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...
