Mom always warned me to keep my database indexes clean

As I have been working on my Web Search with .NET code, one of the interesting items that I have run into is the need to keep indexes clean and defragmented.  If an index becomes overly fragmented, performance can suffer greatly.  Well sports fans, Sql Server 2000 allows you to defragment indexes while the application is running.  The options on the command that I used is “dbcc indexdefrag (databasename, tablename, indexname);“  There are several options on the command that you can see here.  By defragmenting the indexes, performance jumped way back up (100 search urls / minute jumped up to about 3,000 urls / minute with Full-Text indexing turned off).  The beauty of indexdefrag is that you can defrag the indexes without taking the database offline.  So, mom was right.  Things work better when you keep your indexes cleaned up. 

Wally

1 Comment

  • And if you can, run DBCC DBREINDEX occassionally.



    "DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index."



    To see if you need to defragment, run DBCC SHOWCONTIG.

Comments have been disabled for this content.