SQL索引重建命令

在做维护项目的时,我们经常会遇到索引维护的问题,通过语句,我们就可以判断某个表的索引是否需要重建。

执行一下语句:先分析表的索引

分析表的索引建立情况:

DBCC showcontig('Table')

DBCC SHOWCONTIG 正在扫描 ‘Table” 表…
表: ‘Table” (53575229);索引 ID: 1,数据库 ID: 14
已执行 TABLE 级别的扫描。
– 扫描页数…………………………..: 228
– 扫描区数…………………………: 52
– 区切换次数…………………………: 225
– 每个区的平均页数……………………: 4.4
– 扫描密度 [最佳计数:实际计数]…….: 12.83% [29:226]
– 逻辑扫描碎片 ………………: 97.37%
– 区扫描碎片 ………………: 98.08%
– 每页的平均可用字节数……………………: 2686.3
– 平均页密度(满)…………………: 66.81%

当你发现,扫描密度行,最佳计数和实际计数的比例已经严重失调,逻辑扫描碎片占了非常大的百分比,每页平均可用字节数非常大时,就说明

你的索引需要重新整理一下了。

执行重建索引命令:

DBCC DBREINDEX('Table'')

后分析的情况
DBCC SHOWCONTIG 正在扫描 ‘Table” 表…
表: ‘Table” (53575229);索引 ID: 1,数据库 ID: 14
已执行 TABLE 级别的扫描。
– 扫描页数…………………………..: 154
– 扫描区数…………………………: 20
– 区切换次数…………………………: 19
– 每个区的平均页数……………………: 7.7
– 扫描密度 [最佳计数:实际计数]…….: 100.00% [20:20]
– 逻辑扫描碎片 ………………: 0.00%
– 区扫描碎片 ………………: 55.00%
– 每页的平均可用字节数……………………: 86.8
– 平均页密度(满)…………………: 98.93%

解决碎片问题
一旦你确定表或索引有碎片问题,那么你有4个选择去解决那些问题:

1. 删除并重建索引

2. 使用DROP_EXISTING子句重建索引

3. 执行DBCC DBREINDEX

4. 执行DBCC INDEXDEFRAG

尽管每一个技术都能达到你整理索引碎片的最终目的,但各有各的优缺点。
删除并重建索引
用DROP INDEX和CREATE INDEX或ALTER TABLE来 删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时,对于查询它不在可用,查询性能也许会受到明显的影响,直到重建索引为止。另一个 潜在的缺陷是当都请求索引的时候会引起阻塞,直到重建索引为止。通过其他的处理也能解决阻塞,就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆,聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。
删除并重建索引的确有一个好处就是通过重新排序索引页,使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用,以使那些索引回到它们应该在的位置。
使用DROP_EXISTING子句重建索引
为了避免在重建聚集索引时表上的非聚集索引重建两次,可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值,以避免非聚集索引重建两次。和删除并重建索引一样,该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。
除了和上一个方法一样的好处之外,该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。
执行DBCC DBREINDEX
DBCC DBREINDEX类似于第二种方法,但它物理地重建索引,允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引,不象第二种方法。
DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的,所以如果在完成之前中断了,那么你会丢失所有已经执行过的碎片。
执行DBCC INDEXDEFRAG
DBCC INDEXDEFRAG(在SQLServer2000中可用)按照索引键的逻辑顺序,通过重新整理索引里存在的叶页来减少外部碎片,通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理,不会引起其他方法的阻塞问题。
以下是DBREINDEX ,适当地每周或每月在数据库上运行:

declare @tablename varchar(255)
declare TableCursor cursor for
    select table_name from information_schema.tables
    where table_type='base table'
declare @command varchar(255)
open TableCursor 
fetch next from tablecursor into @tablename 
while @@fetch_status=0
begin
    print 'reindexing  '[email protected]
    dbcc DBREINDEX(@tablename)
    fetch next from tablecursor into @tablename
end
close TableCursor
deallocate TableCursor

此条目发表在学习笔记分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据