SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Тюнинг индексов и статистики SQL сервера для повышения эффективности исполнения запросов

ПУБЛИКАЦИИ  

По материалам статьи Mary V. Hooke на devx.com «Tune Up SQL Server Performance: Leverage indexes and statistical information to optimize database performance»

Мэри пишет, что быстродействие приложений баз данных почти всегда является источником беспокойства DBA и SQL программистов. Существует много способов повышения этой эффективности, затрагивающие такие аспекты, как: операционная система; аппаратные средства, тип памяти и дисковой подсистемы; дизайн прикладного программного обеспечения. Дизайн Вашей базы данных также играет важную роль в общей эффективности.
В этой статье Мэри обсуждает способность SQL Server 2000 создать индексы для представлений (View), а также особенности настройки индексов с использованием Index Tuning Wizard (ITW). Она также показывает, как можно удостовериться, что запросы оптимально используют индексы и статистическую информацию.
Вопросы эффективности должны рассматриваться ещё на стадии дизайна базы данных. Однако у Вас есть возможность влиять на эффективность и после того, как ваша база данных перешла в стадию промышленной эксплуатации. Одним из ключевых элементов оптимизации поиска и сортировки данных являются индексы. Правильно построенные индексы могут значительно сократить время выборки/вставки данных, а сам процесс подбора индексов существенно упрощает ITW. ITW выдаёт рекомендации по построению индексов основываясь на указанной Вами рабочей нагрузке. Рабочая нагрузка это SQL сценарий или результаты трассировки, предварительно сохранённые во внешнем файле или специально созданной таблице. Рекомендации ITW будут столь же хороши, сколь хороша Ваша рабочая нагрузка, так что важно, что бы нагрузка была максимально приближена к реальности. Вероятно самый легкий и наиболее полный способ создания рабочей нагрузки для ITW это сохранение результатов трассировки, созданной SQL PROFILER. SQL PROFILER записывает все действия сервера, используя фильтры и критерии, которые Вы предварительно задаёте. Удостоверитесь, что ваша трассировка будет содержать наиболее типичные операции с базой данных. Другими словами, выберите время, когда база данных используется не особенно интенсивно или, наоборот, обращений к ней не мало. Продолжительность трассировки зависит от вашей конкретной системы. В некоторых случаях, Вам придется выполнять трассировку в течение часа, чтобы зафиксировать точное представление системных действий. В других случаях, могут потребоваться дни, чтобы сделать запись всех стандартных разновидностей действий, которые происходят в вашей базе данных.
Как только Вы создадите файл рабочей нагрузки, готовый к использованию, запустите ITW. В Enterprise Manager выберите сервер. Выберите Wizards в меню Tools, затем Index Tuning Wizard. Появляется экран «Добро пожаловать в ITW». Следующий экран ITW позволит Вам определить сервер и базу данных для дальнейшего анализа. В этом экране Вы имеете два дополнительных варианта выбора: сохранить все существующие индексы (Keep All Existing Indexes) и исполнить полное исследование (Perform Thorough Analyses). Отключение опции Keep All Existing Indexes, как правило, позволяет ITW выдать Вам оптимальные рекомендации по построению индексов. Однако ITW может предложить удалить или изменить некоторые из ваших существующих индексов. Оставьте эту опцию включённой, если Вы не хотите вносить изменения в Ваши существующие индексы. Выбор опции Perform Thorough Analyses заставляет ITW выполнять наиболее глубокий анализ. Хотя так можно получить наиболее лучшие результаты, вероятно, что выполнение полного анализа может потребовать большего времени. К тому же, полный анализ часто становится причиной повышенной нагрузки на Ваш сервер. Лучше запускать полный анализ на полигоне или во время малой загрузки промышленного сервера.
Третий экран в ITW предлагает Вам определить рабочую нагрузку, с которой Вы хотите работать. Если Вы используете файл, созданный SQL PROFILER, выберите переключатель My Workload File, и используйте диалоговое окно File, чтобы загрузить предварительно сохраненный файл трассировки. Вы можете также устанавливать некоторые дополнительные параметры, используя Advanced опции. Эти параметры позволяют задать максимальное дисковое пространство, которое рекомендуемые индексы должны будут использовать, и максимальное число запросов в вашем файле рабочей нагрузки, для аналитической выборки. Четвертый экран определяет, для какой таблицы или для каких таблиц Вы хотите получить рекомендации. Выбор только некоторых таблиц может существенно сэкономить время анализа, а также помогает Вам сосредоточиться на определенных прикладных областях. Однако если Вы используете ITW для того, чтобы оптимизировать вашу базу данных целиком, Вы можете выбрать всё таблицы в базе. Следующий экран ITW покажет рекомендованные для построения индексов, основываясь на заданных Вами прежде критериях. У Вас есть возможность выбрать опцию применения рекомендаций немедленно или запланировать выполнение созданных для этого сценариев позже. Есть возможность просто сохранить такой сценарий во внешнем файле.

SQL Server 2000 Enterprise Edition, в дополнение к индексации таблиц, умеет строить индексы для представлений. Для базы данных Pubs можно привести следующий пример SQL кода, который создаёт представление Quantity_Totals:

Use Pubs
GO

CREATE VIEW Quantity_Totals
with SCHEMABINDING
AS
SELECT ord_num, Total_Quantity =
   Sum(qty), Total_Items = Count_Big(*)
FROM dbo.sales
GROUP BY ord_num

В SQL Server 2000 появилась новая функция COUNT_BIG, которая работает аналогично COUNT; различие в том, что возвращается значение типа данных bigint, в противоположность int у COUNT. Любое представление, которое включает предложение GROUP BY, должно также содержать функцию COUNT_BIG, чтобы иметь право содержать индекс. Вы должны создать представление, которое содержит индекс, используя опцию SCHEMABINDING, которая также является новой. Представление привязывается к схеме его основных таблиц, в момент определения этой опции.
Resultset, возвращённый представлением, не сохраняется в базах данных, если представление не имеет индекса. Вы можете иметь представление со ссылками на большое количество строк или включающее комплексную обработку, типа агрегированной группировки и многократных объединений. SQL сервер каждый раз будет обновлять resultset для Ваших представлений.
Индексы для представлений работают почти таким же способом, как индексы для таблиц. Подобно таблицам, представления могут иметь кластерный индекс и составные не кластерные индексы. Однако для представления Вы должны сначала создать уникальный, кластерный индекс, а уж потом можно создавать другие не кластерные индексы. Создание для представления кластерного индекса обеспечивает постоянное хранение его resultset в базе данных. Хотя сохраненный resultset отражает данные на момент его создания, создание индекса обеспечивает автоматическое отражение всех последующих модификаций данных в resultset. Создать индекс для представления можно используя инструкцию «CREATE INDEX» (так же, как Вы создаёте индекс для таблицы):

CREATE UNIQUE CLUSTERED INDEX
   PRIMARY_IDX on
   Quantity_Totals(ord_num)

Этот код создает уникальный, кластерный индекс для представления Quantity_Totals, созданного в предыдущем примере. Кроме повышения эффективности непосредственно представлений, Query Optimizer может использовать существующие индексы по представлению и для других, обычных запросов, даже для тех, у которых нет  ссылок на представление в предложении FROM. Например, этот SQL запрос выбирает суммы всех проданных товаров, сгруппированные по порядковому номеру:

SELECT ord_num, Sum(qty)
FROM sales
GROUP BY ord_num

При выполнении этого запроса, Query Optimizer понимает, что суммы имеются в индексе, который SQL сервер создал для представления Quantity_Totals. В этом случае, Query Optimizer может принять решение, что использование индекса, созданного для представления, будет наиболее эффективным при выполнении этого запроса. В SQL Server 2000 появилось много подобных нововведений, которые можно использовать для оптимизации работы представлений.
Кроме зависимости эффективности от дизайна базы данных, сама структура запросов также может существенно влиять на общую эффективность. Аппаратные проблемы, связанные с недостатком памяти или дискового пространства, могут существенно тормозить запросы. Однако плохая эффективность исполнения запроса может быть вызвана неспособностью Query Optimizer воспользоваться преимуществами индексов или статистической информации.
Статистическая информация отражает распределения данных в столбцах. Query Optimizer использует статистическую информацию для определения оптимального способа исполнения запроса. SQL сервер создает статистическую информацию для столбцов в индексе автоматически, непосредственно при создании индекса. По умолчанию, у SQL сервера включена опция Auto Create Statistics. Обновление статистики происходит так часто, как часто изменяются данные в столбцах или индексах. Обычно обновление статистики не требует ручного вмешательства. Однако иногда случается, что необходимая статистическая информация для ваших таблиц не будет создаваться или не будет обновляться достаточно часто.
Вы можете использовать команду Transact-SQL (t-sql) DBCC SHOW_STATISTICS, чтобы определить какая статистическая информация существует в настоящее время для столбцов в индексе. Эта команда имеет два параметра. Первый параметр - название таблицы, для которой Вы хотите анализировать статистическую информацию; второй - имя индекса. Выполнение следующего SQL запроса в Query Analyzer покажет Вам статистическую информацию, доступную для индекса aunmind в таблице authors базы данных Pubs:

DBCC SHOW_STATISTICS (authors, aunmind)

Информация, возвращаемая командой DBCC SHOW_STATISTICS, включает дату и время последней модификации статистической информация и число строк. Для столбцов, не входящих в индексы, можно отображать эту информацию, с помощью Query Analyzer, в графическом виде. Для этого, откройте Query Analyzer и выберите Manage Statistics в меню Tools. Выберите базу данных, потом таблицу или представление для которых анализируется статистическая информация и которой Вы хотите управлять в диалоговом окне Manage Statistics. Query Analyzer покажет в listbox (внизу экрана) имеющуюся статистику. Выбор существующей статистики и нажатие на ней кнопки Delete или Update позволяет удалять или изменять статистическую информацию. Диалоговое окно Create Statistic появляется, когда Вы щелкаете по кнопке New, и обеспечивает создание новой статистики. При этом Вы имеете возможность выбрать столбец или столбцы, чтобы задать процент данных для выборки в этих столбцах, при превышении которого будет автоматически создаваться новая статистика. Вы можете также создавать или обновлять статистику вручную, используя команды T-sql CREATE STATISTICS и UPDATE STATISTICS.
Одним из способов определения эффективности использования индексов и статистической информации, состоит в рассмотрении плана исполнения запроса в Query Analyzer. Для этого, откройте Query Analyzer, и загрузите ваш запрос. Выберите Show Execution Plan в меню Query, и запустите запрос на выполнение. Ниже текста запроса расположена вкладка Execution Plan. Щелкните по этой вкладке, чтобы увидеть план выполнения запроса. Вы можете анализировать этот план даже без выполнения запроса. Выбрав в меню Query опцию Display Expected Execution Plan, Вы сможете получить графическую диаграмму, иллюстрирующую оценку плана исполнения запроса. Эта диаграмма показывает, какие шаги избрал Query Optimizer, или собирается выполнить при исполнении запроса. Тут Вы сможете увидеть, какие из шагов наиболее ресурсоёмки, и рассмотреть детали каждого такого шага. Также, Вы сможете определить, использует ли Query Optimizer индексы. Если Query Optimizer отметил заголовок таблицы красным цветом, значит для этой таблицы статистическая информация отсутствует или устарела. Вы можете получить очень большое количество информации о каждом шаге, изучая появляющуюся во всплывающем окне информацию, после нажатия на графических элементах клавиш мыши.

SQL PROFILER также очень мощный инструмент для выяснения причин низкой эффективности исполнения запросов. PROFILER может идентифицировать медленные SQL запросы и инструкции, сохраняя информацию об отобранных Вами действиях сервера. Использование SQL PROFILER в целях оптимизации будет рассмотрено в следующих статьях Мэри.
ITW не предлагает рекомендации для primary keys и других уникальных индексов, и не обеспечивает рекомендации по индексам для системных таблиц. Другие ограничения ITW включают неспособность рассматривать больше чем 32,767 запросов в одной рабочей нагрузке, и неспособность выдавать рекомендации для баз данных SQL SERVER 6.5 или более ранних версий. Обратите внимание, что ITW создаёт рекомендации, основываясь на предоставленной ему Вами выборке данных. По этой причине, может случится так, что ITW выдаст Вам различные рекомендации, если Вы запустите его повторно с той же самой рабочей нагрузкой. Если ITW не сможет обеспечивать Вас рекомендациями, это означает, что он или не смог улучшить эффективность уже созданных индексов, или ему не предоставили для этого достаточно данных.

Перевод: Александр Гладченко  2001г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013