首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

SQLServer 表的索引碎片查询和处理

数据库操作教程 2023-05-12 12:12:48 转载来源: 网络整理/侵权必删

英文:SQLServer  Index Fragmentation Query and Processing of Tables

目录1.查看索引的碎片率2

1.查看索引的碎片率

SELECT object_name(ips.object_id) AS TableName, ips.index_id, name AS IndexName, avg_fragmentation_in_percent,db_name(ips.database_id) AS DatabaseNameFROM sys.dm_db_index_physical_stats    (Db_id(DB_NAME())        , NULL        , NULL        , NULL        , NULL) AS ipsINNER JOIN sys.indexes AS SI ON ips.object_id = SI.object_id AND ips.index_id = SI.index_idWHERE ips.avg_fragmentation_in_percent > 5 AND SI.index_id <> 0

索引的碎片率低于5%或者,索引的页数少于1000,可以忽略;
索引碎片率在5%-30%之间的,建议reorganize;
索引碎片率大于30%的,建议rebuild。

2.reorganize索引

alter index [索引名] on [dbo].[表名] reorganize;

3.rebuild索引

alter index [索引名] on [dbo].[表名] rebuild;

4.rebuild表上所有的索引

alter index all on [dbo].[表名] rebuild;

5.rebuild数据库中所有的索引  

USE [数据库名]GODECLARE @NoOfPartitions BIGINT;DECLARE @objectid INT;DECLARE @indexid INT;DECLARE @idxname NVARCHAR(255);DECLARE @objname NVARCHAR(255);DECLARE @partitionnum BIGINT;DECLARE @schemaname NVARCHAR(255);DECLARE @partitions BIGINT;DECLARE @frag FLOAT;DECLARE @statement VARCHAR(8000);-- checking existance of the table that we create for temporary purposeIF OBJECT_ID('defrag_work', 'U') IS NOT NULL  DROP TABLE defrag_work;-- Copy the fragmented indexes data into defrag_work table-- All the indexes that has fragmentation < 5 are getting stored into our work tableSELECT  [object_id] AS objectid ,        index_id AS indexid ,        partition_number AS partition_no ,        avg_fragmentation_in_percent AS fragINTO    defrag_workFROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')WHERE   avg_fragmentation_in_percent >5.0 and index_id > 0;-- cursor to process the list of partitionsDECLARE partitions CURSORFOR    SELECT  *    FROM    defrag_work;-- Open the cursor.OPEN partitions;-- Looping through the partitionsFETCH NEXT   FROM partitions   INTO @objectid, @indexid, @partitionnum, @frag;WHILE @@FETCH_STATUS = 0    BEGIN;        SELECT  @objname= QUOTENAME(so.name) ,                @schemaname = QUOTENAME(ss.name)        FROM    sys.objects AS so                JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id        WHERE   so.object_id = @objectid;        SELECT  @idxname = QUOTENAME(name)        FROM    sys.indexes        WHERE   object_id = @objectid                AND index_id = @indexid;        SELECT  @NoOfPartitions = COUNT(*)        FROM    sys.partitions        WHERE   object_id = @objectid                AND index_id = @indexid;/*Let's say N = fragmentation percentageN <= 5 = IGNORE5 < N < 30 = REORGANIZEN > 30 = REBUILD*/        IF (@frag < 30.0) -- @frag > 5 is already filtered in our first query, so we need that condition here            BEGIN;                SELECT  @statement = 'ALTER INDEX ' + @idxname + ' ON '                        + @schemaname + '.' + @objname + ' REORGANIZE';                IF @NoOfPartitions > 1                    SELECT  @statement = @statement + ' PARTITION='                            + CONVERT (CHAR, @partitionnum);                EXEC (@statement);            END;        IF @frag >= 30.0            BEGIN;                SELECT  @statement = 'ALTER INDEX ' + @idxname + ' ON '                        + @schemaname + '.' + @objname + ' REBUILD';                IF @NoOfPartitions > 1                    SELECT  @statement = @statement + ' PARTITION='                            + CONVERT (CHAR, @partitionnum);                EXEC (@statement);            END;        PRINT 'Executed ' + @statement;        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum,            @frag;    END;-- Close and deallocate the cursor.CLOSE partitions;DEALLOCATE partitions;-- drop the tableIF OBJECT_ID('defrag_work', 'U') IS NOT NULL  DROP TABLE defrag_work;

到此这篇关于SQLServer 表的索引碎片查询处理的文章就介绍到这了,更多相关SQLServer 表索引碎片内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

英文:SQLServer  Index Fragmentation Query and Processing of Tables

标签: SQLServer nbsp 索引 碎片 查询 处理


声明:本文内容来源自网络,文字、图片等素材版权属于原作者,平台转载素材出于传递更多信息,文章内容仅供参考与学习,切勿作为商业目的使用。如果侵害了您的合法权益,请您及时与我们联系,我们会在第一时间进行处理!我们尊重版权,也致力于保护版权,站搜网感谢您的分享!

站长搜索

http://www.adminso.com

Copyright @ 2007~2024 All Rights Reserved.

Powered By 站长搜索

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持