MS SQL Server дело тонкое...: Почему одни статистики остаются устаревшими, в то время как другие обновляются автоматически

Автор: Jose Manuel Jurado (MICROSOFT), Lessons Learned #547:Some SQL DB Statistics Remain Outdated While Others Are Automatically Updated;

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

Более старую дату last_updated.
Высокое значение modification_counter.
Количество строк, значительно меньшее, чем текущее количество строк в таблице.

На первый взгляд это могло бы указывать на то, что AUTO_UPDATE_STATISTICS работает некорректно. Однако более детальный анализ показал, что такой шаблон может полностью соответствовать ожидаемому поведению SQL Server.

1. Типы статистик в SQL Server

SQL Server может поддерживать несколько типов статистик.

Автоматически создаваемые статистики столбцов

Когда включена опция AUTO_CREATE_STATISTICS, SQL Server может автоматически создавать статистику для одного столбца, когда оптимизатору запросов требуется информация о кардинальности для столбца, используемого в предикате запроса. Такие статистики обычно имеют имена вида: _WA_Sys_00000002_47A6D5E5

Имя можно интерпретировать как: _WA_Sys_<column_id в шестнадцатеричном виде>_<object_id в шестнадцатеричном виде>

Например: 00000002 в шестнадцатеричном виде = column_id 2; 47A6D5E5 в шестнадцатеричном виде = object_id таблицы.

Самый надёжный способ определить связанный столбец — не декодировать имя вручную, а выполнить запрос к системным представлениям каталога SQL Server:

DECLARE @TableName AS sysname = N'dbo.CustomerTransactions';

SELECT   s.stats_id,
         s.name AS statistics_name,
         sc.stats_column_id,
         c.column_id,
         c.name AS column_name,
         s.auto_created,
         s.user_created,
         s.no_recompute
FROM     sys.stats AS s
         INNER JOIN
         sys.stats_columns AS sc
         ON sc.object_id = s.object_id
            AND sc.stats_id = s.stats_id
         INNER JOIN
         sys.columns AS c
         ON c.object_id = sc.object_id
            AND c.column_id = sc.column_id
WHERE    s.object_id = OBJECT_ID(@TableName)
         AND s.name = N'_WA_Sys_00000002_47A6D5E5'
ORDER BY sc.stats_column_id;

Статистики, связанные с индексами

Когда SQL Server создаёт индекс, он также создаёт объект статистики, связанный с этим индексом. Например: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId) создаёт объект статистики индекса, обычно называемый IX_CustomerTransactions_ClientId. Когда объект статистики соответствует индексу, его stats_id совпадает с index_id индекса.

Статистики, созданные пользователем

Статистика также может быть создана явно: CREATE STATISTICS ST_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId );. Этот объект идентифицируется в sys.stats с параметрами: user_created = 1, auto_created = 0. Параметр AUTO_UPDATE_STATISTICS применяется к статистикам индексов, автоматически созданным статистикам для одного столбца, статистикам, созданным вручную, и фильтрованным статистикам.

2. Статистики не обновляются одновременно

Предположим, что таблица имеет такие статистики:

_WA_Sys_00000002_xxxxxxxx для ClientId
_WA_Sys_00000003_xxxxxxxx для StatusId
IX_CustomerTransactions_CreatedDate для CreatedDate
PK_CustomerTransactions для TransactionId

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

Рассмотрим этот запрос:

SELECT TransactionId,
       ClientId,
       Amount
FROM   dbo.CustomerTransactions
WHERE  ClientId = 100;

Оптимизатору может потребоваться гистограмма по столбцу ClientId. Если соответствующий объект статистики устарел и превысил порог обновления, SQL Server может обновить именно этот объект статистики.

Ему не нужно обновлять статистики, не связанные с ним:

StatusId
CreatedDate

В результате статистики в одной таблице могут иметь разное время обновления:

PK_CustomerTransactions                 2026-07-22 10:53
IX_CustomerTransactions_ClientId        2026-07-22 10:50
IX_CustomerTransactions_CreatedDate     2026-07-22 10:13
_WA_Sys_00000003_47A6D5E5               2026-07-19 10:00

Такой шаблон может быть свидетельством автоматических обновлений по требованию, а не признаком неисправности.

3. Что представляет собой modification_counter?

modification_counter, возвращаемый представлением sys.dm_db_stats_properties, представляет собой количество изменений, внесённых в первый столбец статистики (ведущий столбец) с момента последнего обновления этого объекта статистики. Это определение особенно важно для многоколоночных статистик.

Например: CREATE INDEX IX_CustomerTransactions_ClientId_Status ON dbo.CustomerTransactions ( ClientId, StatusId );

Связанный объект статистики содержит:

Гистограмму: по ClientId
Информацию о плотности: для ClientId, для ClientId, StatusId

Гистограмма и modification_counter основаны на ведущем столбце, ClientId.

Если изменяется только StatusId: UPDATE dbo.CustomerTransactions SET StatusId = 2 WHERE TransactionId = 100; — это не оказывает такого же влияния на статистику, как изменение ClientId, потому что ClientId является ведущим столбцом гистограммы.

Статистика SQL Server содержит только одну гистограмму, построенную по первому ключевому столбцу. Многоколоночные статистики дополнительно содержат информацию о плотности для префиксов столбцов.

4. Почему несколько статистик иногда имеют схожие значения modification_counter?

Это часто происходит после операции вставки.

Например:

INSERT INTO dbo.CustomerTransactions (TransactionId, ClientId, StatusId, Amount, CreatedDate)
SELECT TransactionId,
       ClientId,
       StatusId,
       Amount,
       CreatedDate
FROM   dbo.StagingCustomerTransactions;

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

Строк в таблице до загрузки: 487 673
Строк, вставленных ETL: 515 244
Текущее приблизительное количество строк: 1 002 917

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

5. Применяется ли автоматическое обновление только к статистикам _WA_Sys?

AUTO_UPDATE_STATISTICS применяется к:

Автоматически создаваемым статистикам _WA_Sys
Статистикам индексов, включая статистики индексов первичного ключа
Статистикам, созданным пользователем

Каждый объект статистики оценивается независимо. Поэтому SQL Server может обновить PK_CustomerTransactions, оставив неизменным объект _WA_Sys_00000003_47A6D5E5. Возможна и обратная ситуация. Поведение зависит от того, какие статистики считаются релевантными во время компиляции или проверки кэшированного плана.

6. Что происходит, когда статистика _WA_Sys и статистика индекса покрывают один и тот же столбец?

Это один из самых интересных сценариев. Предположим, SQL Server изначально создал: _WA_Sys_00000002_xxxxxxxx для ClientId.

Позже кто-то создаёт индекс: CREATE INDEX IX_CustomerTransactions_ClientId ON dbo.CustomerTransactions(ClientId);

Теперь таблица имеет два объекта статистики с гистограммами по ClientId:

_WA_Sys_00000002_xxxxxxxx (статистика _WA_Sys, гистограмма по ClientId)
IX_CustomerTransactions_ClientId (статистика индекса, гистограмма по ClientId)

Для запроса, такого как: SELECT * FROM dbo.CustomerTransactions WHERE ClientId = @ClientId; оптимизатор может иметь более одного потенциально релевантного объекта статистики. Он может положиться на статистику индекса, а не на более старую статистику _WA_Sys.

В этом случае:

IX_CustomerTransactions_ClientId — обновлена недавно
_WA_Sys_00000002_xxxxxxxx — старая дата last_updated, высокий modification_counter

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

Однако это следует излагать осторожно: в публичной документации не указано, что оптимизатор запросов всегда предпочитает статистику индекса эквивалентной статистике _WA_Sys.

Выбранная статистика может зависеть от:

Запроса
Предикатов
Доступных индексов
Фильтрованных и нефильтрованных статистик
Свежести статистики
Качества выборки
Многоколоночной информации о плотности
Поведения оценщика кардинальности
Существующих кэшированных планов

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

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

DECLARE @TableName AS sysname = N'dbo.CustomerTransactions';

WITH     LeadingStatisticsColumns
AS       (SELECT s.object_id,
                 s.stats_id,
                 s.name AS statistics_name,
                 s.auto_created,
                 s.user_created,
                 s.no_recompute,
                 s.has_filter,
                 s.filter_definition,
                 sc.column_id,
                 c.name AS leading_column,
                 i.index_id,
                 i.name AS index_name
          FROM   sys.stats AS s
                 INNER JOIN
                 sys.stats_columns AS sc
                 ON sc.object_id = s.object_id
                    AND sc.stats_id = s.stats_id
                    AND sc.stats_column_id = 1
                 INNER JOIN
                 sys.columns AS c
                 ON c.object_id = sc.object_id
                    AND c.column_id = sc.column_id
                 LEFT OUTER JOIN
                 sys.indexes AS i
                 ON i.object_id = s.object_id
                    AND i.index_id = s.stats_id
          WHERE  s.object_id = OBJECT_ID(@TableName))
SELECT   leading_column,
         statistics_name,
         CASE WHEN index_id IS NOT NULL THEN N'INDEX STATISTICS' WHEN auto_created = 1 THEN N'AUTO-CREATED _WA_SYS' WHEN user_created = 1 THEN N'USER-CREATED STATISTICS' ELSE N'OTHER' END AS statistics_type,
         index_name,
         has_filter,
         filter_definition,
         no_recompute,
         COUNT(*) OVER (PARTITION BY column_id) AS statistics_on_same_leading_column
FROM     LeadingStatisticsColumns
ORDER BY leading_column, statistics_type, statistics_name;

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

7. Скрипт для демонстрации

В следующем примере показано, как автоматически созданная статистика может сосуществовать с более поздней статистикой индекса.

DROP TABLE IF EXISTS dbo.CustomerTransactions;
GO
CREATE TABLE dbo.CustomerTransactions (
    TransactionId INT             NOT NULL,
    ClientId      INT             NOT NULL,
    StatusId      TINYINT         NOT NULL,
    Amount        DECIMAL (12, 2) NOT NULL,
    CreatedDate   DATETIME2 (0)   NOT NULL,
    CONSTRAINT PK_CustomerTransactions PRIMARY KEY CLUSTERED (TransactionId)
);

Вставка примера данных:

WITH Numbers
AS   (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM   sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
INSERT INTO dbo.CustomerTransactions (TransactionId, ClientId, StatusId, Amount, CreatedDate)
SELECT n,
       n % 5000,
       n % 5,
       CONVERT (DECIMAL (12, 2), n % 10000),
       DATEADD(minute, -(n % 100000), SYSUTCDATETIME())
FROM   Numbers;

Убедитесь, что автоматическое создание и обновление статистики включено:

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS ON; 
GO 
ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON; 
GO

Запустите автоматическое создание статистики по столбцу ClientId:

SELECT COUNT_BIG(*)
FROM   dbo.CustomerTransactions
WHERE  ClientId = 100
OPTION (RECOMPILE);

Проверьте созданную статистику для ClientId:

SELECT   s.stats_id,
         s.name AS statistics_name,
         s.auto_created,
         s.user_created,
         c.name AS column_name
FROM     sys.stats AS s
         INNER JOIN
         sys.stats_columns AS sc
         ON sc.object_id = s.object_id
            AND sc.stats_id = s.stats_id
         INNER JOIN
         sys.columns AS c
         ON c.object_id = sc.object_id
            AND c.column_id = sc.column_id
WHERE    s.object_id = OBJECT_ID(N'dbo.CustomerTransactions')
         AND c.name = N'ClientId'
ORDER BY s.stats_id;

Создайте индекс на том же столбце:

CREATE INDEX IX_CustomerTransactions_ClientId
    ON dbo.CustomerTransactions(ClientId);

Теперь таблица может иметь:

Значительно измените данные:

UPDATE dbo.CustomerTransactions
SET    ClientId = ClientId + 10000
WHERE  TransactionId <= 100000;

Снова проверьте счётчики:

SELECT   s.name AS statistics_name,
         sp.last_updated,
         sp.rows,
         sp.rows_sampled,
         sp.modification_counter
FROM     sys.stats AS s OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE    s.object_id = OBJECT_ID(N'dbo.CustomerTransactions')
ORDER BY s.stats_id;

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

SET STATISTICS XML ON;
GO
SELECT COUNT_BIG(*)
FROM   dbo.CustomerTransactions
WHERE  ClientId = 10100
OPTION (RECOMPILE);
GO
SET STATISTICS XML OFF;

После выполнения запроса проверьте:

Фактический XML-план выполнения
Элементы StatisticsInfo
last_updated
modification_counter

То, какой объект обновляется, является решением оптимизатора и может варьироваться в зависимости от версии SQL Server, сборки, уровня совместимости базы данных и формы запроса. Поэтому тест следует использовать для наблюдения за поведением, а не для предположения о фиксированном предпочтении.

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

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

Статьи:

Отказ от ответственности:

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

Запускайте полную демонстрацию только в тестовой или нерабочей среде. Перед выполнением проверьте и адаптируйте имя базы данных, имена объектов, объём строк и инструкции.

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

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

6.8.26

Почему одни статистики остаются устаревшими, в то время как другие обновляются автоматически

1. Типы статистик в SQL Server

Автоматически создаваемые статистики столбцов

Статистики, связанные с индексами

Статистики, созданные пользователем

2. Статистики не обновляются одновременно

3. Что представляет собой modification_counter?

4. Почему несколько статистик иногда имеют схожие значения modification_counter?

5. Применяется ли автоматическое обновление только к статистикам _WA_Sys?

6. Что происходит, когда статистика _WA_Sys и статистика индекса покрывают один и тот же столбец?

7. Скрипт для демонстрации

Статьи:

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

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

Архив блога

Ярлыки

www.sql-ex.ru