Сравнение одноколоночных, многоколоночных и фильтрованных статистик в SQL Server

Автор: Kendra Little, Comparing Single Column, Multi-Column, and Filtered Statistics in SQL Server

Статистики в SQL Server теоретически просты: они помогают оптимизатору оценить, сколько строк может вернуть запрос.

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

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

Я использую тестовую базу данных Stackoverflow2013 на уровне совместимости 160

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

ALTER DATABASE StackOverflow2013 SET COMPATIBILITY_LEVEL = 160;
GO

USE StackOverflow2013;
GO

CREATE OR ALTER PROCEDURE
    dbo.ListStatistics
(
    @ObjectName sysname
)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT
        statistic_name = s.name,
        leading_column = c.name,
        auto_created = s.auto_created,
        user_created = s.user_created,
        has_filter = s.has_filter,
        filter_definition = s.filter_definition,
        no_recompute = s.no_recompute
    FROM sys.stats AS s
    JOIN sys.stats_columns AS sc
      ON  s.object_id = sc.object_id
      AND s.stats_id = sc.stats_id
    JOIN sys.columns AS c
      ON  sc.object_id = c.object_id
      AND sc.column_id = c.column_id
    WHERE sc.stats_column_id = 1
    AND   s.object_id = OBJECT_ID(@ObjectName)
    ORDER BY
        s.name;
END;
GO

Перед началом работы в таблице dbo.Posts есть одна статистика.

Эта статистика связана с кластерным индексом по столбцу Id. Всякий раз, когда в SQL Server создаётся индекс, вместе с ним создаётся и статистика.

Запустим наш пример запроса, который автоматически создаст одноколоночные статистики для помощи в оценках

Наш пример запроса содержит три предиката: PostTypeId, ParentId и AnswerCount.

Для упрощения, чтобы не учитывать автоматическую параметризацию и повторное использование планов запросов, я добавила в запрос указание OPTION (RECOMPILE).

SELECT
    Id
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND   p.ParentId = 0
AND   p.AnswerCount = 0
OPTION (RECOMPILE);

Когда я запускаю его, план запроса показывает…

• SQL Server оценивает, что будет возвращено 3 200 270 строк.
• Фактически возвращается 305 761 строка — всего 9.5% от оценки. Эта оценка была очень завышена.

Я повторно запускаю dbo.ListStatistics и вижу, что автоматически создались три статистики по столбцам, по одному для каждого из трёх предикатов в моём запросе. Для базы данных StackOverflow2013 включена настройка AUTO_CREATE_STATISTICS — это настройка по умолчанию, и она позволяет SQL Server создавать эти небольшие вспомогательные объекты для формирования данной оценки.

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

Например, вот вид гистограммы для автоматически созданной одноколоночной статистики по столбцу PostTypeId:

На основе выборки данных было оценено, что на момент автоматического создания статистики в dbo.Posts было 5977672 строк с PostTypeId=1.

Статистики по столбцам, созданные для ParentId и AnswerCount, аналогично описывают распределение данных в этих столбцах.

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

Когда мы создаём индекс с несколькими ключевыми столбцами, вместе с ним создаётся многоколоночная статистика

Допустим, у нас есть индекс по первым двум столбцам в нашем предикате. Вот этот индекс:

CREATE INDEX
    ix_Posts_PostTypeId_ParentId
ON dbo.Posts
(
    PostTypeId,
    ParentId
)
WITH
    (DATA_COMPRESSION = PAGE);
GO

Я создала некластерный индекс, который не полностью «покрывает» мой запрос: в нём есть два предиката из трёх.

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

• Ранее SQL Server оценивал, что будет возвращено 3 200 270 строк.
• Теперь SQL Server оценивает, что будет возвращено 3 212 350 строк (на 12 080 больше).
• Эта оценка всё ещё очень завышена, фактически возвращается 305 761 строка (то же самое, данные в базе не меняются).

Почему эта оценка стала немного выше?

Если я использую DBCC SHOW_STATISTICS для описания статистики, связанной с индексом, она действительно описывает кое-что большее, чем просто первый столбец, но не много. Важно заметить следующее:

• Эта статистика была создана, когда SQL Server создавал индекс, и для этого ему пришлось просмотреть каждую строку в столбце. Он использовал информацию из всех строк (а не из выборки), поэтому данные в статистике немного отличаются.
• Статистика имеет раздел «Вектор плотности», который описывает среднюю селективность значений столбцов — другими словами, насколько значения обычно уникальны.
• Он даёт обратное значение количества уникальных пар (PostTypeId, ParentId).
• Например, если значение «полной плотности» равно 1.779737E-07, это подразумевает, что существует около 5.6 миллионов уникальных комбинаций (PostTypeId, ParentId) (1/.0000001779737= 5 618 807).
• В таблице 17142169 строк, что подразумевает, что для любой заданной комбинации PostTypeId, ParentId в среднем приходится около 3.05 строк, ЕСЛИ распределение значений очень равномерное.
• Гистограмма статистики построена только по ведущему столбцу в индексе, которым в данном случае является PostTypeId. PostTypeId имеет довольно ограниченный набор значений.
• Гистограмма показывает 6000223 значения для PostTypeId = 1. В статистике по столбцу PostTypeId, основанной на выборке, эта оценка составляла 5977672. Оценка увеличилась на 22551.

В этом случае похоже, что наличие многоколоночной статистики не дало оптимизатору много новой информации, которую он счёл полезной.

Да, вектор плотности подразумевает, что в среднем для любой комбинации значений PostTypeId и ParentId приходится 3.05 строки, но оценка намного выше 3 строк. Похоже, наша оценка всё ещё основана на значениях гистограммы, и оценка немного выросла потому, что гистограмма для нового индекса — которая построена только по ведущему столбцу PostTypeId — оценила немного большее количество строк для PostTypeId = 1.

Создаём многоколоночную отфильтрованную статистику, адаптированную под запрос

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

Я создаю статистику, очень точно настроенную под мой запрос, вот так:

CREATE STATISTICS
    FilteredStats_Posts_PostTypeId_ParentId_AnswerCount
ON dbo.Posts
(
    PostTypeId,
    ParentId,
    AnswerCount
)
WHERE PostTypeId = 1
AND   ParentId = 0
AND   AnswerCount = 0;
GO

Я заметила, что когда вы создаёте статистику таким образом, она не заставит запросы автоматически перекомпилироваться. Однако в моём запросе есть предложение OPTION (RECOMPILE), поэтому мне не нужно об этом беспокоиться.

Повторно запустив мой запрос, я получаю план той же формы, но оценки стали намного точнее:

• С фильтрованной многоколоночной статистикой SQL Server оценивает, что будет возвращено 306267 строк, что очень близко к фактическому количеству в 305761.

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

Реализм: я не создаю статистики подобным образом «на всякий случай» и не предполагаю, что это автоматически решит проблему — но иногда это может помочь в конкретной ситуации, когда нет смысла создавать индекс, а плохие оценки строк вызывают проблему с производительностью.

План запроса зависит от того, какую версию оценщика кардинальности (CE) я использую

В SQL Server есть два оценщика кардинальности: «унаследованный» оценщик кардинальности, который применяется для уровня совместимости базы данных 110 и ниже (или если он включён как конфигурация с областью действия базы данных), и «не имеющий реального названия»/более новый оценщик кардинальности.

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

Если у меня создана фильтрованная статистика и создан двухстолбцовый индекс, и я запускаю свой запрос, используя унаследованный оценщик кардинальности, SQL Server решает использовать непокрывающий индекс и выполнить поиск по вложенному циклу, чтобы получить данные AnswerCount (которых нет в индексе).

Оценка количества строк, которые выйдут из оператора вложенного цикла, очень точна: 306267 строк (фактически 305761).

Однако SQL Server оценивает, что из индекса по PostTypeId, ParentId будет получено 750710 строк, тогда как фактически из оператора было возвращено 6000223 строк.

Советы и выводы о статистиках

• Статистики помогают SQL Server угадывать количество строк.
• Статистики — это оценки, и «достаточно близко» обычно нормально. Не гонитесь за совершенством.
• Автоматически созданные одноколоночные статистики имеют гистограмму, которая описывает распределение данных в одном столбце за раз. Если ваш запрос фильтрует по нескольким столбцам, SQL Server должен угадать, как эти фильтры работают вместе.
• Многоколоночные статистики (созданные с многоколоночными индексами или вручную) имеют гистограмму только для ведущего столбца.
• Отфильтрованные статистики могут помочь со сложными запросами, где оценка сильно отклоняется, и вы не хотите (или не нуждаетесь) в новом индексе. Если вы точно соответствуете фильтру, оценка может стать намного лучше.
• Не предполагайте, что «плохая оценка» означает «плохая статистика» или «плохой план». Оценки могут быть неточными и всё равно приводить к хорошему плану. Убедитесь, связана ли ваша проблема с тем, что «статистики были неверны», или это случай, когда «статистики были хороши для значений, с которыми был скомпилирован план запроса, но затем план выполнялся с другими значениями параметров» (проблема Parameter Sniffing).
• Избегайте ловушки «просто делайте fullscan для всех ваших статистик». Обновление статистик с fullscan всё время сжигает IO и со временем замедляет работу. Зависимость от этого может поставить вас в реактивную позицию, когда вы регулярно сталкиваетесь с плохой производительностью.
• Версия оценщика кардинальности имеет значение. Иногда унаследованный оценщик кардинальности будет работать лучше для запроса. Иногда нет.