Sp updatestats: примеры (SQL)

Простые примеры использования

Пример 1: Запуск процедуры без параметров. Процедура обновляет статистику для всех таблиц с полным сканированием (FULLSCAN).

USE AdventureWorks2022;
GO
EXEC sp_updatestats;
GO

Обновление статистики для таблицы [Sales].[SalesOrderDetail]...
Обновление статистики для таблицы [Production].[Product]...
...
Все статистики успешно обновлены.

Пример 2: Запуск процедуры с параметром @resample = 'RESAMPLE'. Статистика обновляется с использованием предыдущего шаблона выборки.

EXEC sp_updatestats @resample = 'RESAMPLE';

Обновление статистики для таблицы [HumanResources].[Employee] с унаследованной выборкой...
...

Пример 3: Явное указание параметра NORESAMPLE.

EXEC sp_updatestats @resample = 'NORESAMPLE';

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

Альтернативные методы в MS SQL Server

Вместо глобального sp_updatestats можно использовать более целенаправленные команды.

UPDATE STATISTICS: Позволяет обновить статистику для конкретной таблицы или индекса. Обеспечивает детальный контроль (уровень выборки, количество потоков).

UPDATE STATISTICS Sales.SalesOrderHeader WITH SAMPLE 50 PERCENT;

sys.sp_updatestats (недокументированная): Более старая версия процедуры, поведение может отличаться. Использовать не рекомендуется.

Автоматическое обновление статистики: SQL Server может обновлять статистику автоматически при включенных опциях AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS. Это предпочтительный метод для большинства рабочих нагрузок, а sp_updatestats служит для разовых операций после больших изменений данных.

Аналоги в других СУБД

MySQL / MariaDB: Аналогом является команда ANALYZE TABLE. Она собирает статистику по ключам для указанной таблицы.

ANALYZE TABLE employees;

+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| test.employees   | analyze | status   | OK       |
+------------------+---------+----------+----------+

Oracle: Для сбора статистики используется пакет DBMS_STATS. Его процедура GATHER_DATABASE_STATS похожа на sp_updatestats.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PostgreSQL: Применяется команда ANALYZE. Без параметров она собирает статистику по всем таблицам текущей базы.

ANALYZE VERBOSE;

INFO:  analyzing "public.orders"
INFO:  "orders": scanned 30000 of 30000 pages...
ANALYZE

SQLite: Статистика для оптимизатора собирается командой ANALYZE.

ANALYZE;

Главное отличие MS SQL: процедура sp_updatestats является серверной хранимой процедурой с конкретным именем, тогда как в других СУБД чаще используется стандартизированный оператор SQL ANALYZE.

Распространенные ошибки

Ошибка 1: Попытка выполнения в контексте системной базы данных master. Процедура предназначена для пользовательских баз данных.

USE master;
GO
EXEC sp_updatestats;

Сообщения об обновлении статистики для системных таблиц. Это не является стандартной практикой и может быть нежелательно.

Ошибка 2: Блокировки и конфликты. Длительное выполнение процедуры на большой таблице может удерживать блокировки и мешать другим операциям.

Ошибка 3: Неверное значение параметра. Передача некорректного значения аргументу @resample приводит к ошибке.

EXEC sp_updatestats @resample = 'WRONG_VALUE';

Msg 102, Level 15, State 1, Procedure sp_updatestats, Line 185 [Batch Start Line 0]
Incorrect syntax near 'WRONG_VALUE'.

Ошибка 4: Недостаточно прав. Для выполнения процедуры требуется членство в предопределенной роли базы данных db_owner или разрешение ALTER на все таблицы.

История изменений

В SQL Server 2016 (13.x) и более поздних версиях процедура была усовершенствована для работы с фоновой задачей обновления статистики. Процедура больше не затрагивает статистику, которая уже находится в очереди на фоновое обновление, что снижает конфликты и избыточную работу.

Начиная с SQL Server 2016, процедура по умолчанию выполняет обновление с опцией FULLSCAN. В более ранних версиях (до SQL Server 2016) поведение по умолчанию могло отличаться и зависеть от размера таблицы.

Изменений в синтаксисе или аргументах процедуры в последних основных версиях (2017, 2019, 2022) не было.

Расширенные сценарии применения

Пример 1: Обновление статистики в цикле для всех пользовательских баз данных на экземпляре сервера.

DECLARE @db_name SYSNAME;
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0; -- Пользовательские базы

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Обновление статистики в базе: ' + @db_name;
    EXEC('USE [' + @db_name + ']; EXEC sp_updatestats;');
    FETCH NEXT FROM db_cursor INTO @db_name;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

Пример 2: Логирование результатов выполнения процедуры во временную таблицу для последующего анализа.

CREATE TABLE #UpdateStatsLog (
    LogTime DATETIME,
    TableName NVARCHAR(255),
    MessageText NVARCHAR(MAX)
);

INSERT INTO #UpdateStatsLog (LogTime, TableName, MessageText)
EXEC sp_updatestats;

SELECT * FROM #UpdateStatsLog ORDER BY LogTime;

Пример 3: Комбинация с проверкой устаревания статистики. Сначала находится статистика, которая потенциально устарела (большое количество изменений), затем для соответствующих таблиц выполняется целевое обновление, а не для всей базы.

-- Поиск таблиц с более чем 1000 изменений с последнего обновления статистики
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatsName,
    sp.modification_counter AS ModCount
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 1000
ORDER BY sp.modification_counter DESC;

-- Для этих таблиц можно запустить sp_updatestats для всей базы,
-- либо точечно обновить статистику через UPDATE STATISTICS.

Пример 4: Использование внутри планировщика заданий SQL Server Agent для регулярного выполнения в период низкой нагрузки, например, каждое воскресенье в 2:00 ночи. Это позволяет поддерживать статистику в актуальном состоянии, дополняя работу автоматического обновления.