一、必要性:
大多数 SQL Server 表需要索引来提高数据的访问速度,如果没有索引, SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引,簇索引通过重排表中的数据来提高数据的访问速度,而非簇索引则通过维护表中的数据指针来提高数据的索引。
在数据库中创建索引时,查询所使用的索引信息存储在索引页中。连续索引页由从一个页到下一个页的指针链接在一起。当对数据的更改影响到索引时,索引中的信息可能会在数据库中分散开来。重建索引可以重新组织索引数据(对于聚集索引还包括表数据 ) 的存储,清除碎片。这可通过减少获得请求数据所需的页读取数来提高磁盘性能。
二、 何时需要重构索引,如何检测?
为了克服数据分块带来的负面影响,需要重构表的索引,这是非常费时的,因此只能在需要时进行。可以通过 DBCC SHOWCONTIG 来确定是否需要重构表的索引。
dbcc showcontig ( ’ 表名 ’ ) ;
以一个测试表为例,输出结果;
- Pages Scanned....................................: 197214 - Extents Scanned...............................: 24659 - Extent Switches DBCC...............................: 24658 - Avg. Pages per Exten.....................: 8.0 - Scan DensityBest Coun....................: 99.97%[24652:24659] - Extent Scan Fragmentation.............................: 15.46% - Avg. Bytes Free per Page.......................: 374.6 - Avg. Page Density (full)....................: 95.37% |
通过分析这些结果可以知道该表的索引是否需要重构。下边描述了每一行的意义描述
Pages Scanned 表或索引中的长页数
Extents Scanned 表或索引中的长区页数
Extent Switches DBCC 遍历页时从一个区域到另一个区域的次数
Avg. Pages per Extent 相关区域中的页数
Scan DensityBest Count 是连续链接时的理想区
[Best Count:Actual Count] 域改变数, Actual Count 是实际区域改变数, Scan Density 为 100%, 表示没有分块。
Logical Scan Fragmentation 扫描索引页中失序页的百分比
Extent Scan Fragmentation 不实际相邻和包含链路中所有链接页的区域数
Avg. Bytes Free per Page 扫描页面中平均自由字节数
Avg. Page Density (full) 平均页密度,表示页有多满
从上面命令的执行结果可以看的出来, Best count 为 3 而 Actual Count 为 5 这表明 orders 表有分块需要重构表索引。
三、重构索引的方法
<1> 重构单个表,
DBCC DBREINDEX 重建指定数据库中表的一个或多个索引。
语法
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
语法见 SQL Server 帮助。
比如:重构一个表,执行 DBCC DBREINDEX ( ’ 表名 ’ )
以 northwind 库的 orders 表为例,
dbcc dbreindex('northwind.dbo.orders')
dbcc showcontig('northwind.dbo.orders'), 显示结果如下,
DBCC SHOWCONTIG scanning 'Orders' table... Table: 'Orders' (21575115); index ID: 1 , database ID: 6 TABLE level scan performed. - Pages Scanned................................: 22 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 7.3 - Scan Density [Best Count:Actual Count].......: 100.00% [3:3] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 33.33% - Avg. Bytes Free per Page.....................: 869.2 - Avg. Page Density (full).....................: 89.26%
|
通过结果我们可以看到 Scan Denity 为 100% 表没有分块不需要重构表索引了。
<2> 重构一个库中的所有表,
use 库名
sp_msforeachtable 'dbcc DBREINDEX("?")'
sp_msforeachtable 表示对库中每一个表执行某一条命令,相当于一个循环,执行完后,所有的表的索引都被重构;
<3> 整体重构 DBCC CHECKDB ;
DBCC CHECKDB ( 'pubs',repair_rebuild )
DBCC CHECKDB 不仅仅会修复索引,检查指定数据库中的所有对象的分配和结构完整性。
对于数据库中每个表, DBCC CHECKDB 检查其:
1. 索引和数据页是否已正确链接。
2. 索引是否按照正确的顺序排列。
3. 各指针是否一致。
4. 每页上的数据是否均合理。
5. 页面偏移量是否合理。
组合命令如下:
<1> 将数据库置为单用户模式;
sp_dboption 库名 , single, true
<2> 对整个库进行重构;
DBCC CHECKDB(' 库名 ',repair_rebuild)
附注:如果想对单个表进行重构,
DBCC CHECKTABLE(Authors, REPAIR_REBUILD )
<3> 将数据库置为多用户模式;
sp_dboption zrb, single, false
注意:
DBCC CHECKDB 是大量占用 CPU 和磁盘的操作。每一个需要检查的数据页都必须首先从磁盘读入内存。另外, DBCC CHECKDB 使用 tempdb 排序。
如果在 DBCC CHECKDB 运行时动态执行事务,那么事务日志会继续增长,因为 DBCC 命令在完成日志的读取之前阻塞日志截断。
建议在服务器负荷较少的时候运行 DBCC CHECKDB 。如果在负荷高峰期运行 DBCC CHECKDB ,那么事务吞吐量性能和 DBCC CHECKDB 完成时间性能都会受到影响。
四、定时重构,
如果数据库访问非常频繁的话,非常容易出现数据分块的现象,因此可以利用作业来系统相对空闲的时候重构索引。