Новая система по перестройке индексов, совместил скрипт микрософта и гилева, так как скрипт гилева перестал нормально отрабатывать. Опытным путем установил, что скорость работы базы зависит от частоты индексирования. Сейчас размер базы около 400 ГБ. Индексацию делаю каждый час после backup

Declare @ToFile Nvarchar(256);
Declare @Name Nvarchar(256);
use agroupp82;
set @ToFile=N'd:\Backup\'+db_name()+N'_'+CONVERT(nvarchar(10),getdate(),112)+REPLACE(CONVERT(nvarchar(8),GETDATE(),108),':','')+N'.trn'
set @Name=N'log_'+db_name()+N'_'+CONVERT(nvarchar(10),getdate(),112)+REPLACE(CONVERT(nvarchar(8),GETDATE(),108),':','')
BACKUP log AGROUPP82 TO  disk=@ToFile WITH COMPRESSION, NOFORMAT, NOINIT,  NAME=@Name, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Связано с тем что очень быстро набивается журнал логов. Ну и как итог сам скрипт:

use agroupp82;

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 @datetime1 datetime;
declare @datetime2 datetime;
declare @time time;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;

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 > 5.0 AND index_id > 0
order by avg_fragmentation_in_percent desc;

-- 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;
FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        
        --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;

            -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE END TRY BEGIN CATCH END CATCH;';
        IF @frag >= 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD  WITH (ONLINE=ON) END TRY BEGIN CATCH ALTER INDEX '+@indexname+N' ON '+ @schemaname+N'.'+ @objectname+ N' REBUILD WITH (ONLINE=OFF) END CATCH;';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        set @datetime1 = getdate();
        --EXEC (@command);
        set @datetime2 = getdate();
        set @time  = @datetime2-@datetime1;
        PRINT N'Executed: ' + @command+N' Затрачено время:'+CAST(@time as NvarChar(13));
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
    END;

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

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO