По материалам статьи 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 не сможет
обеспечивать Вас рекомендациями, это означает, что он или не
смог улучшить эффективность уже созданных индексов, или ему не
предоставили для этого достаточно данных.