怎样检查fragmentation

SQL索引碎片整理

最新推荐文章于 2021-01-12 14:44:24 发布

转载最新推荐文章于 2021-01-12 14:44:24 发布 · 92 阅读

本文提供了一种使用SQL Server查询平均碎片百分比的方法，并提供了一个存储过程来重建那些碎片率大于或等于30%的索引。

可以运行下面SQL语句：

SELECT dbschemas.[name] AS 'Schema', 
 dbtables.[name] AS 'Table', 
 dbindexes.[name] AS 'Index',
 indexstats.index_type_desc,
 indexstats.avg_fragmentation_in_percent,
 indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON (dbtables.[object_id] = indexstats.[object_id])
INNER JOIN sys.schemas dbschemas ON (dbtables.[schema_id] = dbschemas.[schema_id])
INNER JOIN sys.indexes AS dbindexes ON (dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id)
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC

View Code

参考运行后这个avg_fragmentation_in_percent值，肯定是越小越好：

以下内容于2014-10-07 13:15分更新：
Insus.NET 写了一个存储过程，把avg_fragmentation_in_percent值大于或等于30的，rebuild索引：

CREATE PROCEDURE [dbo].[usp_RebuildIndex]
AS
BEGIN
    IF OBJECT_ID('#t') IS NOT NULL
        DROP TABLE #t
    CREATE TABLE #t
    (
    [ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Table] NVARCHAR(128) NOT NULL,
    [Index] NVARCHAR(128) NOT NULL
    )

    INSERT INTO #t ([Table],[Index]) SELECT  dbtables.[name],dbindexes.[name] 
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables ON (dbtables.[object_id] = indexstats.[object_id])
    INNER JOIN sys.schemas dbschemas ON (dbtables.[schema_id] = dbschemas.[schema_id])
    INNER JOIN sys.indexes AS dbindexes ON (dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id)
    WHERE indexstats.database_id = DB_ID() AND dbindexes.[name] IS NOT NULL AND indexstats.avg_fragmentation_in_percent >=　30

    DECLARE @r INT = 1, @rs INT = 0
    SELECT @rs = MAX([ID]) FROM #t

    WHILE @r <= @rs
    BEGIN
        DECLARE @table NVARCHAR(128), @index NVARCHAR(128)
        SELECT @table = [Table],@index = [Index] FROM #t WHERE [ID] = @r

        EXECUTE('ALTER INDEX '+@index +' ON '+ @table +' REBUILD;')

        SET @r = @r + 1
    END
END
GO