SET STATISTICS IO: примеры (SQL)
SET STATISTICS IO(ON | OFF): N/AОписание команды SET STATISTICS IO
SET STATISTICS IO не является функцией в классическом понимании, это инструкция Transact-SQL, управляющая выводом статистической информации о активности дискового ввода-вывода для инструкций языка Transact-SQL. Её основное назначение — анализ производительности запросов, предоставление количественных данных о логических и физических операциях чтения данных с диска.
Использование этой инструкции актуально при профилировании и оптимизации запросов, когда необходимо оценить ресурсоёмкость операций и выявить узкие места, связанные с доступом к данным.
Инструкция принимает один параметр, который может находиться в двух состояниях:
- ON: включает вывод статистики ввода-вывода. После активации для каждой последующей выполняемой команды в том же сеансе будет выводиться отдельное сообщение с метриками.
- OFF: отключает вывод статистики. Это состояние установлено по умолчанию.
Результатом выполнения команды является не табличный набор данных, а текстовое сообщение в разделе 'Сообщения' среды SQL Server Management Studio. Оно содержит следующие ключевые метрики:
- Таблица 'ИмяТаблицы': указывает обработанную таблицу.
- число просмотров: количество логических операций чтения (логических чтений), выполненных с момента загрузки страницы данных в кэш буферного пула.
- число логических чтений: часто совпадает с 'число просмотров' и также обозначает логические чтения.
- число физических чтений: количество операций физического чтения с диска, когда требуемая страница не была найдена в буферном кэше.
- число упреждающих чтений: отражает активность механизма упреждающего чтения, который асинхронно загружает страницы в кэш.
- LOB-число логических чтений: логические чтения для страниц больших объектов (LOB), таких как TEXT, NTEXT, IMAGE, VARCHAR(MAX).
- LOB-число физических чтений: физические чтения для страниц LOB.
- LOB-число упреждающих чтений: упреждающие чтения для страниц LOB.
Базовые примеры использования
Пример включения статистики, выполнения простого запроса и её отключения.
SET STATISTICS IO ON;
GO
SELECT * FROM [Sales].[SalesOrderHeader] WHERE OrderDate > '2013-12-31';
GO
SET STATISTICS IO OFF;
GO(31465 строк затронуто) Таблица "SalesOrderHeader". Число просмотров = 1, число логических чтений = 689, число физических чтений = 0, число упреждающих чтений = 0, LOB-число логических чтений = 0, LOB-число физических чтений = 0, LOB-число упреждающих чтений = 0.
Пример, демонстрирующий разницу в статистике до и после создания индекса (симуляция).
SET STATISTICS IO ON;
GO
-- Запрос без подходящего индекса
SELECT CustomerID FROM [Sales].[SalesOrderHeader] WHERE SalesOrderNumber = 'SO43697';
GO
-- После условного создания индекса...
SELECT CustomerID FROM [Sales].[SalesOrderHeader] WHERE SalesOrderNumber = 'SO43697';
GO
SET STATISTICS IO OFF;(1 строка затронута) Таблица "SalesOrderHeader". Число просмотров = 1, число логических чтений = 689, число физических чтений = 0, ... (1 строка затронута) Таблица "SalesOrderHeader". Число просмотров = 1, число логических чтений = 3, число физических чтений = 0, ...
Похожие инструменты анализа в MS SQL Server
SET STATISTICS TIME: выводит данные о времени, затраченном на синтаксический анализ, компиляцию и выполнение каждого запроса. Используется совместно со STATISTICS IO для комплексного анализа производительности.
Динамические административные представления (DMV): предоставляют более структурированный и гибкий способ получения статистики. Например, sys.dm_exec_query_stats кэширует агрегированную статистику по планам выполнения, включая логические и физические операции ввода-вывода. Предпочтительны для программного анализа и мониторинга.
Расширенные события (Extended Events): предлагают низкоуровневую и настраиваемую систему сбора широкого спектра данных о выполнении запросов, включая события, связанные с вводом-выводом. Используются для глубокой диагностики сложных проблем.
SQL Server Profiler / Trace: устаревшие, но иногда используемые инструменты для трассировки событий сервера, включая события завершения запросов с показателями Reads и Writes, которые аналогичны логическим чтениям.
Типичные ошибки и недопонимания
1. Интерпретация 'физических чтений' как текущей нагрузки на диск: Значение 'число физических чтений' может быть равно нулю при повторном выполнении запроса, так как данные уже находятся в кэше буферного пула. Для оценки реальной нагрузки на подсистему хранения необходим анализ первого выполнения после очистки кэша или перезапуска службы.
2. Игнорирование статистики для LOB-данных: При работе с большими полями (VARCHAR(MAX), VARBINARY(MAX)) основные метрики ('число логических чтений') могут не отражать полный объём работы. Необходимо отдельно анализировать строки 'LOB-число логических/физических чтений'.
3. Попытка получить результат в табличном виде: Вывод STATISTICS IO направляется только в поток сообщений. Его нельзя напрямую выбрать в таблицу с помощью SELECT. Для программной обработки требуется использовать альтернативы, такие как динамические административные представления (DMV), или парсить текст сообщений.
-- Неправильно: не вернёт статистику как результирующий набор.
SELECT * FROM (SET STATISTICS IO ON; SELECT 1 AS A) T;Сообщение 156, уровень 15, состояние 1, строка 1 Неправильный синтаксис около ключевого слова 'SET'.
4. Сброс статистики кэша: Иногда для чистоты эксперимента пытаются сбросить кэш с помощью CHECKPOINT; DBCC DROPCLEANBUFFERS; на тестовом сервере. Важно помнить, что это действие влияет на всю базу данных и недопустимо в производственной среде.
Изменения в последних версиях SQL Server
Основной синтаксис и поведение SET STATISTICS IO остаются стабильными на протяжении многих версий. Однако, выходные данные были расширены с введением типов данных больших объектов (LOB).
В SQL Server 2012 и более поздних версиях формат вывода был дополнен строками, относящимися к операциям ввода-вывода для страниц LOB ('LOB-число логических чтений' и т.д.), что позволяет точнее анализировать запросы, работающие с большими данными.
Поведение и интерпретация метрик также зависят от архитектурных улучшений ядра СУБД, таких как изменения в алгоритмах упреждающего чтения или работа с колоночными индексами, но это не отражается на синтаксисе самой инструкции.
Расширенные примеры анализа
1. Сравнение эффективности различных типов соединений.
SET STATISTICS IO ON;
GO
-- Хэш-соединение (часто при отсутствии индексов)
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID;
GO
-- Вложенные циклы (чаще с индексами)
SELECT *
FROM Sales.SalesOrderDetail sod WITH (INDEX(IX_SalesOrderDetail_ProductID))
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID;
GO
SET STATISTICS IO OFF;-- Для первого запроса (Hash Match) Таблица "Product". Число просмотров = 1, число логических чтений = 15, ... Таблица "SalesOrderDetail". Число просмотров = 1, число логических чтений = 1246, ... -- Для второго запроса (Nested Loops) Таблица "Product". Число просмотров = 1, число логических чтений = 15, ... Таблица "SalesOrderDetail". Число просмотров = 1, число логических чтений = 1, ... (для поиска по индексу) Таблица "SalesOrderDetail". Число просмотров = 1, число логических чтений = 1216, ... (доп. чтения для вывода данных)
2. Анализ влияния выборки данных на статистику.
SET STATISTICS IO ON;
GO
-- Полное сканирование таблицы
SELECT COUNT(*) FROM [Production].[TransactionHistory];
GO
-- Поиск по индексу
SELECT COUNT(*) FROM [Production].[TransactionHistory] WHERE TransactionDate BETWEEN '2013-01-01' AND '2013-01-31';
GO
SET STATISTICS IO OFF;-- Полное сканирование Таблица "TransactionHistory". Число просмотров = 1, число логических чтений = 108, ... -- Поиск по диапазону Таблица "TransactionHistory". Число просмотров = 1, число логических чтений = 3, ... (чтение индексных страниц) Таблица "TransactionHistory". Число просмотров = 1, число логических чтений = 2, ... (чтение данных по ключу)
3. Выявление проблем с параметризацией и 'прогревом' кэша.
-- Очистка кэша (ТОЛЬКО для тестового сервера!)
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS IO ON;
GO
-- Первый запуск с новым значением параметра
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 11000;
GO
-- Повторный запуск с тем же значением
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 11000;
GO
-- Запуск с другим значением (может использовать другой план)
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 30118;
GO
SET STATISTICS IO OFF;-- Первый запуск: возможны физические чтения. Таблица "SalesOrderHeader". ... число физических чтений = 45, ... -- Второй запуск: все данные в кэше, физических чтений 0. Таблица "SalesOrderHeader". ... число физических чтений = 0, ... -- Третий запуск: статистика зависит от распределения данных и плана выполнения.
Аналоги в других СУБД и системах
PostgreSQL: Используется команда EXPLAIN (ANALYZE, BUFFERS). Ключевое слово BUFFERS добавляет вывод информации о чтении буферов (аналог логических чтений). Разделение на физические/логические чтения менее явное.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date > '2013-12-31';... Buffers: shared hit=523 read=689 ...
Oracle: Статистику ввода-вывода можно получить, включив трассировку на уровне сессии командой ALTER SESSION SET STATISTICS_LEVEL = ALL; или используя хинт /*+ GATHER_PLAN_STATISTICS */, после чего запросить план выполнения из V$SQL_PLAN_STATISTICS_ALL. Более простой способ — использование SET AUTOTRACE TRACEONLY STATISTICS в SQL*Plus.
SET AUTOTRACE TRACEONLY STATISTICS;
SELECT * FROM scott.emp WHERE deptno = 10;Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
...
MySQL: Для анализа производительности запросов используется команда EXPLAIN ANALYZE (доступна с версии 8.0.18), которая выводит информацию о фактическом выполнении, включая итерации и стоимость, но без детализации по типу операций ввода-вывода, характерной для SQL Server. Более детальную статистику можно получить из Performance Schema или статус-переменных.
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2013-12-31';-> Filter: (orders.order_date > TIMESTAMP'2013-12-31 00:00:00') (cost=... rows=... actual time=... rows=... loops=1)
-> Table scan on orders (cost=... rows=... actual time=... rows=... loops=1)
SQLite: Практически отсутствуют встроенные средства для получения детальной статистики ввода-вывода. Команда EXPLAIN QUERY PLAN показывает только логический план выполнения без метрик производительности.