Автоматическое создание статистики SQL Server

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

Зачем нужна статистика

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

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

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

Идентификация объектов статистики в базе данных

Существует много способов сделать SQL Server запрос о свойствах имеющихся объектов статистики, чтобы получить представление об их составе и содержащихся сведениях о распространении данных, но мы сосредоточимся на простом процессе идентификации. Для этого необходимо изучить всего два простых запроса SQL Server. Мы рассмотрим как системное представление каталога sys.stats, так и функцию динамического управления sys.dm_db_stats_properties.

С помощью описанных ниже запросов можно возвратить базовую информацию «не существует» и другие полезные сведения о ваших объектах статистики в базе данных. Сначала рассмотрим подход с использованием sys.stats, он показан в листинге 1.

Я выполняю присоединение к другому системному представлению каталога, sys.objects, с целью разрешения имени объекта, к которому привязана статистика (через object_id), а также для фильтрации результатов, чтобы искать только объекты, созданные пользователем (с использованием столбца is_ms_shipped в sys.objects).

На этом этапе еще не создано никаких объектов в базе данных Tech_Outbound, используемой в данной серии статей. Поэтому не возвращается никаких результатов. Однако я хочу построить несколько базовых таблиц для просмотра статистики, так что сначала я создаю таблицу чисел с использованием программного кода, подготовленного Ициком Бен-Ганом. Бен-Ган является автором многочисленных статей, в том числе опубликованных в этом журнале, и данный программный код заимствован из его арсенала инструментов (листинг 2).

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

С помощью запроса к sys.dm_db_stats_properties можно получить дополнительные сведения, помимо тех, которые предоставляются из sys.stats. Для запроса к этой функции динамического управления Dynamic Management Function (DMF) нужно предоставить два значения параметров: object_id и stats_id. Эти значения известны из результатов, возвращенных ранее из sys.stats. Используя их и присоединение к sys.objects для извлечения разъясняющей информации, мы получаем такой запрос, как показан в листинге 3.

Функция динамического управления sys.dm_db_stats_properties позволяет установить время, когда статистика была обновлена в последний раз, а также получить информацию о базовом домене объекта (в данном случае 50 000 строк) и количестве строк, включенном в выборку при создании или обновлении статистики, количестве шагов в статистической гистограмме, количестве нефильтрованных строк, которое учитывается при работе с фильтрованной статистикой и, наконец, modification_counter для объекта статистики.

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

Результаты подтверждают сказанное ранее об использовании полного просмотра при создании или перестроении индекса: этот вывод можно сделать, взглянув на столбцы rows и rows_sampled из sys.dm_db_stats_properties. Учитывая, что они равные, можно заключить, что выбрано 100% строк для построения статистики для первичного ключа в этой таблице.

Сведения о создании статистики

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

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

Если повторно выполнить запрос к sys.stats (первый запрос обнаружения), будут получены результаты, приведенные на рисунке 1, которые показывают, что просто акт создания таблицы не сформирует новых объектов статистики:

CREATE TABLE T0 (c1 INT NOT NULL,
   c2 NCHAR (200) NOT NULL DEFAULT '#');

Рисунок 1. Результаты повторного запроса к sys.stats

Теперь воспользуемся таблицей dbo.Numbers, чтобы загрузить в таблицу 100 000 строк со значением 1000 в столбец c1 и одной записью со значением 2000. Эта таблица будет использована позднее при рассмотрении влияния кратности — величины, характеризующей уникальность значений данных, на планы выполнения и статистику (листинг 4).

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

Теперь при запуске запроса обнаружения sys.stats мы видим в базе данных дополнительный объект статистики (рисунок 2).

Рисунок 2. Дополнительный объект статистики

Этого вполне следует ожидать, то же самое произошло с таблицей dbo.Numbers. Но что можно сказать о другой стороне автоматического создания статистики? Она проявляется, когда в статистике необходимо отразить, как обрабатываются объединения и предикаты поиска при создании плана выполнения. На сегодня понимание статистики распространения существует лишь для столбца dbo.Numbers.n (его столбца первичного ключа) и столбца dbo.T0.c1, для которого был вручную создан кластеризованный индекс. Нам предстоит создать еще одну таблицу, специально чтобы показать результирующее поведение:

CREATE TABLE T1 (c1 INT NOT NULL,
   c2 NCHAR (200) NOT NULL DEFAULT
   'SQL Cruise')

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

Но что произойдет, если выполнить следующий запрос?

SELECT T1.c1
FROM T0
INNER JOIN T1 ON T1.c1 = T0.c1;

Будет получен единственный результат (2000), но следует подчеркнуть, что происходит со статистикой. Не существует объекта статистики для T1 до тех пор, пока запрос не выполнен (вернее, до момента непосредственно перед выполнением запроса), так как оптимизатор запросов определил, что у него нет понимания данных для выполнения объединения. Поэтому он создал статистику в процессе построения плана выполнения. Выходные данные, возвращаемые теперь запросом обнаружения sys.stats, показаны на рисунке 3.

Рисунок 3. Выходные данные запроса обнаружения sys.stats

Установив значение AUTO_CREATE_STATISTICS = ON, вы увидите статистику, созданную для объединений при отсутствии начального столбца статистики.

Такое же поведение можно увидеть при фильтрации через предикат предложения WHERE. В листинге 7 два запроса не создают никакой статистики. Дело в том, что оптимизатор запросов не нуждается в дополнительных сведениях о распространении данных для любого из этих запросов. Для первого запроса это объясняется тем, что мы уже имеем статистику благодаря режиму автоматического создания из только что выполненного объединения, что привело к формированию запросов _WA_Sys_… (это соглашение об именовании по умолчанию для автоматически формируемой статистики). Причина, по которой второй запрос не создает статистики, заключается в том, что оптимизатор запросов может определить результаты на основе определения таблицы, не допускающего значений NULL.

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

SELECT T0.c2
FROM dbo.T0
WHERE c2 = '1';

Это приводит к созданию выделенной статистики, показанной в строке 4 на рисунке 4.

Рисунок 4. Автоматическое формирование статистики

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

В следующей статье мы рассмотрим гистограмму статистики и дополнительные варианты сбора подробных метаданных статистики через sys.stats, sys.dm_db_stats_properties и с помощью вызова пока не изученной нами команды BCC SHOW STATISTICS.

Листинг 1. Запрос с использованием sys.stats

USE Tech_Outbound;
GO

SELECT O.[name] AS [the_object_name]
       , S.object_id
       , S.name AS [the_stats_name]
       , S.stats_id
       , S.auto_created
       , S.user_created
       , S.no_recompute
       , S.has_filter
       , S.filter_definition
       , S.is_temporary
       , S.is_incremental

FROM sys.stats AS S
       INNER JOIN sys.objects AS O
              ON S.[object_id] = O.[object_id]
WHERE O.is_ms_shipped = 0
ORDER BY O.[name];

Листинг 2. Создание таблицы чисел

--СОЗДАНИЕ ТАБЛИЦЫ ЧИСЕЛ

CREATE TABLE Numbers (n INT NOT NULL PRIMARY KEY);
GO

INSERT INTO dbo.Numbers(n)
SELECT rn FROM
       (
       SELECT ROW_NUMBER()
              OVER(ORDER BY CURRENT_TIMESTAMP) AS rn
       FROM sys.trace_event_bindings AS b1
              , sys.trace_event_bindings AS b2
       ) AS rd
WHERE rn <= 500000;

SELECT * FROM dbo.Numbers;

Листинг 3. Запрос к функции динамического управления sys.dm_db_

stats_properties
SELECT O.[name] AS [the_object_name]
       , S.object_id
       , S.stats_id
       , S.last_updated
       , S.rows
       , S.rows_sampled
       , S.steps
       , S.unfiltered_rows
       , S.modification_counter
FROM sys.dm_db_stats_properties(565577053,1) AS S
       INNER JOIN sys.objects AS O
              ON S.[object_id] = O.[object_id]
WHERE O.is_ms_shipped = 0
       AND O.[name] != 'foo'
ORDER BY O.[name];

Листинг 4. Загрузка в таблицу строк

--INSERT 100000 строк. Все строки содержат значение 1000 для столбца c1

INSERT INTO T0(c1)
SELECT 1000 FROM dbo.Numbers
WHERE n <= 10000;

--INSERT 1 строку со значением 2000

INSERT INTO T0(c1)
VALUES (2000);

Листинг 5. Создание некластеризованного индекса для столбца c1

--СОЗДАНИЕ НЕКЛАСТЕРИЗОВАННОГО ИНДЕКСА ДЛЯ СТОЛБЦА c1
CREATE NONCLUSTERED INDEX ix_T0_1 ON T0(c1);

Листинг 6. Добавление строки в таблицу Т1

INSERT INTO T1(c1)
VALUES (2000)

Листинг 7. Два запроса без создания статистики

SELECT T1.c2
FROM dbo.T1
WHERE c1 = 2000;
SELECT T0.c2
FROM dbo.T0
WHERE c2 IS NULL;