Новое в SQL Server 2022: параметр AUTO_DROP для статистики

В SQL Server 2022 добавилась новая функция для статистики — AUTO_DROP. В этой статье мы расскажем, что она даёт и как её включать и выключать. Также будут представлены несколько примеров и показаны некоторые распространенные ошибки и способы их решения. Для демонстрационных примеров в этой статье мы будем использовать следующее:

1. Установленный SQL Server 2022

2. Развёрнута база данных AdventureWorks

3. Установлена SSMS

Что такое статистики в SQL Server?

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

В SSMS при раскрытии дерева объектов таблицы можно увидеть папку «Statistics», а в ней статистики для этой таблицы.

Кроме этого, можно увидеть статистики для таблицы с помощью запроса на T-SQL. Следующая команда показывает, как посмотреть статистики:

select * from sys.stats

Вот пример результата её выполнения:

В SQL Server 2022 для административного представления sys.stats добавлен новый столбец с именем AUTO_DROP. Если AUTO_DROP равен 0 (ложь), это означает, что параметр отключен, а если 1 (истина), то значит включен.

Что такое настройка SQL Server AUTO_DROP?

До SQL Server 2022 параметра AUTO_DROP не было, поэтому обновление статистики могло блокировать изменение схемы. Давайте рассмотрим пример.

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt])
WITH AUTO_DROP = ON;

Мы создали статистику myPasswordStats для таблицы Person.Password, в которой участвуют три столбца: (BusinessEntityID, PasswordHash, PasswordSalt). После этого, мы устанавливаем для параметра AUTO_DROP значение ON.

После создания, мы можем увидеть новую статистику в SSMS:

Щелкните правой кнопкой мыши по таблице, выберите пункт Design и удалите столбец PasswordHash.

Обратите внимание, что после этого статистика myPasswordStats тоже окажется удалённой:

Это произошло из-за изменения в структуре таблицы после удаления столбца, статистика с его участием стала не актуальна и поэтому она была автоматически удалена. Произошло это в следствие того, что мы активировали новый параметр AUTO_DROP.

Параметр AUTO_DROP недоступен до SQL Server 2022

Если вы запустите показанный ниже скрипт в SQL Server 2019 или более ранних версиях, сервер вернёт ошибку:

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password]
(BusinessEntityID, [PasswordHash], [PasswordSalt])
WITH AUTO_DROP = ON;

Msg 155, Level 15, State 1, Line 4
'AUTO_DROP' is not a recognized CREATE STATISTICS option.

Параметр AUTO_DROP не распознается в более ранних версиях. Чтобы проверить версию SQL Server, перейдите по этой ссылке: Как узнать, какую версию SQL Server вы используете.

Удаление статистики без включения AUTO_DROP

Создадим статистику без параметра AUTO_DROP. Вот как её можно создать в базе данных SQL Server 2019.

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password]
(BusinessEntityID, [PasswordHash], [PasswordSalt])

Теперь попробуем удалить столбец:

Вот сообщение об ошибке, которое мы в ответ получим:

'Password (Person)' table - Unable to modify table.
The statistics 'myPasswordStats' is dependent on column 'PasswordHash'.
ALTER TABLE DROP COLUMN PasswordHash failed because one or more objects access this column.

Существует зависимость столбца от статистики, что не позволяет вносить такие изменения в таблицу.

Включение и отключение параметра AUTO_DROP

В этом примере показано, как отключить AUTO_DROP.

UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = OFF;

Тут [Person].[Password] — имя таблицы, а myPasswordStats — имя статистики.

В этом примере показано, как включить настройку AUTO_DROP.

UPDATE STATISTICS [Person].[Password]  [myPasswordStats] WITH AUTO_DROP = ON;