|
|
Перевод: Александр Гладченко 2001г По материалам статьи Steve Jones на SWYNK.COM «SQL Server – Indexes and Performance». В этой статье Стив пишет, что одно из ключевых условий эффективности SQL сервера состоит в создании такого набора индексов для таблиц, чтобы любые запросы к таблицам могли бы выполняться эффективно. Существует большое количество статей, посвящённых проектированию индексов, подбору столбцов, и т.д. для оптимизации и повышения эффективности, так что можно воздержаться от повторения прописных истин, которые можно узнать в другом месте. После того, как Вы построили все необходимые индексы, необходимо задуматься над тем, что через какое-то время Ваши базы данных вырастут. SQL сервер будет поддерживать существующие индексы Ваших данных настолько эффективно, насколько он умеет. Тем не менее, Вы должны выполнять обслуживание индексов через какое-то время, несмотря на наличие у сервера автоматических средств. Эта статья рассматривает некоторые проблемы, связанные с ростом данных через какое-то время и методику обслуживания индексов. ОБРАТИТЕ ВНИМАНИЕ: Автор рассматривает некоторые особенности SQL 2000, но эти методы, с незначительными модификациями, полезны и для SQL v6.5/v7. Что случится, когда Ваши базы вырастут? SQL сервер имеет свойство автоматического обновления статистики, обучаемый оптимизатор запросов, и т.п. Когда Вы строите индекс по таблице (давайте рассматривать кластерный индекс), SQL сервер разбивает пакеты данных на страницы и экстенты. С v7.x и выше, экстенты могут быть разделяемыми между объектами (экстенты v6.5, принадлежат только одному объекту). В результате, давайте предполагать, что Вы создаете таблицу, строки которой располагаются в нескольких страницах. К примеру, Вы имеете 20 строк, размер которых соответствует 5 страницам данных. Будут ли Ваши данные занимать 5 страниц? Только, если FILLFACTOR установлен в 100%. Fillfactor определяет, на сколько процентов заполнены ваши страницы. Давайте принимать FILLFACTOR равным 50%, тогда Вы имели бы 10 страниц данных, занимаемых этой таблицей. Через какое-то время, данные могут быть разбросаны на 100 страницах. Они (как минимум) займут 7 экстентов, если эти экстенты не разделяются с другими объектами. Каждая страница экстента связывается со следующей страницей специальным указателем. Следующая страница в цепочке, однако, не обязательно будет в том же самом экстенте. Поэтому, при чтении страниц, мы можем быть "переключены" к другому экстенту. Самый простой пример - предполагает, что мы принимаем для 3-х последовательных страницы данных следующий порядок:
В этом примере показаны три страницы, где страница n связана со страницей n+1, а та, в свою очередь, связана со страницей n+2 и так далее. Чтобы прочитать эти три страницы, мы читаем экстент 1, затем переключаем к экстенту 2, затем переключаем назад к экстенту 1. Эти действия не обязательно влекут за собой физический I/O, но все эти операции складываются. Они могут быть не заметны на Вашем локальном сервере или даже слегка загруженном сервере. Зато WEB приложения, которое имеют тысячи пользователей, могут столкнутся с большой загрузкой сервера из за частых сканирований этой таблицы. Почему нормальные в начале своего использования таблицы начинают так себя вести? Потому, что при их разработке не учитывались особенности их функционирования через какое-то время. SQL сервер распределит место для каждой строки в доступном в то время пространстве. В результате, в то время как кластерный индекс должен хранить данные в страницах в физическом порядке, сами страницы не будут располагаться в физическом порядке. Вместо этого, каждая страница будет расположена далеко от следующей в цепочке страницы. Эффект, аналогичный фрагментации жёсткого диска, будет наблюдаться и при распределении страниц таблицы, которые могут быть фрагментированы через какое-то время в заполненных данными экстентах. SQL сервер не перестраивает индексы автоматически. Программисты из Redmond оставили это для DBA, который должен отслеживать фрагментацию данных и перестраивать индексы по мере необходимости. Как бороться с фрагментацией читайте ниже. Использование DBCC SHOWCONTIG До появления SQL 2000 Вы должны были сначала получить ID объекта, используя команду: select object_id('<object name>') Для пользовательской таблицы выполняется: select object_id('user') Возвращаемым результатом является длинный номер (из sysobjects, например 2030630277) который очень неудобен для дальнейшего использования. Видимо вследствие жалоб потребителей, в SQL 2000 Вы можете использовать имя объекта в запросе dbcc showcontig. Например: dbcc showcontig (user) Это выдаст следующую статистику по вашим индексам:
DBCC SHOWCONTIG scanning 'User' table... Давайте проанализируем этот отчёт: Pages Scanned – Количество физических страницы базы данных, принадлежащих этому индексу. Дает Вам полный размер занятых этим индексом страниц (каждая страница - 8КБ); Extents scanned - экстент - 8 страниц. Pages Scanned / 8. В этом примере мы имеем 121 экстентов, которые содержат 968 страниц. Так как индекс занимает только 899 страниц, мы делаем вывод, что ряд экстентов разделяемы между другими объектами. Это не всегда плохо, но это может натолкнуть Вас на мысль, что присутствует небольшая фрагментация данных. Конечно, Вы не знаете точный процент физической фрагментации, являющейся причиной увеличения времени исполнения запросов. Минимальное количество экстентов, которые могли бы быть заняты, для 899 страниц было бы 113 (899/8). Extent Switches – Количество страниц, которые имеют связи со следующей, расположенной в другом экстенте страницей. Когда это значение близко к общему количеству страниц, Вы имеете довольно высокую фрагментацию. Вам нужно перестроить кластерный индекс. Average Pages/Extent – Арифметическое деление Pages Scanned / Extents Scanned. Если бы экстенты были полностью заполненные, в результате было бы 8. Scan Density [Best Count:Actual Count].......: 12.58% [113:898] Показывает процент идеальности размещения страниц. В идеале должно было быть 113, но мы имеем 898, что составляет 12.58% от идеального. Здесь мы имеем обратное представление фрагментации. Чем ближе результат к 100%, тем меньше фрагментация. Вы можете видеть, что эта таблица довольно сильно фрагментирована. Сканирование постоянно использует переключение вперед и назад от одного экстента к другому, вместо использования только ссылки одной страницы на следующую в пределах экстента. Logical Scan Fragmentation ..................: 99.89 % Процент страниц в индексе, которые имеют указатель на следующую страницу, которая не является следующей страницей в последовательности расположения листьев. Этот процент имеет смысл только для кластерных индексов, поскольку данные (страницы листьев) должны быть физически размещены в порядке кластерного индекса. Для OLAP систем желательно низкое значение этого параметра, в OLTP системах приемлемо среднее значение. Extent Scan Fragmentation ...................: 80.99 % Показывает процент страниц, для которых следующая страница в индексе не расположена рядом (физически) с текущей страницей. Это говорит о том, что система I/O будет загружена на 80 % времени просмотра индекса, для поиска следующей страницы. Avg. Bytes Free per Page.....................: 2606.5 Среднее число байт, свободных на странице. Так как страница - 8096 байтов, для нашего примера заполнены приблизительно 68 % страниц. Это может быть как хорошо, так и плохо. Если это OLTP система с частыми вставками в таблицу, то большое количество свободного пространства в странице снижает вероятность разбиения страниц, что может существенно увеличить периодичность перестройки индексов. Если это OLAP система, то предпочтительнее иметь этот значение ближе к нолю, так как здесь желательно при чтении захватывать настолько большое количество данных, насколько это возможно для каждой страницы (чтобы уменьшить время сканирования индекса). Avg. Page Density (full).....................: 67.80% Это процентное значение основано на результате предыдущего параметра 2606.5 и разделённого на 8096 (размер страницы). Большой процент говорит о высокой фрагментации. Т.е. имеется большое количество переключений между экстентами, что вызывает большую утилизацию I/O при использовании индекса. Дефрагментация индексов Во всех версиях сервера Вы можете перестроить кластерный индекс, что заставит сервер заново разместить данные в экстентах и страницах, которые будут расположены в физическом порядке и уменьшать, таким образом, фрагментацию. Но в SQL 2000 есть другой путь! Добавлена новая опция DBCC, которая называется INDEXDEFRAG. С помощью её можно дефрагментировать и кластерные и не кластерные индексы, что (согласно BOOKS ONLINE) должно улучшить эффективность, поскольку физический порядок будет соответствовать логическому порядку и (теоретически) уменьшится I/O при просмотре индекса. Если Вы разместили индексы в отдельных файлах, то будет выполнена дефрагментация каждого файла в отдельности, и страницы между файлами перемещаться не будут. Это не удобно, когда Вы добавили новую filegroup и разрешили объектам размещаться в новых файлах. При выполнении дефрагментации, пользователю выводится графическая полоса продвижения процесса. Ещё одним дополнением является возможность остановить процесс исполнения DBCC команды Есть и ещё одно дополнение, которое по значимости выше предыдущих двух. Оно состоит в том, что это дефрагментация является теперь обычной операцией и не приводит к блокировке таблиц. Она использует стандартный механизм транзакций для перемещения страниц. Кроме того, это работает быстрее, чем построение нового индекса. Конечно, это означает, что Вы должны иметь достаточно места для журнала транзакций, что бы туда поместился самый большой индекс. В заключении, Стив отмечает, что поддержка индексов все еще требуют вмешательства DBA для оптимизации эффективности работы вашего сервера базы данных. Как часто Вы должны поддержать эти индексы? Это зависит (подобно всему остальному в мире RBDMS) от вашей конкретной системы. Это искусство, которое требует некоторой практики, опыта, и осторожных корректировок через какое-то время. |