应该多久在我们的 SQL Server 数据库中重建索引? [英] How often should the indexes be rebuilt in our SQL Server database?

问题描述

目前，我们的数据库大小为 10 GB，并且每月增长约 3 GB.我经常听说应该不时重建索引，以提高查询执行时间.那么在给定的场景中，我应该多久重建一次索引?

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?

推荐答案

人们普遍认为，一旦索引碎片达到 5 个以上(有时为 10%)，就应该重新组织(碎片整理")索引，并且当超过 30% 时应该完全重建它们(至少这是我在很多地方听到的提倡的数字).

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(又名SQL 傻瓜")有一个 自动索引碎片整理脚本，它使用这些确切的限制来决定何时重组或重建索引.

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.

另请参阅 Brad McGehee 关于重建索引的技巧，其中有一些不错的想法和关于如何处理索引重建的提示.

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
    

这篇关于应该多久在我们的 SQL Server 数据库中重建索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！