次の方法で共有

  • [アーティクル]

インデックスの保守を自動化する方法

更新 : 2009-04-30

次のスクリプトは、Planning Server データベース上のすべてのインデックスをチェックし、断片化の平均値が 10% を超えるインデックスを検出します。次に、断片化の程度に応じて、インデックスの再編成または再構築を行います。

例

サンプル クエリ

-- ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
-- conditionally select from the function, converting 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.
FETCH NEXT
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
    BEGIN;
        SELECT @objectname = o.name, @schemaname = 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 = 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
    BEGIN;
    SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

    EXEC (@command);
    END;

IF @frag >= 30.0
    BEGIN;
    SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);

    EXEC (@command);
    END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
GO

サンプルの結果

次の結果は、Planning アプリケーション データベースでこのクエリを実行した結果のサンプルです。

Executed ALTER INDEX XPKAsyncWorkItems ON dbo.AsyncWorkItems REBUILD

Executed ALTER INDEX XPKAsyncWorkItemStatusHistory ON dbo.AsyncWorkItemStatusHistory REBUILD

Executed ALTER INDEX XPKWorkbooks ON dbo.Workbooks REBUILD

Executed ALTER INDEX XPKRecurrentCycles ON dbo.RecurrentCycles REBUILD

Executed ALTER INDEX XPKCycleInstances ON dbo.CycleInstances REBUILD

Executed ALTER INDEX XPKAG_AccountType ON dbo.AG_AccountType REBUILD

Executed ALTER INDEX XPKAssignmentDefinitions ON dbo.AssignmentDefinitions REBUILD

Executed ALTER INDEX XPKD_Account ON dbo.D_Account REBUILD

Executed ALTER INDEX XD_Account_Label_U ON dbo.D_Account REBUILD

Executed ALTER INDEX XPKAssignments ON dbo.Assignments REBUILD

Executed ALTER INDEX MG_ConsolidationModel_MeasureGroup_default_partition_PK ON dbo.MG_ConsolidationModel_MeasureGroup_default_partition REBUILD

Executed ALTER INDEX H_Account_Consolidation_PK ON dbo.H_Account_Consolidation REBUILD

Executed ALTER INDEX D_Product_PK ON dbo.D_Product REBUILD

Executed ALTER INDEX H_Product_ResortProducts_PK ON dbo.H_Product_ResortProducts REBUILD

Executed ALTER INDEX MG_Corporate Costs_MeasureGroup_default_partition_PK ON dbo.MG_Corporate Costs_MeasureGroup_default_partition REORGANIZE

Executed ALTER INDEX XPKRuleSetsOrRules ON dbo.RuleSetsOrRules REBUILD

Executed ALTER INDEX RuleSetsOrRules_GUID_U ON dbo.RuleSetsOrRules REBUILD

Executed ALTER INDEX RuleSetsOrRules_Label_U ON dbo.RuleSetsOrRules REBUILD

Executed ALTER INDEX XPKRuleSets_Rules_Assoc ON dbo.RuleSets_Rules_Assoc REBUILD

Executed ALTER INDEX XPKBizRoles ON dbo.BizRoles REBUILD

Executed ALTER INDEX H_HR_HR_PK ON dbo.H_HR_HR REORGANIZE

Executed ALTER INDEX XPKBizDataPermissions ON dbo.BizDataPermissions REBUILD

データベース管理者は前述のスクリプトをカスタマイズすることで、異なるインデックスを異なる頻度で再構築または再編成できます。たとえば、このスクリプトを変更して、メジャー グループ テーブルのクラスタ インデックスを毎日、他のインデックスを週 1 回の頻度で再構築または再編成できます。

関連項目