Данный документ представляет собой стратегию по переиндексации таблиц в крупных базах данных (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.';
ENDCREATE 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
Обслуживание теплых таблиц
Для теплых таблиц предусмотрена ежедневная переиндексация на основе уровня фрагментации:
Уровень фрагментации |
Действие |
|---|---|
< 5% |
Обновление статистики |
5% - 30% |
Реорганизация (Reorganize) индексов |
≥ 30% |
Перестроение (Rebuild) индексов |
Дополнительно планируется еженедельное перестроение (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