Если Вы ищите статьи, опубликованные на SQL.RU, статьи на тему MS SQL Server можно найти в блоге mssqlforever.blogspot.com Telegram-канал блога тут: t.me/mssqlhelp
Планы запросов на вставку, изменение и удаление состоят из двух частей. Первая часть называется курсор чтения, и она определят то множество строк, которое необходимо вставить, изменить или удалить. Вторая часть называется курсор записи, и в этой части непосредственно происходит вставка, изменение или удаление данных. Давайте рассмотрим простой пример:
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_idindex_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_idindex_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
axenta_iptv
Как поднять просмотр