君，已阅读到文档的结尾了呢~~
扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
[IT/计算机]SQL server 2008 索引与视图 详解
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口SQLSERVER聚集索引的整理（重建）的必要性测试
SQLSERVER聚集索引的整理（重建）的必要性测试
SQLSERVER 在日常DBA工作中有一项叫索引整理
一般整理的多为非聚集索引
问题：聚集索引是否需要整理？在什么情况下需要整理？整理的效果如何？有没有负面作用？
测试环境:WIN2003+SQL2008R2
测试表:wkf_test 存放条记录,wkf_test_all表是该表的备份
1.首先来次DBCC结果如下:
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 64860
- 扫描区数..............................: 8142
- 区切换次数..............................: 8145
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.53% []
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.07%
- 每页的平均可用字节数........................: 20.6
- 平均页密度(满).....................: 99.75%
2.删除1部分连续数据，注意看页数相应减少，页密度保持不动
delete from dbo.wkf_test where id between 50 and 2767550
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 50803
- 扫描区数..............................: 6377
- 区切换次数..............................: 6377
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.58% []
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.02%
- 每页的平均可用字节数........................: 20.8
- 平均页密度(满).....................: 99.74%
3.删除奇数数据，亮点来了，页数保持不变的同时，页密度大幅下降 '（此时数据表性能很差）'
delete from dbo.wkf_test where id%2=0
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 50803
- 扫描区数..............................: 6377
- 区切换次数..............................: 6377
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.58% []
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 0.02%
- 每页的平均可用字节数........................: 4060.2
- 平均页密度(满).....................: 49.84%
4.REBUILD一下聚集索引,以90的填充率后,页数大量下降(约50%)，密度90&
ALTER INDEX ALL ON wkf_test REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 28064
- 扫描区数..............................: 3546
- 区切换次数..............................: 3569
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.26% []
- 逻辑扫描碎片 ..................: 0.38%
- 区扫描碎片 ..................: 1.83%
- 每页的平均可用字节数........................: 793.8
- 平均页密度(满).....................: 90.19%
5.再把奇数数据补回来。--运行时间 01:36
set identity_insert [dbo].[wkf_test] on&
INSERT INTO [dbo].[wkf_test](id,[usernmae],[tablename])&
SELECT * from wkf_test_all where id%2=0
set identity_insert [dbo].[wkf_test] off
6.此时的DBCC结果显示 数据表页数再次暴涨.很显然数据是追上去的而不是塞上去的,古怪的现象是数据页变得稀了。注意这个细节，马上的步骤会有怪异的现象发生
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 82431
- 扫描区数..............................: 10338
- 区切换次数..............................: 59658
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 17.27% []
- 逻辑扫描碎片 ..................: 68.19%
- 区扫描碎片 ..................: 0.29%
- 每页的平均可用字节数........................: 2429.9
- 平均页密度(满).....................: 69.98%
7.好吧，现在再把刚才&追&上去的数据再删了，理论上应该回退到4的结果。但是现实情况是页数不变。继续变稀。理解一下：后被的数据不被SQLSERVER理解为连续的方式&追&上去的
delete from dbo.wkf_test where id%2=0
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 75403
- 扫描区数..............................: 9485
- 区切换次数..............................: 58521
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 16.11% []
- 逻辑扫描碎片 ..................: 73.90%
- 区扫描碎片 ..................: 0.69%
- 每页的平均可用字节数........................: 5377.4
- 平均页密度(满).....................: 33.56%
8.那么我们再把数据补回去一次，很好，SQLSERVER发现了上次的&空&，把数据原生态补回去了。而且INSERT性能明显强过整理过的那次（时间是第一次耗时的1/3）
set identity_insert [dbo].[wkf_test] on&
INSERT INTO [dbo].[wkf_test]
[usernmae]
,[tablename])&
SELECT * from wkf_test_all where id%2=0
set identity_insert [dbo].[wkf_test] off
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 82431
- 扫描区数..............................: 10337
- 区切换次数..............................: 59660
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 17.27% []
- 逻辑扫描碎片 ..................: 68.22%
- 区扫描碎片 ..................: 0.30%
- 每页的平均可用字节数........................: 2429.9
- 平均页密度(满).....................: 69.98%
9.再REBULID一下.这个没什么说的了，顺利到指定的90
ALTER INDEX ALL ON wkf_test
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON,ONLINE = ON);
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 63932
- 扫描区数..............................: 8078
- 区切换次数..............................: 8097
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.69% []
- 逻辑扫描碎片 ..................: 0.25%
- 区扫描碎片 ..................: 4.51%
- 每页的平均可用字节数........................: 790.4
- 平均页密度(满).....................: 90.23%
10.delete from dbo.wkf_test where id between 50 and
DBCC SHOWCONTIG 正在扫描 'wkf_test' 表...
表: 'wkf_test' ()；索引 ID: 1，数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 11234
- 扫描区数..............................: 1420
- 区切换次数..............................: 1421
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 98.80% []
- 逻辑扫描碎片 ..................: 0.27%
- 区扫描碎片 ..................: 14.37%
- 每页的平均可用字节数........................: 791.6
- 平均页密度(满).....................: 90.22%
总结如下：
以IDENTITY(1,1)为聚集索引时。
如果连续的删除大量索引在这些索引以有序的方式进行排列时(是不是有点绕口？)会被数据库引擎识别，将这一串页从页链中删除。无需做聚集索引的整理工作
如果不连续的删除，则数据页被保存，页密度被稀释
如果整理后填充了原有的页空间，则数据会追加到新页中，而不是分裂旧页，此时INSERT效率会下降，因为要新开页
反之如果删除后没有整理释放页空间，则补回来的数据会加到原有的位置（可能会引起页分裂），在补回去的数据不超过旧有数据的情况下。INSERT效率会比较快
顶一下(0) 踩一下(0)
热门标签：trackbacks-0
索引碎片：
内部碎片（或说叶级填充率）：反映数据叶级的空间占用率或空闲率
外部碎片：由于sqlserver以连续的8个page作为一个数据库块（区）extent作为读取单位，故此由于物理存储上的区和逻辑上不一致（不连续）而造成io读取切换
逻辑碎片：这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的&下一页&指针所指向的页
区碎片：这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。（微软真不会解释概念:(
查询碎片情况：
　　dbcc showcontig：四部分对象名，【索引名】|【索引id】
　　dbcc showcontig：当前库对象id，【索引名】|【索引id】　　　　
　　sys.dm_db_index_physical_stats：数据库id，对象id，索引id，分区id，扫描模式
五个参数，基本上，【0（特殊的，index可以为0，故该处为-1）】|【null】|【default】 意义是一样的
基本指标：
扫描密度（%）[最佳计数:实际计数]：这是&最佳计数&与&实际计数&的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。&最佳计数&是指在一切都连续链接的情况下，区更改的理想数目。&实际计数&是指区更改的实际次数。
逻辑扫描碎片（%）：扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的&下一页&指针所指向的页。
区扫描碎片（%）：扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意： 如果索引跨越多个文件，则此数字无意义。
avg_page_space_used_in_percent：平均page空间使用率。相关的概念：页拆分、页填充率
avg_fragment_size_in_pages：平均多少个page就有一个碎片，该值 越大越好
avg_fragmentation_in_percent：碎片率，不解释。该值越小越好，和avg_fragment_size_in_pages 反比！
page_count：扫描的总page数
record_count：扫描的总记录数。注意：是相对于当前的扫描来说的记录数，不一定是你所认为的 用户表的一行数据
forwarded_record_count：页拆分的记录数目
　　索引、堆，因其本质为B数结构，B数是分层级的，故可以多种选择来扫描：非页级？or 仅取一代的样本？or 完全的扫描？
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。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 分配单元中的每个非叶级别，不返回与其对应的一行。
请始终确保使用 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 值成反比。因此，重新生成或重新组织索引会减少碎片数量，但同时增大碎片大小。
阅读(...) 评论()编程开发子分类