Reorganise Indexes
When all indexes are known that are involved in the slow queries, it is worth to try to reorganise them and run the queries again.
Reorganise vs Rebuild
The main difference between them, is what the name says: reorganising means a form of defragmentation of the stored data, while rebuilding is dropping the index and re-creating.
Unlike reorganising an index, which is always an online operation, meaning it can be done without holding long-term locks, rebuilding on the other hand can either be an online or offline operation and uses more resources. Indexes being rebuilt may not be available for the SQL query planner to use when calculating on how to execute a query, causing potential performance hits on the query execution.
For a more detailed and technical explanation, read this Microsoft article: Optimize index maintenance to improve query performance and reduce resource consumption.
Index Defragmentation Guidelines
It is generally advised to use index reorganisation first unless only a rebuild can improve the performance. Everything comes down to the amount of fragmentation an index has:
- Fragmentation < 10%: no defragmentation is required.
- 10% <= Fragmentation < 30%: index reorganization is suggested.
- Fragmentation >= 30%: index rebuild is most likely the better option.
Some sources even suggest that the split between reorganise and rebuild should be an index fragmentation of up to 40%. The real value to use depends on the data size of the table and how many transactions are recorded on the table/index. If an index must be rebuild regularly because the fragmentation exceeds the 30% limit on a repeatedly basis, using the 40% as the new limit might be a good idea to decrease the number of rebuild actions.
Checking Index Fragmentation
To know the amount of index fragmentation, consult the Properties dialog from the index, item Fragmentation.
The percentage on the right states the index fragmentation. With the 2.44% in the example above, it is clear that that index doesn't require any action.
De-fragmenting Indexes
De-fragmenting single indexes can be done straight from SQL Server Management Studio.
- Right-click on the index to be de-fragmented.
Select either Rebuild or Reorganize:
Click OK in the dialog to start the rebuild or reorganisation.
Next Steps
After improving the indexes, the next step would be to Update Statistics of tables.
Automatic Reorganisation
Alternatively, a maintenance plan can be created for automatic index reorganisation. De-fragmenting indexes one by one is fine for a couple of indexes and for a one-time action.
However if it is necessary to restructure indexes on a more regular basis, it is advised to create a maintenance plan to automate those actions: Create Maintenance Plan.