SQL Server: миф дня (8/30) – Фоновые операции с индексами

 

Миф №8 – Фоновые операции с индексами не вызывают блокировок

FALSE

        Фоновые операции с индексами  вызывают краткосрочные блокировки в начале и в конце операции, что может привести к значительным проблемам с производительностью.

 

Примечание: Под фоновой операцией с индекcом понимается инструкция CREATE INDEX, ALTER INDEX, DROP INDEX и ALTER TABLE выполняющаяся с параметром ONLINE.

 

        В начале фоновой оперции с индексом накладывается совмещаемая блокировка (S lock) на таблицу. Эта блокировка удерживается пока новый, пустой индекс не будет создан; версионный скан старого индекса не будет запущен; и младший номер схемы таблицы не сместится на 1.

        Проблема в том, что эта совмещаемая блокировка стоит в очереди с остальными блокировками таблицы. Никакие блокировки, которые несовместимы с ней не могут быть получены, пока S lock стоит в очереди, либо уже получена. Это значит, что операции обновления данных не выполняются пока S lock не будет получена и операция не закончится. Кроме этого S lock не может быть получена, пока все, выполняющиеся в данный момент, операции UPDATE не выполнятся и пока IX или X блокировки не будут сняты.

        После того как блокировка была установлена (это очень быстро), блокировка снимается, но вы можете видеть как возникают блокировки обновления. Смещение младшего номера версии схемы приводит к тому, что все планы запросов, которые обновляют таблицу, перекомпилируются. В этом случае операторы плана запроса будут использовать уже новый индекс.

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

        Когда операция с индексом заканчивается, старый и новый индекс связаны, до тех пор пока происходят операции обновления. Блокировка схемы (SCH_M lock mode) необходима для завершения операции. Вы можете представлять себе это как super-table-X lock – это необходимо для смещения старшего номера версии таблицы – никакие операции не выполняются над таблицей и никакие планы не могут быть перекомпилированы, пока удерживается эта блокировка.

        Эта проблема с блокировками похожа на ту, что мы разбирали вначале, возникающую при старте операции – но в этот раз никакие операции чтения или записи не выполняются, пока блокировка находится в состоянии ожидания либо получена, и не может быть получена, пока все текущие операции чтения/записи не будут завершены.

        Во время удержания блокировки , структуры распределения старых индексов освобождаются и индекс ставится в очередь на отложенное удаление, в то же время структуры распределения нового индекса связываются с метаданными старого (поэтому ID у индекса не меняется), старший номер таблицы смещается, и  вуаля! Вы получили  новенький индекс.

        Как видите, есть возможность для возникновения продолжительных блокировок в начале и в конце операции. Скорее это должно называться "Почти фоновые операции с индексами", но это не очень хороший маркетинговый ход…

        Узнать больше о фоновых операциях с индексами вы можете здесь - Online Indexing Operations in SQL Server 2005.

 

Об авторе:

Пол С. Рэндал (Paul S. Randal) — генеральный директор SQLskills.com является MVP по SQL Server. Он работал в группе подсистемы хранилища SQL Server в корпорации Майкрософт с 1999 по 2007 г. Пол является автором DBCC CHECKDB/восстановления SQL Server 2005 и в ходе разработки SQL Server 2008 отвечал за программу базовой подсистемы хранилища. Пол является специалистом по аварийному восстановлению, высокой доступности и обслуживанию базы данных и часто дает презентации на различных конференциях по всему миру. Он ведет блог по адресу SQLskills.com/blogs/paul.

 

Ссылки по теме:

- Оригинал статьи

- Online Indexing Operations in SQL Server 2005

- Выполнение операции с индексами в сети

- О фоновых операциях с индексом

- Правила выполнения фоновых операций с индексами

blog comments powered by Disqus

Обо мне

MVP

Data Architect at Intapp, Inc.

PASS Regional Mentor, CEE

MCT, MCITP, MCPD, MCTS


Microsoft MVP

Month List