sys.dm_db_index_operational_stats (Transact-SQL)

Статья
06/18/2013

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

Значок ссылки на раздел Синтаксические обозначения Transact-SQL

Синтаксис

sys.dm_db_index_operational_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | 0 | NULL | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
)

Аргументы

database_id| NULL | 0 | DEFAULT
Идентификатор базы данных. Аргумент database_id имеет тип smallint. Допустимыми входными значениями являются идентификатор базы данных, NULL, 0 или DEFAULT. Значение по умолчанию равно 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите значение NULL, чтобы вернуть сведения для всех баз данных в экземпляре SQL Server. Если будет указано значение NULL для аргумента database_id, также необходимо указать значение NULL для аргументов object_id, index_id и partition_number.

Может быть указана встроенная функция DB_ID. При использовании функции DB_ID без указания имени базы данных уровень совместимости текущей базы данных должен быть равен 90.
object_id | NULL | 0 | DEFAULT
Идентификатор объекта таблицы или представления, которые содержат индекс. Аргумент object_id имеет тип int.

Допустимыми входными значениями являются идентификатор таблицы, NULL, 0 или DEFAULT. Значение по умолчанию равно 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите значение NULL, чтобы вернуть кэшированные данные для всех таблиц и представлений в указанной базе данных. Если будет указано значение NULL для аргумента object_id, также необходимо указать значение NULL для аргументов index_id и partition_number.
index_id | 0 | NULL |-1 | DEFAULT
Идентификатор индекса. Аргумент index_id имеет тип int. Допустимыми входными значениями являются идентификатор индекса, 0 — если аргумент object_id является кучей, NULL, -1 или DEFAULT. Значение по умолчанию равно -1. Значения NULL, -1 и DEFAULT в данном контексте эквивалентны.

Укажите значение NULL, чтобы вернуть кэшированные данные для всех индексов базовой таблицы или представления. Если будет указано значение NULL для аргумента index_id, также необходимо указать значение NULL для аргумента partition_number.
partition_number | NULL | 0 | DEFAULT
Номер секции в объекте. Аргумент partition_number имеет тип int. Допустимыми входными значениями являются номер partion_number индекса или кучи, NULL, 0 или DEFAULT. Значение по умолчанию равно 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.

Укажите NULL, чтобы возвратить кэшированные данные для всех секций индекса или кучи.

Аргумент partition_number имеет нумерацию, которая начинается с 1. Несекционированный индекс или куча имеет аргумент partition_number, установленный в 1.

Возвращаемая таблица

Имя столбца	Тип данных	Описание
database_id	smallint	Идентификатор базы данных.
object_id	int	Идентификатор таблицы или представления.
index_id	int	Идентификатор индекса или кучи. 0 = куча
partition_number	int	Номер секции внутри индекса или кучи (нумерация начинается с 1).
leaf_insert_count	bigint	Совокупное количество вставок конечного уровня.
leaf_delete_count	bigint	Совокупное количество удалений конечного уровня.
leaf_update_count	bigint	Совокупное количество обновлений конечного уровня.
leaf_ghost_count	bigint	Совокупное количество строк конечного уровня, которые помечены как удаленные, но еще не удалены. Эти строки будут удалены потоком очистки в установленные интервалы времени. Это значение не включает строки, которые сохранены, из-за необработанной транзакции изоляции моментальных снимков.
nonleaf_insert_count	bigint	Совокупное количество вставок выше конечного уровня. 0 = куча или columnstore
nonleaf_delete_count	bigint	Совокупное количество удалений выше конечного уровня. 0 = куча или columnstore
nonleaf_update_count	bigint	Совокупное количество обновлений выше конечного уровня. 0 = куча или columnstore
leaf_allocation_count	bigint	Совокупное количество размещений страниц конечного уровня в индексе или куче. Для индекса размещение страницы соответствует разбиению страницы.
nonleaf_allocation_count	bigint	Совокупное количество размещений страниц, вызванных разбиениями страниц выше конечного уровня. 0 = куча или columnstore
leaf_page_merge_count	bigint	Совокупное количество слияний страниц на конечном уровне. Всегда 0 для индекса columnstore.
nonleaf_page_merge_count	bigint	Совокупное количество слияний страниц выше конечного уровня. 0 = куча или columnstore
range_scan_count	bigint	Совокупное количество просмотров диапазонов и таблиц, запущенных на индексе или куче.
singleton_lookup_count	bigint	Совокупное количество извлечений одиночных строк из индекса или кучи.
forwarded_fetch_count	bigint	Число строк, выбранных через перенаправляющую запись. 0 = индексы
lob_fetch_in_pages	bigint	Совокупное количество страниц больших объектов (LOB), извлеченных из единицы распределения LOB_DATA. Эти страницы содержат данные, которые хранятся в столбцах типа text, ntext, image, varchar(max), nvarchar(max), varbinary(max) и xml. Дополнительные сведения см. в разделе Типы данных (Transact-SQL).
lob_fetch_in_bytes	bigint	Совокупное количество извлеченных байтов данных LOB.
lob_orphan_create_count	bigint	Совокупное количество потерянных значений LOB, созданных для массовых операций. 0 = некластеризованный индекс
lob_orphan_insert_count	bigint	Совокупное количество потерянных значений LOB, вставленных во время массовых операций. 0 = некластеризованный индекс
row_overflow_fetch_in_pages	bigint	Совокупное количество превышающих размер страницы данные строки, извлеченных из единицы распределения ROW_OVERFLOW_DATA. Эти страницы содержат данные, сохраненные в столбцах типа varchar(n), nvarchar(n), varbinary(n) и sql_variant, которые были принудительно отправлены вне строки.
row_overflow_fetch_in_bytes	bigint	Совокупное количество извлеченных байтов, превышающих размер страницы данные строки.
column_value_push_off_row_count	bigint	Совокупное количество значений столбца для данных LOB и превышающих размер страницы данные строки, которые вытесняются из строки, чтобы вместить на странице вставленную или обновленную строку.
column_value_pull_in_row_count	bigint	Совокупное количество значений столбцов для данных LOB и превышающих размер страницы данные строки, которые помещаются в строку. Это происходит, когда операция обновления освобождает пространство в записи и предоставляет возможность поместить одно или несколько выходящих за пределы строки значений из единиц распределения LOB_DATA или ROW_OVERFLOW_DATA в единицу распределения IN_ROW_DATA.
row_lock_count	bigint	Совокупное количество запрошенных блокировок строк.
row_lock_wait_count	bigint	Совокупное количество раз, когда компонент Компонент Database Engine ожидал блокировки строки.
row_lock_wait_in_ms	bigint	Общее время в миллисекундах, которое компонент Компонент Database Engine ожидал блокировки строки.
page_lock_count	bigint	Совокупное количество запрошенных блокировок страниц.
page_lock_wait_count	bigint	Совокупное количество раз, которое компонент Компонент Database Engine ожидал блокировки страницы.
page_lock_wait_in_ms	bigint	Общее время в миллисекундах, которое компонент Компонент Database Engine ожидал блокировки страницы.
index_lock_promotion_attempt_count	bigint	Совокупное количество раз, которое компонент Компонент Database Engine пытался повышать уровень блокировок.
index_lock_promotion_count	bigint	Совокупное количество раз, которое компонент Компонент Database Engine повышал уровень блокировок.
page_latch_wait_count	bigint	Совокупное количество раз, когда компонент Компонент Database Engine ожидал из-за конфликтов кратковременной блокировки.
page_latch_wait_in_ms	bigint	Совокупное количество миллисекунд, которое компонент Компонент Database Engine ожидал из-за конфликтов кратковременной блокировки.
page_io_latch_wait_count	bigint	Совокупное количество раз, когда компонент Компонент Database Engine ожидал кратковременной блокировки страницы ввода-вывода.
page_io_latch_wait_in_ms	bigint	Совокупное количество миллисекунд, которое компонент Компонент Database Engine ожидал кратковременной блокировки страницы ввода-вывода.
tree_page_latch_wait_count	bigint	Подмножество множества page_latch_wait_count, которое включает только страницы сбалансированного дерева верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_latch_wait_in_ms	bigint	Подмножество множества page_latch_wait_in_ms, которое включает только страницы сбалансированного дерева верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_io_latch_wait_count	bigint	Подмножество множества page_io_latch_wait_count, которое включает только страницы сбалансированного дерева верхнего уровня. Всегда 0 для кучи или индекса columnstore.
tree_page_io_latch_wait_in_ms	bigint	Подмножество множества page_io_latch_wait_in_ms, которое включает только страницы сбалансированного дерева верхнего уровня. Всегда 0 для кучи или индекса columnstore.
page_compression_attempt_count	bigint	Количество страниц, которые были оценены как пригодные для сжатия на уровне страницы для конкретных секций таблицы, индекса или индексированного представления. Включает несжатые страницы, поскольку это не привело бы к значительной экономии. Всегда 0 для индекса columnstore.
page_compression_success_count	bigint	Количество страниц данных, которые были сжаты с помощью сжатия PAGE для конкретной секции таблицы, индекса или индексированного представления. Всегда 0 для индекса columnstore.

Замечания

Этот объект DMO не принимает коррелированные параметры из CROSS APPLY и OUTER APPLY.

Для отслеживания продолжительности ожидания пользователями считывания из таблицы, индекса или секции и записи в таблицу, индекс или секцию, а также для определения таблиц или индексов, в которых наблюдается значительная интенсивность операций ввода-вывода или присутствуют перегруженные участки, можно использовать представление sys.dm_db_index_operational_stats.

Используйте следующие столбцы для идентификации областей состязаний.

Анализ типичного шаблона доступа к секции таблицы или индекса

leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count

Чтобы идентифицировать состязание кратковременных и обычных блокировок, используйте следующие столбцы.

page_latch_wait_count и page_latch_wait_in_ms

Эти столбцы показывают наличие состязания кратковременной блокировки в индексе или куче и значимость конфликта.
row_lock_count и page_lock_count

Эти столбцы указывают, сколько раз компонент Компонент Database Engine пытался получить блокировки строк и страниц.
row_lock_wait_in_ms и page_lock_wait_in_ms

Эти столбцы показывают наличие состязания блокировок в индексе или куче и значимость состязания.

Анализ статистики физического ввода-вывода на индексе или секции кучи

page_io_latch_wait_count и page_io_latch_wait_in_ms

Эти столбцы указывают, были ли произведены физические операции ввода-вывода, чтобы занести страницы индекса или кучи в память, и сколько операций ввода-вывода было произведено.

Примечания по столбцам

Значения в lob_orphan_create_count и lob_orphan_insert_count всегда должны быть равны.

Значение в столбцах lob_fetch_in_pages и lob_fetch_in_bytes может быть больше нуля для некластеризованных индексов, содержащих один или более LOB-столбцов в качестве включенных. Дополнительные сведения см. в разделе Создание индексов с включенными столбцами. Аналогично значение в столбцах row_overflow_fetch_in_pages и row_overflow_fetch_in_bytes может быть больше 0 для некластеризованных индексов, если индекс содержит столбцы, которые могут быть принудительно отправлены вне строки.

Переустановка счетчиков в кэше метаданных

Данные, возвращенные sys.dm_db_index_operational_stats, существуют до тех пор, пока объект кэша метаданных, представляющий кучу или индекс, является доступным. Эти данные не являются постоянными и не согласованы на уровне транзакций. Это означает, что эти счетчики не позволяют определить факт использования индекса или время, когда индекс применялся последний раз. Дополнительные сведения об этом см. в разделе sys.dm_db_index_usage_stats (Transact-SQL).

Значения для каждого столбца устанавливаются в нуль всякий раз, когда метаданные для кучи или индекса заносятся в кэш метаданных, и статистические данные накапливаются, пока объект кэша не удаляется из кэша метаданных. Поэтому активная куча или индекс будут, вероятно, всегда иметь эти метаданные в кэше, и совокупные значения количества могут отражать активность с момента последнего запуска экземпляра SQL Server. Метаданные для менее активной кучи или индекса будут перемещаться в кэш и из него по мере их использования. В результате метаданные могут иметь или не иметь действительных значений. Удаление индекса приведет к удалению соответствующих статистических данных из памяти, и они больше не будут передаваться функцией. При других DDL-операциях с индексом может произойти обнуление статистических данных.

Использование системных функций для указания значений параметра

Для указания значений параметров database_id и object_id можно использовать функции языка Transact-SQL DB_ID и OBJECT_ID. Однако передавая значения, которые не допустимы для этих функций, можно получить неожиданные результаты. Всегда следует проверять, что функции DB_ID и OBJECT_ID возвращают допустимый идентификатор. Дополнительные сведения см. в подразделе «Примечания» раздела sys.dm_db_index_physical_stats (Transact-SQL).

Разрешения

Требуются следующие разрешения:

Разрешение CONTROL на указанный объект в базе данных.
Разрешение VIEW DATABASE STATE для возврата сведений обо всех объектах в пределах указанной базы данных с помощью использования шаблона базы данных @object\_id = NULL.
Разрешение VIEW SERVER STATE для получения сведений обо всех базах данных с использованием символа-шаблона @database\_id = NULL.

Предоставление разрешения VIEW DATABASE STATE позволяет всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, запрещенных на определенные объекты.

Запрет разрешения VIEW DATABASE STATE запрещает всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, предоставленных на определенные объекты. Кроме того, если для базы данных указан шаблон @database\_id=NULL, эта база данных пропускается.

Дополнительные сведения см. в разделе Динамические административные представления и функции (Transact-SQL).

Примеры

А.Возвращение данных для указанной таблицы

В следующем примере возвращаются сведения по всем индексам и секциям таблицы Person.Address в базе данных AdventureWorks2012. Выполнение этого запроса требует как минимум разрешения CONTROL на таблицу Person.Address.

Важно!
При использовании Transact-SQL функций DB_ID и OBJECT_ID для возврата значения параметра необходимо убедиться в правильности возвращаемого идентификатора. Если имя базы данных или объекта не может быть найдено, например если база данных или объект не существуют или неправильно записаны, то обе функции возвратят значение NULL. Функция sys.dm_db_index_operational_stats интерпретирует значение NULL как значение шаблона, указывающего все базы данных или все объекты. Так как эта операция может быть непреднамеренной, примеры в этом разделе демонстрируют безопасный способ определения идентификаторов базы данных и объекта.

При использовании Transact-SQL функций DB_ID и OBJECT_ID для возврата значения параметра необходимо убедиться в правильности возвращаемого идентификатора. Если имя базы данных или объекта не может быть найдено, например если база данных или объект не существуют или неправильно записаны, то обе функции возвратят значение NULL. Функция sys.dm_db_index_operational_stats интерпретирует значение NULL как значение шаблона, указывающего все базы данных или все объекты. Так как эта операция может быть непреднамеренной, примеры в этом разделе демонстрируют безопасный способ определения идентификаторов базы данных и объекта.

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL 
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Б.Возвращение сведений для всех таблиц и индексов

В следующем примере возвращаются сведения по всем таблицам и индексам в экземпляре SQL Server. Выполнение этого запроса требует разрешения VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO

См. также

Справочник

Динамические административные представления и функции (Transact-SQL)

Динамические административные представления и функции, связанные с индексом (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.dm_db_index_usage_stats (Transact-SQL)

sys.dm_os_latch_stats (Transact-SQL)

sys.dm_db_partition_stats (Transact-SQL)

sys.allocation_units (Transact-SQL)

sys.indexes (Transact-SQL)

Основные понятия

Наблюдение и настройка производительности

Поделиться через