Недавно на конференции IT/Dev Connections я выступал с докладом на тему статистики распределения для Microsoft SQL Server. На мой взгляд, эта тема достаточно важна, чтобы посвятить ей несколько статей.

Почему для SQL Server важны статистические данные? Потому что без сведений о том, как распределены данные по таблицам и индексам, оптимизатор запросов SQL Server Query Optimizer (QO) не сможет составить полную картину распределения ваших данных. Следовательно, оптимизатору не удастся выяснить, какие операции наиболее эффективны для обработки запросов. В результате в планах выполнения, создаваемых оптимизатором, постоянно используются просмотр индексов и таблиц и неэффективные объединения, из-за которых эксплуатировать платформу неудобно.

Что такое статистика распределения

Статистика представляет распределение данных в столбце или столбцах. QO хранит статистику для таблицы или индексированного представления в объекте статистики. Для таблицы объект статистики создается на индексе или на списке столбцов таблицы. Объект статистики состоит из трех компонентов: заголовка с метаданными о статистике, гистограммы с распределением значений в первом ключевом столбце объекта статистики и вектора плотности для оценки различимости значений между столбцами, который также именуют кратностью. Компонент Database Engine может оценивать кратность с использованием любых данных в объектах статистики.

Как используется статистика

Оптимизатор запросов задействует статистику, чтобы определить, как обслужить запрос SQL в зависимости от распределения значений данных, выяснив, сколько строк будет возвращено из каждой операции в плане запроса, а также выбирая наиболее подходящую операцию плана запроса. Варианты таких операций могут быть следующие: поиск в таблице или индексе или просмотр таблицы или индекса для выбора данных; хеш-соединение или соединение слиянием для объединения наборов данных из различных операций на пути в плане выполнения и т. д. Если оптимизатор запросов QO не может определить, имеет ли данный предикат в поиске или соединении высокую кратность (уникален в сравнении с полным доменом значений), то на таких участках планов выполнения всегда будет преобладать неоптимальное соединение, в частности постоянные просмотры полных таблиц или индексов. Зная значения кратности, оптимизатор запросов может использовать менее затратные операции для ускоренного возврата результатов по запросам, направленным в базу данных.

Как создается статистика

Статистика создается автоматически, в зависимости от параметров уровня базы данных и через административные действия, такие как создание или перестраивание индексов, или вручную, согласно вашим предпочтениям. Когда индекс будет создан или перестроен, SQL Server потратит некоторое время, чтобы создать или обновить существующую статистику. Для создания или перестраивания индекса требуется прочитать все страницы таблицы или индекса. Это наиболее подходящее время, чтобы ядро системы управления базами данных SQL Server сформировало представление о распределении данных самым исчерпывающим способом — с полным просмотром данных.

Кроме того, существуют параметры уровня базы данных, управляющие способами автоматического создания и обновления статистики. Эти параметры: Auto Create Statistics, Auto Update Statistics и Auto Update Statistics Asynchronously. Они устанавливаются с помощью команды ALTER DATABASE, как показано в приведенном листинге.

Описание AUTO_CREATE_STATISTICS

Если присвоено значение ON, параметр AUTO_CREATE_STATISTICS обеспечивает автоматическое создание статистики для столбцов, используемых для поиска, или предикатов соединения. Это означает, что если нет существующего объекта статистики, созданного одновременно с созданием индекса, или статистики, созданной вручную, то SQL Server создаст необходимую статистику в рамках построения плана выполнения запроса как часть дополнительных действий по подготовке плана. Это также произойдет, если столбец является частью статистики нескольких столбцов, но не начальным столбцом, так как начальному столбцу индекса уделяется особое внимание по сравнению с другими столбцами, которые могут следовать за ним. Более подробно о различии во внимании, уделяемом начальному и остальным столбцам, будет рассказано в следующих статьях на тему статистики распределения в SQL Server. Напомню, это событие создания произойдет в рамках формирования плана и увеличивает накладные затраты при его подготовке. По умолчанию этот параметр имеет значение ON.

Описание AUTO_UPDATE_STATISTICS

Если присвоено значение ON, параметр AUTO_UPDATE_STATISTICS обеспечивает автоматическое обновление статистики программой SQL Server, когда существующая статистика признана устаревшей обработчиком запросов. Процесс и дополнительные затраты обновления статистики возникают, когда связанные столбцы востребованы как часть следующего запроса, выполняемого после того, как SQL Server обнаруживает устаревание статистики. Это достигается с помощью счетчика изменений, который отслеживает количество изменений в объекте, связанном с объектом статистики (со времени последнего события создания или обновления, в зависимости от того, какое из них произошло последним). До выпуска SQL Server 2016 поведением по умолчанию по отношению к статистике, отмеченной как устаревшая, было следующее:

  • для таблиц без строк статистика отмечается как подлежащая обновлению после первой вставки строки;
  • для таблиц, содержащих до 500 строк, статистика отмечается как подлежащая обновлению, когда начальный столбец в объекте статистики встречает более 500 обновлений со времени последнего обновления статистики;
  • для таблиц, содержащих более 500 строк, статистика отмечается как подлежащая обновлению, когда начальный столбец в объекте статистики встречает более 500 обновлений + 20% количества строк в столбцах со времени последнего обновления статистики.

Очевидно, что последняя ситуация не способствует своевременному обновлению статистики в больших таблицах: удовлетворить требование 20% значений столбцов + 500 становится все сложнее по мере роста таблицы. С момента выпуска SQL Server 2008 с пакетом обновления SP1 вы можете воспользоваться флагом трассировки 2371, чтобы сделать 20-процентный порог более гибким (читай «низким») при увеличении количества строк в таблице, как показано на рисунке.

 

Влияние флага трассировки 2371 на обновление статистики
Рисунок. Влияние флага трассировки 2371 на обновление статистики

В версии SQL Server 2016 использование этого флага трассировки представляет собой новый стандарт для автоматического обновления статистики, и задействовать флаг TF2371 более не требуется. Как и в случае с AUTO_CREATE_STATISTICS, статистика, удовлетворяющая условиям устаревания, будет обновлена при построении плана выполнения запроса. Обновление статистики таким способом неявно происходит синхронно с формированием плана выполнения. По умолчанию этот параметр имеет значение ON.

Описание AUTO_UPDATE_STATISTICS_ASYNC

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

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

Листинг. Параметры уровня базы данных 
ALTER DATABASE [имя_базы_данных] SET AUTO_CREATE_STATISTICS ON|OFF;
GO
ALTER DATABASE [имя_базы_данных] SET AUTO_UPDATE_STATISTICS ON|OFF;
GO
ALTER DATABASE [имя_базы_данных] SET AUTO_UPDATE_STATISTICS_ASYNC ON|OFF;
GO