DBREINDEX does nothing!!!

Question

DBREINDEX does nothing!!!

doctordoom

Newbie

Points: 3
More actions
October 6, 2004 at 9:51 am

#89456

I'm trying to rebuild my tables' indexes using DBREINDEX, but it seems to do nothing, 'cause the results of SHOWCONTIG are exactly the same before and after the reindexing. What could be happening?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Why were you doing a reindex? Were your indexes fragmented? If they weren't fragmented then there won't be any noticable change.

Did you run DBCC UPDATEUSAGE after the reindex?

-SQLBill

doctordoom Newbie Points: 3 More actions · Answer 2

No... I'm not familiar with that command. Could you explain to me what is it for, and what is its syntax?

And yes, the indexes were fragmented according to the SHOWCONTIG report.

Thanks for your help

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hello,

Are you sure you are rebuilding in the same database where you run SHOWCONTIG? Sometimes we have several windows open in Query Analyzer connected to Development and Production servers.....

Did you specify a table name? Are you rebuilding and SHOWCONTIG the same table?

Did you try DBCC INDEXDEFRAG ?

Yelena

Regards,Yelena Varsha

schumacr SSC Veteran Points: 282 More actions · Answer 4

Check out this paper. It talks about Embarcadero space products for SQL Server, but it has a lot of good information on fragmentation, including why you might not be seeing any benefits after a reorg.

http://www.embarcadero.com/resources/tech_papers/smart_sql_server_space_management_with_space_analyst.pdf

SQLBill SSC Guru Points: 51440 More actions · Answer 5

DBCC UPDATEUSAGE 'refreshes' the sysindexes table. Without running that command, Enterprise Manager, sp_spaceused and other commands can return incorrect information. I ALWAYS run that command after I rebuild or defrag my indexes. I also run it after I shrink the database.

Refer to the BOL for more information on this command and it's syntax.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP

Installed as part of the Client Tools

Found at Start>Programs>Microsoft SQL Server>Books OnLine

GeBlack Ten Centuries Points: 1363 More actions · Answer 6

Showcontig will not show usable statistics for heap indexes. If you are looking at fragmentation statistics for your heap indexes then they probably won't change after doing a dbcc dbreindex.