Menu
Currently our database has size 10 GB and is growing by around 3 GB per month. Often I hear that one should from time to time rebuild the indexes, to improve the query execution time. So how often should I rebuild the indexes in the given scenario?
610
Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.
The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented).
You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table.
When and how often should you Rebuild Indexes? The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics.
There's a general consensus that you should reorganize ("defragment") your indices as soon as index fragmentation reaches more than 5 (sometimes 10%), and you should rebuild them completely when it goes beyond 30% (at least that's the numbers I've heard advocated in a lot of places).
Michelle Ufford (a.k.a. "SQL Fool") has an automated index defrag script, which uses those exact limits for deciding when to reorganize or rebuild an index.
Also see Brad McGehee's tips on rebuild indexes with some good thoughts and tips on how to deal with index rebuilding.
I use this script here (can't remember when I got this from - whoever it was: many thanks! Really helpful stuff) to display the index fragmentation on all your indices in a given database:
SELECT t.NAME 'Table name', i.NAME 'Index name', ips.index_type_desc, ips.alloc_unit_type_desc, ips.index_depth, ips.index_level, ips.avg_fragmentation_in_percent, ips.fragment_count, ips.avg_fragment_size_in_pages, ips.page_count, ips.avg_page_space_used_in_percent, ips.record_count, ips.ghost_record_count, ips.Version_ghost_record_count, ips.min_record_size_in_bytes, ips.max_record_size_in_bytes, ips.avg_record_size_in_bytes, ips.forwarded_record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.tables t ON ips.OBJECT_ID = t.Object_ID INNER JOIN sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id WHERE AVG_FRAGMENTATION_IN_PERCENT > 0.0 ORDER BY AVG_FRAGMENTATION_IN_PERCENT, fragment_count "When you need to" and "When you can"!
For example...
Test for fragmentation first and decide whether to do nothing, reorg or rebuild. SQL Fool's script does this, for example, has @minFragmentation and @rebuildThreshold parameters
Do statistics daily, say, but indexes at weekends. What is your maintenance window?
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
