SQL Server索引碎片处理

一、碎片产生的原因

        碎片是由于表中的数据修改产生的。当插入、更新表中的数据时，表对应的聚簇索引被修改，如果对索引的修改不能容纳在同一页面中，可能导致索引叶子页面被分割。从而添加一个新的页面用以包含原来页面的一部分，并且维持索引键中行的逻辑顺序。

        虽然新的页面维护了与原页面的中行的逻辑顺序，但是两个页面一般情况下在硬盘上是不相邻的

 

二、分析查看碎片数量

在这里可以使用SQL Server动态函数sys.dm_db_index_physical_stats来分析索引的碎片比率，下面看看微软的官方文档对该函数的解释：

 

[html] view plain copy print ?

1. 语法  
2.    
3. sys.dm_db_index_physical_stats (   
4.     { database_id | NULL | 0 | DEFAULT }  
5.     , { object_id | NULL | 0 | DEFAULT }  
6.     , { index_id | NULL | 0 | -1 | DEFAULT }  
7.     , { partition_number | NULL | 0 | DEFAULT }  
8.     , { mode | NULL | DEFAULT }  
9. )  
10.    
11.   
12. 参数  
13. database_id | NULL | 0 | DEFAULT  
14. 数据库的 ID。database_id 的数据类型为 smallint。有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。  
15. 指定 NULL 可返回 SQL Server 实例中所有数据库的信息。如果为 database_id 指定 NULL，则还必须为 object_id、index_id 和 partition_number 指定 NULL。  
16. 可以指定内置函数 DB_ID。如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90。  
17.   
18. object_id | NULL | 0 | DEFAULT  
19. 该索引所基于的表或视图的对象 ID。object_id 的数据类型为 int。  
20. 有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。  
21. 指定 NULL 可返回指定数据库中的所有表和视图的信息。如果为 object_id 指定 NULL，则还必须为 index_id 和 partition_number 指定 NULL。  
22.   
23. index_id | 0 | NULL | -1 | DEFAULT  
24. 索引的 ID。index_id 的数据类型为 int。有效输入包括索引的 ID 号、0（如果 object_id 为堆）、NULL、-1 或 DEFAULT。默认值为 -1。在此上下文中，NULL、-1 和 DEFAULT 是等效值。  
25. 指定 NULL 可返回基表或视图的所有索引的信息。如果为 index_id 指定 NULL，则还必须为 partition_number 指定 NULL。  
26.   
27. partition_number | NULL | 0 | DEFAULT  
28. 对象中的分区号。partition_number 的数据类型为 int。有效输入包括索引或堆的 partion_number、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。  
29. 指定 NULL，以返回有关所属对象的所有分区的信息。  
30. partition_number 从 1 开始。未分区的索引或堆的 partition_number 设置为 1。  
31.   
32. mode | NULL | DEFAULT  
33. 模式的名称。mode 指定用于获取统计信息的扫描级别。mode 的数据类型为 sysname。有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。默认值 (NULL) 为 LIMITED。  
34.   
35. 返回的表  
36. 列名                       数据类型                    说明    
37. database_id               smallint                表或视图的数据库 ID。  
38.    
39. object_id                 int                     索引所在的表或视图的对象 ID。  
40.    
41. index_id                  int                     索引的索引 ID。0 = 堆。  
42.    
43. partition_number          int                     所属对象内从 1 开始的分区号；表、视图或索引。1 = 未分区的索引或堆。  
44.    
45. index_type_desc           nvarchar(60)            索引类型的说明：HEAP,CLUSTERED INDEX,NONCLUSTERED INDEX,PRIMARY XML INDEX,                                                    SPATIAL INDEX,XML INDEX  
46.    
47. alloc_unit_type_desc      nvarchar(60)            对分配单元类型的说明：IN_ROW_DATA,LOB_DATA,ROW_OVERFLOW_DATA,LOB_DATA 分配单元包含类                                                  型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中                                                  所存储的数据。有关详细信息，请参阅数据类型 (Transact-SQL)。ROW_OVERFLOW_DATA 分配单元包                                                  含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和 sql_variant 的列（已推送到行外）中                                                  所存储的数据。有关详细信息，请参阅行溢出数据超过 8 KB。  
48.    
49. index_depth               tinyint                 索引级别数。1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。  
50.    
51. index_level               tinyint                 索引的当前级别。0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。大于 0                                                  的值表示非叶索引级别。index_level 在索引的根级别中属于最高级别。仅当 mode = DETAILED才处                                                  非叶级别的索引。  
52.    
53. avg_fragmentation_in_percent float                索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。此值按百分比计算，并将考虑多个文件。有                                                  关逻辑碎片和区碎片的定义，请参阅“备注”。0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。  
54.                                                   对于堆，当 mode 为 SAMPLED 时，为 NULL。  
55.    
56. fragment_count             bigint                 IN_ROW_DATA 分配单元的叶级别中的碎片数。有关碎片的详细信息，请参阅“备注”。对于索引的非叶级                                                   别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。对于堆，当 mode 为 SAMPLED                                                  时，为 NULL。  
57.    
58. avg_fragment_size_in_pages float                  IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。对于索引的非叶级别，以及 LOB_DATA 或                                                   ROW_OVERFLOW_DATA 分配单元，为 NULL。对于堆，当 mode 为 SAMPLED 时，为 NULL。  
59.    
60. page_count                 bigint                 索引或数据页的总数。对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。  
61.                                                   对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                    分配单元，表示该分配单元中的总页数。  
62.    
63. avg_page_space_used_in_percent  float             所有页中使用的可用数据存储空间的平均百分比。对于索引，平均百分比应用于 IN_ROW_DATA 分配单元                                                  中 b 树的当前级别。对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。对于 LOB_DA                                                  TA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。当 mode 为 LIMITE                                                  D 时，为 NULL。  
64.    
65. record_count               bigint                 总记录数。对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。对于堆，表示 I                                                  N_ROW_DATA 分配单元中的总记录数。注意：对于堆，此函数返回的记录数可能与通过对堆运行 SELECT                                                  COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可                                                   能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆                                                  分为多个记录。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。  
66.                                                   当 mode 为 LIMITED 时，为 NULL。  
67.    
68. ghost_record_count         bigint                 分配单元中将被虚影清除任务删除的虚影记录数。对于 IN_ROW_DATA 分配单元中索引的非叶级别，为0                                                   ,当 mode 为 LIMITED 时，为 NULL。  
69.    
70. version_ghost_record_count  bigint                由分配单元中未完成的快照隔离事务保留的虚影记录数。对于 IN_ROW_DATA 分配单元中索引的非叶级别                                                  ，为 0。当 mode 为 LIMITED 时，为 NULL。  
71.    
72. min_record_size_in_bytes     int                  最小记录大小（字节）。对于索引，最小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。  
73.                                                   对于堆，表示 IN_ROW_DATA 分配单元中的最小记录大小。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                  分配单元，表示整个分配单元中的最小记录大小。当 mode 为 LIMITED 时，为 NULL。  
74.    
75. max_record_size_in_bytes      int                 最大记录大小（字节）。对于索引，最大记录的大小应用于 IN_ROW_DATA 分配单元中b树的当前级别                                                  ,对于堆，表示IN_ROW_DATA 分配单元中的最大记录大小。对于LOB_DATA 或 ROW_OVERFLOW_DATA 分                                                  配单元，表示整个分配单元中的最大记录大小。当 mode 为 LIMITED 时，为 NULL。  
76.    
77. avg_record_size_in_bytes     float                平均记录大小（字节）。对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。  
78.                                                  对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                    分配单元，表示整个分配单元中的平均记录大小。当 mode 为 LIMITED 时，为 NULL。  
79.    
80. forwarded_record_count       bigint               堆中具有指向另一个数据位置的转向指针的记录数。（在更新过程中，如果在原始位置存储新行的空间不足                                                ，将会出现此状态。）除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。当 mode =                                                 LIMITED 时，对于堆为 NULL。                       
81.    
82. compressed_page_count        bigint              压缩页的数目。对于堆，新分配的页未进行 PAGE 压缩。堆在以下两种特殊情况下进行 PAGE 压缩：大量                                                 导入数据时和重新生成堆时。导致页分配的典型 DML 操作不会进行 PAGE 压缩。                                                 当 compressed_page_count 值增长到超过您所需的阈值时，将重新生成堆。对于具有聚集索引的表，c                                                 ompressed_page_count 值表示 PAGE 压缩的效率。  
83.   
84.    
85.   
86. 注释  
87. sys.dm_db_index_physical_stats 动态管理函数将替换 DBCC SHOWCONTIG 语句。此动态管理函数不接受来自 CROSS APPLY 和 OUTER APPLY 的相关参数。  
88.   
89. 扫描模式  
90. 函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。该函数遍历分配单元的页链，这些分配单元构成表或索引的指定分区。sys.dm_db_index_physical_stats 只需要一个意向共享 (IS) 表锁，而忽略其运行所处的模式。有关锁定的详细信息，请参阅锁模式。  
91.   
92. LIMITED 模式运行最快，扫描的页数最少。对于索引，只扫描 B 树的父级别页（即叶级别以上的页）。对于堆，只检查关联的 PFS 和 IAM 页；不扫描堆的数据页。在 SQL Server 2005 中，在 LIMITED 模式下扫描堆的所有页。  
93.   
94. 在 LIMITED 模式下，compressed_page_count 为 NULL，这是因为数据库引擎只能扫描 B 树的非叶页和堆的 IAM 和 PFS 页。使用 SAMPLED 模式可以获取 compressed_page_count 的估计值，使用 DETAILED 模式可以获取 compressed_page_count 的实际值。SAMPLED 模式将返回基于索引或堆中所有页的 1％ 样本的统计信息。如果索引或堆少于 10,000 页，则使用 DETAILED 模式代替 SAMPLED。  
95.   
96. DETAILED 模式将扫描所有页并返回所有统计信息。  
97.   
98. 从 LIMITED 到 DETAILED 模式，速度将越来越慢，因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别，请使用 LIMITED 模式。它的速度最快，并且对于索引的 IN_ROW_DATA 分配单元中的每个非叶级别，不返回与其对应的一行。  
99.   
100. 使用系统函数指定参数值  
101. 可以使用 Transact-SQL 函数 DB_ID 和 OBJECT_ID 指定 database_id 和 object_id 参数的值。但是，将无效的值传递给这些函数可能会导致意外结果。例如，如果找不到数据库或对象名（因为它们不存在或拼写错误），则两个函数都返回 NULL。sys.dm_db_index_physical_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。  
102.   
103. 此外，将在调用 sys.dm_db_index_physical_stats 函数之前处理 OBJECT_ID 函数，因此会在当前数据库（而不是在 database_id 中指定的数据库）的上下文中对 OBJECT_ID 函数取值。此行为可能会导致 OBJECT_ID 函数返回 NULL 值；或者，如果当前数据库上下文和指定数据库中都存在对象名，则可能返回一条错误消息。以下示例说明这些意外的结果。  
104.   
105. 在下面的示例中，将在 master 数据库的上下文中计算 OBJECT_ID。因为 master 中不存在 Person.Address，所以此函数返回 NULL。当将 NULL 指定为 object_id 时，将返回数据库中的所有对象。当指定了无效的对象时，将返回相同的结果。  
106.   
107. USE master;  
108. GO  
109. SELECT * FROM sys.dm_db_index_physical_stats  
110.     (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
111. GO  
112.    
113.   
114. 下面的示例显示当指定的一个有效对象名称同时位于当前数据库上下文中和在 sys.dm_db_index_physical_stats 函数的 database_id 参数中指定的数据库中时所生成的结果。因为 OBJECT_ID 返回的 ID 值与指定数据库中的对象的 ID 值不匹配，所以返回一个错误。  
115.   
116. CREATE DATABASE Test;  
117. GO  
118. USE Test;  
119. GO  
120. CREATE SCHEMA Person;  
121. GO  
122. CREATE Table Person.Address(c1 int);  
123. GO  
124. USE AdventureWorks2008R2;  
125. GO  
126. SELECT * FROM sys.dm_db_index_physical_stats  
127.     (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');  
128. GO  
129. -- Clean up temporary database.  
130. DROP DATABASE Test;  
131. GO  
132.    
133.   
134. 最佳实践  
135. 请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。例如，在使用 OBJECT_ID 时，请指定三部分的名称，如 OBJECT_ID(N'AdventureWorks2008R2.Person.Address')，或者在 sys.dm_db_index_physical_stats 函数中使用由函数返回的值之前对这些值进行测试。下面的示例 A 和 B 演示了一种指定数据库和对象 ID 的安全方法。  
136.   
137. 检测碎片  
138. 在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和 DELETE 语句）的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询，这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。  
139.   
140. SQL Server 2008 中的碎片计算算法比 SQL Server 2000 中的更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果一个表的第 11 页和第 13 页在同一区中，而第 12 页不在该区中，该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作，因此，在 SQL Server 2008 中，这将算作碎片。  
141.   
142. 索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆，此值表示堆的区碎片。对于索引，此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同，这两种情况下的碎片计算算法都会考虑跨越多个文件的存储，因而结果是精确的。  
143.   
144. 逻辑碎片   
145.   
146. 这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。  
147.   
148. 区碎片   
149.   
150. 这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。  
151.   
152. 为了获得最佳性能，avg_fragmentation_in_percent 的值应尽可能接近零。但是，从 0 到 10％ 范围内的值都可以接受。所有减少碎片的方法（例如重新生成、重新组织或重新创建）都可用于降低这些值。有关如何分析索引中碎片程度的详细信息，请参阅重新组织和重新生成索引。  
153.   
154. 减少索引中的碎片  
155. 当索引分段的方式导致碎片影响查询性能时，有三种方法可减少碎片：  
156.   
157. （1）、删除并重新创建聚集索引。  
158.   
159. 重新创建聚集索引将对数据进行重新分布，从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。这种方法的缺点是索引在删除和重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不能重新创建索引。有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。  
160.   
161.   
162. （2）、使用 ALTER INDEX REORGANIZE（代替 DBCC INDEXDEFRAG）按逻辑顺序重新排序索引的叶级页。由于这是联机操作，因此在语句运行时仍可使用索引。中断此操作时不会丢失已经完成的任务。此方法的缺点是在重新组织数据方面不如索引重新生成操作的效果好，而且不更新统计信息。  
163.   
164.   
165. （3）、使用 ALTER INDEX REBUILD（代替 DBCC DBREINDEX）联机或脱机重新生成索引。有关详细信息，请参阅 ALTER INDEX (Transact-SQL)。  
166.   
167.   
168. 不需要仅因为碎片的原因而重新组织或重新生成索引。碎片的主要影响是，在索引扫描过程中会降低页的预读吞吐量。这将导致响应时间变长。如果含有碎片的表或索引中的查询工作负荷不涉及扫描（因为工作负荷主要是单独查找），则删除碎片可能不起作用。有关详细信息，请参阅此 Microsoft 网站。  
169.   
170. 注意：   
171. 如果在收缩操作中对索引进行部分或完全移动，则运行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能产生碎片。因此，如果必须执行收缩操作，则不应在删除碎片后进行。  
172.    
173.   
174.   
175. 减少堆中的碎片  
176. 若要减少堆的区碎片，请对表创建聚集索引，然后删除该索引。在创建聚集索引时将重新分布数据。同时会考虑数据库中可用空间的分布，从而使其尽可能优化。当删除聚集索引以重新创建堆时，数据不会移动并保持最佳位置。有关如何执行这些操作的信息，请参阅 CREATE INDEX 和 DROP INDEX。  
177.   
178. 压缩大型对象数据  
179. 默认情况下，ALTER INDEX REORGANIZE 语句将压缩包含大型对象 (LOB) 数据的页。因为不会释放空的 LOB 页，所以在删除大量 LOB 数据或 LOB 列时，压缩此数据可改善磁盘空间使用情况。  
180.   
181. 重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。重新组织非聚集索引将压缩作为索引中非键（已包括）列的所有 LOB 列。如果语句中指定 ALL，则将对与指定表或视图关联的所有索引进行重新组织。此外，将压缩与聚集索引、基础表或带有包含列的非聚集索引关联的所有 LOB 列。  
182.   
183. 评估磁盘空间使用状况  
184. avg_page_space_used_in_percent 列指示页填充度。为了使磁盘使用状况达到最优，对于没有很多随机插入的索引，此值应接近 100％。但是，对于具有很多随机插入且页很满的索引，其页拆分数将不断增加。这将导致更多的碎片。因此，为了减少页拆分，此值应小于 100％。使用指定的 FILLFACTOR 选项重新生成索引可以改变页填充度，以便符合索引中的查询模式。有关填充因子的详细信息，请参阅填充因子。此外，ALTER INDEX REORGANIZE 还试图通过将页填充到上一次指定的 FILLFACTOR 来压缩索引。这会增加 avg_space_used_in_percent 的值。请注意，ALTER INDEX REORGANIZE 不会降低页填充度。相反，必须执行索引重新生成。  
185.   
186. 评估索引碎片  
187. 碎片由分配单元中同一文件内的物理连续的叶级页组成。一个索引至少有一个碎片。索引可以包含的最大碎片数等于索引的页级别页数。碎片越大，意味着读取相同页数所需的磁盘 I/O 越少。因此，avg_fragment_size_in_pages 值越大，范围扫描的性能越好。avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值成反比。因此，重新生成或重新组织索引会减少碎片数量，但同时增大碎片大小。  

语法
 
sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
    , { object_id | NULL | 0 | DEFAULT }
    , { index_id | NULL | 0 | -1 | DEFAULT }
    , { partition_number | NULL | 0 | DEFAULT }
    , { mode | NULL | DEFAULT }
)
 

参数
database_id | NULL | 0 | DEFAULT
数据库的 ID。database_id 的数据类型为 smallint。有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回 SQL Server 实例中所有数据库的信息。如果为 database_id 指定 NULL，则还必须为 object_id、index_id 和 partition_number 指定 NULL。
可以指定内置函数 DB_ID。如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90。

object_id | NULL | 0 | DEFAULT
该索引所基于的表或视图的对象 ID。object_id 的数据类型为 int。
有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回指定数据库中的所有表和视图的信息。如果为 object_id 指定 NULL，则还必须为 index_id 和 partition_number 指定 NULL。

index_id | 0 | NULL | -1 | DEFAULT
索引的 ID。index_id 的数据类型为 int。有效输入包括索引的 ID 号、0（如果 object_id 为堆）、NULL、-1 或 DEFAULT。默认值为 -1。在此上下文中，NULL、-1 和 DEFAULT 是等效值。
指定 NULL 可返回基表或视图的所有索引的信息。如果为 index_id 指定 NULL，则还必须为 partition_number 指定 NULL。

partition_number | NULL | 0 | DEFAULT
对象中的分区号。partition_number 的数据类型为 int。有效输入包括索引或堆的 partion_number、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL，以返回有关所属对象的所有分区的信息。
partition_number 从 1 开始。未分区的索引或堆的 partition_number 设置为 1。

mode | NULL | DEFAULT
模式的名称。mode 指定用于获取统计信息的扫描级别。mode 的数据类型为 sysname。有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。默认值 (NULL) 为 LIMITED。

返回的表
列名                       数据类型                    说明  
database_id               smallint                表或视图的数据库 ID。
 
object_id                 int                     索引所在的表或视图的对象 ID。
 
index_id                  int                     索引的索引 ID。0 = 堆。
 
partition_number          int                     所属对象内从 1 开始的分区号；表、视图或索引。1 = 未分区的索引或堆。
 
index_type_desc           nvarchar(60)            索引类型的说明：HEAP,CLUSTERED INDEX,NONCLUSTERED INDEX,PRIMARY XML INDEX,                                                    SPATIAL INDEX,XML INDEX
 
alloc_unit_type_desc      nvarchar(60)            对分配单元类型的说明：IN_ROW_DATA,LOB_DATA,ROW_OVERFLOW_DATA,LOB_DATA 分配单元包含类                                                  型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中                                                  所存储的数据。有关详细信息，请参阅数据类型 (Transact-SQL)。ROW_OVERFLOW_DATA 分配单元包                                                  含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和 sql_variant 的列（已推送到行外）中                                                  所存储的数据。有关详细信息，请参阅行溢出数据超过 8 KB。
 
index_depth               tinyint                 索引级别数。1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。
 
index_level               tinyint                 索引的当前级别。0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。大于 0                                                  的值表示非叶索引级别。index_level 在索引的根级别中属于最高级别。仅当 mode = DETAILED才处                                                  非叶级别的索引。
 
avg_fragmentation_in_percent float                索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。此值按百分比计算，并将考虑多个文件。有                                                  关逻辑碎片和区碎片的定义，请参阅“备注”。0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。
                                                  对于堆，当 mode 为 SAMPLED 时，为 NULL。
 
fragment_count             bigint                 IN_ROW_DATA 分配单元的叶级别中的碎片数。有关碎片的详细信息，请参阅“备注”。对于索引的非叶级                                                   别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。对于堆，当 mode 为 SAMPLED                                                  时，为 NULL。
 
avg_fragment_size_in_pages float                  IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。对于索引的非叶级别，以及 LOB_DATA 或                                                   ROW_OVERFLOW_DATA 分配单元，为 NULL。对于堆，当 mode 为 SAMPLED 时，为 NULL。
 
page_count                 bigint                 索引或数据页的总数。对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。
                                                  对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                    分配单元，表示该分配单元中的总页数。
 
avg_page_space_used_in_percent  float             所有页中使用的可用数据存储空间的平均百分比。对于索引，平均百分比应用于 IN_ROW_DATA 分配单元                                                  中 b 树的当前级别。对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。对于 LOB_DA                                                  TA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。当 mode 为 LIMITE                                                  D 时，为 NULL。
 
record_count               bigint                 总记录数。对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。对于堆，表示 I                                                  N_ROW_DATA 分配单元中的总记录数。注意：对于堆，此函数返回的记录数可能与通过对堆运行 SELECT                                                  COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可                                                   能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆                                                  分为多个记录。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。
                                                  当 mode 为 LIMITED 时，为 NULL。
 
ghost_record_count         bigint                 分配单元中将被虚影清除任务删除的虚影记录数。对于 IN_ROW_DATA 分配单元中索引的非叶级别，为0                                                   ,当 mode 为 LIMITED 时，为 NULL。
 
version_ghost_record_count  bigint                由分配单元中未完成的快照隔离事务保留的虚影记录数。对于 IN_ROW_DATA 分配单元中索引的非叶级别                                                  ，为 0。当 mode 为 LIMITED 时，为 NULL。
 
min_record_size_in_bytes     int                  最小记录大小（字节）。对于索引，最小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
                                                  对于堆，表示 IN_ROW_DATA 分配单元中的最小记录大小。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                  分配单元，表示整个分配单元中的最小记录大小。当 mode 为 LIMITED 时，为 NULL。
 
max_record_size_in_bytes      int                 最大记录大小（字节）。对于索引，最大记录的大小应用于 IN_ROW_DATA 分配单元中b树的当前级别                                                  ,对于堆，表示IN_ROW_DATA 分配单元中的最大记录大小。对于LOB_DATA 或 ROW_OVERFLOW_DATA 分                                                  配单元，表示整个分配单元中的最大记录大小。当 mode 为 LIMITED 时，为 NULL。
 
avg_record_size_in_bytes     float                平均记录大小（字节）。对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
                                                 对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。对于 LOB_DATA 或 ROW_OVERFLOW_DATA                                                    分配单元，表示整个分配单元中的平均记录大小。当 mode 为 LIMITED 时，为 NULL。
 
forwarded_record_count       bigint               堆中具有指向另一个数据位置的转向指针的记录数。（在更新过程中，如果在原始位置存储新行的空间不足                                                ，将会出现此状态。）除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。当 mode =                                                 LIMITED 时，对于堆为 NULL。                     
 
compressed_page_count        bigint              压缩页的数目。对于堆，新分配的页未进行 PAGE 压缩。堆在以下两种特殊情况下进行 PAGE 压缩：大量                                                 导入数据时和重新生成堆时。导致页分配的典型 DML 操作不会进行 PAGE 压缩。                                                 当 compressed_page_count 值增长到超过您所需的阈值时，将重新生成堆。对于具有聚集索引的表，c                                                 ompressed_page_count 值表示 PAGE 压缩的效率。

 

注释
sys.dm_db_index_physical_stats 动态管理函数将替换 DBCC SHOWCONTIG 语句。此动态管理函数不接受来自 CROSS APPLY 和 OUTER APPLY 的相关参数。

扫描模式
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。该函数遍历分配单元的页链，这些分配单元构成表或索引的指定分区。sys.dm_db_index_physical_stats 只需要一个意向共享 (IS) 表锁，而忽略其运行所处的模式。有关锁定的详细信息，请参阅锁模式。

LIMITED 模式运行最快，扫描的页数最少。对于索引，只扫描 B 树的父级别页（即叶级别以上的页）。对于堆，只检查关联的 PFS 和 IAM 页；不扫描堆的数据页。在 SQL Server 2005 中，在 LIMITED 模式下扫描堆的所有页。

在 LIMITED 模式下，compressed_page_count 为 NULL，这是因为数据库引擎只能扫描 B 树的非叶页和堆的 IAM 和 PFS 页。使用 SAMPLED 模式可以获取 compressed_page_count 的估计值，使用 DETAILED 模式可以获取 compressed_page_count 的实际值。SAMPLED 模式将返回基于索引或堆中所有页的 1％ 样本的统计信息。如果索引或堆少于 10,000 页，则使用 DETAILED 模式代替 SAMPLED。

DETAILED 模式将扫描所有页并返回所有统计信息。

从 LIMITED 到 DETAILED 模式，速度将越来越慢，因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别，请使用 LIMITED 模式。它的速度最快，并且对于索引的 IN_ROW_DATA 分配单元中的每个非叶级别，不返回与其对应的一行。

使用系统函数指定参数值
可以使用 Transact-SQL 函数 DB_ID 和 OBJECT_ID 指定 database_id 和 object_id 参数的值。但是，将无效的值传递给这些函数可能会导致意外结果。例如，如果找不到数据库或对象名（因为它们不存在或拼写错误），则两个函数都返回 NULL。sys.dm_db_index_physical_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。

此外，将在调用 sys.dm_db_index_physical_stats 函数之前处理 OBJECT_ID 函数，因此会在当前数据库（而不是在 database_id 中指定的数据库）的上下文中对 OBJECT_ID 函数取值。此行为可能会导致 OBJECT_ID 函数返回 NULL 值；或者，如果当前数据库上下文和指定数据库中都存在对象名，则可能返回一条错误消息。以下示例说明这些意外的结果。

在下面的示例中，将在 master 数据库的上下文中计算 OBJECT_ID。因为 master 中不存在 Person.Address，所以此函数返回 NULL。当将 NULL 指定为 object_id 时，将返回数据库中的所有对象。当指定了无效的对象时，将返回相同的结果。

USE master;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
 

下面的示例显示当指定的一个有效对象名称同时位于当前数据库上下文中和在 sys.dm_db_index_physical_stats 函数的 database_id 参数中指定的数据库中时所生成的结果。因为 OBJECT_ID 返回的 ID 值与指定数据库中的对象的 ID 值不匹配，所以返回一个错误。

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2008R2;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO
 

最佳实践
请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。例如，在使用 OBJECT_ID 时，请指定三部分的名称，如 OBJECT_ID(N'AdventureWorks2008R2.Person.Address')，或者在 sys.dm_db_index_physical_stats 函数中使用由函数返回的值之前对这些值进行测试。下面的示例 A 和 B 演示了一种指定数据库和对象 ID 的安全方法。

检测碎片
在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和 DELETE 语句）的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询，这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。

SQL Server 2008 中的碎片计算算法比 SQL Server 2000 中的更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果一个表的第 11 页和第 13 页在同一区中，而第 12 页不在该区中，该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作，因此，在 SQL Server 2008 中，这将算作碎片。

索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆，此值表示堆的区碎片。对于索引，此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同，这两种情况下的碎片计算算法都会考虑跨越多个文件的存储，因而结果是精确的。

逻辑碎片 

这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。

区碎片 

这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。

为了获得最佳性能，avg_fragmentation_in_percent 的值应尽可能接近零。但是，从 0 到 10％ 范围内的值都可以接受。所有减少碎片的方法（例如重新生成、重新组织或重新创建）都可用于降低这些值。有关如何分析索引中碎片程度的详细信息，请参阅重新组织和重新生成索引。

减少索引中的碎片
当索引分段的方式导致碎片影响查询性能时，有三种方法可减少碎片：

（1）、删除并重新创建聚集索引。

重新创建聚集索引将对数据进行重新分布，从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。这种方法的缺点是索引在删除和重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不能重新创建索引。有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。


（2）、使用 ALTER INDEX REORGANIZE（代替 DBCC INDEXDEFRAG）按逻辑顺序重新排序索引的叶级页。由于这是联机操作，因此在语句运行时仍可使用索引。中断此操作时不会丢失已经完成的任务。此方法的缺点是在重新组织数据方面不如索引重新生成操作的效果好，而且不更新统计信息。


（3）、使用 ALTER INDEX REBUILD（代替 DBCC DBREINDEX）联机或脱机重新生成索引。有关详细信息，请参阅 ALTER INDEX (Transact-SQL)。


不需要仅因为碎片的原因而重新组织或重新生成索引。碎片的主要影响是，在索引扫描过程中会降低页的预读吞吐量。这将导致响应时间变长。如果含有碎片的表或索引中的查询工作负荷不涉及扫描（因为工作负荷主要是单独查找），则删除碎片可能不起作用。有关详细信息，请参阅此 Microsoft 网站。

注意： 
如果在收缩操作中对索引进行部分或完全移动，则运行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能产生碎片。因此，如果必须执行收缩操作，则不应在删除碎片后进行。
 


减少堆中的碎片
若要减少堆的区碎片，请对表创建聚集索引，然后删除该索引。在创建聚集索引时将重新分布数据。同时会考虑数据库中可用空间的分布，从而使其尽可能优化。当删除聚集索引以重新创建堆时，数据不会移动并保持最佳位置。有关如何执行这些操作的信息，请参阅 CREATE INDEX 和 DROP INDEX。

压缩大型对象数据
默认情况下，ALTER INDEX REORGANIZE 语句将压缩包含大型对象 (LOB) 数据的页。因为不会释放空的 LOB 页，所以在删除大量 LOB 数据或 LOB 列时，压缩此数据可改善磁盘空间使用情况。

重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。重新组织非聚集索引将压缩作为索引中非键（已包括）列的所有 LOB 列。如果语句中指定 ALL，则将对与指定表或视图关联的所有索引进行重新组织。此外，将压缩与聚集索引、基础表或带有包含列的非聚集索引关联的所有 LOB 列。

评估磁盘空间使用状况
avg_page_space_used_in_percent 列指示页填充度。为了使磁盘使用状况达到最优，对于没有很多随机插入的索引，此值应接近 100％。但是，对于具有很多随机插入且页很满的索引，其页拆分数将不断增加。这将导致更多的碎片。因此，为了减少页拆分，此值应小于 100％。使用指定的 FILLFACTOR 选项重新生成索引可以改变页填充度，以便符合索引中的查询模式。有关填充因子的详细信息，请参阅填充因子。此外，ALTER INDEX REORGANIZE 还试图通过将页填充到上一次指定的 FILLFACTOR 来压缩索引。这会增加 avg_space_used_in_percent 的值。请注意，ALTER INDEX REORGANIZE 不会降低页填充度。相反，必须执行索引重新生成。

评估索引碎片
碎片由分配单元中同一文件内的物理连续的叶级页组成。一个索引至少有一个碎片。索引可以包含的最大碎片数等于索引的页级别页数。碎片越大，意味着读取相同页数所需的磁盘 I/O 越少。因此，avg_fragment_size_in_pages 值越大，范围扫描的性能越好。avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值成反比。因此，重新生成或重新组织索引会减少碎片数量，但同时增大碎片大小。

 

三、实际运用

在官方文档中涉及到二十几列，但是实际上用不着关心那么多列，只需要查看几列的信息就足以了。

[sql] view plain copy print ?

1. SELECT   
2.     dbInfo.name AS databaseName,  
3.     objInfo.name AS objName,  
4.     ixInfo.name AS ixName,  
5.     index_stats.avg_fragmentation_in_percent,  
6.     index_stats.fragment_count,  
7.     index_stats.page_count,  
8.     index_stats.avg_page_space_used_in_percent,  
9.     index_stats.record_count,  
10.     index_stats.avg_record_size_in_bytes  
11. FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) AS index_stats  
12. INNER JOIN sys.databases AS dbInfo ON index_stats.database_id = dbInfo.database_id  
13. INNER JOIN sys.objects AS objInfo ON objInfo.object_id=index_stats.object_id  
14. INNER JOIN sys.indexes AS ixInfo ON index_stats.index_id = ixInfo.index_id  
15. WHERE 1=1  
16. AND dbInfo.name=N'test'  
17. AND objInfo.name='TB_TEST'  
18. AND ixInfo.name='IX_PK_TB_TEST'  

SELECT 
	dbInfo.name AS databaseName,
	objInfo.name AS objName,
	ixInfo.name AS ixName,
	index_stats.avg_fragmentation_in_percent,
	index_stats.fragment_count,
	index_stats.page_count,
	index_stats.avg_page_space_used_in_percent,
	index_stats.record_count,
	index_stats.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) AS index_stats
INNER JOIN sys.databases AS dbInfo ON index_stats.database_id = dbInfo.database_id
INNER JOIN sys.objects AS objInfo ON objInfo.object_id=index_stats.object_id
INNER JOIN sys.indexes AS ixInfo ON index_stats.index_id = ixInfo.index_id
WHERE 1=1
AND dbInfo.name=N'test'
AND objInfo.name='TB_TEST'
AND ixInfo.name='IX_PK_TB_TEST'

（1）、avg_fragmentation_in_percent 

表示索引与堆的逻辑平均碎片百分比，

如果该值为10%-20%，碎片应该没有什么大问题；

如果该值为20%-40%，碎片有可能成为问题，可以通过索引重组来完成

如果该值为大于40%，可能要求索引重建

（2）、fragment_count

表示碎片数量，或者是组成索引单独页面的数量

（3）、page_count

组成统计的索引或数据页面数量的统计

 

四、碎片处理

[html] view plain copy print ?

1. 卸载并且重建索引  
2.   
3. 使用DROP_EXISTING子句重建索引  
4.   
5. 执行ALTER INDEX REBUILD语句  
6.   
7. 执行ALTER INDEX REORGANIZE  

卸载并且重建索引

使用DROP_EXISTING子句重建索引

执行ALTER INDEX REBUILD语句

执行ALTER INDEX REORGANIZE

（1）、卸载并且重建索引

该方式是最简单 的方法，就是先删除在创建，但是该方式也有许多缺点

[html] view plain copy print ?

1. 1、阻塞，该方式会造成系统大的开销，并且可能导致阻塞，因为如果数据过大，该操作是很耗时间的，在该期间可能阻塞该表上的其他操作  
2.   
3. 2、丢失索引，因为在索引卸载等待重建期间，该表的查询没有索引可以使用，导致查询性能下降  
4.   
5. 3、非聚簇索引，因为卸载重建的索引是聚簇索引，那么其他的非聚簇索引也必须被重建，因为非聚簇索引是建立在聚簇索引之上的  
6.   
7. 4、唯一性约束，用于定义主键或者唯一性约束的索引不能被删除。并且主键等可能被外键引用，在卸载主键之前必须先卸载外键  

1、阻塞，该方式会造成系统大的开销，并且可能导致阻塞，因为如果数据过大，该操作是很耗时间的，在该期间可能阻塞该表上的其他操作

2、丢失索引，因为在索引卸载等待重建期间，该表的查询没有索引可以使用，导致查询性能下降

3、非聚簇索引，因为卸载重建的索引是聚簇索引，那么其他的非聚簇索引也必须被重建，因为非聚簇索引是建立在聚簇索引之上的

4、唯一性约束，用于定义主键或者唯一性约束的索引不能被删除。并且主键等可能被外键引用，在卸载主键之前必须先卸载外键

 

因此，该方式只适合在没有什么人访问数据库的的空闲时候采用，有足够的时间慢慢倒腾

（2）、使用DROP_EXISTING子句重建索引

在重建聚簇索引的时候，使用DROP_EXISTING子句可以避免重建非聚簇索引，因为行定位器使用的索引键值保持不变，并且是在同一原子步骤中。

可以为聚簇索引与非聚簇索引使用DROP_EXISTING子句，甚至可以将非聚簇索引转换为聚簇索引，但是不能将聚簇索引转换为非聚簇索引。

当然该方式也是有缺点的

[html] view plain copy print ?

1. 1、阻塞，与卸载重建方式类似，当资源紧张时也可能导致阻塞  
2.   
3. 2、使用约束的索引，该方式可以重新创建具有约束的索引，但是如果该约束是主键或是与外键相关的唯一性约束，有可能会有问题  
4.   
5. 3、具有多个碎片化的索引的表，由于数据产生碎片，索引常常也随之产生碎片，如果使用该碎片技术，表上所有索引必须单独重建  

1、阻塞，与卸载重建方式类似，当资源紧张时也可能导致阻塞

2、使用约束的索引，该方式可以重新创建具有约束的索引，但是如果该约束是主键或是与外键相关的唯一性约束，有可能会有问题

3、具有多个碎片化的索引的表，由于数据产生碎片，索引常常也随之产生碎片，如果使用该碎片技术，表上所有索引必须单独重建

（3）、使用ALTER INDEX REBUILD语句

该操作是在一个原子步骤中完成的，是物理的重建索引。但是并不是先删除在创建而是动态的重建索引，将碎片降低到最低程度。

尽管该方式是很常用的索引重建方式，但是仍有一些缺陷：

[html] view plain copy print ?

1. 1、阻塞，与前两种方式类似，当资源紧张时也是有可能造成堵塞的  
2.   
3. 2、事务回滚，因为该操作是一个原子操作，如果该操作中途停止，事务将会回滚，那么之前碎片整理操作将会丢失  

1、阻塞，与前两种方式类似，当资源紧张时也是有可能造成堵塞的

2、事务回滚，因为该操作是一个原子操作，如果该操作中途停止，事务将会回滚，那么之前碎片整理操作将会丢失

（4）、使用ALTER INDEX REORGANIZE语句

该方式并不是物理重建，而是采用逻辑方式按照索引键的逻辑顺序重新排列现有的索引叶子页面来减少碎片。

该方式是非原子性的操作

当然该方式也是有缺点的：

[html] view plain copy print ?

1. 1、该方式不能像REBUILD方式有效的减少碎片  
2.   
3. 2、对于高度碎片化的索引，该方式比=重建索引更耗时间  
4.   
5. 3、如果索引跨越多个文件，该方式不能在文件之间移动页面  

1、该方式不能像REBUILD方式有效的减少碎片

2、对于高度碎片化的索引，该方式比=重建索引更耗时间

3、如果索引跨越多个文件，该方式不能在文件之间移动页面

（5）、集中方式的特性比较

特性/问题	卸载并且重建	使用DROP_EXISTING	REBUILD	REORGANIZE
在整理聚簇索引碎片时 重建非聚簇索引	两次	无	无	无
丢失索引	是	无	无	无
整理具有约束的索引碎片	很复杂	一般复杂	简单	简单
同时进行多个索引碎片整理	否	否	是	是
并发性	低	低	一般	高
中途撤销	因为不使用事务而存在危险	进程丢失	进程丢失	进程被保留
碎片整理程度	高	高	高	中一般
应用新的填充因子	是	是	是	否
更新统计	是	是	是	否