Построение индексов – часть 7: Фоновое построение индексов (Часть 2)

http://blogs.technet.com/lyudmila_fokina/archive/2007/06/06/7-2.aspx

Lyudmila Fokina

Последовательность фонового построения индекса:

    1.   Ждем пока завершаться исполняющиеся на данный момент DML операции;

2. Берем Intend Share (IS) блокировку на таблицу - эта блокировка будет держаться до конца построения индекса, что ограничивает набор операций выполняемых в параллель с построением индекса. (Например: truncate table, BCP операции будут недоступны); 
3. Берем Share (S) блокировку, чтобы просканировать таблицу. Эта блокировка держится в течении короткого времени; любые параллельные операции на данных невозможны в этот период;
4. Отпускаем Share блокировку; 
5. Собственно построение индекса – сканирование, сортировка, построение индексного дерева; 
6. Если нужно перестроит некластерные индексы* – возвращаемся к шагу 2 и повторяем для каждого индекса;
7. Заканчиваем построение:

а. Если удаляется существующий индекс – берем Schema Modification блокировку (SCH_M). Например: построение кластерного индекса требует удаления кучи – следовательно необходимо взять SCH_M блокировку**. Эта блокировка конфликтует с любой параллельной деятельностью, включая чтение данных (Select);

б. Если просто создается некластерный индекс – берется S блокировка;

в. Изменяем версию метаданных для таблицы;

г. Отпускаем блокировки;

д. Отложенное удаление старых индексов.

** Когда кластерный индекс модифицируется в фоновом режиме, все некластерные индексы, существующие на таблицы также перестраиваются. 

*** Когда мы держим S или Sch_M блокировки, которые конфликтуют с параллельными операциями на таблице, параллельно исполняющаяся транзакция не будет отвергнута. Она будет ждать, пока

блокировка не будет отпущена.  Для пользователя, исполняющего параллельную транзакцию, это должно остаться незаметным.

Некоторые соображения об использовании дискового пространства:

            Построение/ перестроение кластерного индекса в фоновом режиме требует дополнительного дискового пространства для хранения промежуточных данных. В некоторых  (экстремальных) случаях  - больше размера самого индекса. Эти промежуточные данные будут храниться там же где хранятся промежуточные структуры сортировки (либо tempdb, либо пользовательская база данных) – см. предыдущие посты. Это дополнительное пространство будет освобождено после завершения построения индекса.

Некоторые соображения производительности:

            Производительность фонового построения некластерных индексов  не должна сильно отличаться от производительности автономного построения некластерных индексов (предполагая, что нет параллельно исполняющихся DML). 

Производительность фонового построения кластерных индексов хуже производительности автономного построения кластерных индексов (даже без параллельно исполняющихся DML).

При наличии параллельно исполняющихся DML, производительность, как построения индекса, так и DML, будет хуже. Конкретные цифры можно посмотреть в документе, который мы недавно опубликовали, и который посвящен фоновому построению индексов. Документ на английском, так что, если будут вопросы – задавайте. Документ здесь: http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/OnlineIndex.doc

Фоновое удаление кластерного индекса заметно медленнее автономного и сильно влияет на параллельно исполняющиеся DML. Так как фоновое удаление кластерного индекса не рассматривалось как ключевой сценарий, многие оптимизации не были имплементированы и общая рекомендация здесь – без крайней необходимости не использовать фоновое удаление кластерного индекса, а пользоваться автономным.