MS SQL Server дело тонкое...: Адаптивный мониторинг актуальности статистики в SQL Server

Adaptive Statistics Monitoring in SQL Server

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

Проблема стандартного порога

SQL Server использует динамический порог для автоматического обновления статистики. Для таблиц с более чем 500 строками порог вычисляется по формуле SQRT(1000 * rows).

Документация Microsoft описывает работу автоматического обновления статистики следующим образом: для таблиц с более чем 500 строками условием для срабатывания является изменение количества строк более чем на 500 + 20% от числа строк на момент последнего обновления статистики.

Для небольших и средних таблиц этот подход работает хорошо. Однако для очень больших таблиц он даёт чрезмерно высокий порог:

Для таблицы с 1 млн строк: порог ≈ 31 622 изменения.
Для таблицы с 100 млн строк: порог ≈ 316 227 изменений.
Для таблицы с 900 млн строк: порог ≈ 951 228 изменений.

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

Адаптивный порог

Чтобы решить эту проблему, мы предлагаем использовать адаптивный порог, который зависит от размера таблицы. Вместо единой формулы SQRT(1000 * rows) мы применяем разные пороги для разных диапазонов размера таблицы:

CASE 
    WHEN stats_properties.rows <= 500 THEN 500
    WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
    WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
    WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
    ELSE stats_properties.rows * 0.001
END

Эта шкала позволяет:

Для таблиц до 1 млн строк использовать стандартный порог SQL Server.
Для таблиц от 1 до 10 млн строк — 0.5% от количества строк.
Для таблиц от 10 до 100 млн строк — 0.2% от количества строк.
Для таблиц свыше 100 млн строк — 0.1% от количества строк.

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

Скрипт для мониторинга

Ниже представлен полный скрипт, который выявляет статистики, требующие обновления, используя адаптивный порог. Скрипт использует динамическое административное представление sys.dm_db_stats_properties, которое возвращает информацию о количестве изменений (modification_counter) и количестве строк (rows) для каждого объекта статистики [citation:1][citation:6].

SELECT DISTINCT
    SCHEMA_NAME(database_objects.schema_id) AS schema_name,
    database_objects.name AS table_name,
    statistics_object.name AS stats_name,
    stats_properties.modification_counter,
    stats_properties.rows,
    CASE 
        WHEN stats_properties.rows <= 500 THEN 500
        WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
        WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
        WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
        ELSE stats_properties.rows * 0.001
    END AS adaptive_threshold,
    stats_properties.modification_counter - 
    CASE 
        WHEN stats_properties.rows <= 500 THEN 500
        WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
        WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
        WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
        ELSE stats_properties.rows * 0.001
    END AS threshold_exceeded_by
FROM sys.stats AS statistics_object
JOIN sys.objects AS database_objects 
    ON database_objects.object_id = statistics_object.object_id
OUTER APPLY sys.dm_db_stats_properties(statistics_object.object_id, statistics_object.stats_id) AS stats_properties
WHERE database_objects.type IN ('IT', 'U', 'V')
    AND stats_properties.modification_counter >= CASE 
        WHEN stats_properties.rows <= 500 THEN 500
        WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
        WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
        WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
        ELSE stats_properties.rows * 0.001
    END
    AND INDEXPROPERTY(statistics_object.object_id, statistics_object.name, 'IsDisabled') = 0
ORDER BY stats_properties.rows DESC;

Этот скрипт можно использовать как для разового анализа, так и для регулярного мониторинга.

Автоматическое обновление проблемных статистик

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

Ключевые особенности скрипта:

Адаптивный SAMPLE: процент выборки зависит от размера таблицы — от 100% (FULLSCAN) для микро-таблиц до 5% для таблиц с более чем 100 млн строк.
Адаптивный MAXDOP: для маленьких таблиц используется однопоточный режим (MAXDOP = 1), для средних — умеренный параллелизм, для больших — до 8 потоков.
SET LOCK_TIMEOUT 0: обновление не ждёт блокировок и завершается с ошибкой, если таблица заблокирована (это позволяет избежать длительных ожиданий).
Исключение критических таблиц: таблицы с более чем 100 млн строк исключаются из массового обновления и обрабатываются отдельными джобами со своим расписанием (см. раздел ниже).

Параметр MAXDOP для оператора UPDATE STATISTICS доступен начиная с SQL Server 2016 SP2 и SQL Server 2017 CU3. Он позволяет переопределить серверную настройку степени параллелизма для конкретной операции обновления статистики.

DECLARE @sql_command NVARCHAR(2000), 
        @current_table_name SYSNAME, 
        @current_stats_name SYSNAME, 
        @current_schema_name SYSNAME,
        @table_row_count BIGINT,
        @sample_percent INT,
        @max_degree_of_parallelism TINYINT,
        @retry_count INT

DECLARE statistics_cursor CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT DISTINCT
    SCHEMA_NAME(database_objects.schema_id) AS schema_name,
    database_objects.name AS table_name,
    statistics_object.name AS stats_name,
    stats_properties.rows
FROM sys.stats AS statistics_object
JOIN sys.objects AS database_objects 
    ON database_objects.object_id = statistics_object.object_id
OUTER APPLY sys.dm_db_stats_properties(statistics_object.object_id, statistics_object.stats_id) AS stats_properties
WHERE database_objects.type IN ('IT', 'U', 'V')
    AND stats_properties.modification_counter >= CASE 
        WHEN stats_properties.rows <= 500 THEN 500
        WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
        WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
        WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
        ELSE stats_properties.rows * 0.001
    END
    AND INDEXPROPERTY(statistics_object.object_id, statistics_object.name, 'IsDisabled') = 0
    AND stats_properties.rows < 100000000
    AND stats_properties.rows IS NOT NULL
    AND stats_properties.rows > 0

OPEN statistics_cursor

WHILE 1 = 1
BEGIN
    BEGIN TRY
        FETCH statistics_cursor INTO @current_schema_name, @current_table_name, @current_stats_name, @table_row_count
        
        IF @@fetch_status <> 0 BREAK
        
        -- Адаптивный процент выборки
        SET @sample_percent = CASE 
            WHEN @table_row_count < 1000 THEN 100
            WHEN @table_row_count < 10000 THEN 75
            WHEN @table_row_count < 100000 THEN 50
            WHEN @table_row_count < 1000000 THEN 30
            WHEN @table_row_count < 5000000 THEN 20
            WHEN @table_row_count < 10000000 THEN 15
            WHEN @table_row_count < 50000000 THEN 10
            ELSE 7
        END
        
        -- Адаптивный MAXDOP (максимум 8)
        SET @max_degree_of_parallelism = CASE 
            WHEN @table_row_count < 10000 THEN 1
            WHEN @table_row_count < 100000 THEN 2
            WHEN @table_row_count < 1000000 THEN 4
            WHEN @table_row_count < 10000000 THEN 6
            ELSE 8
        END
        
        -- Формируем команду без SET LOCK_TIMEOUT
        IF @sample_percent = 100
            SELECT @sql_command = 'UPDATE STATISTICS [' + @current_schema_name + '].[' + @current_table_name + '] [' + @current_stats_name + 
                          '] WITH FULLSCAN' + 
                          CASE WHEN @max_degree_of_parallelism > 1 THEN ', MAXDOP = ' + CAST(@max_degree_of_parallelism AS VARCHAR(2)) ELSE '' END
        ELSE
            SELECT @sql_command = 'UPDATE STATISTICS [' + @current_schema_name + '].[' + @current_table_name + '] [' + @current_stats_name + 
                          '] WITH SAMPLE ' + CAST(@sample_percent AS VARCHAR(3)) + ' PERCENT' + 
                          CASE WHEN @max_degree_of_parallelism > 1 THEN ', MAXDOP = ' + CAST(@max_degree_of_parallelism AS VARCHAR(2)) ELSE '' END
        
        PRINT @sql_command
        
        -- Попытка выполнить с таймаутом через SET LOCK_TIMEOUT
        -- Используем отдельный блок, чтобы ошибка не закрывала курсор
        BEGIN TRY
            SET LOCK_TIMEOUT 5000  -- 5 секунд ожидания
            EXECUTE sp_executesql @sql_command
            PRINT '  OK: ' + @current_stats_name
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() = 1222
                PRINT '  SKIPPED (lock timeout): ' + @current_stats_name
            ELSE
                PRINT '  ERROR: ' + @current_stats_name + ' - ' + ERROR_MESSAGE()
        END CATCH
        
        -- Сбрасываем LOCK_TIMEOUT в бесконечное ожидание
        SET LOCK_TIMEOUT -1
        
    END TRY
    BEGIN CATCH
        -- Если произошла ошибка курсора, пробуем переоткрыть
        IF ERROR_NUMBER() = 16917
        BEGIN
            PRINT 'Cursor error - attempting to reopen...'
            -- Закрываем старый курсор если он открыт
            IF CURSOR_STATUS('global','statistics_cursor') >= 0
            BEGIN
                CLOSE statistics_cursor
                DEALLOCATE statistics_cursor
            END
            
            -- Пересоздаем и открываем курсор заново
            DECLARE statistics_cursor CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
            SELECT DISTINCT
                SCHEMA_NAME(database_objects.schema_id) AS schema_name,
                database_objects.name AS table_name,
                statistics_object.name AS stats_name,
                stats_properties.rows
            FROM sys.stats AS statistics_object
            JOIN sys.objects AS database_objects 
                ON database_objects.object_id = statistics_object.object_id
            OUTER APPLY sys.dm_db_stats_properties(statistics_object.object_id, statistics_object.stats_id) AS stats_properties
            WHERE database_objects.type IN ('IT', 'U', 'V')
                AND stats_properties.modification_counter >= CASE 
                    WHEN stats_properties.rows <= 500 THEN 500
                    WHEN stats_properties.rows < 1000000 THEN SQRT(CAST(1000 AS BIGINT) * stats_properties.rows)
                    WHEN stats_properties.rows < 10000000 THEN stats_properties.rows * 0.005
                    WHEN stats_properties.rows < 100000000 THEN stats_properties.rows * 0.002
                    ELSE stats_properties.rows * 0.001
                END
                AND INDEXPROPERTY(statistics_object.object_id, statistics_object.name, 'IsDisabled') = 0
                AND stats_properties.rows < 100000000
                AND stats_properties.rows IS NOT NULL
                AND stats_properties.rows > 0
            
            OPEN statistics_cursor
            
            PRINT 'Cursor reopened successfully'
            -- Продолжаем выполнение
            CONTINUE
        END
        ELSE
        BEGIN
            PRINT 'Unexpected error in main loop: ' + ERROR_MESSAGE()
            BREAK
        END
    END CATCH
END

-- Закрытие курсора с проверкой состояния
IF CURSOR_STATUS('global','statistics_cursor') >= 0
BEGIN
    CLOSE statistics_cursor
    DEALLOCATE statistics_cursor
END

Масштабирование: несколько джобов для большого числа таблиц

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

Основные преимущества такого подхода:

Снижение нагрузки на сервер: каждый джоб работает только с определённой группой таблиц, что уменьшает конкуренцию за ресурсы.
Управляемость: можно настроить разную частоту выполнения для разных групп таблиц (например, критичные таблицы обновлять чаще).
Предсказуемость времени выполнения: каждый джоб завершается быстрее, чем один большой.
Параллельное выполнение: если сервер позволяет, можно запускать несколько джобов одновременно (на разных группах таблиц).
Гибкость расписания: таблицы с высокой интенсивностью изменений можно обновлять чаще, а стабильные таблицы — реже.

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

Способ 1: исключение таблиц по размеру (рекомендуемый)

Создаётся основной джоб, который обрабатывает все таблицы до 100 млн строк. Таблицы свыше этого порога исключаются и обрабатываются отдельными специализированными джобами.

-- Основной джоб: таблицы до 100 млн строк
AND stats_properties.rows < 100000000

-- Отдельный джоб для таблиц от 100 до 500 млн строк
AND stats_properties.rows BETWEEN 100000000 AND 500000000

-- Отдельный джоб для таблиц свыше 500 млн строк
AND stats_properties.rows > 500000000

Способ 2: исключение по списку имён (для специализированных джобов)

Создаётся основной джоб, который обрабатывает большинство таблиц, но исключает те, которые обслуживаются отдельно. Это удобно, когда нужно вынести конкретные таблицы в отдельное расписание.

-- Пример: основной джоб, который не трогает указанные таблицы
AND database_objects.name NOT IN ('CRITICAL_TABLE_1','CRITICAL_TABLE_2','CRITICAL_TABLE_3')

-- Специализированный джоб только для этих таблиц
AND database_objects.name IN ('CRITICAL_TABLE_1','CRITICAL_TABLE_2','CRITICAL_TABLE_3')

Способ 3: ограничение выборки списком таблиц (для нескольких джобов)

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

-- Джоб 1: таблицы группы A
AND database_objects.name IN ('TABLE_1','TABLE_2','TABLE_3')

-- Джоб 2: таблицы группы B
AND database_objects.name IN ('TABLE_4','TABLE_5','TABLE_6')

-- Джоб 3: таблицы группы C
AND database_objects.name IN ('TABLE_7','TABLE_8','TABLE_9')

Рекомендации по стратегии разделения:

По размеру: таблицы до 100 млн строк — в основной джоб, от 100 до 500 млн — в отдельный джоб с 5% выборки, свыше 500 млн — в отдельный джоб с 3% выборки.
По частоте изменений: таблицы с высокой интенсивностью DML — обновлять чаще (например, каждый час), таблицы с редкими изменениями — реже (раз в сутки).
По времени выполнения: распределить таблицы по джобам так, чтобы каждый джоб выполнялся не более 30–60 минут.
По функциональному признаку: можно выделить отдельные джобы для разных бизнес-процессов, чтобы обновление статистики не влияло на критичные операции.

Особенности для секционированных таблиц

Важно отметить, что представленное решение не учитывает секционирование таблиц. Оно работает на уровне всей таблицы, используя общее количество строк и общее количество изменений. Это означает, что:

Статистика анализируется и обновляется для всей таблицы целиком, а не для отдельных секций.
Порог изменения рассчитывается на основе общего количества строк во всех секциях.
Если изменения происходят неравномерно по секциям, статистика для активно изменяемых секций может устаревать быстрее, чем показывает общий счётчик изменений.

Однако алгоритм может быть адаптирован для секционированных таблиц несколькими способами.

В SQL Server существует механизм инкрементальной статистики, который позволяет поддерживать статистику на уровне секций. Согласно документации Microsoft, инкрементальная статистика создаётся на основе секционированной таблицы и позволяет обслуживать статистику на уровне секций вместо уровня таблицы. Это даёт значительное преимущество: вместо сканирования всей таблицы можно сканировать только нужные секции, что значительно сокращает время выполнения операций обслуживания.

Управление параллелизмом и Resource Governor

При использовании параметра MAXDOP важно понимать иерархию его применения. Согласно документации Microsoft, действует следующий порядок приоритетов:

Хинт запроса MAXDOP переопределяет как серверную настройку (sp_configure), так и настройку на уровне базы данных.
Однако хинт может быть ограничен сверху настройкой MAX_DOP в группе рабочей нагрузки Resource Governor.

Как указано в документации: "Если хинт запроса установлен в ноль (0), он переопределяется настройкой Resource Governor. Если хинт запроса не равен нулю, он ограничивается настройкой Resource Governor" .

Также в описании параметра MAXDOP для оператора UPDATE STATISTICS указано: "Итоговая степень параллелизма ограничивается параметром группы рабочей нагрузки MAX_DOP, если используется Resource Governor" .

Это означает, что даже если в скрипте указан MAXDOP = 8, но в Resource Governor для вашей группы рабочей нагрузки установлено MAX_DOP = 4, обновление статистики будет выполняться с 4 потоками. Данное поведение гарантирует, что администратор может централизованно контролировать использование ресурсов, и его нельзя обойти хинтами на уровне запроса.

Заключение

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

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

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

Это решение уже успешно применяется в production-среде с таблицами, содержащими сотни миллионов строк, и показывает отличные результаты.

MS SQL Server дело тонкое...

14.8.26

Адаптивный мониторинг актуальности статистики в SQL Server

Проблема стандартного порога

Адаптивный порог

Скрипт для мониторинга

Автоматическое обновление проблемных статистик

Масштабирование: несколько джобов для большого числа таблиц

Способ 1: исключение таблиц по размеру (рекомендуемый)

Способ 2: исключение по списку имён (для специализированных джобов)

Способ 3: ограничение выборки списком таблиц (для нескольких джобов)

Рекомендации по обновлению сверхбольших таблиц

Особенности для секционированных таблиц

Управление параллелизмом и Resource Governor

Заключение

Комментариев нет:

Отправить комментарий

Архив блога

Ярлыки

www.sql-ex.ru