• 碎片批处理


    -- Ensure a USE <databasename> statement has been executed first.
    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;
    DECLARE @command nvarchar(4000);
    DECLARE @sql 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;

    set @sql='alter index '++ @indexname + N' ON ' + @schemaname + N'.' + @objectname +' SET ( ALLOW_PAGE_LOCKS = ON );'
    -- 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));

    print @objectname+ @indexname+'碎片'+cast(@frag as varchar(20))
    PRINT @command+'--------------------start'
    exec (@sql) --执行 ALLOW_PAGE_LOCKS
    exec (@command) --执行
    PRINT @command+'--------------------END'


    END;

    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;

    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO
  • 相关阅读:
    当前日志损坏解决一例
    Oracle 1204 RAC failover 测试 (一)
    Logical standby跳过个别SQL不Apply的测试
    Data guard 又出问题了
    CPIO无反应
    回答棉花糖先生关于我说的DB自动增加Index的问题
    SSD硬盘,先不要用在Server上
    ORA00304: requested INSTANCE_NUMBER is busy,终于解决
    .Net运行时的相互关系
    CSS布局探密04
  • 原文地址:https://www.cnblogs.com/qanholas/p/2160634.html
Copyright © 2020-2023  润新知