BEST way to reindex a table

http://technet.microsoft.com/en-us/library/cc966523.aspx

In short, it depends on what you've got (how many indices, how much data), and how much time you've got to get the job done.  DBCC DBREINDEX vs DBCC INDEXDEFRAG.   The former is an off-line operation, the latter is an on-line op which can be done whilst the database is in use.  The INDEXDEFRAG requires more time to complete, and in my opinion, the DBREINDEX is better, as it is a complete rebuild of the index(es).

best thing to do is run DBCC SHOWCONTIG on your tables to assess your fragmentation.  based on your scan density, per table, you can decide which should be redindexed, and whether it should be done now, or it can wait until your down time.  

now = indexdefrag
down time = dbreindex

there are a million procedures out there that will help you make the decision on which indices to defrag and when.  this is a very good starting point:
http://www.sqlskills.com/resources/demoscripts/sp_RebuildIndexes.txt

>>what would you consider the best way is to reindex them?<<
I would never schedule that.  Reindex should be done only when necessary.

@acperkins,
Given the explaination what would you suggest then. (I was thinking about a one off reindex)
It has been found that an inner join to a second table is causing the query to blow up from 2 seconds to 1+ minutes.

@all,
I think I should open a new question about the specific problem, but one question to do with this question I do have.
I have found conflicting advice on the net for re-doing the statistics after doing the DBCC DBREINDEX command.
Some saying it helps, some saying it actually hurts.

Your thoughts/experiences?
(Please remember this is SQL 2000 and not SQL 2005 or above)

Thanks for your help so far,
Terry

Also, DBCC DBREINDEX does update the stats, but it usex sp_updatestats, and only gets a sample of the statistics.   I believe to gain the most from your reindexing, you may wish to update stats (per table)with fullscan afteward.  

>>It has been found that an inner join to a second table is causing the query to blow up from 2 seconds to 1+ minutes.<<
Address the problem with the query and why it is taking a long time. Don't resort to the reflex action of reindexing the whole database.

@acperkins
This is not a reflex reaction.
The query itself is fine.
Example:
Running the query for all data dated 1-apr-2008 returns 52 records taking 14 seconds.
Running the query for all data dated 1-apr-2009 returns 42 records taking 1 minute 10 seconds.
Running the query for all data dated 1-apr-2000 returns 15 records taking 3 seconds.
Running the query for all data dated 1-apr-2006 returns 37 records taking 1 minute 31 seconds.

Always for records dated 2009 and 2006 it is > 1 minute to return yet returning less results that in 2008.
Since we are getting acceptable results for 2008 and 2000 and nothing is changing baring the indexes we would be looking at it indicates to me SQL is making a bad decision on how it runs the query.

I have seen something very similar before.  a VLDB (v2000), one of my queries was just not performing acceptably, and it made no sense whatsoever.  It was not the indices, actually.  i had been re-indexing, as needed, but a particular query performance was just intermittently unacceptable.  sometimes it was great, sometimes it was crap

I needed only to update stats with full scan.  

i did that one time based on the suggestion of an EE expert, and my runtimes improved drastically.  i have been doing it ever since, once daily, the problem has not recurred.

Thanks dbaSQL
The strange part with this is it is very consistent on when it's crap and when it's good.
The results for 2009 ALWAYS take > 1 minute, Same with the 2006 results.
2008 and 2000 results always < 15 seconds.

You can run the query on 2009, wait for it to return results, then run the exact same query (changing nothing) and it still takes > 1 minute.
You are right it makes no sense, our own DBA's are confused as to why it's doing what it's doing.
Strange part is the executation plan does not seem to do a table scan. (Or SQL 2000 is not reporting it's table scanning) which makes it all the more confusing.
I would have thought if it was the indices then you would see a table scan in the executation plan.

I have a scheduled outage Sunday my time for some database management to try and allievate this.
I'll look at updating the statistics during that outage if the reindex does not help.

>>The query itself is fine.<<
Somehow your own comments and the fact that you are posting this question makes that statement suspect.

But fair enough, you asked my opinion and I gave it to you.  If you want to continue to reindex your database then more power to you.  We have tables with many millions of rows and have not reindexed them in years.  IMHO you are wasting your time.

Good luck and I see no further point in contributing.

Thanks tdsqadi and dbaSQL.
A reindex with a fill factor of 80 chopped 45 seconds off the affected query.
Updating the stats saved me around another 20-30 seconds.
This has bought it back into the realms of acceptable.
@acperkins
Live and learn.

Excellent.  I am glad to hear things have improved.  Unfortunately, this problem could always recur.  Might be a good idea to take a look at your showplan, just incase...  Just to see where the time is being spent, and what's really going on behind the scenes.

set showplan_all on
go
exec procedure  (or whatever your sql statement is)
go
set showplan_all off
go

Once again, I have to agree with acperkins here.  No doubt continguous data pages have their place, especially when you're retrieving ranges of rows (I'll not go into why this is important here).  However, the improvement you see from reindexing is NOT going to be monumental...sounds more like you're missing an index...not just having one where the pages are broken up all willy nilly.  :)

Then explain the results of the reindex cutting the query time by 45 seconds?