SQL 인덱스 관리 (임시)

ALTER INDEX [TCNGT~0] ON [dsm].[TCNGT] REORGANIZE

go

UPDATE STATISTICS [dsm].[TCNGT] ([TCNGT~0]) WITH FULLSCAN

go

 

 

DSM reorg script

 

use DSM
go


    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.

    declare @DB_ID int;
    set @DB_ID = db_id();

    SELECT
        aa.[object_id] AS objectid,
        aa.index_id AS indexid,
    --    bb.[name] as strIndexName,
    --    bb.[type_desc] as strIndexType,
        aa.partition_number AS partitionnum,
        aa.avg_fragmentation_in_percent AS frag
    INTO work_to_do
    FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL , NULL, 'LIMITED') aa
    --left join sys.indexes bb On aa.[object_id] = bb.[object_id] and aa.[index_id] = bb.[index_id]
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0
    --order by objectid, indexid;

    -- 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;

    DECLARE @intCnt INT;
    SET @intCnt = 0

    WHILE @@FETCH_STATUS = 0
    BEGIN;
        SET @intCnt = @intCnt + 1;

        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;

        IF @frag < 20.0
        BEGIN;
            SELECT @command = 'ALTER INDEX [' + @indexname + '] ON [' + @schemaname + '].[' + @objectname + '] REORGANIZE';
            IF @partitioncount > 1
                SELECT @command = @command + ' PARTITION=' + CONVERT (varchar(5), @partitionnum);
               
                set @command = @command + char(10) + 'go' + char(10) + char(10)
            --EXEC (@command);
            --PRINT convert(varchar(3), @intCnt) + '. ' + @command
            PRINT @command;

            --통계 업데이트
            SET @command = 'UPDATE STATISTICS ['+ @schemaname + '].[' + @objectname + '] ([' + @indexname + ']) WITH FULLSCAN';
            set @command = @command + char(10) + 'go' + char(10) + char(10);
            --EXEC (@command);
            --PRINT convert(varchar(3), @intCnt) + '. ' + @command
            PRINT @command;
        END;

        IF @frag >= 20.0
        BEGIN;
            SELECT @command = 'ALTER INDEX [' + @indexname +'] ON [' + @schemaname + '].[' + @objectname + '] REBUILD';
            IF @partitioncount > 1
            begin
                SELECT @command = @command + ' PARTITION=' + CONVERT (varchar(5), @partitionnum);
                set @command = @command + ' with (maxdop=18)'
            end
            else
            begin
                set @command = @command + ' with (maxdop=18, PAD_INDEX = on, fillfactor=90, '
                --if (@indexname = 'PK_RMMessage')
                if (1=2)
                    set @command = @command + 'online=off)'
                else
                    set @command = @command + 'online=on)'
            end

            set @command = @command + char(10) + 'go' + char(10) + char(10)
            --EXEC (@command);
            --PRINT convert(varchar(3), @intCnt) + '. ' + @command
            PRINT @command;
        END;

        PRINT '--FRAGMENTATION : ' + CAST(@frag AS VARCHAR(10)) + '%' + CHAR(10);
        --PRINT '' + @command + char(10) + 'go' + char(10) + char(10);
        --PRINT @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

 

 

 

이 글은 스프링노트에서 작성되었습니다.