如何对SQL Server 重建索引__factor_Server_fill_TableCursor_SQL_

当前位置: 数据库>sqlserver

如何对SQL Server 重建索引

来源: 互联网发布时间：2014-08-29

本文导语: 我们知道重建索引是为了减少数据碎片。数据碎片会导致 SQL Server 进行不必要的数据读，降低 SQL Server 的性能。重建索引也会同时更新列统计，而如果查询所使用的列缺少或遗漏统计信息，这可能导致 SQL Server 内部的优化...

我们知道重建索引是为了减少数据碎片。数据碎片会导致 SQL Server 进行不必要的数据读，降低 SQL Server 的性能。重建索引也会同时更新列统计，而如果查询所使用的列缺少或遗漏统计信息，这可能导致 SQL Server 内部的优化器选择比预期效率低的查询计划。

如果您重建了某张表上的聚集索引，该表上的非聚集索引也同时会被更新。

要更新索引，您可以使用 Maintenance Wizard （相关内容您可以参考 http://msdn.microsoft.com/en-us/library/ms180074.aspx ），或在 SQL Server 代理（ Agent ）中运行如下的自定义代码来更新某个数据库中所有表上的索引：

代码如下:

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar ( 255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema . tables

WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

DBCC DBREINDEX( @TableName, ' ' , 90)
FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

可以根据您的需求修改 DBREINDEX 的参数。

需要注意的是，重建非聚集索引时该表会暂时加上共享锁，对用户不可进行 SELECT 以外的操作；重建聚集索引时该表会暂时加上排外锁，不允许任何用户访问。因此需要制定好计划来预防可能的访问问题。

REBUILD 有一个 fill factor 参数，如果 fill factor 设置为 100% ，这意味着每一个索引页都是完全满的，如果 fill factor 设置为 50% 意味着每个索引页都是半满的。对于 fill factor 100% ，每次新插入或更新一个记录，由于当前页没有空间可用，可能有分页情况产生。过多的分页会降低 SQL Server 的性能。下面具体举个例子：

假设您在一张表上建立了一个使用默认 fill factor 的新索引。当 SQL Server 创建索引时，它会把索引放置在连续的物理页上，以使数据顺序地被读， I/O 访问最优化。但当表因 INSERT ， UPDATE ， DELETE 等操作增长改变时，分页发生， SQL Server 在磁盘的其他地方分配新的页，导致新的页与原物理页不连续，增加了随机 I/O ，访问索引页变慢。

    那么 fill factor 的合适值应该为多少？这取决于表的读 / 写比：
    低更新表（读 / 写比： 100 比 1 ）： 100% fill factor
    高更新表（写超过读）： 50%-70% fill factor
    居中： 80%-90% fill factor

过低的 fill factor 会增加页的数量，也会导致更多的页需要被移至缓存，缓存中有用的数据减少。默认的 fill factor 为 0 （即 100% fill factor ），通常这不是个好的选择，特别是对于聚集索引。

如果您无法判断设置什么 fill factor, 您首先需要确定磁盘的读 / 写比 . 方法就是使用如下两个计数器：
Physical Disk Object: % Disk Read Time 和 Physical Disk Object: % Write Time 。另外一个可能有用的计数器就是： SQL Server Access Methods: Pages Splits/Sec 。这个计数器测量 SQL Server 内每秒分页的次数。如果该数值过高，您需要降低 fill factor 防止新的分页。

如果您想确认您的索引因分页产生的碎片程度，您可以运行 DBCC SHOWCONTIG 命令。如果看特定表和特定索引，您可以运行如下代码：

代码如下:

--Script to identify table fragmentation
--Declare variables

DECLARE
@ID int ,
@IndexID int ,
@IndexName varchar ( 128)

--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID ( 'table_name' ) --enter name of table

--Get the Index Values
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName

--Display the fragmentation
DBCC SHOWCONTIG ( @id, @IndexID)

     结果集中最重要的参数是 Scan Density ，越接近 100% 越好。如果 Scan Density 小于 75% ，那么您可能需要重建表中的索引。
    对于小于 100 数据页，重建索引并不会有明显的性能改善。这是因为物理硬件缓存， SQL Server 缓存和 SQL Server 预读机制隐藏了碎片的负面作用。
    但对于非常大的表，重建索引会使它受益匪浅，因为涉及大量磁盘 I/O 操作。