В предыдущей статье Внутренняя оптимизация операций изменения для индексов было рассказано о том, что планы запроса для UPDATE состоят из двух частей: курсора чтения, который выбирает строки, которые необходимо изменить, и курсора записи, который и выполняет изменения. SQL Server использует такую логику изменения, следуя которой курсоры чтения и записи в плане с UPDATE выполняются двумя отдельными шагами или фазами. Другими словами, фактическое изменение в строке не должно влиять на выбор строк изменения. С этим связана описанная ниже проблема, для преодоления которой нужно обеспечить такую работу курсора записи в плане с UPDATE, чтобы он не влиял на курсор чтения, эта проблема известна под названием «Halloween Protection». Такое имя она получила поскольку была обнаружена исследователями IBM более 30 лет назад в Хэллоуин.

Одним из простых решений проблемы Хэллоуина является физическое разделение курсоров чтения и записи в плане запроса с UPDATE с помощью оператора блокировки, например, это может быть как активная буферизация так и сортировка. Вставка оператора блокировки в середину плана UPDATE гарантирует что курсор чтения отработает полностью и получит все нужные строки прежде чем курсор записи начнет изменять соответствующие строки. К сожалению, вставка оператора блокировки в такой план запроса потребует получения всех строк из курсора чтения, а это обычно обходится довольно дорого. К счастью, во многих случаях SQL Server может определить, что курсор записи не влияет на курсор чтения и не станет добавлять оператор блокировки.

Давайте посмотрим это на примере:

CREATE TABLE T (PK INT, A INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
CREATE INDEX TA ON T(A)

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

UPDATE T SET A = A + 10

Вот план с оператором UPDATE:

  |--Clustered Index Update(OBJECT:([T].[TPK]), OBJECT:([T].[TA]), SET:([T].[A] = [Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))
            |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))
                 |--Compute Scalar(DEFINE:([Expr1003]=[T].[A]+(10), [Expr1004]=CASE WHEN [T].[A] = ([T].[A]+(10)) THEN (1) ELSE (0) END))
                      |--Top(ROWCOUNT est 0)
                           |--Clustered Index Scan(OBJECT:([T].[TPK]) )

В этом плане просмотр кластерного индекса является курсором чтения, а изменения в кластерном индексе — курсором записи. Обратите внимание, что в этом плане нет операторов блокировки. Поскольку мы изменяем столбец A, который не входит в ключ кластерного индекса, SQL Server знает, что строки из-за этого не будут перемещаться в дереве кластерного индекса, и, следовательно, нет необходимости разделять операторы просмотра и изменения. Теперь давайте воспользуемся подсказкой оптимизатору, чтобы заставить SQL Server просмотреть некластерный индекс по столбцу A:

UPDATE T SET A = A + 10 FROM T WITH (INDEX(TA))

Давайте посмотрим на плат с хинтом:

  |--Clustered Index Update(OBJECT:([T].[TPK]), OBJECT:([T].[TA]), SET:([T].[A] = [Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))
            |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))
                 |--Top(ROWCOUNT est 0)
                      |--Compute Scalar(DEFINE:([Expr1003]=[T].[A]+(10), [Expr1004]=CASE WHEN [T].[A] = ([T].[A]+(10)) THEN (1) ELSE (0) END))
                           |--Table Spool
                                |--Index Scan(OBJECT:([T].[TA]) , ORDERED FORWARD)

Обратите внимание, что в этом плане присутствует оператор блокировки, а именно «Table Spool», который активно буферизует таблицу (сгенерированный SHOWPLAN_TEXT план не показывает, что эта буферизация активна, но при SHOWPLAN_ALL, а также в графическом и XML плане эти подробности видны). На этот раз SQL Server распознает, что изменение касается столбца A, что может привести к перемещению строк в индексе по этому столбцу, а это уже может привести к тому, что просмотр возвратит эти строки более одного раза. Если так произойдёт, может получиться что план изменит одни и те же строки несколько раз. Поэтому тут добавлена очередь, которая гарантирует получение правильного результата за счёт промежуточной материализации результатов просмотра перед обновлением строк.

Невозможно заставить SQL Server исключить «Table Spool» из приведенного выше плана, поскольку это приведет к неверным результатам. Однако мы можем посмотреть, что произойдет, если будет использоваться динамический курсор. Ниже приведён пример батча, который создает динамический курсор для просмотра индекса TA, а затем изменяет каждую строку перед тем, как выбрать следующие строки. Поскольку в динамическом курсоре изменения видны сразу, этот батч даст тот же результат, что и приведенный выше план, если бы можно было удалить из него «Table Spool». Обратите внимание, что я не предлагаю кому-либо реализовывать изменение таким образом, мало того, следующий пример прекрасно иллюстрирует одну из ловушек динамических курсоров.

DECLARE @PK INT
DECLARE C CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT PK FROM T WITH (INDEX(TA))
OPEN C
WHILE 0=0
    BEGIN
        FETCH NEXT FROM C INTO @PK
        IF @@FETCH_STATUS <> 0
            BREAK
        UPDATE T SET A = A + 10 WHERE PK = @PK
    END
CLOSE C
DEALLOCATE C

Если запустить этот батч на выполнение, он войдет в бесконечный цикл, поскольку неоднократно просматривает, изменяет, а затем снова просматривает одни и те же три строки. С другой стороны, батч отработает правильно, если мы изменим подсказку оптимизатору на использование кластерного индекса (где буферизация не требуется) или если мы используем статический курсор (который создает копию таблицы точно так же, как «Table Spool»):

DECLARE C CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT PK FROM T WITH (INDEX(TPK) )
DECLARE C CURSOR STATIC FOR SELECT PK FROM T WITH (INDEX(TA))

Для защиты Хэллоуина SQL Server может использовать любой оператор блокировки, а не только «Table Spool». Обычно, SQL Server добавляет «Table Spool», поскольку он самый дешевый из операторов блокировки. Однако если такой план уже использует другой оператор блокировки, SQL Server не станет добавлять «Table Spool». Например, если мы изменяем столбец PK, который реализован уникальным кластерным индексом, как побочный продукт мы получим сортировку, о чём говорилось в статье Погружение в уникальные индексы. Поскольку в этом плане уже есть сортировка, у нас нет нужды в операторе «Table Spool».

UPDATE T SET PK = PK + 10 FROM T WITH (INDEX(TPK))

  |--Index Update(OBJECT:([T].[TA]), SET:([PK1015] = [T].[PK],[A1016] = [T].[A]))
       |--Split
            |--Clustered Index Update(OBJECT:([T].[TPK]), SET:([T].[PK] = [T].[PK],[T].[A] = [T].[A]))
                 |--Collapse(GROUP BY:([T].[PK]))
                      |--Sort(ORDER BY:([T].[PK] ASC, [Act1014] ASC))
                           |--Split
                                |--Top(ROWCOUNT est 0)
                                     |--Compute Scalar(DEFINE:([Expr1003]=[T].[PK]+(10)))
                                          |--Clustered Index Scan(OBJECT:([T].[TPK]))

Как уже упоминалось выше, добавление «Table Spool» не является бесплатным и увеличивает стоимость плана с UPDATE. Если мы вставим в таблицу достаточно много строк, этот эффект станет заметным. Например, давайте загрузим туда 100000 строк следующим образом:

TRUNCATE TABLE T
SET NOCOUNT ON
DECLARE @I INT
SET @I = 0
WHILE @I < 100000
    BEGIN
        INSERT T VALUES (@I, @I)
        SET @I = @I + 1
    END
SET NOCOUNT OFF

Затем используем SET STATISTICS TIME ON, чтобы измерить время выполнения первых двух операторов изменения, которые были представлены выше. У себя я выполнил каждый оператор дважды, чтобы убедиться, что буферный пул прогрелся.

UPDATE T SET A = A + 10

SQL Server Execution Times:
CPU time = 3046 ms, elapsed time = 3517 ms.

UPDATE T SET A = A + 10 FROM T WITH (INDEX(TA))

SQL Server Execution Times:
CPU time = 4391 ms, elapsed time = 4666 ms.

Как видите, то же изменение отработало на 30% дольше и заняло на 40% больше времени процессора. Я проводил этот эксперимент на рабочей станции Pentium Xeon 2,2 ГГц с 2 ГБ оперативной памяти, Windows Server 2003 SP2 и SQL Server 2005 SP2. Другие конфигурации могут дать другие результаты.

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