Wednesday, March 7, 2012

DBCC DBREINDEX does nothing

Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
I have tried:
DBCC DBREINDEX ('<table_name>')
DBCC DBREINDEX (<table_name>)
DBCC DBREINDEX ('<table_name>', '')
DBCC DBREINDEX ('<table_name>', '<index_name>')
DBCC DBREINDEX ('<table_name>', '<index_name>', 80)

Maybe its "optimising" and not thinking anything needs updating.

Should I look for an alternative way of rebuilding an index like dropping and recreating?

The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.For small tables (up to around 1000 pages), DBCC is pretty quick. Your hardware will also determine the speed of the operation, as well. In order to see if DBCC is actually doing anything, you should run dbcc showcontig before and after to see if the density has changed. Note also for very small tables (under 8 pages), DBCC DBREINDEX has no effect. Neither does drop/rebuild of indexes. These tables are simply too small to actually have the data rearranged.|||From the help it suggests you get something like:

Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.|||I have never seen those messages (Index (ID = 1) is being rebuilt). Doesn't seem to return messages even with trace flag 3604 turned on. The article says it was updated July 2003, too. Looks like you may have found a bug in the documentation.|||I also do exactly what you mention (do the reindex on all tables in my database from the system list of user tables) on a weekly basis, and just get the summary data that you mention in your posts. I have never seen the interim "status" message in my reindex logs, and as mentioned perform the operation on my complete database (approx 35 tables) on a weekly basis.|||It looks like DBCC DBREINDEX isn't the answer.
In my case I need to drop and recreate the indexes for them to be properly rebuilt.
While I can write a script with an entry for every single index this is hard work as there are a lot of indexes.

I really would like to write a script to loop through the tables, get the index info and drop and recreate them in a single statement.
The problem with this is that the database has foreign keys.
Therefore tables need to be done in a certain order.
If you drop an index a foreign key is based on, it naturally produces an error.

No comments:

Post a Comment