在SQLServer2000和2005中怎样探测表碎片
在SQLServer2000和2005中怎样探测表碎片--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址
怎样知道一个表什么时候有碎片? - 查询超时，性能下降
- 磁盘活动增多
- 缓存利用不高
- 检查查询的I/O
- 在2000中使用DBCC SHOWCONTIG检查Scan density，在2005中使用动态管理视图sys.dm_db_index_physical_stats.
当使用INSERT、UPDATE或DELETE语句修改数据时就可能产生表碎片，随着时间的推移，每个页面都可能产生碎片。 如果一个查询是基于表扫描或部分基于表扫描，那么为了额外的页面读操作将为SQLServer创建很多的处理，从而导致CPU过高以致没有反应。 碎片整理的对象是索引页级的逻辑碎片，以确保最优性能。
索引碎片的影响有哪些? 它将降低性能；正常的查询会花费更长的时间去显示结果。如果查询参数是小的，基于取得的数据，查询优化器选择顺序扫描操作，一旦表有正确的索引将很快找到数据。表是否有正确的索引不总是一个很容易回答的问题，你不得不依赖于查看所有的执行计划或者和事件探查器一起使用索引优化向导。
怎样测量表的碎片并减少碎片? 在SQLServer2000里，可以使用DBCC SHOWCONTIG来显示一个表的scan density和碎片级别。如果该语句带上WITH TABLERESULTS子句，那么它将显示一个额外信息的行集。DBCC INDEXDEFRAG和DBCC DBREINDEX语句用来减少碎片是最好的语句。
DBCC INDEXDEFRAG将整理索引的页级，这将使页的物理顺序和页节点的逻辑顺序相匹配。该语句每隔5分钟向用户报告预计完成的百分比。
即使正确地创建了索引，随着时间的推移，由于通过INSERT、UPDATE、DELETE操作引起的碎片往往引起性能问题。碎片（页面上的自由空间）的结果在为了一个特定的数据集使用查询参数时将导致内存使用不正确。
SQL Server 2005中的新特性是什么? DBCC SHOWCONTIG语句将被取代。使用动态管理函数
sys.dm_db_index_physical_stats将帮助你检测表或索引视图上特定索引或者所有索引的碎片。如果是分区索引，DMV也将为每个分区提供碎片信息。该DMV的通常用法如下：
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'<Database Name>'), OBJECT_ID(N'<Table Name>'), NULL, NULL , '<SCANNING MODE>'); 如要返回所有表的所有索引的信息，使用：
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL); 函数的扫描模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。有3种模式分别为：LIMITED、 SAMPLED和DETAILED，且只需要意向共享 (IS) 表锁。缺省的模式时LIMITED, 它显示页计数和外部碎片，没有任何页密。模式SAMPLED和DETAILED将为内部和外部碎片分析整个表。 列
avg_fragmentation_in_percent 表示一个索引或堆的索引级别。表值函数返回一个结果集。可以使用SELECT INTO捕获整个结果集。
SELECT * INTO #tbl_Fragmentation FROM sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL, Mode); 请注意上面的动态管理函数的扫描将是LIMITED，在2005里它是缺省模式。为可得到更多的信息，执行如下语句：
SELECT * INTO #tbl_Fragmentation FROM sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL, DETAILED); 在确定的情况下，SQL Server 2005使用一个称作"forwarded rows"的特征。例如，当一个有变长列的表被一个页面都容不下的数据更新时，SQL Server在那页创建一个前向指针。该指针一直保留直到行被压缩到能移回原来的位置。当数据库收缩时它也会压缩，这将避免产生前向行而再分配一个行标识。为了获得第一个行指针去重新定位行而创建额外的I/O，这会对性能有负面影响。动态管理函数
sys.dm_db_index_physical_stats既提供前向记录计数，也提供记录百分比。该函数帮助监控正在改变的信息如锁、索引状态等等。为了仅得到索引信息的碎片百分比，可以和表
sysindexes 做联合查询，如下：
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName, phystat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 10 AND phystat.avg_fragmentation_in_percent < 40 一些避免碎片的建议 - 创建数据库时，尽量为数据文件分配一个大的空间。你可以估计某一时期（比如至少3年）可能达到的最大值。
- 有时将数据文件设为自动增长是可行的。通过设置数据文件最大增长尺寸保持增量限制，在硬盘上保留一些可用空间。
- 一段时间后，如果需要，确定并重新估计数据库尺寸的最大值，为数据库添加更多的文件或者文件组。
- 如果有很多数据文件共用一个磁盘分区，别让数据文件自动增长。如果数据文件使用过多，那么将它们分配到不同的文件组或者磁盘。
- 定期执行数据库维护任务，如DBCC DBREINDEX、重新编译存储过程和触发器。
- 如果表的记录被频繁的修改或者删除，最好在该表上不定期的运行UPDATE STATISTICS，这将从执行计划里帮助避免性能降低。
