Rebuild indexes

For the query optimizer to generate the best possible query plans and use the right indexes, the indexes shouldn’t be fragmented. Indexes become fragmented over time based on the update, insert, or delete rate. Make sure that tables are re-indexed on a regular basis. The rebuild frequency depends on the rate at which the database handles data manipulation language (DML) operations.

A good starting point would be to rebuild indexes that are fragmented more than 30%, and reorganize indexes that are fragmented less than 30%. The 30% value works in a majority of use cases, but if you still see poor query plans due to unused indexes, you might need to revisit this percentage.

Use a query like the following to check for fragmentation:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'<your_database>'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

We recommend that you create a maintenance job to rebuild the indexes on a regular basis.