sqlserver索引碎片怎么避免

毫无疑问，给表添加索引是有好处的，你要做的大部分工作就是维护索引，在数据更改期间索引可能产生碎片，所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。

那么到底什么是索引碎片呢？索引碎片实际上有2种形式：外部碎片和内部碎片。不管哪种碎片基本上都会影响索引内页的使用。这也许是因为页的逻辑顺序错误（即外部碎片）或每页存储的数据量少于数据页的容量（内部错误）。无论索引产生了哪种类型的碎片，你都会因为它而面临查询的性能问题。

外部碎片

当 索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为了让新 的键按照正确的顺序插入，可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页 的过程会引起索引页偏离逻辑顺序。

下面的例子将比实际的言论更加清晰的解释这个概念。

假定在任何另外的数据插入你的表之前存在索引上的结构如下

（注：下面图片里应该是7和8，原文里是6和8）：

INSERT语句往索引里添加新的数据，假定添加的是5。INSERT将引起新页创建，为了给5在原来的页上留出空间，7和8被移到了新页上。这个创建将引起索引页偏离逻辑顺序。

在有特定搜索或者返回无序结果集的查询的情况下，偏离顺序的索引页不会引起问题。对于返回有序结果集的查询，搜索那些无序的索引页需要进行额外的处理。有序结果集的例子如查询返回4到10之间的记录。为了返回7和8，查询不得不进行额外的页切换。虽然一个额外的页切换在一个长时间运行里是无关紧要的，然而想象一下一个有好几百页偏离顺序的非常大的表的情形。

内部碎片

当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个fill factor（填充因子）会在索引页上留下空间，服务器内部碎片会导致索引尺寸增加，从而在返回需要的数据时要执行额外的读 *** 作。这些额外的读 *** 作会降低查询的性能。

怎样确定索引是否有碎片？

SQLServer提供了一个数据库命令――DBCC SHOWCONTIG――来确定一个指定的表或索引是否有碎片。

DBCC SHOWCONTIG

数据库平台命令，用来显示指定的表的数据和索引的碎片信息。

DBCC SHOWCONTIG 权限默认授予 sysadmin固定服务器角色或 db_owner 和 db_ddladmin固定数据库角色的成员以及表的所有者且不可转让。

语法（SQLServer2000）

DBCC SHOWCONTIG

[ ( { table_name | table_id| view_name | view_id }

[ , index_name | index_id ]

)

]

[ WITH { ALL_INDEXES

| FAST [ , ALL_INDEXES ]

| TABLERESULTS [ , { ALL_INDEXES } ]

[ , { FAST | ALL_LEVELS } ]

}

]

语法（SQLServer7.0）

DBCC SHOWCONTIG

[ ( table_id [,index_id ]

)

]

示例：

显示数据库里所有索引的碎片信息

SET NOCOUNT ON

USE pubs

DBCC SHOWCONTIG WITH ALL_INDEXES

GO

显示指定表的所有索引的碎片信息

SET NOCOUNT ONUSE pubs

DBCC SHOWCONTIG (authors) WITH ALL_INDEXES

GO

显示指定索引的碎片信息

SET NOCOUNT ON

USE pubs

DBCC SHOWCONTIG (authors,aunmind)

GO

结果集

DBCC SHOWCONTIG将返回扫描页数、扫描扩展盘区数、遍历索引或表的页时，DBCC 语句从一个扩展盘区移动到其它扩展盘区的次数、每个扩展盘区的页数、扫描密度（最佳值是指在一切都连续地链接的情况下，扩展盘区更改的理想数目）。

DBCC SHOWCONTIG 正在扫描 'authors' 表...

表: 'authors'（1977058079）；

索引 ID: 1，数据库 ID: 5

已执行 TABLE 级别的扫描。

- 扫描页数.....................................: 1

- 扫描扩展盘区数...............................: 1

- 扩展盘区开关数...............................: 0

- 每个扩展盘区上的平均页数.....................: 1.0

- 扫描密度［最佳值:实际值］....................: 100.00%［1:1］

- 逻辑扫描碎片.................................: 0.00%

- 扩展盘区扫描碎片.............................: 0.00%

- 每页上的平均可用字节数.......................: 6010.0

- 平均页密度（完整）...........................: 25.75%

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

寻找什么

扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。

扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。

扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。

每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。

平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

备注

DBCC SHOWCONTIG实际上仅对那些大表有用。小表显示的结果根本不符合正常标准，因为他们也许没有由多于8个的页面组成。你在查看小表上执行DBCC SHOWCONTIG的结果时应该忽略一些结果。在处理小表时只需关心扩展盘区开关数、逻辑扫描碎片、每页上的平均可用字节数、平均页密度（完整）。

DBCC SHOWCONTIG默认输出的结果是：扫描页数、扫描扩展盘区数、扩展盘区开关数、每个扩展盘区上的平均页数、扫描密度［最佳值:实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。可以用FAST和TABLERESULTS选项来控制这个输出结果。

FAST选项指定执行索引的快速扫描，输出结果是最小的，该选项不读索引的叶或数据页且只返回扫描页数、扫描扩展盘区数、扫描密度［最佳值:实际值］、逻辑扫描碎片。

TABLERESULTS选项将用行集的形式显示信息，将返回扩展盘区开关数、扫描密度［最佳值:实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。

如果既指定FAST选项又指定TABLERESULTS选项，那么将返回对象名、对象ID、索引名、索引ID，页数、扩展盘区开关数、扫描密度［最佳值:实际值］和逻辑扫描碎片。

ALL_INDEXES选项将显示指定表和试图的所有索引的结果，即使指定了一个索引。

ALL_LEVELS选项指定是否为所处理的每个索引的每个级别产生输出（默认只输出索引的页级或表数据级的结果），并且只能与 TABLERESULTS 选项一起使用。

解决碎片问题

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

删除并重建索引

使用DROP_EXISTING子句重建索引

执行DBCC DBREINDEX

执行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比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。

SQLServer提供了一个数据库命令——DBCC SHOWCONTIG——来确定一个指定的表或索引是否有碎片。

示例：

显示数据库里所有索引的碎片信息

DBCC SHOWCONTIG WITH ALL_INDEXES

显示指定表的所有索引的碎片信息

DBCC SHOWCONTIG (authors) WITH ALL_INDEXES

显示指定索引的碎片信息

DBCC SHOWCONTIG (authors,aunmind)

DBCC 执行结果：

扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。

扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。

扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。

每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。

平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

解决碎片问题 ：

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比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。

清除数据库日志语句

--清除sqlserver 2000/2005 数据库日志

替换下红字部分数据库名称

declare @databasename varchar(15)

select @databasename='hdjjgl_journal'

DUMP TRANSACTION @databasename WITH NO_LOG

BACKUP LOG @databasename WITH NO_LOG

DBCC SHRINKDATABASE(@databasename)

或者

USE 数据库名

DUMP TRANSACTION 数据库名 WITH NO_LOG

BACKUP LOG 数据库名 WITH NO_LOG

DBCC SHRINKFILE(2)

**********************************

--清除sqlserver 2008 数据库日志 ,执行两遍

替换下红字部分数据库名称，建立datalogbak清楚的日志备份文件夹，注意路径自定义。

use gdzjg_journal

declare @databasename varchar(100)

declare @databasenamelog varchar(100)

declare @databasenamedir varchar(100)

select @databasename='gdzjg_journal'

select @databasenamelog=@databasename+'_log'

select @databasenamedir='g:\datalogbak\'+@databasename

BACKUP LOG @databasename to disk=@databasenamedir

DBCC SHRINKFILE (@databasenamelog,1)

或者

ALTER DATABASE 数据库名称SET RECOVERY SIMPLE

ALTER DATABASE 数据库名称SET RECOVERY FULL

DBCC SHRINKDATABASE(数据库名称,0)

sqlserver 2000、2008会保存所有的数据库 *** 作过程，将指令保存在ldf文件中，如果误删除数据，想恢复数据的话，可以通过Lumigent Log Explorer For SQLServer 软件分析ldf文件，可以看到所有执行过的insert、update、delete数据，导出来再逆向执行即可，本人曾经用过一回，确实奏效。

1、sql server 2000清理数据库日志

USE 数据库名

DUMP TRANSACTION 数据库名 WITH NO_LOG

BACKUP LOG 数据库名 WITH NO_LOG

DBCC SHRINKFILE(2)

SHRINKFILE(2)，后面的2是file_id，可以在当前数据库用select * from sysfiles看得到，看数据库的log文件的fileid是多少，正常情况下log的ldf文件是2，mdf文件的fileid是1。

这个查询语句可以随时执行，不影响数据库的运行。

sql server 2005、2008清理数据库日志

USE 数据库名

ALTER DATABASE 数据库名 SET RECOVERY SIMPLE

ALTER DATABASE 数据库名 SET RECOVERY FULL

DBCC SHRINKDATABASE(数据库名,0)

这个查询语句可以随时执行，不影响数据库的运行。

清理ldf的 *** 作可以使用sql server代理，每天自动执行一次，就不怕文件增长撑爆硬盘了。

2、一般mdf用不着收缩，收缩多了容易产生文件碎片，因为delete数据之后，mdf文件中可用页面空间会保留在那里，等下次有新数据进来时，会继续使用。如果实在要清理，可以参考下面的语句：

sql server 2000、2005、2008 收缩mdf文件

DBCC SHRINKDATABASE(数据库名)

DBCC SHRINKFILE(1,0)

DBCC UPDATEUSAGE(0)

执行上述 *** 作后，你会发现mdf的文件减少了，ldf的文件增大了，再用上面1的日志清理 *** 作一次即可。

3、最后再附送一个查看数据表的行数、占用文件空间、存储情况的查询语句

-- drop table #test

create table #test(

name varchar(50),

rows int,

reserved varchar(20),

data varchar(20),

index_size varchar(20),

unused varchar(20)

)

set nocount on

insert into #test

EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

select * from #test order by cast(replace(reserved,'KB','') as int) desc

---