Tags
Alter Index, fragmentation, index fragmentation, index maintenance, Index Rebuild, Index Reorganize, indexes
To keep the indexes in proper shape, a timely defragmentation is necessary. There is no any thumb rule when to defragment the indexes, it depends on the environment. However, this script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.
Firstly do not blindly REBUILD every index which not only increases your I/O’s but also create huge amount of T-log, and if you have enabled mirroring or log shipping then it would take considerable time to ship these logs, defragment your indexes smartly.
In our environment we follow the index maintenance as given below:
If fragmentation is less than 10% or index page count is less than 100 no action will be taken.
If fragmentation is between 10-30% then the index will be reorganized and statistics will be updated. (REORGANISE index will not update STATISITCS automatically)
If fragmentation is greater than 30% index will be rebuilt.
You can also download this script from Microsoft Gallery
Hope this helps.!
sqlerrors 