SQL 2005+

Removing SQL Server Index Fragmentation

by Richard Carr, published at http://www.blackwasp.co.uk/SQLIndexFragmentation.aspx

Over time, the indexes applied to tables in a SQL Server database can become fragmented because of row insertions, updates and deletions. This fragmentation can severely impact the performance of the database so should be rectified periodically.

Using SQL Server Management Studio to Rebuild or Reorganise Indexes

Using scripts to reorganise and rebuild indexes is ideal when you wish to automate these processes. For one-off changes you can achieve the same results with SQL Server Management Studio commands. To determine the level of fragmentation of an index, use the Object Explorer to locate the index by expanding the tree node for the associated table, then expanding the Indexes node before right-clicking the index name and choosing Properties from the context-sensitive menu. The fragmentation percentage appears on the Fragmentation page.

To reorganise or rebuild an index simply right-click it in the Object Explorer and choose either Reorganize or Rebuild from the pop-up menu. You can also perform the action against every index in a table by right-clicking the table name and selecting the appropriate action.