I'd have to double check but I'm pretty sure the DBCC reindex command will do the entire database.
Good luck. --- "Dunwiddie, Bruce" <[EMAIL PROTECTED]> wrote: > I had looked at the reindex command, but wasn't > prepared to do one for each > index as the syntax appeared, but that command > without any other attributes > will reindex the entire db? I've also never seen the > runtime index > declaration syntax you suggested, so I'll play with > that. I'll let you know > if I get anywhere with it. > > -----Original Message----- > From: Chris Stallo > To: [EMAIL PROTECTED] > Sent: 11/30/02 6:19 AM > Subject: Re: [KCFusion] messed up indexes in sql > server > > 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/cf-list@kcfusion.org > Questions, Comments or Glowing Praise.. > mailto:[EMAIL PROTECTED] > To Subscribe.................... > mailto:[EMAIL PROTECTED] > To Unsubscribe................ > mailto:[EMAIL PROTECTED] > > __________________________________________________ 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/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
