BEST way to reindex a table

The maintenance plans in SQL 2000 for reindexing only cater for doing whole databases at once.
This is causing me some serious grief with it running for 5-6 hours and contention with the backup.
I want to be able to reindex individual tables.
I know about the reindex command and also dropping and doing the indexes from scratch.
REINDEX for two tables however had not yielded great results (> 1 minute response times for getting back 42 results using an inner join).

So I thought I'd ask here.
Given 45 tables ranging from 500,000 rows to 110,000,000 rows (all exist in the same database) most of which are in the millions+ rows what would you consider the best way is to reindex them?
Are there any scripts for just doing selected tables which can be scheduled?

LVL 15
Who is Participating?
dbaSQLConnect With a Mentor Commented:
This is the best write-up on v2000 index fragmentation/reindexing, in my opinion.

it, too, supports that you first must identify the indices that are fragmented, and then walks thru the factors to be considered when you are determining exactly how and when to reindex

further, it states this re updating stats:

>>There are two distinct advantages of running DBCC DBREINDEX over DBCC INDEXDEFRAG:

DBCC DBREINDEX rebuilds statistics automatically during the rebuild of the indexes; this can have dramatic improvements on workload performance.

DBCC DBREINDEX can take advantage of multiple-processor computers and can be significantly faster when rebuilding large or heavily fragmented indexes.
tbsgadiConnect With a Mentor Commented:
Hi qz8dsw,

Have a look at Tips for Rebuilding Indexes

Good Luck!


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:
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Anthony PerkinsCommented:
>>what would you consider the best way is to reindex them?<<
I would never schedule that.  Reindex should be done only when necessary.
qz8dswAuthor Commented:
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.

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,
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.  
Anthony PerkinsCommented:
>>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.
qz8dswAuthor Commented:
This is not a reflex reaction.
The query itself is fine.
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.

qz8dswAuthor Commented:
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.
dbaSQLConnect With a Mentor Commented:
i hear what you are saying.  i strongly encourage you to try updating with fullscan.  for some reason, I believe you're dealing with a situation much like the one i had.  even moreso now that you say it is consistent.  i had a data load in the am, after which one particular retrieval was perfect.  subsequent executions of the same procedure, but on  a SMALLER dataset, performed atrociously.  i thought it was a problem w/the cache and/or recompilation, i droppedcleanbuffers, rebuilt the indices --- the only thing that touched it was the update with fullscan

the first time i did it, it ran for just over an hour.  (update stats with fullscan)   interestingly enough, because the update stats without fullscan was completing in like 15 minutes.  anyway, the first time i did it w/fullscan it took more than an hour, and ever since i implemented that one step into the nightly maint on this instance, the query problem has not resurfaced.
Anthony PerkinsCommented:
>>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.
qz8dswAuthor Commented:
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.
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
exec procedure  (or whatever your sql statement is)
set showplan_all off

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.  :)
qz8dswAuthor Commented:
Then explain the results of the reindex cutting the query time by 45 seconds?
All Courses

From novice to tech pro — start learning today.