Как работает автоматическое удаление статистики в SQL Server 2022
Пересказ статьи Dennes Torres. SQL Server 2022 How Auto-Drop Statistics Work
Автоматическое создание статистики хорошо известно как решающее средство для улучшения производительности запросов. Когда автоматически создается некая статистика, изменение структуры таблицы не блокируется присутствием этой статистки. Автоматически создаваемая статистика также автоматически удаляется, когда происходит изменение схемы.
С другой стороны, если статистика создается пользователем, любое изменение схемы будет блокироваться наличием статистики.
Настройка автоматического удаления статистики является новой функцией в SQL Server 2022 для изменения этого поведения. Если статистика, созданная пользователем, установлена с опцией автоматического удаления, она будет себя вести как автоматически созданная: т.е. будет автоматически удаляться при изменении схемы.
Давайте сделаем пример, используя базу данных AdventureWorks2019. Вы можете загрузить ее отсюда. Восстановите бэкап в SQL Server 2022.
Наш пример будет использовать таблицу Production.Product и фокусироваться на столбце ListPrice. Выполним последовательно следующие шаги.
1) Удалим ограничение CK_product_listprice, иначе оно будет блокировать демонстрацию:
2) Проверим существующую статистику. Вы не обнаружите ничего, связанного с полем ListPrice.
3) Выполним следующий запрос:
4) Снова проверим статистику. Новая статистика для поля ListPrice была автоматически создана.
5) Изменим столбец ListPrice.
6) Проверим статистику. SQL Server удалит автоматически созданную статистику.
7) Создадим новую статистику на столбце ListPrice. Это уже будет созданная пользователем статистика.
Попытаемся изменить схему еще раз. Возникнет ошибка.
9) Удалим созданную пользователем статистику:
10) Снова создадим статистику, теперь с опцией автоудаления.
11) Опять попробуем изменить схему. Теперь это работает, и статистика mystats будет удалена.
Помочь с определением установки опции автоудаления статистики может простой запрос:
Эта простая новая функция может помочь в некоторых сценариях контроля версий и автоматического распространения схем баз данных.
1. Введение в статистику SQL Server
2. План выполнения: статистика
3. Когда обновляется статистика?
4. Установка частоты выборки для автоматического обновления статистики
Демонстрация
Наш пример будет использовать таблицу Production.Product и фокусироваться на столбце ListPrice. Выполним последовательно следующие шаги.
1) Удалим ограничение CK_product_listprice, иначе оно будет блокировать демонстрацию:
ALTER TABLE production.product
DROP CONSTRAINT ck_product_listprice 2) Проверим существующую статистику. Вы не обнаружите ничего, связанного с полем ListPrice.
3) Выполним следующий запрос:
SELECT *
FROM production.product
WHERE listprice = 10 4) Снова проверим статистику. Новая статистика для поля ListPrice была автоматически создана.
5) Изменим столбец ListPrice.
ALTER TABLE production.product
ALTER COLUMN listprice NUMERIC(18, 2) 6) Проверим статистику. SQL Server удалит автоматически созданную статистику.
7) Создадим новую статистику на столбце ListPrice. Это уже будет созданная пользователем статистика.
CREATE STATISTICS [mystats] ON production.product(listprice) Попытаемся изменить схему еще раз. Возникнет ошибка.ALTER TABLE production.product
ALTER COLUMN listprice MONEY 9) Удалим созданную пользователем статистику:
DROP statistic production.product.mystats 10) Снова создадим статистику, теперь с опцией автоудаления.
CREATE statistics [mystats] ON production.product(listprice) WITH auto_drop=ON 11) Опять попробуем изменить схему. Теперь это работает, и статистика mystats будет удалена.
ALTER TABLE production.product
ALTER COLUMN listprice MONEY Проверка, какая статистика имеет опцию автоудаления
Помочь с определением установки опции автоудаления статистики может простой запрос:
SELECT object_id,
NAME,
auto_drop
FROM sys.stats Заключение
Эта простая новая функция может помочь в некоторых сценариях контроля версий и автоматического распространения схем баз данных.
Ссылки по теме
1. Введение в статистику SQL Server
2. План выполнения: статистика
3. Когда обновляется статистика?
4. Установка частоты выборки для автоматического обновления статистики
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой