Лучшие методы: оптимизация индексов в SQL Server 2005

Stefano Demiliani (оригинал: Best practice when optimizing indexes on SQL Server 2005)
Перевод Моисеенко С.И.

В больших системах баз данных с большим количеством команд вставки и обновления проблема фрагментации индекса - одна из главных причин деградации производительности, и надлежащая стратегия оптимизации индекса - насущная необходимость.

Я вижу каждый день, как многие администраторы баз данных планируют оптимизацию индексов посредством написанных скриптов T-SQL или через стандартный SQL Maintenance Plan, но они не подозревают, что фактически сам SQL Server 2005 позволяет Вам "настраивать" этот процесс.

SQL Server 2005 предлагает опцию (ONLINE = ON or OFF), чтобы помочь настроить производительность и требования параллелизма при создании или перестройке индекса. С новой возможностью Online Indexing (ONLINE=ON) Вы можете продолжать выполнять запросы и операции с таблицей, индекс которой перестраивается, в то время как автономная индексация (ONLINE=OFF) блокирует таблицу.

Важно помнить: онлайновое создание или перестройка индекса (ONLINE=ON), обеспечивая максимальный параллелизм, потребляет больше ресурсов и требует больше времени на выполнение!!

В помощь управлению временным пространством во время операций с индексом SQL Server предоставляет еще одну опцию: SORT_IN_TEMPDB. SQL Server использует временное хранилище для сортировки и других промежуточных задач во время создания или перестройки индекса. Для этого временного хранилища может использоваться пользовательская база данных или база данных TEMPDB.

Когда опция SORT_IN_TEMP оператора CREATE INDEX или ALTER INDEX установлена в значение OFF (принимается по умолчанию), для временного хранилища используется пользовательская база данных. Когда опция SORT_IN_TEMP включена (ON), временное хранилище будет находиться в базе данных TEMPDB.

Вот рекомендации для лучшей стратегии создания/перестройки индекса (непосредственно от Microsoft). Я рекомендую распечатать ее:

Убедитесь, что TEMPDB находится на дисковой подсистеме, которая обеспечивает достаточную пропускную способность ввода/вывода, и что TEMPDB является достаточно большой, чтобы предоставить временное пространство, которое потребуется для операции создания или восстановления индекса. По умолчанию TEMPDB создается в каталоге Data в папке, куда установлен SQL Server (например, C:\SQL2005\MSSQL.1\MSSQL\Data). При такой конфигурации в TEMPDB может не оказаться достаточно места для обеспечения адекватной пропускной способности ввода/вывода. Поэтому лучшим методом является перемещение TEMPDB на носитель с достаточным количеством свободного пространства и производительностью после установки SQL Server. Кроме того, имейте в виду, что база данных TEMPDB - это общий ресурс для всего экземпляра SQL Server. Вам следует учитывать активность всех пользовательских баз данных, которые могут использовать TEMPDB, при планировании действий с TEMPDB.

Чтобы достичь наименьшего времени на создание или перестройку индекса, используйте автономный режим (ONLINE=OFF). Однако это предотвращает любой доступ пользователей к данной таблице на все время создания или перестройки индекса.

Чтобы достичь наименьшего влияния на доступ пользователей к таблице, используйте опцию онлайн (ONLINE=ON). Однако такая операция онлайн занимает больше времени и использует большее пространство в TEMPDB по сравнению с автономной операцией.

Чтобы использовать наименьший объем памяти в TEMPDB во время перестройки кластерного индекса, используйте автономный вариант (ONLINE=OFF). Однако это повлияет на параллелизм, поскольку доступ к таблице предотвращается на все время перестройки индекса.

Чтобы использовать наименьшее количество памяти в TEMPDB во время перестройки некластерного индекса, используйте онлайновый режим (ONLINE=ON). Онлайновая перестройка также обеспечивает лучший параллелизм, но потребует большего времени на выполнение.

Если для таблицы имеются транзакции, которые выполняются параллельно с онлайновым созданием или перестройкой индекса, Вы должны планировать дополнительное место в TEMPDB для хранения версий.