SQL Server通过整理索引碎片和重建索引提高速度

浏览数：69 / 时间：2015年06月12日

本文章转载：http://database.51cto.com/art/201108/282408.htm

SQL Server数据库中，当索引碎片太多时，就会拖慢数据库查询的速度。这时我们可以通过整理索引碎片和重建索引来解决，本文我们主要就介绍了这部分内容，希望能够对您有所帮助。

SQL Server数据库操作中，当数据库中的记录比较多的时候，我们可以通过索引来实现查询。但是当索引碎片太多的时候，就会很严重地影响到查询的速度。这时候我们可以采取两种方法来解决：一种时整理索引碎片，另一种是重建索引。

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构，用来帮助查找和整理数据。索引的重要性体现在能够使数据库引擎快速返回查询
结果。当对索引所在的基础数据表进行修改时(包括插入、删除和更新等操作)，会导致索引碎片的产生。当索引的逻辑排序和基础表或视图的物理排序不匹配时，
就会产生索引碎片。随着索引碎片的不断增多，查询响应时间就会变慢，查询性能也会下降。在SQL Server
2005中，要解决这个问题，要么重新组织索引要么重新生成索引。

索引碎片的产生：http://blog.sina.com.cn/s/blog_792e033201013fkj.html

索引能够加快对表的访问速度，然而任何事物都有两面性，索引在带给我们便利的同时也会占用额外的磁盘空间，并且我们在对表进行增删改的操作时也要消耗额外的时间来更新索引。而在我们对包含索引的表进行增删改时，也会造成索引碎片，久而久之，索引碎片程度越来越高，反而会降低我们对表的访问速度。因此作为数据库管理员，要定期维护索引，修复索引碎片。

怎样确定索引是否有碎片？ http://blog.tianya.cn/blogger/post_read.asp?BlogID=2587659&PostID=24488142
　　
　　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 ]
　　)
　　]

那么SQL Server如何的定期清理索引碎片呢？可以做个Job作业计划，定期的执行。

--更新统计信息
EXEC sp_updatestats
 
 
 
---索引优化
DECLARE @tableName NVARCHAR(50) ,
    @indexName NVARCHAR(50) ,
    @fragmentPercent NVARCHAR(20) ,
    @sql NVARCHAR(200)= ‘‘
DECLARE 
indexFragment_cursor CURSOR
FOR
    SELECT  
o.name AS tableName ,
            ix.name 
AS indexName ,
            avg_fragmentation_in_percent AS 
fragmentPercent--,
    --dip.fragment_count,
    --dip.avg_fragment_size_in_pages
    FROM    
sys.dm_db_index_physical_stats(DB_ID() ,NULL 
,NULL ,NULL ,NULL) dip
            INNER 
JOIN sys.indexes ix ON 
ix.index_id = dip.index_id
                                         AND 
ix.object_id = dip.object_id
            INNER 
JOIN sys.objects o ON 
ix.object_id = o.object_id
    WHERE   
dip.index_id > 0
            AND 
avg_fragmentation_in_percent > 5
    ORDER 
BY avg_fragmentation_in_percent DESC
 
--打开游标
OPEN indexFragment_cursor 
FETCH NEXT
    FROM 
indexFragment_cursor 
    INTO 
@tableName ,@indexName ,@fragmentPercent
WHILE @@FETCH_STATUS = 0 
    BEGIN
    --print @tableName+‘----‘+@indexName++‘----‘+@fragmentPercent
        SET 
@sql = ‘ALTER INDEX ‘ 
+ QUOTENAME(@indexName) + ‘ on ‘
            + QUOTENAME(@tableName)
            + CASE 
WHEN @fragmentPercent <= ‘30‘ 
THEN ‘ REORGANIZE;‘
                   WHEN 
@fragmentPercent > ‘30‘ 
THEN ‘ REBUILD;‘
              END
    --print @sql
        EXEC(@sql)
    --移到下一行记录
        FETCH 
NEXT
        FROM 
indexFragment_cursor 
        INTO 
@tableName ,@indexName ,@fragmentPercent
    END
 
--关闭，释放游标
CLOSE indexFragment_cursor
DEALLOCATE 
indexFragment_cursor
 
 
 
GO

　　SQL 2008 R2索引的重建:http://www.2cto.com/database/201204/128616.html

项目升级数据库由SQL2000升级到2008R2，今天对数据库表进行碎片扫描，发现有些表碎片较大，于是决定重建索引，联机帮助是最好的老师，将相关脚本摘录备后查。
 
参考sys.dm_db_index_physical_stats
 
检查索引碎片情况
 
  
 
SELECT
 
    OBJECT_NAME(object_id) as 
objectname,
 
    object_id AS 
objectid,  www.2cto.com  
 
    index_id AS 
indexid,
 
    partition_number AS 
partitionnum,
 
    avg_fragmentation_in_percent AS 
fra
 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL 
, NULL, ‘LIMITED‘)
 
WHERE avg_fragmentation_in_percent > 10.0 AND 
index_id > 0;
 
使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 （来源于联机帮助）
 
  
 
SET NOCOUNT ON;
 
DECLARE 
@objectid int;
 
DECLARE 
@indexid int;
 
DECLARE 
@partitioncount bigint;
 
DECLARE 
@schemaname nvarchar(130); 
 
DECLARE 
@objectname nvarchar(130); 
 
DECLARE 
@indexname nvarchar(130); 
 
DECLARE 
@partitionnum bigint;
 
DECLARE 
@partitions bigint;
 
DECLARE 
@frag float;  www.2cto.com  
 
DECLARE 
@command nvarchar(4000); 
 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
 
-- and convert object and index IDs to names.
 
SELECT
 
    object_id AS 
objectid,
 
    index_id AS 
indexid,
 
    partition_number AS 
partitionnum,
 
    avg_fragmentation_in_percent AS 
frag
 
INTO #work_to_do
 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL 
, NULL, ‘LIMITED‘)
 
WHERE avg_fragmentation_in_percent > 10.0 AND 
index_id > 0;
 
  
 
-- Declare the cursor for the list of partitions to be processed.
 
DECLARE 
partitions CURSOR 
FOR SELECT * FROM #work_to_do;
 
  
 
-- Open the cursor.
 
OPEN partitions;
 
  
 
-- Loop through the partitions.
 
WHILE (1=1)
 
    BEGIN;
 
        FETCH 
NEXT
 
           FROM 
partitions
 
           INTO 
@objectid, @indexid, @partitionnum, @frag;
 
        IF @@FETCH_STATUS < 0 BREAK;
 
        SELECT 
@objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
 
        FROM 
sys.objects AS 
o  
 
        JOIN 
sys.schemas as 
s ON s.schema_id = o.schema_id
 
        WHERE 
o.object_id = @objectid;
 
        SELECT 
@indexname = QUOTENAME(name)
 
        FROM 
sys.indexes
 
        WHERE  
object_id = @objectid AND 
index_id = @indexid;
 
        SELECT 
@partitioncount = count 
(*)
 
        FROM 
sys.partitions
 
        WHERE 
object_id = @objectid AND 
index_id = @indexid;
 
  
 
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
 
        IF @frag < 30.0
 
            SET 
@command = N‘ALTER INDEX ‘ 
+ @indexname + N‘ ON ‘ 
+ @schemaname + N‘.‘ 
+ @objectname + N‘ REORGANIZE‘;
 
        IF @frag >= 30.0
 
            SET 
@command = N‘ALTER INDEX ‘ 
+ @indexname + N‘ ON ‘ 
+ @schemaname + N‘.‘ 
+ @objectname + N‘ REBUILD‘;
 
        IF @partitioncount > 1
 
            SET 
@command = @command + N‘ PARTITION=‘ 
+ CAST(@partitionnum AS 
nvarchar(10));  www.2cto.com  
 
        EXEC 
(@command);
 
        PRINT N‘Executed: ‘ 
+ @command;
 
    END;
 
  
 
-- Close and deallocate the cursor.
 
CLOSE 
partitions;
 
DEALLOCATE 
partitions;
 
  
 
-- Drop the temporary table.
 
DROP 
TABLE #work_to_do;
 
GO