Rebuild indexes in SQL Server 2005

If you have a database with millions of records on which you provide search facility to customers then you will need to maintain indexes so that the visitor experience is enhanced.

SQL Server 2005 allows you to create indexes. Its up to you to decide which columns should be indexed. Most probably those columns should be indexed which serve as the search criteria for the visitor for e.g. name, address, phone etc.

Now you may say that you had created indexes once then why should you rebuild them?
You should remember that as new data is inserted into the table the index is not updated. So after some time you will start to see performance drop when searching. This is the time when your index requires you to rebuild it.

Use the following SQL statement in SQL Server 2005 to rebuild the index

ALTER INDEX [ALL|] ON REBUILD

If you are an experienced DBA then you can also specify the FILLFACTOR after the REBUILD. If you are not then the above statement will do the trick.

You will see a marked improvement in the performance.

Hope the above helps

Leave a Reply

Your email address will not be published.