MS SQL 索引(三)

    碎片:当索引所在页面的基于主关键字的逻辑顺序,和数据文件中的物理顺序不匹配时,碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情况下,数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言,这将带来极佳的性能。当物理排序和逻辑排序不匹配时,磁盘的工作性能会变得低效,这是因为磁头必须向前和向后移动来查找索引,而不是只象某个单一方向来搜索。碎片会影响I/O性能,不过对于位于SQL Server数据缓冲内的数据页而言,碎片并不会带来任何影响。当索引第一次创建时,没有或者只有极少碎片。随着时间推移,插入,更新和删除数据,和这些数据相关的索引上的碎片就增加了

在索引碎片整理前,请确保系统资源的一些问题,比如物理磁盘碎片,不合理的基础结构等因素会给性能带来负面影响:

系统资源问题:在索引碎片整理之前,要确认系统任何性能问题和系统资源限制无关。关于这方面的详细讨论已经超出了本文的范围,不过有些更常见的资源问题和I/O子系统性能,内存使用以及CPU使用率相关。关于分析这些类型资源问题的更深入讨论,请见本文最后的更多的信息章节。

 

物理磁盘碎片:在某些系统上,磁盘碎片会带来很糟的性能。要确定是否存在磁盘碎片,可以使用Microsoft Windows自带的系统工具,或者第三方提供的工具来分析SQL Server所在的分区。对于常规的I/O子系统上的规模较小的数据库,建议在运行索引碎片整理工具前,先进行磁盘碎片整理。而对于更智能的磁盘子系统上的规模较大的数据库,例如SAN(存储区域网络 storage area networks)环境,磁盘碎片整理就不是必要的。

 

     索引在数据库占据了很重要的位置,索引需要牵涉到索引创建、优化和维护多方面的工作,本文以实例结合相关原理来介绍索引维护相关的知识。 实现步骤:

 

1. 以什么标准判断索引是否需要维护?

2. 索引维护的方法有哪些?

3. 能否方便地整理出比较通用的维护过程,实现自动化维护?

 

(一)、 以什么标准判断索引是否需要维护?

从索引维护的角度来讲,最主要的参考标准就是索引碎片的大小。通常情况下,索引碎片在10%以内,是可以接受的。下面介绍获取索引碎片的方法:

 

SQL Server 2000: DBCC SHOWCONTIG  (显示指定的表的数据和索引的碎片信息)

执行 DBCC SHOWCONTIG  的结果如下:

 

DBCC SHOWCONTIG 正在扫描 'StoreContact' 表...
表: 'StoreContact' (30623152);索引 ID: 1,数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 5
- 扫描区数..............................: 2
- 区切换次数..............................: 1
- 每个区的平均页数........................: 2.5
- 扫描密度 [最佳计数:实际计数].......: 50.00% [1:2]
- 逻辑扫描碎片 ..................: 20.00%
- 区扫描碎片 ..................: 50.00%
- 每页的平均可用字节数........................: 1319.0
- 平均页密度(满).....................: 83.70%

DBCC SHOWCONTIG 正在扫描 'Address' 表...
表: 'Address' (53575229);索引 ID: 1,数据库 ID: 6
已执行 TABLE 级别的扫描。
- 扫描页数................................: 278
- 扫描区数..............................: 35
- 区切换次数..............................: 34
- 每个区的平均页数........................: 7.9
- 扫描密度 [最佳计数:实际计数].......: 100.00% [35:35]
- 逻辑扫描碎片 ..................: 0.00%
- 区扫描碎片 ..................: 14.29%
- 每页的平均可用字节数........................: 79.1
- 平均页密度(满).....................: 99.02%

 

 

扫描页数(Page Scanned)如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。
扫描区数(Extents Scanned):用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果
返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
扩展盘区切换次数(Extent Switches):该数应该等于扫描区数减1
。高了则说明有外部碎片。
每个扩展盘区的平均页数(Avg. Pages per Extent):扫描页数除以扫描扩展盘区数,一般是8。小于8
说明有外部碎片。
扫描密度[最佳值:实际值](Scan Density [Best Count:Actual Count])DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

逻辑扫描碎片(Logical Scan Fragmentation):无序页的百分比。该百分比应该在0%到10%之间,高则有外部碎片。
扩展盘区扫描碎片(Extent Scan Fragmentation):无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0
%,高了则说明有外部碎片。
每页上的平均可用字节数(Avg. Bytes Free per Page):所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。

平均页密度(完整)Avg. Page Density (full):每页上的平均可用字节数的百分比的相反数。低百分比说明有内部碎片。

 

 

 

 

      检查DBCC SHOWCONTIG运行后的结果时,需要特别留意逻辑扫描碎片(Logical Scan Fragmentation)平均页密度(Average Page Density)。Logic scan fragmentattion表示索引上乱序的百分比(注意: 该数值和堆和文本索引不相关。所谓堆表示一个没有聚集索引的表。)

     碎片会影响I/O。因此要集中关注较大的索引,这些索引被SQL Server放入缓存的可能性较小。通过DBCC SHOWCONTIG得到的页数,可以估算出索引的大小(每页大小为8KB)。一般来说,没有必要关注那些碎片级别小于1,000页的索引。在测试中,包含超过10,000页的索引才会影响性能,特别是包含更多的页(超过50,000页)的索引,会引起最大的性能提升。 
     逻辑扫描碎片(logical scan fragmentation)值太高,会大大降低索引扫描的性能。在测试中,那些逻辑碎片大于10%的聚集索引,在碎片整理后性能得到了提升;对那些大于20%的聚集索引,性能提升尤其明显。因此关注那些逻辑碎片大于等于20%的索引。注意,对于堆(Index ID=0)来说,该标准是无意义的。 
     平均页密度(average page density)太低,将导致查询中需要读取更多的页。重新组织这些页,可以提高平均页密度,从而完成相同的查询只要读取较少的页。一般来说,在第一次载入数据后,表拥有较高的页密度。随着数据的插入,页密度会降低,从而带来叶级页拆分。检查平均页密度时,记住该值依赖于创建表时设置的填充因子取值。 
     扫描密度(scan density)虽然可以作为碎片级别的参考,不过当索引跨越多个文件时,该参考无效。因此,当检查跨越多个文件的索引时,扫描密度不应该被考虑。

 

SQL Server 2005: sys.dm_db_index_physical_stats 在SQL Server 2005中,已经不建议使用DBCC SHOWCONTIG了,建议使用新的动态管理函数sys.dm_db_index_physical_stats返回能确定索引碎片级别的信息。语法如下: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 }  )

表28-5 sys.dm_db_index_physical_stats的参数 

 

参  数

描  述

database_id | NULL

要检测索引的数据库ID。如果为NULL,返回SQL Server实例中的所有数据库的信息

object_id | NULL

要检测的表和视图(索引视图)的对象ID。如果为NULL,返回所有表的信息

index_id | NULL | 0

要检测的指定索引ID。如果为NULL,返回表中所有索引的信息

partition_number | NULL

要检测的分区的指定分区编号。如果为NULL,返回基于已定义数据库/表/选择的索引的所有分区的信息

LIMITED | SAMPLED | DETAILED | NULL | DEFAULT

这些模式影响了如何收集碎片数据。LIMITED模式扫描堆所有的页,但对于索引,则只扫描叶级上面的父级别页。SAMPLED收集在堆或索引中1%采样率的数据。DETAILED模式扫描所有页(堆或索引)。DETAILED是执行最慢的,但也是最精确的选项。指定NULL或DEFAULT的效果与LIMITED模式的相同

获取数据库'adventureworks'的索引碎片:
select db_name(a.database_id)[db_name], --根据数据库ID得到数据库名称
  c.name[table_name],                             --表名称
  b.name [index_name],                           --索引名称
  a.avg_fragmentation_in_percent             --碎片程度
from sys.dm_db_index_physical_stats(db_id('adventureworks'),Null,Null,Null,'limited' ) a
join
sys.indexes b
on a.object_id=b.object_id and a.index_id=
b.index_id
join
sys.tables c
on c.object_id=a.object_id

where a.index_id>0

and avg_fragmentation_in_percent>20  --碎片程度大于20

 结果:

db_name             table_name               index_name                           avg_fragmentation_in_percent 

AdventureWorks  ProductProductPhoto  PK_ProductID_ProductPhotoID     50
AdventureWorks  StoreContact             AK_StoreContact_rowguid           66.66

AdventureWorks  StoreContact             IX_StoreContact_ContactID         50
AdventureWorks  StoreContact             IX_StoreContact_ContactTypeID   50

查询返回了数据库AdventureWorks中对象的碎片大于20%的几行。列avg_fragmentation_in_ percent显示聚集索引或非聚集索引的逻辑碎片,返回索引的叶级无序页的百分比。对于堆来说,avg_fragmentation_in_percent显示区级碎片。

 

(二)、 索引维护的方法有哪些?

注:维护方式的选择,一方面要考虑是否是联机维护,另一方面就是速度上的考虑。一般碎片<=30%时,使用重新组织的方法速度比索引重建快;碎片>30%时,索引重建的速度比重新组织要快。决定是否用 DBCC DBREINDEX 还是 DBCC INDEXDEFRAG 取决于你的需求以及硬件环境。 DBCC DBREINDEX会带来更新统计(updating statistics)的副作用,而DBCC INDEXDEFRAG不会。可以通过在执行DBCC INDEXDEFRAG后执行UPDATE STATISTICS来增加其影响。

 

 

1. 联机维护

 

SQL Server2000:

DBCC INDEXDEFRAG 重新组织索引,占用资源少,锁定资源周期短,可联机进行。

DBCC INDEXDEFRAG (
    { database_name | database_id | 0 }
    , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } [ , { partition_number | 0 } ] ]  )
    [ WITH NO_INFOMSGS ]


SQL Server 2005:

1. 联机重新组织:

ALTER INDEX [index_name] ON [table_name]

REORGANIZE;

2. 联机重建:

ALTER INDEX [index_name] ON [table_name]

REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = OFF,

STATISTICS_NORECOMPUTE = ON,ONLINE = ON);

 

2. 脱机维护

SQL Server2000DBCC DBREINDEX

DBCC DBREINDEX用于在指定的表上重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时,涉及到的表就无法被用户访问。DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何,是否用主键或者唯一性约束等信息;重建的时候会自动管理的。DBCC DBREINDEX完全重建索引,也就是说,将页密度级别恢复到最初(默认)的填充因子水平;当然你也可以选择页密度的新值。从内部运行看,DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。

 

 1:DBCC DBREINDEX('表名',pk_索引名,100) 

重做第一步,如发现扫描密度/Scan Density还是小于100%则重构表的所有索引,并不一定能达100%

第二步:重构SQL Server数据库表所有索引

 2DBCC DBREINDEX('表名',’’,100) 

 

表5 DBCC DBREINDEX 和 DBCC INDEXDEFRAG的比较
FunctionalityDBCC DBREINDEXDBCC INDEXDEFRAG
Online/OfflineOfflineOnline
Faster when logical fragmentation is:HighLow
Parallel processingYesNo
Compacts pagesYesYes
Can be stopped and restarted without losing work completed to that pointNoYes
Able to untangle interleaved indexesMay reduce interleavingNo
Additional free space is required in the data file for defragmentingYesNo
Faster on larger indexesYesNo
Rebuilds statisticsYesNo
Log space usageHigh 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 systemsNoYes

 

SQL Server 2005ALTER INDEX [indexname] ON [table_name] REBUILD;

CREATE INDEX WITH DROP_EXISTING

3.比较通用的维护过程,实现自动化维护

a) 获取及查看所有索引的碎片情况

   

b) 根据(a)查询结果得到的索引碎片的情况自动选择合适的处理方法

针对Sql Server2000的联机维护:

针对SQL Server 2000的脱机维护:

sp_msforeachtable @command1="dbcc dbreindex('?','',85)"

 

 

SQL Server 2005的通用维护过程:(碎片小于30%的联机组织,碎片>=30%的脱机重建)

   找出索引碎片程度大于 10% 的索引,然后根据索引碎片程度,分别来采取不同的方法来整理索引碎片。小于 30% 的使用 alter index reorganize;大于等于 30% 的使用 alter index rebuild。其中 reorganize 相当于 dbcc indexdefrag()rebuild 相当于 dbcc dbreindex()

        SQL 碎片整理后,索引数据页在数据库文件中排列的更紧凑,可以大幅提高一些 SQL 查询的效率。DBA 可以每周进行一次碎片整理。另外要注意的是,不要在收缩数据库(dbcc shrinkfile, dbcc shrinkdatabase)前整理索引碎片。

   

花了我一天的时间弄索引SQL SERVER优化和整理,索引真的很重要,没有索引何来之优化!数据库的优化大部分都是基于索引的优化,是重中之重!现在只是学习的起点而且,对自己说加油。明天再好好消化消化!

 

 

 

 

 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值