Различия между sys.dm_db_index_usage_stats и sys.dm_db_index_operational_stats

Автор: Craig Freedman What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

В SQL Server есть два административных представления (DMV) - sys.dm_db_index_usage_stats и sys.dm_db_index_operational_stats - которые очень полезны для мониторинга использования индексов. Оба DMV выдают схожие статистические данные о количестве просмотров, поисков и изменений в индексах. Однако важно понимать разницу между ними.

Главное различие между этими DMV:

sys.dm_db_index_usage_stats показывает сколько раз оптимизатор запросов использует индекс в плане. Факт использования индекса фиксируется каждый раз при исполнении плана, но не учитываются перебираемые планы, которые отброшены оптимизатором. Однако, и это важно, для статистики имеет значение, сколько раз процессор запросов выполняет ссылающийся на индекс оператор. А тут получается, что не имеет значения, будет ли процессор запросов выполнять этот оператор вообще, поскольку сам факт выполнение плана засчитывается как использование каждого используемого в плане индекса.

sys.dm_db_index_operational_stats показывает, сколько раз выполнялись операторы с индексом. Эта статистика учитывает то, сколько раз процессор запросов выполняет каждый такой оператор. Если оператор в плане запроса не выполнялся, считается, что никаких операций с индексом не было, а DMV покажет, что индекс не использовался. Если оператор выполняется в плане несколько раз, будет засчитано несколько операций над индексом, и DMV сообщит, что индекс использовался несколько раз.

Давайте рассмотрим пример, который покажет эту разницу в действии. Будем использовать следующую простую схему:

CREATE TABLE T (A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TA ON T(A)
CREATE UNIQUE INDEX TB ON T(B)

Сразу после создания таблицы статистика отсутствует:

SELECT index_id, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('tempdb') and object_id = OBJECT_ID('tempdb..t')
ORDER BY index_id
 
SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID('tempdb'), OBJECT_ID('tempdb..t'), NULL, NULL)
ORDER BY index_id

 

index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
 
 
index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           0                    0
2           0                    0

Теперь организуем просмотр кластерного индекса:

SELECT * FROM T

  |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))

Повторный запрос к DMV показывает, что был один просмотр кластерного индекса, и этот просмотр отражён в обоих DMV. SQL Server регистрирует просмотр, даже если в таблице нет записей, а запрос возвращает пустой результат:

index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
1           0                    1                    0                    0
 
index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           1                    0
2           0                    0

Теперь выполним одноэлементный поиск по кластерному индексу:

SELECT * FROM T WHERE A = 1

  |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

Таблица снова не содержит таких записей, и запрос возвращает пустой результат. Тем не менее, DMV теперь сообщают об одном user_seeks и одном singleton_lookup:

index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
1           1                    1                    0                    0
 
index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           1                    1
2           0                    0

Напомню, что результаты обращения к DMV являются кумулятивными, поэтому нужно в уме вычитать предыдущие значения из текущих при выполнении каждого из этих экспериментов. Таким образом, мы можем проигнорировать просмотр, который уже был получен в предыдущем примере.

Теперь давайте попробуем что-нибудь поинтереснее. Давайте организуем поиск закладок (bookmark lookup):

SELECT * FROM T WHERE B = 1

  |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[T].[TB]), SEEK:([tempdb].[dbo].[T].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)

Как и ожидалось, sys.dm_db_index_usage_stats сообщает о поиске по индексу TB (index_id = 2) и о bookmark lookup в кластерном индексе (index_id = 1). Однако sys.dm_db_index_operational_stats сообщает только о singleton_lookup в индексе TB, но не сообщает о какой-либо новой активности по кластерному индексу:

index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
1           1                    1                    1                    0
2           1                    0                    0                    0
 
 
index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           1                    1
2           0                    1

Чтобы понять, что произошло, вспомним, как работает соединение вложенных циклов. Сервер выполняет поиск (singleton lookup) по индексу TB и, как в предыдущем примере, оба DMV обновляются, даже если поиск ничего не возвращает. Однако, поскольку поиск по индексу TB записей не возвращает, соединение вложенных циклов не выполняет поиск по кластерному индексу (т. е. тут обходимся без поиска закладок). Данные в представлении sys.dm_db_index_usage_stats обновляются, фиксируя тот факт, что был выполнен план запроса, включающий поиск закладок по таблице T, но не происходит изменений в представлении sys.dm_db_index_operational_stats, поскольку запросом фактически не было выполнен оператор bookmark lookups.

Далее, давайте добавим в таблицу три строки и проведём ещё один эксперимент с bookmark lookups. Будем использовать подсказку индекса, чтобы принудительно получить план с bookmark lookups. Без подсказки оптимизатор просто использовал бы просмотр по кластерному индексу, поскольку запрос возвратит все три строки из таблицы:

INSERT T VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2)

SELECT * FROM T WITH (INDEX (TB))

  |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
       |--Index Scan(OBJECT:([tempdb].[dbo].[T].[TB]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)

На этот раз в результат sys.dm_db_index_usage_stats добавилась информация о просмотре индекса TB и bookmark lookups в кластерном индексе (плюс изменения оператором INSERT). Но sys.dm_db_index_operational_stats отразил просмотр индекса TB и три bookmark lookups в кластерном индексе:

index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
1           1                    1                    2                    1
2           1                    1                    0                    1
 
index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           1                    4
2           1                    1

Когда сервер выполняет указанный выше запрос, он три раза запускает поиск по кластерному индексу — по одному разу для каждой строки, возвращенной просмотром индекса. Мы запустили запрос только один раз, но он выполнил три bookmark lookups. Таким образом, как и в предыдущем примере, сервер обновляет фиксирует в sys.dm_db_index_usage_stats то, что он выполнил план запроса с bookmark lookups в таблице T, но в отличие от предыдущего примера, он фиксирует в sys.dm_db_index_operational_stats, что запрос фактически выполнил три bookmark lookups.

В приведенных выше примерах использовался bookmark lookups, но любые Nested Loops соединения дадут похожие результаты. Всё это свидетельствует о том, что статистика, возвращаемая этими двумя DMV, может существенно различаться, и не стоит ожидать, что статистика по индексам у них будут совпадать. В представлении sys.dm_db_index_usage_stats можно найти информацию от том, сколько планов запросов были выполнены с использованием разных индексов. Эта информация полезна для вывода о том, сколько было выполнено планов запросов где был затронут индекс, но в ней нет данных, сколько фактических было выполнено операций с использованием индекса. С другой стороны, sys.dm_db_index_operational_stats может дать информацию о том, как часто индексы используются во время выполнения планов запросов и помочь определить, какие индексы способствуют повышению производительности. Но, даже если согласно sys.dm_db_index_operational_stats указывает что индекс используется не очень часто или даже вообще не используется, не спешите автоматически делать вывод, что вы можете удалить этот индекс. Во-первых, убедитесь, что в sys.dm_db_index_usage_stats ни один запрос не ссылается на этот индекс. В некоторых случаях наличие индекса может изменить план запроса в лучшую сторону, даже если при исполнении плана сам индекс не используется.