Внутренняя оптимизация операций изменения для индексов

Автор оригинала: Craig Freedman

Планы запросов на вставку, изменение и удаление состоят из двух частей. Первая часть называется курсор чтения, и она определят то множество строк, которое необходимо вставить, изменить или удалить. Вторая часть называется курсор записи, и в этой части непосредственно происходит вставка, изменение или удаление данных. Давайте рассмотрим простой пример:

CREATE TABLE T (PK INT, A INT, B INT, C INT, D INT, E INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
CREATE INDEX TB ON T(B)CREATE INDEX TCD ON T(C,D)CREATE INDEX TE ON T(E)

INSERT T VALUES(0, 10, 20, 30, 40, 50)

UPDATE T SET A = 19

Вот план запроса для изменения:

  |--Clustered Index Update(OBJECT:([T].[TPK]), SET:([T].[A] = [@1]))
       |--Top(ROWCOUNT est 0)
            |--Index Scan(OBJECT:([T].[TE]), ORDERED FORWARD)

В этом плане сканирование индекса является курсором чтения, а обновление кластерного индекса — курсором записи. Назначение ROWCOUNT TOP мы рассмотрели в предыдущей статье. Хотя у таблицы «T» есть три некластерных индекса, SQL Server понимает, что эти индексы не затрагивают подлежащие изменению столбцы, и с ними не нужно ничего делать. Следовательно, этот план запроса обновляет только кластерный индекс.

Если же есть некластерные индексы, значения столбцов которых затронуты изменением, оптимизатор построит аналогичный план, в котором кроме изменения кластерного индекса будут изменены и эти некластерные индексы. В качестве альтернативы оптимизатор может добавить в план отдельные операторы изменения кластерного индекса для каждого некластерного индекса. Мы называем план с одним оператором изменения «построчным» или «узким» планом, а план с одним оператором изменения каждого затронутого индекса — «поиндексным» или «широким» планом. Барт Дункан обсуждает разницу между этими типами планов в своей статье: Wide vs. Narrow Plans.

Давайте посмотрим, что произойдет, если мы попытаемся изменить столбцы, входящие в некластерные индексы:

UPDATE T SET B = 29, C = 39, D = 49

  |--Clustered Index Update(OBJECT:([T].[TPK]), OBJECT:([T].[TB]), OBJECT:([T].[TCD]), SET:([T].[B] = [@1],[T].[C] = [@2],[T].[D] = [@3]))
       |--Compute Scalar(DEFINE:([Expr1015]=[Expr1015], [Expr1016]=[Expr1016]))
            |--Compute Scalar(DEFINE:([Expr1015]=CASE WHEN CASE WHEN [T].[B] = [@1] THEN (1) ELSE (0) END THEN (1) ELSE (0) END, [Expr1016]=CASE WHEN CASE WHEN [T].[C] = [@2] THEN (1) ELSE (0) END AND CASE WHEN [T].[D] = [@3] THEN (1) ELSE (0) END THEN (1) ELSE (0) END))
                 |--Top(ROWCOUNT est 0)
                      |--Clustered Index Scan(OBJECT:([T].[TPK]))

Это построчный план. Оператор изменения для кластерного индекса «Clustered Index Update» вносит изменения в кластерный индекс (TPK) и в два некластерных индекса (TB и TCD). Столбцы третьего некластерного индекса (TE) не затронуты, и его не нужно трогать. В представленной информации о плане запроса изменения кластерного индекса перечисляются все три объекта, дающие строки на вход оператора (эта развёрнутая информация об объектах добавлена в SQL Server 2005, до этого была только информация о кластерном индексе).

План последнего изменения включает два новых оператора «Compute Scalar», которых не было в первом плане. Эти операторы для каждой строки вычисляют были ли изменения в каждом некластерном индексе. Если в некластерном индексе для конкретной строки нет изменений, сервер не обновляет этот индекс для этой строки (это тоже добавлено в SQL Server 2005).

Первый «Compute Scalar» вычисляет логическое выражение для каждого индекса, сравнивая старые и новые значения для каждого столбца в этом индексе, и определяя нужно ли изменение. Выражения выглядят сложными, но, если вы посмотрите внимательно, вы увидите, что оно оцениваются как «1», если нет изменений в индексе и этот индекс не нуждается в изменении строки, а в ином случае оценка будет «0» (если изменение строки индекса необходимо). Выражение [Expr1015] проверяет столбец B и указывает, нужно ли обновлять индекс TB. Выражение [Expr1016] проверяет столбцы C и D и указывает, нужно ли обновлять индекс TCD. Второй «Compute Scalar» нужен только для внутренних целей.

Есть как минимум три способа убедиться, что SQL Server затрагивает только те строки некластерного индекса, которые подлежат изменению. Во-первых, мы можем сравнить результаты запроса к sys.dm_db_index_operational_stats до и после изменения. Во-вторых, мы можем проверить, какие блокировки удерживаются при выполнении изменений используя sys.dm_tran_locks. В-третьих, мы можем посмотреть на количество логических операций чтения, о которых сообщает SET STATISTICS IO ON. Давайте попробуем каждый из этих способов. Если вы уже запускали любой из приведенных выше операторов UPDATE, откатите всё назад и пересоздайте таблицу, чтобы получились те же результаты.

Вот что возвращает sys.dm_db_index_operational_stats сразу после создания таблицы и вставки одной строки:

SELECT index_id, leaf_insert_count, leaf_update_count
FROM sys.dm_db_index_operational_stats (db_id(), object_id('T'), NULL, NULL)
ORDER BY index_id

index_id    leaf_insert_count    leaf_update_count
----------- -------------------- --------------------
1           1                    0
2           1                    0
3           1                    0
4           1                    0

Как и ожидалось, мы вставили по одной строке в кластерный индекс (index_id 1) и в каждый некластерный индекс (index_id от 2 до 4). Далее откройте новую транзакцию и выполните изменение, которое изменяет каждый столбец:

SET STATISTICS IO ON

BEGIN TRANUPDATE T SET B = 29, C = 39, D = 49

Результаты со STATISTICS IO показывает, что мы выполнили 10 логических операций чтения:

Table 'T'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Теперь перепроверим через sys.dm_db_index_operational_stats:

index_id    leaf_insert_count    leaf_update_count
----------- -------------------- --------------------
1           1                    1
2           2                    0
3           2                    0
4           1                    0

Это административное представление возвращает суммарные данные. Если мы сравним эти данные с данными до изменения, мы ожидаемо увидим, что была изменена одна строка в кластерном индексе. Также было вставлено по одной строке в два не кластерных индекса. Эти две вставки на самом деле являются изменениями, которые SQL Server «под капотом» разделяет на удаление и вставку.

Теперь давайте просмотрим результат sys.dm_tran_locks:

SELECT index_id, request_mode, request_type, request_status
FROM sys.dm_tran_locks JOIN sys.partitions
ON resource_associated_entity_id = hobt_id
WHERE request_session_id = @@spid AND resource_type = 'KEY'
ORDER BY index_id

index_id     request_mode  request_type  request_status
-----------  ------------  ------------  --------------
1            X             LOCK          GRANT
2            X             LOCK          GRANT
2            X             LOCK          GRANT
3            X             LOCK          GRANT
3            X             LOCK          GRANT

Как и ожидалось, мы видим, что удерживаем эксклюзивные блокировки на кластерном индексе и на двух не кластерных индексах. На самом деле у нас есть по две блокировки для каждого некластерного индекса, потому что, как отмечалось выше, SQL Server заменяет каждое изменение на удаление и вставку.

Теперь давайте зафиксируем предыдущую транзакцию и повторим изменение:

COMMIT TRAN

BEGIN TRAN UPDATE T SET B = 29, C = 39, D = 49

На этот раз изменение не изменит данных, поскольку мы уже обновили эту единственную строку до этих же значений. Это отражено в результатах отслеживания STATISTICS IO, которые показывают, что было выполнено только 2 логических чтения вместо 10 при первом выполнении:

Table 'T'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Если мы теперь заглянем в sys.dm_db_index_operational_stats, то увидим, что на этот раз обновлялся только кластерный индекс. SQL Server не затронул строки в не кластерном индексе:

index_id    leaf_insert_count    leaf_update_count
----------- -------------------- --------------------
1           1                    2
2           2                    0
3           2                    0
4           1                    0

И если мы снова проверим sys.dm_tran_locks, мы увидим, что удерживаем эксклюзивную блокировку только для кластерного индекса:

index_id     request_mode  request_type  request_status
-----------  ------------  ------------  --------------
1            X             LOCK          GRANT

В следующий раз рассмотрим как работает план изменения по индексам с «широким» планом. Также дополнительную информацию по теме можно узнать в статье разработчика из группы оптимизации запросов Стефано: Non updating updates