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

本小说转发:http://database.51cto.com/art/201108/282408.htm

SQL
Server数据库中,当索引碎片太多时,就会拖慢数据库查询的快慢。这时大家能够透过整理索引碎片和重建索引来化解,本文大家最主要就介绍了那部分内容,希望能够对您有所协助。

 

 

SQL
Server
数据库操作中,当数据库中的记录比较多的时候,大家得以因而索引来完结查询。不过当索引碎片太多的时候,就会很严重地震慑到查询的速度。那时候大家可以动用三种艺术来缓解:一种时整理索引碎片,另一种是重建索引

 

目录是数据库引擎中针对表(有时候也本着视图)建立的专门数据结构,用来增派寻找和整理数据。索引的重大展现在力所能及使数据库引擎火速回到查询
结果。当对索引所在的基础数据表进行改动时(包罗插入、删除和立异等操作),会促成索引碎片的爆发。当索引的逻辑排序和基础表或视图的大体排序不匹配时,
就会时有发生索引碎片。随着索引碎片的不断增多,查询响应时间就会变慢,查询品质也会降低。在SQL
Server
二零零五中,要化解那几个难点,要么重新组织目录要么重新生成索引。

 

目录碎片的发出: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 二零零六昂Cora2索引的重建: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

  

 UPDATE
STATISTICS
立异计算音讯来增进查询作用. 

该命令在一张表也许索引了的视图上更新查询优化总括数字音信. 暗中认可情形下,
查询优化器已经更新了必需的用来抓牢查询陈设的总括消息; 在一些意况下,
你能够透过利用UPDATE STATISTICS
命令只怕存款和储蓄进度sp_updatestats
来比私下认可更频仍地换代计算消息来增长查询功效. 

 

立异总计音讯能担保查询能以最新的总计音讯来编写翻译. 可是,
更新计算新闻会滋生查询的再一次编译. 大家建议并非过度频繁地换代总括音讯,
因为此处有五个在升高查询布置和用来再一次编译查询的权衡.
具体的衡量要看您的应用程序而定.

相关文章