Обсудить
бизнес-задачи
бизнес-задачи
Автоматическое обновление статистики
блог о bi, №1 в рунете
SQL Server использует оптимизатор запросов, который анализирует статистику для определения наиболее эффективного плана выполнения запроса. Поэтому актуальность статистики напрямую влияет на скорость запросов. В статье пойдет речь о статистике и опциях MS SQL Server, которые позволяют автоматически ее обновлять.
Статистика — это информация о распределении значений в одном или нескольких полях таблицы. Она состоит из гистограммы значений и вектора плотностей. Посмотреть статистику можно в обозревателе объектов в таблицах.
Гистограмма показывает частоту значений в поле таблицы. По ней оптимизатор запросов рассчитывает количество строк, которое будет прочитано и использовано в дальнейшем. Пример гистограммы показан на рисунке 1.
Гистограмма показывает частоту значений в поле таблицы. По ней оптимизатор запросов рассчитывает количество строк, которое будет прочитано и использовано в дальнейшем. Пример гистограммы показан на рисунке 1.
Рисунок 1. Пример гистограммы
Гистограмма состоит из множества групп строк, максимум таких групп может быть 200.
RANGE_HI_KEY определяет верхнюю границу группы, то есть в группе значения от RANGE_HI_KEY предыдущей группы не включительно до текущего значения включительно. По каждой группе собирается статистика, а именно:
Например, опишем статистику для третьей группы, RANGE_HI_KEY которой равен 159.
Группа состоит из значений от 157, не включительно, до 159, включительно. RANGE_ROWS равен 1, значит в таблице одно значение 157 < n < 159, в нашем случае это только 158. EQ_ROWS также равен 1, то есть в таблице одно значение, равное 159. DISTINCT_RANGE_ROWS равен одному, это только уникальное значение 158, AVG_RANGE_ROWS равно 1.
Плотность – это информация о избирательности поля. Она равняется единице, деленной на количество уникальных значений поля или уникальных сочетаний нескольких полей. Пример плотности изображен на рисунке 2.
RANGE_HI_KEY определяет верхнюю границу группы, то есть в группе значения от RANGE_HI_KEY предыдущей группы не включительно до текущего значения включительно. По каждой группе собирается статистика, а именно:
- количество значений, не равных RANGE_HI_KEY (RANGE_ROWS);
- количество значений равных RANGE_HI_KEY (EQ_ROWS);
- количество уникальных строк (DISTINCT_RANGE_ROWS);
- среднее количество уникальных строк (AVG_RANGE_ROWS = DISTINCT_RANGE_ROWS / RANGE_ROWS).
Например, опишем статистику для третьей группы, RANGE_HI_KEY которой равен 159.
Группа состоит из значений от 157, не включительно, до 159, включительно. RANGE_ROWS равен 1, значит в таблице одно значение 157 < n < 159, в нашем случае это только 158. EQ_ROWS также равен 1, то есть в таблице одно значение, равное 159. DISTINCT_RANGE_ROWS равен одному, это только уникальное значение 158, AVG_RANGE_ROWS равно 1.
Плотность – это информация о избирательности поля. Она равняется единице, деленной на количество уникальных значений поля или уникальных сочетаний нескольких полей. Пример плотности изображен на рисунке 2.
Рисунок 2. Плотность поля ID
Согласно изображению, плотность поля ID составляет 0.005464481, что равняется 183 уникальным значениям. Столбец Average Length показывает средний размер значения в байтах.
Плотность для нескольких полей составляет вектор. Например, если статистика состоит из полей Customer, Item, Price, вектор будет состоять из плотностей 1) Customer, 2) Customer и Item, 3) Customer, Item и Price.
Мы в своей работе используем три опции для регулирования автоматического создания статистик. Ниже представлены рекомендации, основанные на анализе данных статистики по нескольким проектам.
Плотность для нескольких полей составляет вектор. Например, если статистика состоит из полей Customer, Item, Price, вектор будет состоять из плотностей 1) Customer, 2) Customer и Item, 3) Customer, Item и Price.
Мы в своей работе используем три опции для регулирования автоматического создания статистик. Ниже представлены рекомендации, основанные на анализе данных статистики по нескольким проектам.
Auto Create Statistics
При включенной опции оптимизатор для каждого поля из запроса создает статистику, если ее не существует. По умолчанию опция включена.
Рекомендуется оставить включенной эту опцию, иначе отсутствие статистик приведет к неэффективным планам запроса и ухудшению производительности.
Выключить/включить опцию для базы данных master можно следующим запросом:
Рекомендуется оставить включенной эту опцию, иначе отсутствие статистик приведет к неэффективным планам запроса и ухудшению производительности.
Выключить/включить опцию для базы данных master можно следующим запросом:
ALTER DATABASE master
SET AUTO_CREATE_STATISTICS {OFF | ON}
Auto Update Statistics
Модификация данных таблицы изменяет распределение значений и делает статистику не актуальной. А опция AUTO_CREATE_STATISTICS только создает недостающие статистики, но не обновляет их. Оптимизатор, используя не актуальную статистику, рискует выбрать неэффективный план. Поэтому рекомендуется включить опцию AUTO_UPDATE_STATISTICS, которая позволяет оптимизатору периодически обновлять статистики.
Оптимизатор до выбора оптимального плана проверяет актуальность используемых статистик. Для этого сравнивается количество операций модификации данных этого поля с граничным значением, которое основано на количестве строк в таблице. Необходимое количество модификаций для признания статистики неактуальной представлено в таблице 1.
Оптимизатор до выбора оптимального плана проверяет актуальность используемых статистик. Для этого сравнивается количество операций модификации данных этого поля с граничным значением, которое основано на количестве строк в таблице. Необходимое количество модификаций для признания статистики неактуальной представлено в таблице 1.
Таблица 1. Количество модификаций для признания статистики неактуальной
Тип таблицы
Количество строк, n
До SQL Server 2014 (12.x)
После SQL Server 2016 (13.x)
Временная
n < 6
6
6
Временная
6 <= n <= 500
500
500
Обычная
n <= 500
500
500
Временная или обычная
500 < n <= 20000*
500 + (0.20 x n)
500 + (0.20 x n)
Временная или обычная
n > 20000*
500 + (0.20 x n)
SQRT (1,000 x n)
* Приблизительные значения
По умолчанию опция выключена. Включить/выключить опцию для базы данных master можно следующим запросом:
ALTER DATABASE master
SET AUTO_UPDATE_STATISTICS {ON | OFF}
Опция AUTO_UPDATE_STATISTICS синхронно обновляет статистику, оптимизатор ждет окончания обновления для выбора плана текущего запроса. При включенном асинхронном обновлении, выбор плана оптимизатором не требует завершения обновления статистики. Это приводит к тому, что текущий запрос выполняется по старому неэффективному плану. Поэтому не рекомендуется использовать эту опцию.
Включить/выключить опцию для базы данных master можно следующим запросом:
Включить/выключить опцию для базы данных master можно следующим запросом:
ALTER DATABASE master
SET AUTO_UPDATE_STATISTICS_ASYNC {ON | OFF}
Auto Create Incremental Statistics
Обновление статистики требует полного чтения таблицы, что может занимать продолжительное время. Часто такие таблицы делят на секции, и существует возможность создавать статистику для каждой секции. Такая статистика называется инкрементальной и для нее не требуется чтение всей таблицы.
По умолчанию опция выключена. Рекомендуется включить опцию, если в базе данных используются большие секционированные таблицы. Включить/выключить опцию можно следующим запросом:
По умолчанию опция выключена. Рекомендуется включить опцию, если в базе данных используются большие секционированные таблицы. Включить/выключить опцию можно следующим запросом:
ALTER DATABASE master
SET AUTO_CREATE_STATISTICS {OFF | ON [ (INCREMENTAL = {ON | OFF} ) ] }
Следующий запрос создает инкрементальную статистику:
CREATE STATISTICS statistics_name
ON {table name} (column [ ,...n ] ) INCREMENTAL = {ON | OFF}
Проверить значение опций для каждой базы данных на сервере можно следующим запросом:
SELECT
name AS database_name
,is_auto_create_stats_on
,is_auto_update_stats_on
,is_auto_update_stats_async_on
,is_auto_create_stats_incremental_on
FROM sys.databases
Сравнение времени
Произведем сравнение времени выполнения простого запроса, соединяющего две таблицы, с актуальной и неактуальной статистиками. Создадим статистику на пустую таблицу, заполним ее 6 млн. строк без обновления статистики и выполним запрос. После этого обновим статистику и выполним тот же запрос. В результате запрос с неактуальной статистикой выполнялся 69 секунд, запрос с актуальной - 52 секунды. Время запроса сократилось на 25% благодаря обновлению статистики. Стоит заметить, что для таблиц большего размера разница будет существенней.
Отметим, что статистика играет критическую роль в составлении эффективного плана запроса. Поэтому рекомендуется включать создание и обновление статистик и создавать инкрементные статистики для секционированных таблиц.
Отметим, что статистика играет критическую роль в составлении эффективного плана запроса. Поэтому рекомендуется включать создание и обновление статистик и создавать инкрементные статистики для секционированных таблиц.