Советы по эффективному обслуживанию баз данных SQL Server - Фрагментация индексов

 

Фрагментация индексов

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

• Фрагментация внутри отдельных страниц данных и индекса (порой именуемая внутренней фрагментацией)
• Фрагментация внутри структур индекса или таблиц, состоящих из страниц (именуемая логической фрагментацией просмотра и фрагментацией просмотра по экстентам)

Внутренняя фрагментация – это наличие в странице больших пустых пространств. Как показывает рис. 1, каждая страница в базе данных имеет размер 8 КБ и 96-байтный заголовок; как следствие, страница может хранить примерно 8096 байтов данных индексов или таблиц (конкретные внутренние структуры таблиц и индексов для данных и структур строк можно найти в моем блоге по адресу sqlskills.com/blogs/paul, в категории Inside The Storage Engine («Внутри механизма хранения»)). Пустое пространство может возникнуть, если каждая таблица или запись в индексе превышают по размерам половину страницы, поскольку тогда на странице можно сохранить лишь одну запись. Исправить это очень сложно или невозможно, поскольку для исправления необходимо изменение схемы таблицы или индекса, например, путем изменения ключа индекса на что-то, что не вызывает случайные точки вставки, как это делает GUID.

Рис. 1. Структура страницы базы данных

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

Рассмотрим, для примера, таблицу из 100 миллионов строк, где средняя запись имеет размер 400 байтов. Со временем шаблон изменения данных приложения приведет к появлению в среднем 2800 байтов свободного пространства на страницу. Общее пространство, требуемое таблицей, составляет 59 ГБ, это выводится путем следующего расчета: 8096-2800 / 400 = 13 записей на 8-килобайтную страницу, затем делим 100 миллионов на 13, чтобы получить число страниц. Если бы пространство не пропадало, то на одной странице можно было бы уместить 20 записей, что уменьшает общее требуемое пространство до 38 ГБ. Огромная экономия!

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

Логическая фрагментация просмотра вызывается операцией, именуемой разбиением страницы. Это происходит, когда запись необходимо вставить на определенную страницу индекса (согласно определению ключа индекса), но на странице недостаточно места, чтобы разместить вставляемые данные. Страница разбивается пополам, и примерно 50% записей перемещаются на свежевыделенную страницу. Эта новая страница обычно не является физически смежной со старой и, следовательно, именуется фрагментированной. Концепция фрагментации просмотра по блокам аналогична. Фрагментация внутри структур таблиц/индексов влияет на возможность SQL Server выполнять эффективные просмотры как по всей таблице/индексу, таки и ограниченные предложением WHERE запроса (такие как SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

На рис. 2 показаны свежесозданные страницы индекса со 100-процентным коэффициентом заполнения – страницы полны, и физический порядок страниц совпадает с логическим порядком. На Рис. 3 показана фрагментация, которая может происходить после случайных вставок/обновлений/удалений.

Рис. 2. Свежесозданные страницы индекса без фрагментации, страницы полны на 100%

Рис. 3 Страницы индекса, показывающие внутреннюю фрагментацию и фрагментацию логического просмотра после случайных вставок, обновлений и удалений

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

Восстановление индекса подразумевает создание новой копии индекса (аккуратно сжатой и настолько непрерывной, насколько это возможно, с последующим отказом от старой и фрагментированной). Поскольку SQL Server создает новую копию индекса перед удалением старой, ему требуется свободное место в файлах данных, приблизительно равное размеру индекса. В SQL Server 2000, восстановление индекса всегда проводилось в автономном режиме. Однако в SQL Server 2005 Enterprise Edition восстановление индекса можно выполнить в интерактивном режиме с некоторыми ограничениями. Реорганизация, с другой стороны, использует имеющийся алгоритм для сжатия и дефрагментации индекса; она требует лишь 8 КБ дополнительного пространства для выполнения – и всегда работает в интерактивном режиме. Кстати, в SQL Server 2000 я специально написал код реорганизации как интерактивную, экономящую пространство альтернативу перестройке индекса.

В SQL Server 2005 следует обратить внимание на команды ALTER INDEX … REBUILD для восстановления индексов и ALTER INDEX … REORGANIZE для их реорганизации. Этот синтаксис заменяет команды SQL Server 2000 DBCC DBREINDEX и DBCC INDEXDEFRAG, соответственно.

Между этими методами есть много различий, влияющих на выбор одного из них, таких как создаваемый объем журнала транзакций, требуемый объем свободного пространства в базе данных и возможность прервать процесс без потери выполненной работы. Технический документ, к котором обсуждаются эти различия, и прочее можно найти на microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Этот документ основан на SQL Server 2000, но концепции хорошо переносятся на поздние версии.

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

Более сложный подход подразумевает использование динамического административного представления sys.dm_db_index_physical_stats (или DBCC SHOWCONTIG в SQL Server 2000) для периодического определения фрагментированных индексов и выбора того, следует ли работать на них, и если да, то как. В данном техническом документе также обсуждается использование этих более узких выборов. Вдобавок, некоторые образцы кода для выполнения этой фильтрации можно найти в примере D записи электронной документации, посвященной динамическому административному представлению sys.dm_db_index_physical_stats в SQL Server 2005 (msdn.microsoft.com/library/ms188917) или примере E записи электронной документации, посвященной DBCC SHOWCONTIG в SQL Server 2000 и далее (на msdn.microsoft.com/library/aa258803).

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

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

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

Обратите внимание, что статистику можно автоматически создавать и поддерживать, включив параметры базы данных AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS, как показано на рис. 4. Они включены по умолчанию, но тем, кто только что унаследовал базу данных, стоит проверить их, чтобы убедиться. Порой статистика может устареть – в этом случае возможно ее обновление вручную с помощью операции UPDATE STATISTICS для конкретного набора статистических показателей. В качестве альтернативы можно использовать хранимую процедуру sp_updatestats, которая обновляет всю устаревшую статистику (в SQL Server 2000 sp_updatestats обновляет всю статистику вне зависимости от возраста).

Рис. 4. Изменение параметров базы данных через SQL Server Management Studio

Если нужно обновлять статистику как часть плана регулярного обслуживания, то нужно помнить об одной хитрости. И UPDATE STATISTICS, и sp_updatestats по умолчанию используют ранее указанный уровень сбора данных (если указан какой-то) – и он может быть ниже, чем полная проверка. Восстановления индекса автоматически обновляют статистику с помощью полной проверки. В случае обновления статистики вручную после восстановления индекса можно получить еще менее точную статистику! Это может произойти, если проверка примеров из обновления вручную перепишет полную проверку, созданную восстановлением индекса. С другой стороны, при реорганизации индекса статистика вообще не обновляется.

Опять-таки, многие имеют план обслуживания, обновляющий всю статистику в какой-либо момент до или после восстановления всех индексов – и сами того не зная, они могут оказаться с менее точной статистикой. Если выбрано простое восстановление всех индексов время от времени, то оно позаботится и о статистике. Если выбран более сложный путь с устранением фрагментации, это стоит делать и для обслуживания статистики. Я предлагаю следующее:

• Проанализируйте индексы и определите, на каком индексе следует работать и как провести дефрагментацию.
• Обновите статистику для всех индексов, которые были восстановлены.
• Обновите статистику для всех неиндексированных столбцов.

Дополнительную информацию о статистике можно найти в техническом документе "Statistics Used by the Query Optimizer in Microsoft® SQL Server 2005" («Статистика, используемая оптимизатором запросов в Microsoft SQL Server 2005») (microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx).