大多数SQL Server表需要索引来提高数据
访问速度
如果没有索引SQL Server要进行表格扫描读取表中每
个记录才能找到索要数据
索引可以分为簇索引和非簇索引簇索引通过重排表中数据来提高数据访问速度而非簇索引则通过维护表中数据指针来提高数据索引索引
体系结构:为什么要不断
维护表索引？首先简单介绍下索引体系结构SQL Server在硬盘中用8KB页面在数据库文件内存放数据缺省情况下这些页面及其包含数据是无组织为了使混乱变为有序就要生成索引生成索引后就有了索引页和数据页数据页保存用户写入数据信息索引页存放用于检索列数据值清单(关键字)和索引表中该值所在纪录地址指针索引分为簇索引和非簇索引簇索引实质上是将表中数据排序就好像是字典索引目录非簇索引不对数据排序它只保存了数据指针地址向个带簇索引表中插入数据当数据页达到100%时由于页面没有空间插入新纪录这时就会发生分页SQL Server 将大约半数据从满页中移到空页中从而生成两个半满页这样就有大量数据空间簇索引是双向链表在每页头部保存了前页、后页地址以及分页后数据移动地址由于新页可能在数据库文件中任何地方因此页面链接不定指向磁盘下个物理页链接可能指向了另个区域这就形成了分块从而减慢了系统速度对于带簇索引和非簇索引表来说非簇索引关键字是指向簇索引而不是指向数据页本身为了克服数据分块带来
负面影响需要重构表索引这是非常费时因此只能在需要时进行可以通过DBCC SHOWCONTIG来确定是否需要重构表索引下面举例来介绍说明DBCC SHOWCONTIG和DBCC REDBINDEX使用思路方法以SQL Server自带northwind数据作为例子带开SQL Server
Query analyzer输入命令:use pubs
declare @table_id
@table_id=object_id(\'tbldlvinfoback\')dbcc showcontig(@table_id)
这个命令显示pubs数据库中
tbldlvinfoback表分块情况结果如下:DBCC SHOWCONTIG 正在扫描 \'tblDlvInfoback\' 表...
表: \'tblDlvInfoback\'(1797581442)；索引 ID: 0
数据库 ID: 5已执行 TABLE 级别
扫描- 扫描页数.....................................: 197214
- 扫描扩展盘区数...............................: 24659
- 扩展盘区开关数...............................: 24658
- 每个扩展盘区上
平均页数.....................: 8.0- 扫描密度［最佳值:实际值］....................: 99.97%［24652:24659］
- 扩展盘区扫描碎片.............................: 15.46%
- 每页上
平均可用字节数.......................: 374.6- 平均页密度(完整)...........................: 95.37%
DBCC 执行完毕
如果 DBCC 输出了
信息请和系统管理员联系通过分析这些结果可以知道该表
索引是否需要重构表1.1描述了每行意义描述Pages Scanned 表或索引中
长页数Extents Scanned 表或索引中
长区页数Extent Switches DBCC遍历页时从
个区域到另个区域次数Avg. Pages per Extent 相关区域中
页数Scan Density Best 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表有分块需要重构表索引下面通过DBCC DBREINDEX来重构表簇索引同样在Query Analyzer中输入命令:
use northwind
dbcc dbreindex(\'northwind.dbo.orders\',pk_orders,90)
执行结果:
DBCC execution completed. If DBCC pr
ed error messages, contact your system administrator.Dbcc dbreindex参数介绍说明:第
个参数为要重构表明第 2个参数为需要重构索引表识‘’表示所有索引第 3个参数为页填充因子填充因子越大页越满然后再用DBCC SHOWCONTIG查看重构簇索引后
结果:use northwind
declare @table_id
t @table_id=object_id(\'orders\')
dbcc showcontig(@table_id)
返回结果如下:
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%
DBCC execution completed. If DBCC pr
ed error messages, contact your system administrator.通过结果我们可以看到Scan Denity为100%表没有分块不需要重构表索引了
如果重构表簇索引Scan Denity还小于100%话可以重构表全部索引命令如下:--use northwind
--dbcc dbreindex(\'northwind.dbo.orders\',’’,90)
使用作业定时重构索引:
如果你
数据库访问非常频繁话非常容易出现数据分块现象因此你可以利用作业来帮你在系统相对空闲时候重构你索引为什么要不断
维护表索引？首先简单介绍下索引体系结构SQL Server在硬盘中用8KB页面在数据库文件内存放数据缺省情况下这些页面及其包含数据是无组织为了使混乱变为有序就要生成索引生成索引后就有了索引页和数据页数据页保存用户写入数据信息索引页存放用于检索列数据值清单(关键字)和索引表中该值所在纪录地址指针索引分为簇索引和非簇索引簇索引实质上是将表中数据排序就好像是字典索引目录非簇索引不对数据排序它只保存了数据指针地址向个带簇索引表中插入数据当数据页达到100%时由于页面没有空间插入新纪录这时就会发生分页SQL Server 将大约半数据从满页中移到空页中从而生成两个半满页这样就有大量数据空间簇索引是双向链表在每页头部保存了前页、后页地址以及分页后数据移动地址由于新页可能在数据库文件中任何地方因此页面链接不定指向磁盘下个物理页链接可能指向了另个区域这就形成了分块从而减慢了系统速度对于带簇索引和非簇索引表来说非簇索引关键字是指向簇索引而不是指向数据页本身为了克服数据分块带来
负面影响需要重构表索引这是非常费时因此只能在需要时进行可以通过DBCC SHOWCONTIG来确定是否需要重构表索引下面举例来介绍说明DBCC SHOWCONTIG和DBCC REDBINDEX使用思路方法以SQL Server自带northwind数据作为例子带开SQL Server
Query analyzer输入命令:use pubs
declare @table_id
@table_id=object_id(\'tbldlvinfoback\')dbcc showcontig(@table_id)
这个命令显示pubs数据库中
tbldlvinfoback表分块情况结果如下:DBCC SHOWCONTIG 正在扫描 \'tblDlvInfoback\' 表...
表: \'tblDlvInfoback\'(1797581442)；索引 ID: 0
数据库 ID: 5已执行 TABLE 级别
扫描- 扫描页数.....................................: 197214
- 扫描扩展盘区数...............................: 24659
- 扩展盘区开关数...............................: 24658
- 每个扩展盘区上
平均页数.....................: 8.0- 扫描密度［最佳值:实际值］....................: 99.97%［24652:24659］
- 扩展盘区扫描碎片.............................: 15.46%
- 每页上
平均可用字节数.......................: 374.6
- 平均页密度(完整)...........................: 95.37%
DBCC 执行完毕
如果 DBCC 输出了信息请和系统管理员联系通过分析这些结果可以知道该表
索引是否需要重构表1.1描述了每行意义描述Pages Scanned 表或索引中
长页数Extents Scanned 表或索引中
长区页数Extent Switches DBCC遍历页时从
个区域到另个区域次数Avg. Pages per Extent 相关区域中
页数Scan Density Best Count是连续链接时
理想[Best Count:Actual Count]
use northwind
dbcc dbreindex(\'northwind.dbo.orders\',pk_orders,90)
执行结果:
DBCC execution completed. If DBCC pr
ed error messages, contact your system administrator.Dbcc dbreindex参数介绍说明:第
个参数为要重构表明第 2个参数为需要重构索引表识‘’表示所有索引第 3个参数为页填充因子填充因子越大页越满然后再用DBCC SHOWCONTIG查看重构簇索引后
结果:use northwind
declare @table_id
@table_id=object_id(\'orders\')dbcc showcontig(@table_id)
返回结果如下:
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%
DBCC execution completed. If DBCC pr
ed error messages, contact your system administrator.通过结果我们可以看到Scan Denity为100%表没有分块不需要重构表索引了
如果重构表簇索引Scan Denity还小于100%话可以重构表全部索引命令如下:--use northwind
--dbcc dbreindex(\'northwind.dbo.orders\',
最新评论