sys.dm_db_index_operational_stats (Transact-SQL)

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

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

Синтаксические обозначения в 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.

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

Замечания

Этот объект 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 как значение шаблона, указывающего все базы данных или все объекты.Так как эта операция может быть непреднамеренной, примеры в этом разделе демонстрируют безопасный способ определения идентификаторов базы данных и объекта.

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)

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

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