在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 Fool）有自动索引碎片整理脚本，它使用这些确切的限制来决定何时重新组织或重建索引。

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屋！