This script remedy index fragmentation by reorganizing or rebuilding an index.
Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy
We are talking about the guidance which is:
•if a table has less than 10000 rows, to do nothing
•if an index has less than 1000 pages, to do nothing
•if the index has:
less than 5% logical fragmentation, to do nothing
between 5% and 30% logical fragmentation, reorganize it
more than 30% logical fragmentation, rebuild it
• New FILLFACTOR = 98%
Why do we fix something we do not need it?
We should start to delete unused indexes if it is possible: [see Unused indexes]
•Unused index, delete it, when :
- Nonclustered indexes
- Non-primary keys
- Non unique
- Non-unused (#Total read < 100 and #total write > 1000 )
- Hypothetical Index.
1. We can buy an application for index monitoring and analysis.
2. We can change the configuration. ex: MIXED_PAGE_ALLOCATION
3. We can create better indexes.
4. We can use this nice solution of Michelle Ufford [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]