Re indexing database tables in SQL server is a very wide topic. Ideally, we have database maintenance tasks scheduled to run during maintenance window to rebuild indexes along with other maintenance activities. The indexes require rebuild as they get fragmented in time because of DML operations being run on tables.

Query to quickly rebuild an index on a table.

Query to quickly reorganize an index on a table

Query to rebuild/reorganize all indexes on a table

The above query declares a cursor to iterate through all indexes in a table and generates the index rebuild statement as shown in first query. The statement generated can be only printed to check the index rebuild queries or can be directly executed based on @executed variable, when 0 the queries are printed and when 1 the index rebuild query is executed. In addition to this, you can specify the index rebuild options to variable @rebuild_options such as fill factor, online etc and can also decide whether to rebuild/reorganize based on @operation variable.

The above query can be scheduled as SQL Agent Job to run during database maintenance window. I would suggest not running it production hours. It will affect overall database performance.

The above query is good however not good enough to decide which index to rebuild and which not. It rebuilds/reorganizes all indexes which in turn will create lot of T-logs and will take lot of time to finish.  The better way is to rebuild/reorganize selected index. This selection is made on general Microsoft guideline as stated below

  1. Rebuild: fragmentation > 30%
  2. Reorganize: fragmentation > 5% and fragmentation < 30%

The fragmentation corresponds to avg_fragmentation_in _percent column from sys.dm_db_index_physical_stats function. The below query is taken from section D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

The query as mentioned earlier, decides which indexes are to be rebuild/reorganized. It takes care of defragmenting partitions too. It queries sys.dm_db_index_physical_stats to get list of all indexes with fragmentation greater than 10% thus avoiding overhead of rebuilding each and every index. It then iterates through the index list and rebuilds indexes with fragmentation greater than or equal to 30% and reorganizes indexes with fragmentation lesser than 30%.

The query can be scheduled or can be used to quickly rebuild indexes to resolve performance as and when needed.


Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook