UPDATE STATISTICS: примеры (SQL)

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

Обновление всей статистики для таблицы с полным сканированием:

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN;

Команда выполнена успешно.

Обновление статистики для конкретного индекса с выборкой 30 процентов:

UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail_ProductID WITH SAMPLE 30 PERCENT;

Команда выполнена успешно.

Обновление статистики только по столбцам с отключением автообновления:

UPDATE STATISTICS Sales.SalesOrderDetail WITH COLUMNS, NORECOMPUTE;

Команда выполнена успешно.

Обновление статистики для индексированного представления:

UPDATE STATISTICS dbo.vIndexedView WITH RESAMPLE;

Команда выполнена успешно.

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

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

EXEC sp_updatestats;

CREATE/ALTER INDEX ... WITH (STATISTICS_NORECOMPUTE = ON) - параметр при создании или изменении индекса, который отключает автоматическое обновление статистики для этого индекса. Полезен для статических таблиц, но требует ручного управления.

Автоматическое обновление статистики - функция SQL Server, включенная по умолчанию. Статистика обновляется автоматически при изменении более 20% строк (для таблиц больше 500 строк). Иногда это поведение требует отключения для очень больших таблиц.

Выбор метода: для планового обслуживания больших баз часто используют UPDATE STATISTICS с SAMPLE в рамках задач обслуживания. sp_updatestats подходит для быстрого обновления всей базы после массовой загрузки данных. Автоматическое обновление эффективно для динамических таблиц среднего размера.

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

PostgreSQL: команда ANALYZE. Выполняет сбор статистики для оптимизатора. Не имеет прямых аналогов параметров SAMPLE или FULLSCAN, но может быть запущена для конкретной таблицы.

ANALYZE sales.order_details;

ANALYZE

Oracle: используется пакет DBMS_STATS. Гораздо более гибкий, позволяет собирать статистику на уровне схемы, таблицы, индекса, с различными методами выборки.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', estimate_percent => 15);

PL/SQL procedure successfully completed.

MySQL: команда ANALYZE TABLE. Обновляет статистику, хранящуюся для таблицы и индексов.

ANALYZE TABLE orders;

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

SQLite: команда ANALYZE. Создает или обновляет внутреннюю таблицу sqlite_stat1 для хранения статистики. Может быть запущена для всей базы, конкретной таблицы или индекса.

ANALYZE main.products;

Основное отличие MS SQL - более детальный контроль над объемом сканирования (FULLSCAN/SAMPLE) и типом обновляемой статистики (ALL/COLUMNS/INDEX). В Oracle и PostgreSQL управление статистикой часто более комплексное и тесно интегрировано в процессы управления базой данных.

Типичные ошибки

Использование команды в транзакции с последующим откатом. UPDATE STATISTICS является физической операцией, ее эффект нельзя откатить с помощью ROLLBACK, если не использовать явное начало транзакции, но это нестандартно.

BEGIN TRANSACTION;
UPDATE STATISTICS dbo.LargeTable WITH FULLSCAN; -- Долгая операция
-- ... другие действия
ROLLBACK; -- Откат не вернет ресурсы, затраченные на сбор статистики

Блокировки. По умолчанию UPDATE STATISTICS использует совместимые блокировки, но может конфликтовать с длительными операциями изменения данных. Это может приводить к ожиданиям или блокировкам. Использование параметра WITH RESUMABLE может смягчить проблему для очень больших таблиц.

Чрезмерное использование FULLSCAN на больших таблицах. Может вызвать проблемы с производительностью и нагрузкой на ввод-вывод. Для больших таблиц рекомендуется использовать SAMPLE.

-- Потенциально проблемный запрос для таблицы в 1ТБ
UPDATE STATISTICS dbo.HugeTable WITH FULLSCAN;

Выполнение может занять многие часы и создать нагрузку на диск.

Обновление статистики для временной таблицы. Статистика по временным таблицам создается автоматически. Явное обновление возможно, но требует использования двухзначного префикса (например, #temp) и может быть неэффективным из-за короткого времени жизни таблицы.

Изменения в новых версиях

В SQL Server 2014 появился параметр INCREMENTAL для секционированных таблиц. Он позволяет обновлять статистику не для всей таблицы, а только для измененных секций, что значительно ускоряет процесс для больших секционированных таблиц.

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

В SQL Server 2019 и более новых версиях улучшена логика для управления памятью при выполнении UPDATE STATISTICS, особенно при работе с таблицами, имеющими кластеризованный индекс columnstore.

Для Azure SQL Database и управляемых экземпляров постоянно вносятся оптимизации в фоновые процессы работы со статистикой, обеспечивая высокую производительность без необходимости частого ручного вмешательства.

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

Использование UPDATE STATISTICS с просмотром результатов через системные представления. Можно проверить дату последнего обновления статистики.

-- Обновляем статистику
UPDATE STATISTICS Production.Product WITH SAMPLE 50 PERCENT;

-- Проверяем дату обновления
SELECT 
    name AS StatsName,
    STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats 
WHERE object_id = OBJECT_ID('Production.Product');

StatsName                     LastStatsUpdate
------------------------------ -----------------------
PK_Product_ProductID          2023-10-26 14:35:22.120
AK_Product_ProductNumber      2023-10-26 14:35:22.120
...                           ...

Обновление статистики для фильтрованного индекса. Статистика для фильтрованных индексов собирается только на основе строк, удовлетворяющих условию фильтра.

CREATE INDEX IX_ActiveProducts ON Production.Product (Name) WHERE DiscontinuedDate IS NULL;
-- Обновляем статистику именно для этого индекса
UPDATE STATISTICS Production.Product IX_ActiveProducts WITH FULLSCAN;

Комбинирование параметров: обновление инкрементальной статистики для секционированной таблицы с отключением автообновления.

UPDATE STATISTICS dbo.PartitionedSalesData 
WITH INCREMENTAL = ON, NORECOMPUTE, SAMPLE 10 PERCENT;

Использование RESAMPLE для наследования плотности выборки. Если для статистики ранее задавалась выборка в 5%, RESAMPLE повторит эту выборку.

-- Первоначальное создание статистики с выборкой
UPDATE STATISTICS dbo.BigTable (IDX_BigTable) WITH SAMPLE 5 PERCENT;
-- ... прошло время
-- Последующее обновление с той же плотностью выборки
UPDATE STATISTICS dbo.BigTable (IDX_BigTable) WITH RESAMPLE;

Принудительное обновление статистики, помеченной как устаревшая, через sys.dm_db_stats_properties.

DECLARE @stats_name NVARCHAR(128);
DECLARE @table_name NVARCHAR(128) = 'Sales.SalesOrderDetail';

DECLARE stats_cursor CURSOR FOR
SELECT s.name 
FROM sys.stats s 
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID(@table_name)
    AND sp.modification_counter > sp.rows * 0.20 -- Более 20% изменений

OPEN stats_cursor;
FETCH NEXT FROM stats_cursor INTO @stats_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('UPDATE STATISTICS ' + @table_name + ' ' + @stats_name + ' WITH SAMPLE 30 PERCENT;');
    FETCH NEXT FROM stats_cursor INTO @stats_name;
END
CLOSE stats_cursor;
DEALLOCATE stats_cursor;