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]
 

Reply via email to