Стратегия обслуживания баз — VLDB Переиндексация таблиц

Данный документ представляет собой стратегию по переиндексации таблиц в крупных базах данных (VLDB), направленную на обеспечение их стабильной работы, высокой производительности и эффективного использования ресурсов.

Данная стратегия разработана с учетом специфики работы с большими объемами данных и ориентирована на минимизацию простоев системы при выполнении обслуживающих операций.

Переиндексация

Основная проблема при обслуживании больших баз данных (VLDB) заключается в том, что переиндексация всех таблиц может занимать очень продолжительное время, иногда растягиваясь на несколько дней. Это создает серьезные трудности для обеспечения непрерывности бизнес-процессов и требует специальных подходов к организации данного процесса.

Проблемы стандартной переиндексации

При работе с крупными базами данных стандартные подходы к переиндексации сталкиваются со следующими проблемами:

• Длительное время выполнения: полная переиндексация всех таблиц может занимать дни, что неприемлемо для систем с требованиями высокой доступности.

• Высокая нагрузка на систему: процесс переиндексации потребляет значительные ресурсы сервера, что может негативно сказываться на производительности основных рабочих нагрузок.

• Блокировки: многие операции переиндексации требуют установки блокировок, что может приводить к простоям в работе пользователей.

Оптимизированный подход к переиндексации

Для решения указанных проблем предлагается следующий оптимизированный подход:

Вместо переиндексации всех таблиц необходимо разделить таблицы на 2 группы:

• Горячие таблицы:

  • Высокая частота обращений (запросов) к таблице.

  • Частые изменения данных (INSERT / UPDATE / DELETE).

  • Участие в наиболее важных бизнес-процессах или транзакциях.

  • Критичность для производительности системы.

• Теплые таблицы:

  • Используются умеренно.

  • Могут быть частью промежуточных процессов или отчетности.

  • Не так критичны для ежедневной операционной нагрузки.

Определение категорий таблиц

Существует несколько методов определения частоты использования таблиц:

• Анализ статистики использования индексов

• Использование Extended Events

• Анализ кэша планов запросов

• Применение SQL Default Trace

• Анализ трейсов с помощью SQL Profiler

В нашем случае мы выбрали метод анализа статистики использования индексов, поскольку он менее трудоёмкий и минимально влияет на производительность системы. Важно отметить, что любой сбор аналитических данных создаёт дополнительную нагрузку на сервер. Ключевой вопрос состоит в объёме и характере этой нагрузки, а также её влиянии на общую производительность системы.

SELECT      OBJECT_NAME(ius.object_id) AS TableName,     i.name AS IndexName,     i.type_desc AS IndexType,     ius.user_seeks + ius.user_scans + ius.user_lookups AS TotalUserReads,     ius.user_updates AS TotalUserWrites,     ius.last_user_seek,     ius.last_user_scan,     ius.last_user_lookup FROM      sys.dm_db_index_usage_stats ius INNER JOIN      sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id WHERE      OBJECTPROPERTY(ius.object_id, 'IsMsShipped') = 0 -- исключить системные таблицы     AND ius.database_id = DB_ID() -- проверим только ткущую БД ORDER BY      TotalUserReads DESC;

Маркировка таблиц

После определения горячих таблиц, необходимо отметить их для дальнейшей обработки. В нашем случае решено добавить расширенное свойство «IsHotTable»

Для удобства использования, я оформил код в виде хранимой процедуры

✅ Для организации работы рекомендуется создать отдельную схему в базе данных, в рамках которой будут размещаться все объекты для обслуживания БД, такие как хранимые процедуры или служебные таблицы.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'DBA') BEGIN     EXEC('CREATE SCHEMA DBA');     PRINT 'Schema [DBA] created successfully.'; END ELSE BEGIN     PRINT 'Schema [DBA] already exists.'; END

CREATE PROCEDURE dba.SetTableExtendedProperty     @TableName SYSNAME,     @SchemaName SYSNAME = 'dbo',     @PropertyName SYSNAME,     @PropertyValue NVARCHAR(4000) AS BEGIN     SET NOCOUNT ON;      DECLARE @SQL NVARCHAR(MAX);      -- Проверить наличие параметра     IF EXISTS (         SELECT 1         FROM fn_listextendedproperty(@PropertyName, 'SCHEMA', @SchemaName, 'TABLE', @TableName, NULL, NULL)     )     BEGIN         -- Если имеется,  тогда нужно удалить и пересоздать          SET @SQL = '         EXEC sp_dropextendedproperty              @name = N''' + @PropertyName + ''',              @level0type = N''SCHEMA'',              @level0name = N''' + @SchemaName + ''',              @level1type = N''TABLE'',              @level1name = N''' + @TableName + ''';';          EXEC sp_executesql @SQL;     END      -- создать новый     SET @SQL = '     EXEC sp_addextendedproperty          @name = N''' + @PropertyName + ''',          @value = N''' + REPLACE(@PropertyValue, '''', '''''') + ''',          @level0type = N''SCHEMA'',          @level0name = N''' + @SchemaName + ''',          @level1type = N''TABLE'',          @level1name = N''' + @TableName + ''';';      EXEC sp_executesql @SQL;      PRINT 'Extended property [' + @PropertyName + '] updated for [' + @SchemaName + '].[' + @TableName + ']'; END;

☝🏼Важно помнить, что «горячие таблицы» определяются не только по частоте обращений, но и по их важности в бизнес-процессах. Рекомендуется составить список таких таблиц вручную, после чего произвести их маркировку в базе данных.

Пример маркировки

EXEC dba.SetTableExtendedProperty     @TableName = 'Customers',     @SchemaName = 'dbo',     @PropertyName = 'IsHotTable',     @PropertyValue = '1';

Пример: Как получить список с маркировкой IsHotTable

SELECT t.name   AS TableName,        ep.name  AS PropertyName,        ep.value AS PropertyValue FROM sys.tables t          CROSS APPLY      fn_listextendedproperty(default, 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, NULL, NULL) ep WHERE t.is_ms_shipped = 0   and ep.name = 'IsHotTable'   and ep.value = '1';

Обслуживание горячих таблиц

В рамках нашей стратегии предлагается ежедневно переиндексировать данный вид таблиц, несмотря на уровни фрагментации.

Для сокращения времени переиндексации будем использовать многопоточный подход. Таблицы будут разделены на несколько групп в зависимости от доступных ресурсов системы. Количество групп может быть настроено в соответствии с производительностью сервера.

Описание системы многопоточного подхода

Система реализована с использованием стандартного механизма СУБД — SQL Agent Job, в рамках которого создается главное задание (Master Job), которое отвечает за создание и управление подзаданиями (потоками).

Master Job состоит из 2 шагов:

• Шаг №1 — Создает динамические подзадания в количестве, равном числу потоков.

• Шаг №2 — отслеживает созданные подзадания и удаляет их после выполнения

Дополнительно необходимо разработать подсистему логирования для отслеживания работы механизма многопоточной переиндексации.

Создание подсистемы логирования

Для начала необходимо создать таблицу для хранения логов.

SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [DBA].[ReindexJobLog]( [LogID] [int] IDENTITY(1,1) NOT NULL, [JobName] [nvarchar](128) NULL, [GroupNumber] [int] NULL, [Tablename] [sysname] NOT NULL, [IndexName] [nvarchar](128) NULL, [ActionType] [nvarchar](128) NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [DurationSeconds]  AS (datediff(second,[StartTime],[EndTime])), [DBName] [nvarchar](128) NULL, PRIMARY KEY CLUSTERED  ( [LogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FIVE] ) ON [FIVE] GO  ALTER TABLE [DBA].[ReindexJobLog] ADD  DEFAULT (getdate()) FOR [StartTime] GO   

Для более эффективной организации кода и упрощения структуры задания (SQL Job), создадим отдельную хранимую процедуру для процесса переиндексации.

🚨Не забудьте изменить название базы данных в скрипте

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO  Create PROCEDURE [DBA].[ReindexTableWithLogging] @JobName NVARCHAR(128),                                                 @GroupNumber INT,                         @DBName Nvarchar(128),                                                 @TableName SYSNAME,                                                 @SchemaName SYSNAME = 'dbo' AS BEGIN     SET NOCOUNT ON;     DECLARE @FullTableName NVARCHAR(256) = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);     DECLARE @LogID INT;     DECLARE @indexName NVARCHAR(256);     Declare @sql_updateStats NVARCHAR(max) DECLARE @Batch1_SQL NVARCHAR(MAX);     DECLARE @Batch2_SQL NVARCHAR(MAX);     DECLARE @Batch3_SQL NVARCHAR(MAX);     DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR         SELECT i.name AS IndexName         FROM sys.indexes i         WHERE i.name IS NOT NULL -- Exclude heaps           and object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))     OPEN IndexCursor;     FETCH NEXT FROM IndexCursor INTO @IndexName;     WHILE @@FETCH_STATUS = 0         begin             INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)             VALUES (@JobName, @GroupNumber, @DBName, @FullTableName, @indexName, 'ReBuild Index All indexes', GETDATE());             SET @LogID = SCOPE_IDENTITY();             PRINT 'Starting reindex for group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\Table' + @FullTableName +                   ' Index ' + @indexname + ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             -- Run reindex             EXEC ('DBCC DBREINDEX(''' + @FullTableName + ''', ''' + @indexname + ''', 90);');             -- Update log             UPDATE [DatabaseName].DBA.ReindexJobLog             SET EndTime = GETDATE()             WHERE LogID = @LogID               and Tablename = @FullTableName               and indexname = @indexName;             PRINT 'Finished reindexing group ' + CAST(@GroupNumber AS NVARCHAR(10)) + '\Table' + @FullTableName +                   ' at ' + CONVERT(NVARCHAR, GETDATE(), 120);             FETCH NEXT FROM IndexCursor INTO @IndexName;         END     INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)     VALUES (@JobName, @GroupNumber, @DBName,  @FullTableName, '', 'Update rest of statistics', GETDATE());     SET @LogID = SCOPE_IDENTITY();    if         (SELECT count(1)          FROM sys.stats s                   INNER JOIN sys.objects obj ON s.object_id = obj.object_id                   INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id          WHERE obj.type = 'U' -- Only user tables            AND sch.name = @SchemaName            AND obj.name = @TableName            AND NOT EXISTS (SELECT 1                            FROM sys.indexes i                            WHERE i.object_id = s.object_id                              AND i.index_id = s.stats_id)) < 20         Begin              SELECT @sql_updateStats = STRING_AGG(                     'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                         + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;',                     CHAR(13)                                       )             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             IF @sql_updateStats IS NOT NULL                 EXEC sp_executesql @sql_updateStats;         end     else         Begin             IF OBJECT_ID('tempdb..#StatsWithBatch') IS NOT NULL                 DROP TABLE #StatsWithBatch;              CREATE TABLE #StatsWithBatch             (                 UpdateCommand NVARCHAR(MAX),                 BatchNumber   INT             )             INSERT INTO #StatsWithBatch (UpdateCommand, BatchNumber)             SELECT 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                        + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;' AS UpdateCommand,                    NTILE(3) OVER (ORDER BY s.stats_id) AS BatchNumber -- Divide work into 2 batches             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             SELECT @Batch1_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 1;              IF (@Batch1_SQL IS NOT NULL AND LEN(@Batch1_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 1 ---';                     PRINT @Batch1_SQL;                     EXEC sp_executesql @Batch1_SQL;                     PRINT '--- BATCH 1 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 1: No commands to execute. ---';                 END              SELECT @Batch2_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 2;              IF (@Batch2_SQL IS NOT NULL AND LEN(@Batch2_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch2_SQL;                     exec sp_executesql @Batch2_SQL;                     PRINT '--- BATCH 2 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 2: No commands to execute. ---';                 END             SELECT @Batch3_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 3;              IF (@Batch3_SQL IS NOT NULL AND LEN(@Batch3_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch3_SQL;                     exec sp_executesql @Batch3_SQL;                     PRINT '--- BATCH 3 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 3: No commands to execute. ---';                 END  DROP TABLE #StatsWithBatch;         END          UPDATE [DatabaseName].DBA.ReindexJobLog     SET EndTime = GETDATE()     WHERE LogID = @LogID       and Tablename = @FullTableName END 

В этой хранимой процедуре мы не только пересоздаем индексы, но и обновляем все статистики таблиц, автоматически созданные сервером СУБД.

Шаг №1 — Создает динамические подзадания в количестве, равном числу потоков.

🚨Не забудьте изменить название базы данных в скрипте

Create PROCEDURE [DBA].[HotTablesReindexingJobCreating] @DBName Nvarchar(125), @TotalGroups int  AS BEGIN -- Cоздать список горячих таблиц  IF OBJECT_ID('tempdb..#HotTables') IS NOT NULL DROP TABLE #HotTables; SELECT      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum,     QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS FullTableName,     t.name AS TableName,     SCHEMA_NAME(t.schema_id) AS SchemaName INTO #HotTables FROM sys.tables t CROSS APPLY fn_listextendedproperty('IsHotTable', 'SCHEMA', SCHEMA_NAME(t.schema_id), 'TABLE', t.name, NULL, NULL) ep WHERE ep.value = '1'   AND t.is_ms_shipped = 0;   IF NOT EXISTS (SELECT * FROM #HotTables) BEGIN     PRINT 'No hot tables found.';     RETURN; END  -- Определить сколько потоков DECLARE @i INT = 1;  WHILE @i <= @TotalGroups BEGIN     DECLARE @JobName NVARCHAR(128) = @DBName + N'_Reindex_HotTables_Group_' + CAST(@i AS NVARCHAR(5));     DECLARE @SqlCommand NVARCHAR(MAX);     -- Создание динамических заданий     SET @SqlCommand = '';     SELECT @SqlCommand = @SqlCommand +          'EXEC [DatabaseName].dba.ReindexTableWithLogging @JobName = ''' + @JobName + ''', @GroupNumber = ' + CAST(@i AS NVARCHAR(10)) +', @DBName = ''' + @DBName + ''' , @TableName = ''' + t.TableName + ''', @SchemaName = ''' +t.SchemaName + ''';' + CHAR(13)+CHAR(10)     FROM #HotTables t     WHERE t.RowNum % @TotalGroups = @i - 1;           IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)         EXEC msdb.dbo.sp_delete_job @job_name = @JobName;           EXEC msdb.dbo.sp_add_job         @job_name = @JobName,         @enabled = 1,         @description = 'Auto-generated reindex job using stored procedure',         @category_name = 'Database Maintenance';           EXEC msdb.dbo.sp_add_jobstep         @job_name = @JobName,         @step_name = N'Reindex Tables',         @subsystem = N'TSQL', @database_name = @DBName,         @command = @SqlCommand,         @retry_attempts = 0,         @retry_interval = 0;         EXEC msdb.dbo.sp_add_jobschedule         @job_name = @JobName,         @name = 'RunOnceNow',         @freq_type = 1,         @active_start_time = 0;     EXEC msdb.dbo.sp_add_jobserver         @job_name = @JobName,         @server_name = N'(local)';     -- запуск     EXEC msdb.dbo.sp_start_job @job_name = @JobName;     SET @i = @i + 1; END END 

Шаг №2 — отслеживает созданные подзадания и удаляет их после выполнения

Create proc [DBA].[CleaningReindexingJob] @DBName Nvarchar(128) as begin  DECLARE @JobName NVARCHAR(128); DECLARE @i INT; DECLARE @IsRunning BIT; DECLARE @WaitInterval CHAR(8) = '00:30:00'; -- 30-minute wait interval   WAITFOR DELAY @WaitInterval; ---------------------------------------------------------------------- -- Section 1: Process the 'Hot' table jobs (1 to 3) ---------------------------------------------------------------------- PRINT '--- Checking HOT Table Jobs ---'; SET @i = 1;  WHILE @i <= 3 BEGIN     SET @JobName = @DBName + '_Reindex_HotTables_Group_' + CAST(@i AS NVARCHAR(5));      IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName)     BEGIN         PRINT 'Checking job: ' + @JobName;         SET @IsRunning = 1;          WHILE @IsRunning = 1         BEGIN             SELECT @IsRunning = COUNT(*)             FROM msdb.dbo.sysjobactivity ja             JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id             WHERE j.name = @JobName AND ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL;              IF @IsRunning = 1             BEGIN                 PRINT 'Job ' + @JobName + ' is still running. Waiting for 30 minutes...';                 WAITFOR DELAY @WaitInterval;             END         END          EXEC msdb.dbo.sp_delete_job @job_name = @JobName;         PRINT '✅ Job deleted: ' + @JobName;     END     ELSE     BEGIN         PRINT '❌ Job does not exist: ' + @JobName;     END      SET @i = @i + 1; END PRINT '--- Finished checking HOT Table Jobs ---'; PRINT ''; -- Add a blank line for readability  ---------------------------------------------------------------------- -- Section 2: Process the 'Warm' table jobs (1 to 10) ---------------------------------------------------------------------- PRINT '--- Checking WARM Table Jobs ---'; SET @i = 1; -- Important: Reset the counter to 1  WHILE @i <= 10 BEGIN     SET @JobName = @DBName + '_Reindex_WarmTables_Group_' + CAST(@i AS NVARCHAR(5));      IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName)     BEGIN         PRINT 'Checking job: ' + @JobName;         SET @IsRunning = 1;          WHILE @IsRunning = 1         BEGIN             SELECT @IsRunning = COUNT(*)             FROM msdb.dbo.sysjobactivity ja             JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id             WHERE j.name = @JobName AND ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL;              IF @IsRunning = 1             BEGIN                 PRINT 'Job ' + @JobName + ' is still running. Waiting for 30 minutes...';                 WAITFOR DELAY @WaitInterval;             END         END          EXEC msdb.dbo.sp_delete_job @job_name = @JobName;         PRINT '✅ Job deleted: ' + @JobName;     END     ELSE     BEGIN         PRINT '❌ Job does not exist: ' + @JobName;     END      SET @i = @i + 1; END PRINT '--- Finished checking WARM Table Jobs ---'; ENd GO 

Обслуживание теплых таблиц

Для теплых таблиц предусмотрена ежедневная переиндексация на основе уровня фрагментации:

Дополнительно планируется еженедельное перестроение (Rebuild) индексов вне зависимости от уровня фрагментации.

Для более чистой организации кода и поддержания высокого уровня документации предлагается создать отдельную хранимую процедуру для обработки переиндексации теплых таблиц.

🚨Не забудьте изменить название базы данных в скрипте

SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE PROCEDURE [DBA].[ReindexTableWithStats] @DBName Nvarchar(128),                                               @SchemaName SYSNAME,                                               @TableName SYSNAME,                                               @JobName NVARCHAR(128),                                               @GroupNumber INT AS BEGIN     SET NOCOUNT ON;     DECLARE @IndexName SYSNAME;     DECLARE @IndexType NVARCHAR(150);     DECLARE @Fragmentation FLOAT;     DECLARE @SQL NVARCHAR(MAX);     DECLARE @Action NVARCHAR(50);     Declare @sql_updateStats NVARCHAR(MAX);     DECLARE @Batch1_SQL NVARCHAR(MAX);     DECLARE @Batch2_SQL NVARCHAR(MAX);     DECLARE @Batch3_SQL NVARCHAR(MAX);     -- Cursor to loop through indexes of the table     DECLARE IndexCursor CURSOR LOCAL FAST_FORWARD FOR         SELECT i.name AS IndexName,                ips.index_type_desc,                ips.avg_fragmentation_in_percent         FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)),                                             NULL, NULL, 'LIMITED') ips                  JOIN              sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id         WHERE i.name IS NOT NULL -- Exclude heaps           and ips.alloc_unit_type_desc = 'IN_ROW_DATA';      OPEN IndexCursor;     FETCH NEXT FROM IndexCursor INTO @IndexName, @IndexType, @Fragmentation;      WHILE @@FETCH_STATUS = 0         BEGIN             -- Determine action based on fragmentation             IF @Fragmentation < 5                 BEGIN                     SET @Action = 'Update Statistics Only';                     SET @SQL = 'UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' +                                QUOTENAME(@IndexName) + ' with FullScan;';                 END             ELSE                 IF @Fragmentation BETWEEN 5 AND 30                     BEGIN                         SET @Action = 'Reorganize + Update Statistics';                         SET @SQL = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' +                                    QUOTENAME(@TableName) + ' REORGANIZE;                         UPDATE STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' +                                    QUOTENAME(@IndexName) + ' with FullScan;';                     END                 ELSE                     BEGIN                         SET @Action = 'Rebuild';                         -- Use DBCC DBREINDEX instead of ALTER INDEX                         SET @SQL = 'DBCC DBREINDEX(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ''', ' +                                    QUOTENAME(@IndexName) + ', 90);';                     END              -- Log start of action             INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, TableName, IndexName, ActionType,                                                    StartTime)             VALUES (@JobName, @GroupNumber, @DBName, QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName), @IndexName,                     @Action, GETDATE());              DECLARE @LogID INT = SCOPE_IDENTITY();              PRINT 'Starting: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;              BEGIN TRY                 EXEC sp_executesql @SQL;                 PRINT 'Finished: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;                  -- Log end time                 UPDATE [DatabaseName].dba.ReindexJobLog                 SET EndTime = GETDATE()                 WHERE LogID = @LogID;             END TRY             BEGIN CATCH                 PRINT 'Error occurred during: ' + @Action + ' on ' + @SchemaName + '.' + @TableName + '.' + @IndexName;                 PRINT ERROR_MESSAGE();                  UPDATE [DatabaseName].dba.ReindexJobLog                 SET EndTime = GETDATE()                 WHERE LogID = @LogID;             END CATCH              FETCH NEXT FROM IndexCursor INTO @IndexName, @IndexType, @Fragmentation;         END     CLOSE IndexCursor;     DEALLOCATE IndexCursor;      --Update the rest of statistics     INSERT INTO [DatabaseName].dba.ReindexJobLog (JobName, GroupNumber, DBName, Tablename, Indexname, ActionType, StartTime)     VALUES (@JobName, @GroupNumber, @DBName, QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName), '',             'Update rest of statistics', GETDATE());     SET @LogID = SCOPE_IDENTITY();     if         (SELECT count(1)          FROM sys.stats s                   INNER JOIN sys.objects obj ON s.object_id = obj.object_id                   INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id          WHERE obj.type = 'U' -- Only user tables            AND sch.name = @SchemaName            AND obj.name = @TableName            AND NOT EXISTS (SELECT 1                            FROM sys.indexes i                            WHERE i.object_id = s.object_id                              AND i.index_id = s.stats_id)) < 20         Begin              SELECT @sql_updateStats = STRING_AGG(                     'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                         + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;',                     CHAR(13)                                       )             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             IF @sql_updateStats IS NOT NULL                 EXEC sp_executesql @sql_updateStats;         end     else         Begin             IF OBJECT_ID('tempdb..#StatsWithBatch') IS NOT NULL                 DROP TABLE #StatsWithBatch;              CREATE TABLE #StatsWithBatch             (                 UpdateCommand NVARCHAR(MAX),                 BatchNumber   INT             )             INSERT INTO #StatsWithBatch (UpdateCommand, BatchNumber)             SELECT 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name)                        + ' ' + QUOTENAME(s.name) + ' WITH FULLSCAN;' AS UpdateCommand,                    NTILE(3) OVER (ORDER BY s.stats_id) AS BatchNumber -- Divide work into 2 batches             FROM sys.stats s                      INNER JOIN sys.objects obj ON s.object_id = obj.object_id                      INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id             WHERE obj.type = 'U' -- Only user tables               AND sch.name = @SchemaName               AND obj.name = @TableName               AND NOT EXISTS (SELECT 1                               FROM sys.indexes i                               WHERE i.object_id = s.object_id                                 AND i.index_id = s.stats_id);             SELECT @Batch1_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 1;              IF (@Batch1_SQL IS NOT NULL AND LEN(@Batch1_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 1 ---';                     PRINT @Batch1_SQL;                     EXEC sp_executesql @Batch1_SQL;                     PRINT '--- BATCH 1 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 1: No commands to execute. ---';                 END              SELECT @Batch2_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 2;              IF (@Batch2_SQL IS NOT NULL AND LEN(@Batch2_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch2_SQL;                     exec sp_executesql @Batch2_SQL;                     PRINT '--- BATCH 2 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 2: No commands to execute. ---';                 END             SELECT @Batch3_SQL = STRING_AGG(CAST(UpdateCommand AS NVARCHAR(MAX)), CHAR(13))             FROM #StatsWithBatch             WHERE BatchNumber = 3;              IF (@Batch3_SQL IS NOT NULL AND LEN(@Batch3_SQL) > 0)                 BEGIN                     PRINT '--- EXECUTING BATCH 2 ---';                     PRINT @Batch3_SQL;                     exec sp_executesql @Batch3_SQL;                     PRINT '--- BATCH 3 COMPLETE ---';                 END             ELSE                 BEGIN                     PRINT '--- BATCH 3: No commands to execute. ---';                 END DROP TABLE #StatsWithBatch;         END           UPDATE [DatabaseName].DBA.ReindexJobLog     SET EndTime = GETDATE()     WHERE LogID = @LogID       and Tablename = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) END GO

Аналогичным образом будет организовано обслуживание теплых таблиц. Их переиндексация будет выполняться в многопоточном режиме для оптимизации производительности.

Хранимая процедура для создания задания по запуску переиндексации теплых таблиц

Create PROCEDURE [DBA].[WarmTablesReindexingJobCreating] @DBName Nvarchar(125), @TotalGroups int AS BEGIN -- Cоздать список таблиц  IF OBJECT_ID('tempdb..#warmTables') IS NOT NULL DROP TABLE #warmTables; SELECT      ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum,     QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS FullTableName,     t.name AS TableName,     SCHEMA_NAME(t.schema_id) AS SchemaName INTO #warmTables FROM sys.tables t inner join    sys.schemas s ON t.schema_id = s.schema_id WHERE      NOT EXISTS (         SELECT 1         FROM fn_listextendedproperty('IsHotTable', 'SCHEMA', s.name, 'TABLE', t.name, NULL, NULL) ep         WHERE ep.value = '1'     ) and t.name not like 'X_%'   IF NOT EXISTS (SELECT * FROM #warmTables) BEGIN     PRINT 'No hot tables found.';     RETURN; END  -- Определить сколько потоков  DECLARE @i INT = 1;   WHILE @i <= @TotalGroups BEGIN     DECLARE @JobName NVARCHAR(128) = @DBName +N'_Reindex_WarmTables_Group_' + CAST(@i AS NVARCHAR(5));     DECLARE @SqlCommand NVARCHAR(MAX);      -- Создание динамических заданий     SET @SqlCommand = '';       SELECT @SqlCommand = @SqlCommand +          'EXEC [DatabaseName].dba.ReindexTableWithStats @JobName = ''' + @JobName + ''', @GroupNumber = ' + CAST(@i AS NVARCHAR(10)) + ', @DBName = ''' + @DBName + '''' + ', @TableName = ''' + t.TableName + ''', @SchemaName = ''' +t.SchemaName + ''';' + CHAR(13)+CHAR(10)     FROM #warmTables t     WHERE t.RowNum % @TotalGroups = @i - 1;           IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)         EXEC msdb.dbo.sp_delete_job @job_name = @JobName;           EXEC msdb.dbo.sp_add_job         @job_name = @JobName,         @enabled = 1,         @description = 'Auto-generated reindex job using stored procedure',         @category_name = 'Database Maintenance';           EXEC msdb.dbo.sp_add_jobstep         @job_name = @JobName,         @step_name = N'Reindex Tables',         @subsystem = N'TSQL', @database_name = @DBName,         @command = @SqlCommand,         @retry_attempts = 0,         @retry_interval = 0;           EXEC msdb.dbo.sp_add_jobschedule         @job_name = @JobName,         @name = 'RunOnceNow',         @freq_type = 1,         @active_start_time = 0;           EXEC msdb.dbo.sp_add_jobserver         @job_name = @JobName,         @server_name = N'(local)';      -- запуск    EXEC msdb.dbo.sp_start_job @job_name = @JobName;      SET @i = @i + 1; END END 

🚧 Было бы хорошо, если бы имелась возможность еженедельно переиндексировать все таблицы с помощью ХП ReindexTableWithLogging

Для упрощения работы администраторов СУБД, я объединил всю систему в единый скрипт, который автоматически создает задание в SQL Agent Job

USE [msdb] GO  /****** Object:  Job [Smart_Reindex_]    Script Date: 20.08.2025 17:23:15 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 20.08.2025 17:23:16 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback  END  DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Smart_Reindex_',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=0,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'No description available.',  @category_name=N'[Uncategorized (Local)]',  @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [HotTable reindex]    Script Date: 20.08.2025 17:23:16 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'HotTable reindex',  @step_id=1,  @cmdexec_success_code=0,  @on_success_action=3,  @on_success_step_id=0,  @on_fail_action=2,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.HotTablesReindexingJobCreating @DBName = ''[DatabaseName]'', @TotalGroups = 5',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [WarmTables]    Script Date: 20.08.2025 17:23:16 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'WarmTables',  @step_id=2,  @cmdexec_success_code=0,  @on_success_action=3,  @on_success_step_id=0,  @on_fail_action=3,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.WarmTablesReindexingJobCreating @DBName = ''[DatabaseName]'', @TotalGroups = 15',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [Clear_after_reindexing]    Script Date: 20.08.2025 17:23:16 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Clear_after_reindexing',  @step_id=3,  @cmdexec_success_code=0,  @on_success_action=1,  @on_success_step_id=0,  @on_fail_action=2,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'Exec [DatabaseName].DBA.CleaningReindexingJob @DBName = ''[DatabaseName]''',  @database_name=N'[DatabaseName]',  @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly_mon_Fri_18-00',  @enabled=1,  @freq_type=8,  @freq_interval=63,  @freq_subday_type=1,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=20250718,  @active_end_date=99991231,  @active_start_time=201000,  @active_end_time=235959,  @schedule_uid=N'b8daac26-30d1-4343-b6a8-3814669bdf83' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO