索引维护 [英] Index Maintenance
问题描述
什么是索引维护,我如何做?我需要多长时间做一次?
有什么好处?
这与一个经常修改的事务表有关;所有DML操作都将在该表上运行。
What is Index Maintenance and how do I do it? How frequently do I have to do it? What are the benefits? This is related to a transaction table which is subject to frequently modifications; all DML operations will be run on that table.
推荐答案
我继承Jonathan所说的一切 - 除了索引维护的频率。
I second everything that Jonathan said - except for the frequency of index maintenance.
好吧,如果你碰巧有一个设计不佳的索引(例如GUID密钥上的聚集索引),你实际上可能需要至少每天晚上做一次 - 或者甚至在白天。
Well, if you happen to have a poorly designed index (such as a clustered index on a GUID key), you might actually need to do it at least every night - or even during the day, too.
作为一般的经验法则:如果你的索引碎片低于5%,一切都很好。如果您的碎片在5%和约5%之间。 30%,您应该进行索引重组:
As a general rule of thumb: if your index fragmentation is below 5%, all is fine. If you have fragmentation between 5% and approx. 30%, you should do an index reorganization:
ALTER INDEX (your index name) ON (your table name) REORGANIZE
如果您的索引的索引碎片大于30%,则需要完全重建:
If your index has index fragmentation of more than 30%, you need to rebuild it completely:
ALTER INDEX (your index name) ON (your table name) REBUILD
重建索引可能会造成中断 - 尝试在非工作时间执行,例如
Rebuilding an index can be disruptive - try to do it at off-hours, e.g. during the night.
为了确定索引碎片,您可以使用此DMV查询:
In order 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有一个很好的自动< a href =http://sqlfool.com/2009/03/automated-index-defrag-script/ =noreferrer> index defrag script - 强烈推荐!或者,您应该考虑设置 SQL Server维护计划,该计划可以运行每天晚上清理您的指数。
Michelle Ufford has a great automatic index defrag script - highly recommended! Or then you should look into setting up SQL Server maintenance plans which can run e.g. every night and clean up your indices.
Marc
这篇关于索引维护的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!