Index Maintenance

Question

What is index maintenance and how to do it? How often do I have to do this? What are the benefits? This is due to the transaction table, which is subject to frequent changes; all DML operations will be performed in this table.

Answer 1

I'm second to everything Jonathan said, except for the frequency of index maintenance.

Well, if you have a poorly designed index (for example, a clustered index in a GUID key), you really need to do this at least every night or even during the day.

As a general rule: if index fragmentation is below 5%, everything is in order. If you have fragmentation between 5% and approx. 30%, you must do an index reorganization:

ALTER INDEX (your index name) ON (your table name) REORGANIZE 

If your index has an index fragmentation of more than 30%, you need to completely restore it:

 ALTER INDEX (your index name) ON (your table name) REBUILD 

Index recovery can be disruptive - try to do it after hours, for example. during the night.

To determine index fragmentation, you can use this DMV query:

 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 

Michelle Ufford has an excellent auto- defrag script index - highly recommended! Or you should look at setting up SQL Server maintenance plans that might run, for example. every night and clear your indexes.

Mark

Answer 2

In general, the DBMS will automatically track the index so that it still works. However, with a sufficient number of insert, delete and update operations, it is possible that there will be room for improvement in the index. That is, if the index has been reset and recreated, the resulting index will be smaller and more efficient than the modified index. And ultimately, smaller indexes mean fewer I / O, and I / O is significantly more expensive than calculations.

There may be answers in SQL Server that I don't know, but:

• Index maintenance will be the process that makes the index more efficient.
• One way to maintain an index is to reset and restore the index.
• Another way could be to modify the table so that the indexes need to be rebuilt (possibly a cluster).
• You probably do not need to do this very often, but it will depend primarily on the quality of MS SQL Server and the way indexes are processed. I expect this to be at least adequate. Maybe once a month, if you have convenient downtime for this. Otherwise, less often than not at all.
• The advantage is less space used by the index and better performance using the index.