sys.dm_db_index_operational_stats (Transact-SQL)

Статья
03/07/2014

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

Индексы с оптимизацией для памяти в данном DMV не отображаются.

Примечание

sys.dm_db_index_operational_stats не возвращает сведения об индексах, оптимизированных для памяти.Сведения об оптимизированных для памяти индексах см. в разделе sys.dm_db_xtp_index_stats (Transact-SQL).

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (с первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Синтаксические обозначения в 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.
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)

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

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

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