合肥建设干部学校网站,建设网站功能,模板做网站影响seo,白云网站(建设信科网络)Oracle 不需要整理碎片#xff0c;原因#xff1f;
1. rowid
默认的索引是#xff22;#xff0d;树索引。索引建立在表中的一个或多个列或者是表的表达式上#xff0c;将列值和行编号一起存储。行编号是唯一标记表中行的伪列。
行编号是物理表中的行数据的内部地址原因
1. rowid
默认的索引是树索引。索引建立在表中的一个或多个列或者是表的表达式上将列值和行编号一起存储。行编号是唯一标记表中行的伪列。
行编号是物理表中的行数据的内部地址包含两个地址其一是指向数据表中包含该行的块所丰放数据文件的地址另一个可以直接定位到数据行自身的这一行在数据块中的地址。 2. IAM 原文Microsoft SQL Server 2000 Index Defragmentation Best Practices 来源Microsoft TechNet 作者Mike Ruthruff 时间February 2003
Summary As Microsoft SQL Server 2000 maintains indexes to reflect updates to their underlying tables, these indexes can become fragmented. Depending on workload characteristics, this fragmentation can ......
--------------------------------------------------------------------------------
摘要 既然SQL Server 2000为了反应数据的更新需要维护表上的索引因而这些索引会形成碎片。根据工作量的特征这些碎片会影响对应的工作性能。该白皮书提供能帮助你决定是否需要整理碎片以改善性能的信息。SQL Server 2000提供了一些命令来实现索引的碎片整理。这里比较其中两个命令DBCC DBREINDEX 和 DBCC INDEXDEFRAG。
目录
概述 了解碎片 整理碎片前需要考虑的因素 小规模环境 vs. 大规模环境 决定何时进行索引碎片整理 DBCC DBREINDEX vs. DBCC INDEXDEFRAG 结论 更多信息 附录 A: 测试环境
概述
本白皮书提供在生产环境中决定是否进行索引的碎片整理工作以改善工作性能的信息。另外本文比较了Microsoft SQL Server 2000中用于索引碎片整理的两个命令DBCC DBREINDEX 和 DBCC INDEXDEFRAG。这个比较包括不同的数据库和硬件环境的测试结果。关于测试环境请见章节小规模环境 vs. 大规模环境和附录A。
注意: 并不是在任何情况下碎片整理都会改善性能。每个场景是不同的。也因为如此所以是否要进行碎片整理工作要根据分析结果而定。
白皮书叙述索引碎片整理的重要性以及常规处理流程。下面列举本文的关键观点 在索引碎片整理前请确保系统资源的一些问题比如物理磁盘碎片不合理的基础结构等因素会给性能带来负面影响。 DBCC SHOWCONTIG可以显示索引碎片数量。当运行该命令时要特别注意逻辑碎片(Logical Fragmentation)和页密度(Page Density)两个指标。 决定是否要碎片整理考察工作类型很重要。不是所有情况下都能从碎片整理中受益。对读取比较多的工作类型来说磁盘I/O是最重要的性能指标。测试显示决策支持系统(DSS: Decision Support System)比很多在线事务处理系统(OLTP: Online Transaction Processing)从碎片整理中获益更多。 碎片将影响磁盘性能和SQL Server预读管理(read-ahead manager)的效果。Windows性能监视器有几个关键指标可以用来支持这一观点。 决定是否用 DBCC DBREINDEX 还是 DBCC INDEXDEFRAG 取决于你的需求以及硬件环境。 DBCC DBREINDEX会带来更新统计(updating statistics)的副作用而DBCC INDEXDEFRAG不会。可以通过在执行DBCC INDEXDEFRAG后执行UPDATE STATISTICS来增加其影响。
了解碎片
当索引所在页面的基于主关键字的逻辑顺序和数据文件中的物理顺序不匹配时碎片就产生了。这里的碎片SSD后还有影响吗或者都在buffer中的话所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情况下数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言这将带来极佳的性能。当物理排序和逻辑排序不匹配时磁盘的工作性能会变得低效这是因为磁头必须向前和向后移动来查找索引而不是只象某个单一方向来搜索。碎片会影响I/O性能不过对于位于SQL Server数据缓冲内的数据页而言碎片并不会带来任何影响。
当索引第一次创建时没有或者只有极少碎片。随着时间推移插入更新和删除数据和这些数据相关的索引上的碎片就增加了主要还是原有页面存不下新开了一个页。为了整理碎片SQL Server提供如下命令
CREATE INDEX后的DROP INDEX命令 不带DROP_EXISTING选项的CREATE INDEX命令 DBCC INDEXDEFRAG DBCC DBREINDEX
本文用 DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令来进行测试。这些命令都可以在在线和离线场景下执行。DBCC DBREINDEX按照CREATE INDEX的方式创建索引因此DBCC DBREINDEX的执行结果和用CREATE INDEX命令的结果很相似。上面所有这些命令的测试结果和功能描述会在本文后面提到。
整理碎片前需要考虑的因素
系统资源问题
在索引碎片整理之前要确认系统任何性能问题和系统资源限制无关。关于这方面的详细讨论已经超出了本文的范围不过有些更常见的资源问题和I/O子系统性能内存使用以及CPU使用率相关。关于分析这些类型资源问题的更深入讨论请见本文最后的“更多的信息”章节。
物理磁盘碎片
在某些系统上磁盘碎片会带来很糟的性能。要确定是否存在磁盘碎片可以使用Microsoft Windows自带的系统工具或者第三方提供的工具来分析SQL Server所在的分区。对于常规的I/O子系统上的规模较小的数据库建议在运行索引碎片整理工具前先进行磁盘碎片整理。而对于更智能的磁盘子系统上的规模较大的数据库例如SAN(存储区域网络 storage area networks)环境磁盘碎片整理就不是必要的。windows 2000 年代才要磁盘整理
执行情况较差的查询
当考察任何性能相关问题时你必须能识别出那些查询执行效率较差。这里讨论的一些信息在后面也会用到这些信息用于决定那些索引碎片将被整理。
可以使用SQL Profiler(事件探查器)来识别执行效率差的查询(关于这方面更多的信息请参考SQL Server联机帮助的SQL Profiler主题)。运行SQL Profiler会带来开销不过只监控下面介绍的一些事件可以收集到必要的信息而且对性能的影响尽可能的小(一般来说小于10%的CPU使用率当然有根据情况有些差异)。
SQL Profiler提供了一个名叫SQLProfilerTSQL_Duration的跟踪模板可以捕获相关的事件。可以很快捷地利用它来识别执行效率较差的查询。也可以手工创建SQL Profiler跟踪来捕获下述事件 TSQL: SQLBatchCompleted Stored Procedures: RPC:Completed
运行SQL Profiler的时间长度要根据服务器工作量而定。为了让跟踪更有效需要选择代表性的任务类型至少应该选择那些能显示性能低下的工作类型。当跟踪被捕获后检场跟踪日志中持续时间那列数据。该列数据以毫秒为单位表示每个批处理或者查询运行需要的时间。
标识出引起性能最差的查询
这里列举能够标识出造成最糟糕性能的查询的一些建议
按查询持续时间对跟踪进行分组。将注意力首先放在前10个最差的查询上。 如果在应用中大量使用了存储过程考虑使用SQLProfilerSP_Counts模板来标识被调用最多的那些存储过程。将注意力放在被调用最频繁同时也是引起较差性能的存储过程。 将收集的数据放到SQL Server表中。这样就可以通过查询表来对工作性能进行更为详细的分析(例如平均运行时间最大运行时间等等)。
基础结构
当找出运行时间最长性能最差得查询后必须确保数据库基础架构对于那个查询来说是最优的。例如确保存在适当的索引并且被那个查询正确地使用了。可以使用查询分析器来显示和检查查询计划以发现在查询任务中那些索引被用到了。当使用查询分析器图形化显示查询的执行计划时以前的数据会以警告的方式标识(例如表名会以红色字体显示)。在整理碎片之前要解决这些问题。
检查查询计划时要牢记以下建议
找到执行计划中开销较大的步骤。这些步骤是查询中最耗时的部分。解决这些步骤带来的问题将会使性能大幅提高。 找出执行索引扫描的步骤。索引扫描是从碎片整理中获利最大的部分。注意那些性能较差的查询索引扫描中用到的索引在碎片整理的时候可以集中在这些索引上进行。 利用SQL Profiler中捕获的跟踪信息以及手工从查询计划中获取的信息就可以使用索引向导(Index Tuning Wizard)来分析工作量。利用索引想到生成的报表来决定是否要对基础结构做改动。在碎片整理前做完这些改动。
小规模环境 vs. 大规模环境
这里做的测试基于两台服务器两台服务器之间的I/O子系统相差很大。一台服务器代表小规模环境而另一台代表大规模环境。用来解释测试结果每台环境的规格如下。
小规模环境
在小规模环境中数据库大小在10GB20GB之间。数据分布再两个物理磁盘上tempdb和数据库日志分别在两个使用RAID 0的额外磁盘上。DSS数据库包含两个文件组每个文件组内有一个文件。OLTP数据库只包含一个文件组文件组内有一个数据文件。
大规模环境
Micorosoft和Hitachi Data System系统配合可以用Hitachi Freedom Storage Lightning 9900 Series Lightning 9960 system来构建SAN环境用于存储数据。用于测试的那个数据库大小大约为1TB。数据分散在64个物理磁盘上使用RAID10结构。存储数据的磁盘由8个LUNs(Logical Unit Numbers)连接数据库包含一个文件组该文件组中包含8个数据文件。tempdb和数据库日志单独放在一组磁盘上与数据文件隔离开48个磁盘用于存放tempdb而日志分布在8个磁盘上。为了快速备份和恢复有碎片的数据库镜像在SAN中维护中有两个Hitachi ShadowImage拷贝数据/日志备份Lightning 9960系统用于同步在线数据和ShadowImage备份数据。在该环境中重复在三个碎片级别上运行两次因为大容量的存储需要维护每个级别(大约1.4TB)的备份。
索引碎片整理对性能的影响
测试结果在后面会详细讨论。但是虽然碎片整理对两个环境(小规模和大规模)环境都带来负面影响但是无疑对大规模环境的影响要小得多。因为大规模环境从SAN中获取了极高的I/O性能因此这个结论应该是对的数据不光分散在多个磁盘上而且SAN还提供16GB的数据缓冲区。I/O benchmark测试显示创建1TB数据量最大的读取速度为354 MB/sec, 而小规模环境下只有71 MB/sec。
注意: 这些数值会根据各人的实现步骤和存储配置而变。
显然高性能的I/O子系统对SQL Server性能十分有利不过索引碎片整理的确会对所有系统带来性能的提升。当创建数据库时要谨慎考虑I/O子系统并确保尽可能将日志文件和数据库数据文件隔离开。
决定何时进行索引碎片整理
决定何时进行索引碎片整理时请考虑以下重要的建议
标识有碎片的索引。 了解何种任务会从碎片整理中获利。 确定查询的I/O性能。 理解碎片整理带来的影响和SQL Server预读管理器。
/***************************************************************************************/ 下一节中测试的结果可以用来帮助理解这些建议。
使用 DBCC SHOWCONTIG 来标识有碎片的索引
在决定何时进行碎片整理前必须先确定那些索引有碎片。DBCC SHOWCONTIG可以用于衡量索引上的碎片程度和页密度级别(Page Density level)。
下面是运行 DBCC SHOWCONTIG 后的得到的示例信息:
DBCC SHOWCONTIG scanning table_1 table... Table: table_1 (453576654); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 48584 - Extents Scanned..............................: 6090 - Extent Switches..............................: 12325 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 49.27% [6073:12326] - Logical Scan Fragmentation ..................: 10.14% - Extent Scan Fragmentation ...................: 32.74% - Avg. Bytes Free per Page.....................: 1125.2 - Avg. Page Density (full).....................: 86.10% DBCC SHOWCONTIG scanning table_1 table... Table: table_1 (453576654); index ID: 2, database ID: 8 LEAF level scan performed. - Pages Scanned................................: 41705 - Extents Scanned..............................: 5221 - Extent Switches..............................: 6094 - Avg. Pages per Extent........................: 8.0 - Scan Density [Best Count:Actual Count].......: 85.55% [5214:6095] - Logical Scan Fragmentation ..................: 7.80% - Extent Scan Fragmentation ...................: 6.63% - Avg. Bytes Free per Page.....................: 877.7 - Avg. Page Density (full).....................: 83.20% 检查DBCC SHOWCONTIG运行后的结果时需要特别留意Logical Scan Fragmentation和Average Page Density。Logic scan fragmentattion表示索引上乱序的百分比(注意: 该数值和堆和文本索引不相关。所谓堆表示一个没有聚集索引的表。)。Page density是索引叶级页填充程度的度量。请查找SQL Server联机帮助的“DBCC SHOWCONTIG”主题以获取更多信息。
分析DBCC SHOWCONTIG的输出结果
在分析DBCC SHOWCONTIG的输出结果时请考虑下面问题 碎片会影响I/O。因此要集中关注较大的索引因为这些索引被SQL Server放入缓存的可能性比较小。通过DBCC SHOWCONTIG得到的页数可以估算出索引的大小(每页大小为8KB)。一般来说没有必要关注那些碎片级别小于1,000页的索引。在测试中包含超过10,000页的索引才会影响性能特别是包含更多的页(超过50,000页)的索引会引起最大的性能提升。 逻辑扫描碎片(logical scan fragmentation)值太高会大大降低索引扫描的性能。在测试中那些逻辑碎片大于10%的聚集索引在碎片整理后性能得到了提升对那些大于20%的聚集索引性能提升尤其明显。因此关注那些逻辑碎片大于等于20%的索引。注意对于堆(Index ID0)来说该标准是无意义的。 平均页密度(average page density)太低将导致查询中需要读取更多的页。重新组织这些页可以提高平均页密度从而完成相同的查询只要读取较少的页。一般来说在第一次载入数据后表拥有较高的页密度。随着数据的插入页密度会降低从而带来叶级页拆分。检查平均页密度时记住该值依赖于创建表时设置的填充因子取值。 虽然扫描密度(scan density)可以作为碎片级别的参考不过当索引跨越多个文件时该参考无效。因此当检查跨越多个文件的索引时扫描密度不应该被考虑。
监视碎片级别
有规律地监控索引的碎片级别是良好的实践习惯。SQL Server联机帮助的DBCC SHOWCONTIG主题中有一个示例脚本用于自动捕获和重建碎片程度较大的索引。建议每隔一段固定时间就使用带TABLERESULTS选项的DBCC SHOWCONTIG命令将得到的结果保存在表内。这样做可以使你不受时间限制地监控碎片级别。另外还建议在繁忙的服务器上使用带有WITH FAST选项的DBCC SHOWCONTIG命令。带有WITH FAST选项的DBCC SHOWCONTIG命令可以避免扫描索引的叶级页从而是的执行比较快。不过因为它不扫描叶级页所以就无法报告页密度指标。
表1 显示在小规模和大规模DSS环境下运行DBCC SHOWCONTIG命令所需的时间。每次测试中都带 ALL_INDEXES 和 TABLERESULTS 选项。 表1 DBCC SHOWCONTIG 执行结果 DBCC SHOWCONTIG options Total number of index pages (all indexes) Run time (minutes) Small-Scale Environment Not using the WITH FAST option 1,702,889 5.02 Using the WITH FAST option 1,702,889 0.90 Large-Scale Environment Not using the WITH FAST option 111,626,354 382.35 Using the WITH FAST option 111,626,354 48.73 理解哪些工作类型从索引的碎片整理中获益最多
当你决定进行索引的碎片整理时理解有些工作类型将比另一些工作类型从碎片整理中获益更多这个结论是十分重要的。碎片会对I/O带来负面影响。在大范围内用到索引页的查询受碎片的影响最大从而也最能从碎片整理中获益。
测试利用两种数据库工作类型比较了碎片整理前和整理后的性能。测试对象包括了具有代表性的OLTP和DSS数据库。OLTP数据库上的工作主要集中在特定范围的数据的更新(insertupdate和delete)和有选择性的读取。而对于DSS系统由于主要是读取工作类型因此测试集中在多表联接的查询任务。特别是这些查询必须需要扫描到一个或更多的索引。测试结果表明碎片对OLTP工作影响极小而对DSS系统则从碎片整理获益多多。
DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令常用来整理索引碎片。对这两个命令特定功能的更详细讨论后面会涉及到。
OLTP型工作量
测试中OLTP数据库模拟数据仓库环境下的订单处理流程。工作量包括5个存储过程涉及流程包括新建订单订单状态查询分发库存状态以及交付流程。存储过程中用到了插入更新和删除语句以及有目的选择的SELECT查询。
图1 显示了使用DBCC INDEXDEFRAG 和 DBCC DBREINDEX 进行索引碎片整理前后每个存储过程完成查询时性能的差异。
图1: 碎片整理前后OLTP型工作环境下每个存储过程的平均执行时间。值越小表示性能越好。点击看原图 --------------------------------------------------------------------------------
如图1所示碎片整理前后存储过程性能差别不是很大。因为存储过程中的基础查询有选择性地选取部分数据因此索引碎片给性能带来的影响不大。图1还显示运行碎片整理工具的确降低存储过程的性能不过在存储过程运行期间本身就会给性能带来10%20%之间的波动。图1显示的差异也在这个范围之内。更重要的是结果显示当碎片级别上升时并未带来存储过程性能的下降。
DSS型工作量
测试中DSS工作量包含22个由复杂SELECT语句构成的报表类型的查询。这些查询严格地以批处理方式在服务器端运行。所有查询包含一个或多个多表联接大多数查询需要扫描很大范围的索引。
表2 记录测试中用到的索引的平均碎片和页密度级别。碎片级别通过下属行为组合得到 以Bulk INSERT方式插入新数据到数据库并模拟周期性地刷新数据。 删除某个范围内的数据。 按关键值执行一些更新操作虽然这至少会影响碎片级别不过和插入和删除操作相比更新涉及到的记录相对还是比较少。 表2 小规模和大规模环境中平均逻辑碎片和页密度测试 Fragmentation level Average logical fragmentation (%) Average page density (%) Small-Scale Environment Low (1) 7.8 80.1 Medium (2) 16.6 68.1 High (3) 29.5 69.2 Large-Scale Environment Low (1) 5.9 84.4 Medium (2) 13.8 70.3 DSS工作类型的测试结果和OLTP工作类型相差很大。在整理索引碎片后性能提高很明显。因为此时工作量的性能强烈依赖于磁盘吞吐量(大多数查询会包含索引扫描)因此该结果是可以预料到的。下面的图2和图3显示DSS型工作量在整理索引碎片前后的性能收益情况。如图中所示性能从碎片整理中显著提升。
在小规模环境中在碎片较低级别性能提升了60而在碎片较高级别提升了460。在大规模环境中在碎片较低级别性能提升13%在中等级别提升了40%。结果显示大规模环境中碎片对性能影响影响相对较小这是因为该环境从磁盘子系统的表现获益更多一些。更详细的讨论见本文后面的“碎片对磁盘吞吐量的影响和SQL Server预读管理器”章节。
图2: 小规模环境下整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。 点击看原图 -------------------------------------------------------------------------------- 图3: 大规模环境下整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。点击看原图 --------------------------------------------------------------------------------
图2从数值上显示在小规模环境下DBCC INDEXDEFRAG的结果比DBCC DBREINDEX要好一些。不过在大多数情况下完全重建索引应该具有更好的性能。
在解释这些测试结果时需要牢记以下几点 图2和图3显示的结果意味着这是应用DBCC INDEXDEFRAG的“最佳”场合。测试中DBCC INDEXDEFRAG运行在一个禁止的系统上因此DBCC INDEXDEFRAG可以完全消除碎片。当DBCC INDEXDEFRAG运行在一个动态的系统上即数据保持在线更新DBCC INDEXDEFRAG会跳过那些被锁住的页。因此DBCC INDEXDEFRAG也许就无法完全消除碎片。要衡量DBCC INDEXDEFRAG发挥了多大作用可以在DBCC INDEXDEFRAG后立刻运行DBCC SHOWCONTIG。 数据的分布情况会影响磁盘性能。小规模环境中数据只分布在两个物理磁盘(磁盘容量加起来一共33.5GB)上在数据库创建前这两个磁盘是空的。数据库创建后数据文件大小在22GB到30GB之间。当数据库创建时数据分布在磁盘外围部分。DBCC INDEXDEFRAG整理碎片也是从最邻近原始数据的位置开始。因为DBCC DBREINDEX完全重建索引在释放旧的索引页前它必须首先给新索引页分配空间。分配新空间使得数据离原始数据位置较远并且分布在磁盘内侧因此造成I/O吞吐量有轻微的下降。在小规模环境下的benchmark测试中这种下降表现为读取数据吞吐量下降15%。 剩余空间容量同样会影响DBCC DBREINDEX。如果没有大量连续的空闲空间DBREINDEX会强迫使用数据文件中的空闲空间从而导致索引重建时带有一小部分数量的逻辑碎片。关于DBCC DBREINDEX对剩余空间的需求的信息见本文后面的DBCC DBREINDEX章节。
确定查询的I/O流量
因为具有大量I/O读写的查询从碎片整理中获益最多所以讨论如何确定某个特定查询的I/O流量是必要的。SET STATISTICS IO命令可以报告完成一个特定查询时服务器实例上的读取量和读取类型。可以在查询管理器中选择该命令开关为ON和OFF使用方法如下
SET STATISTIC IO ON GO SELECT * FROM table_1 GO SET STATISTIC IO OFF GO
输出示例 Table table_1. Scan count 1, logical reads 12025, physical reads 0, read-ahead reads 11421. 表3 关于SET STATISTIC IO 输出结果的描述 Value Description Scan count Number of scans performed logical reads Number of pages read from the data cache physical reads Number of pages read from disk read-ahead reads Number of pages placed into the cache for the query 通过physical reads和read-ahead reads值可以对某个特定查询涉及的I/O量有一个估计。physical reads和read-ahead reads都表示从磁盘读取的页数。多数情况下read-ahead reads比physical reads数值要大。
注意 在通过SQL Profiler 获取信息时reads列表示的是逻辑读取量(logical reads)而不是物理读取量(physical reads)。
除了重新对页进行排序通过增加索引叶级页的页密度索引的碎片整理还降低执行某个查询的I/O数量。页密度的提高导致完成相同的查询需要读取更少的页从而提高性能。
理解碎片整理带来的影响和SQL Server预读管理器
碎片对大量读取磁盘边缘的操作带来负面影响。可以使用Windows性能监视器来获取这种影响的衡量。通过性能监视器可以观测磁盘活动情况并且有助于决定何时进行碎片整理。
为了理解为什么碎片对DSS型工作具有如此的影响首先很重要的是要理解碎片是如何影响SQL Server预读管理器的。为了完成需要扫描一个或多个索引的查询SQL Server预读管理器负责提前对索引页进行扫描并且将额外的数据页放到SQL Server数据缓存中。根据基础页的物理顺序预读管理器动态地调整读取量。当碎片较少时预读管理器即时可以读取较大的数据块更高效地利用I/O子系统。当数据产生碎片时预读管理器只能读取较小的数据块。预读的数量虽然和数据的物理顺序无关不过因为较小的读取请求消耗更多的CPU/时钟所以最终就会降低整个磁盘的吞吐量。
在所有情况下预读管理器能提高性能但是当存在碎片且预读管理器无法读取较大的数据块整个磁盘的吞吐量就下降。通过检查性能监视器中的Physical Disk相关的计数器可以发现该现象。下表列举并描述了这些计数器。 表4 性能监视器中物理磁盘计数器 Physical Disk counter Description Avg Disk sec/ Read 该计数器用于衡量磁盘延迟。测试显示当碎片出于很高水平(大于等于30%)时会增加磁盘延迟。 Disk Read Bytes/ sec 该计数器能很好地衡量全面的磁盘吞吐量。一段时间内工作量的下降趋势可以用来表示碎片正在影响性能。 Avg Disk Bytes/ Read 该计数器用于衡量每个读取请求带来的数据读取量。当索引页连续SQL Server预读管理器可以一次读取较大的数据块对I/O子系统的利用效率较高。测试显示该计数器值和碎片数量间有着直接联系。当碎片级别上升该值就下降从而影响全面的磁盘吞吐量。 Avg Disk Read Queue Length 一般而言该计数器为每两个物理磁盘上持续的平均数值。测试中很可能由于较高的延迟和较低的全面磁盘吞吐量使得该计数器随着碎片的增加而增加。 图4到图7显示在DSS型工作中性能监视器报告的磁盘吞吐量和平均读取大小。
图4: 小规模环境下DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。点击看原图 -------------------------------------------------------------------------------- 图5: 小规模环境下DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。点击看原图 -------------------------------------------------------------------------------- 图6: 小规模环境下DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。点击看原图 -------------------------------------------------------------------------------- 图7: 小规模环境下DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。点击看原图 --------------------------------------------------------------------------------
上面的图显示碎片对磁盘性能的影响趋势。虽然使用DBCC DBREINDEX和DBCC INDEXDEFRAG得到的结果不同但是注意在所有系统上都得到了一致的结果即每次读取的平均大小和整体磁盘吞吐量随着碎片的增加而降低。正如你所见的整理所有碎片对磁盘吞吐量提高极大。
每次读取的平均大小还可以用来展示碎片是如何影响预读管理器读取较大数据块的能力的。这点需牢记在心不过平均读取数量较大并不总是意味着整体吞吐量较高。平均读取量大表示数据传输中CPU负荷较小。当索引没有碎片时读取64KB大小数据的速度和读取256KB大小数据的速度几乎一样。这个结论在那些数据分布在多个磁盘上的大型系统而言尤其如此。这些普遍和特殊的结论会根据不同的系统因为各种各样的原因(例如不同的I/O子系统工作类型差异数据在磁盘的分布特征等等)而不同。当监视系统时寻找那些持续时间较长的磁盘吞吐量和读取数量的下降阶段。这些阶段以及DBCC SHOWCONTIG命令提供的信息可以用于帮助来确定什么时候该进行索引的碎片整理了。
测试结果显示碎片也会带来磁盘延迟不过至于那些最高级别的碎片才会对磁盘延迟带来巨大的负面影响并且也只在小规模环境下才有此现象。在大规模环境下由于SAN提供了很高的I/O性能因此磁盘延迟值十分小而从来不会带来问题。
DBCC DBREINDEX vs. DBCC INDEXDEFRAG
除了使用CREATE INDEX命令来删除或者重新创建索引还可以使用DBCC DBREINDEX和DBCC INDEXDEFRAG命令来帮助维护索引。
DBCC DBREINDEX
DBCC DBREINDEX用于在指定的表上重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时涉及到的表就无法被用户访问。DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何是否用主键或者唯一性约束等信息重建的时候会自动管理的。DBCC DBREINDEX完全重建索引也就是说将页密度级别恢复到最初(默认)的填充因子水平当然你也可以选择页密度的新值。从内部运行看DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。
下面两点是DBCC DBREINDEX比DBCC INDEXDEFRAG优越的地方 DBCC DBREINDEX在重建索引过程中自动重建统计这将显著提高工作性能。 DBCC DBREINDEX可以运行在多处理器环境下利用多处理器的优势当重建较大和碎片厉害的索引时速度可以十分快。
DBCC DBREINDEX的所有工作是一个单一的原子事务。必须完成创建新的索引并替换旧索引然后旧索引页被释放。完成重建需要数据文件中有足够的空余空间。如果空余空间不够DBCC DBREINDEX要么无法重建索引要么会产生大于0的逻辑碎片。所需空余空间视情况而定取决于事务中要创建的索引数目。对于聚集索引而言一个不错的指导公式为所需空余空间 1.2 * (平均行大小) * (行数量)。
对于非聚集索引可以通过计算非聚集索引包含的每行平均大小(非聚集索引包含关键字长度 聚集中的索引关键字或者row ID长度)乘以行数量得到所需空间。如果对整个表进行索引重建需要为聚集索引和非聚集索引留出足够的空间。同样如果重建不唯一非聚集索引也需要为聚集和非聚集索引留出空余空间。因为SQL Server必须为每行创建唯一标识因此非聚集索引是隐式重建的。使用DBCC DBREINDEX时较佳的做法是指定那些索引需要整理。这样做可以使得对操作有更多的控制力以及避免不必要的麻烦。
DBCC INDEXDEFRAG
DBCC INDEXDEFRAG用于对指定的索引进行重建。和DBCC DBREINDEX类似也不需顾及表的基础结构不过DBCC INDEXDEFRAG无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索引都必须运行一次DBCC INDEXDEFRAG。
和DBCC DEREINDEX不同的是DBCC INDEXDEFRAG是在线操作的因此在整理索引碎片时仍然可以访问被整理到的表和索引。另外一个和DBCC INDEXDEFRAG很不同的地方是DBCC INDEXDEFRAG可以中止和重新开始而不丢失任何工作信息。整个DBCC DBREINDEX操作作为一个原子事务运行。这意味着如果中止DBCC DBREINDEX操作整个操作会回滚继续的话必须重新开始。但是如果中止DBCC INDEXDEFRAG任务会立刻中止并不会丢失已经完成的任务因为DBCC INDEXDEFRAG每个工作单位是独立的事务。
DBCC INDEXDEFRAG包括两个阶段 压缩页并试图根据索引创建时指定的填充因子来调整页密度。DBCC INDEXDEFRAG会根据最初的填充因子尽可能提高页密度级别。而DBCC INDEXDEFRAG不会但是它会将那些高于最初填充因子的页密度降低。 通过移动页来使得物理顺序和索引叶级页的逻辑顺序一致从而整理索引碎片。这个工作由一系列独立的很小的事务来完成因此DBCC INDEXDEFRAG对整体系统性能影响很小。图8显示DBCC INDEXDEFRAG的碎片整理阶段中页移动情况。
图8: DBCC INDEXDEFRAG的数据文件页移动情况 --------------------------------------------------------------------------------
DBCC INDEXDEFRAG并不会帮助整理分散插入到数据文件中的索引这种分散插入称为Interleave。同样DBCC INDEXDEFRAG也不对扩展页碎片进行整理。当索引扩展页(扩展页8页)中的数据并不连续的时候出现Interleave此时多个扩展页的数据在文件中是交叉状态。因为即使逻辑顺序和物理顺序一致情况下所有的索引页也不见得一定是连续的因此即使没有逻辑碎片情况下Interleave也会存在。
虽然上面提到了DBCC INDEXDEFRAG的限制但是测试显示DBCC INDEXDEFRAG对性能的改善和DBCC DBREINDEX一样有用。实际上从测试结果可以看出即使重建了索引使得Interleave最小这部分优化并不会对性能带来显著提升。减少逻辑碎片级别这部分优化才对性能提升最多。这就是为什么检查索引碎片时建议将重点放在逻辑碎片整理和页密度碎片上的原因。表5总结了DBCC DBREINDEX和DBCC INDEXDEFRAG之间的差别。 表5 DBCC DBREINDEX 和 DBCC INDEXDEFRAG的比较 Functionality DBCC DBREINDEX DBCC INDEXDEFRAG Online/Offline Offline Online Faster when logical fragmentation is: High Low Parallel processing Yes No Compacts pages Yes Yes Can be stopped and restarted without losing work completed to that point No Yes Able to untangle interleaved indexes May reduce interleaving No Additional free space is required in the data file for defragmenting Yes No Faster on larger indexes Yes No Rebuilds statistics Yes No Log space usage High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space) Varies based on the amount of work performed May skip pages on busy systems No Yes 性能: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
测试显示无论是DBCC DBREINDEX还是DBCC INDEXDEFRAG都可以有效地整理索引碎片并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果下面需要决定什么时候应用哪种整理方式。
如果允许有一段时间进行离线索引重建DBCC DBREINDEX一般来说比DBCC INDEXDEFRAG要快。DBCC DBREINDEX可以充分利用多处理器系统的平行性能。DBCC INDEXDEFRAG用于对生产环境干扰不大对工作性能影响不大的场合。测试显示即使同时几个DBCC INDEXDEFRAG并行工作对性能下降的影响也从来不会超出10%。但是这也同样使得DBCC INDEXDEFRAG针对较大的索引整理时需要很长的时间才能完成。而且工作时间的长短还依赖于当时在服务器上运行的访问工作。
图9为DBCC INDEXDEFRAG和DBCC DBREINDEX的性能比较。图中的数据为小规模环境下对所有索引进行整理的时间(大规模环境下结果类似DBCC INDEXDEFRAGY运行时间为DBCC INDEXREINDEX的8倍)。当碎片级别增加索引大小增加时DBCC DBREINDEX可以比DBCC INDEXDEFRAG执行得更快。
图9: 小规模环境下整理所有索引碎片所需时间点击看原图 --------------------------------------------------------------------------------
日志考虑: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
最后要考察的问题是使用DBCC INDEXDEFRAG和DBCC DBREINDEX时写入事务日志的数据量差别。DBCC INDEXDEFRAG中写入事务日志的数据量依赖于碎片的级别和完成的工作量。测试显示当数据库完全恢复模式下DBCC INDEXDEFRAG写入事务日志的数据量远远小于DBCC DBREINDEX。DBCC INDEXDEFRAG的日志数据量可以变化很大。这是因为DBCC INDEXDEFRAG完成的碎片整理工作量由页移动数量和必要的页压缩数量决定。因为DBCC INDEXDEFRAG工作由一系列小事务组成因此可以通过备份来回收DBCC INDEXDEFRAG使用的那部分日志空间。
从日志使用的角度看DBCC DBREINDEX和DBCC INDEXDEFRAG稍有不同在大批量(bulk)日志恢复模式下日志量具有最大的差异。在完全恢复模式下DBCC DBREINDEX对每个索引页有日志镜像在日志恢复模式下就没有。因此在完全恢复模式下DBCC DBREINDEX所需的日志空间大约等于索引页数量乘以8KB。可以通过DBCC SHOWCONTIG来确定给定索引的页数量。在大规模环境下运行DBCC DBREINDEX时建议将恢复模式改为日志恢复模式。而在运行结束后再改为完全恢复模式。
注意由于大规模环境下回滚事务需要付出巨大的时间代价因此理解日志的需求很重要。
图10 显示在小规模和中度碎片级别环境下DBCC INDEXDEFRAG和DBCC DBREINDEX日志空间使用的差别。虽然DBCC INDEXDEFRAG日志空间波动很大不过测试结果可以体现DBCC DBREINDEX和DBCC INDEXDEFRAG的一般性差别。
图10: 对DSS型数据库所有索引碎片整理时DBCC INDEXDEFRAG和DBCC DBREINDEX所用的整个日志空间点击看原图 --------------------------------------------------------------------------------
结论
对于不同的工作类型索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征系统性能和SQL Server提供的碎片统计信息是正确决定何时进行碎片整理的关键。SQL Server提供一些命令来完成索引碎片整理。本文可以帮助我们来决定何时以及如何整理索引碎片从而使性能得到最大的改善。
