11.5.5. Оптимизация системы индексов

Оптимизация индексов в SQL Server 2005, Database Engine Tuning Advisor вместо Index Tuning Wizard, dta.exe

Одна из возможностей повышения производительности, которая может дать сильный эффект, — это оптимизация системы индексов. Конечно, необходимые индексы в базе данных в идеале должны реализовывать разработчики, но практика показывает, что для многих задач это не так. Чаще всего разработчики создают только служебные индексы (которые, например, обеспечивают уникальность значений в определенном столбце таблицы), особо не задумываясь над применением индексов для оптимизации запросов. В то же время во многих задачах создание нескольких дополнительных индексов может привести к повышению производительности выполнения запросов в разы. Отметим, что оптимизацию системы индексов может производить и администратор. При этом функциональность приложения никак не пострадает.

В теории при оптимизации системы индексов следовало бы собрать информацию о наиболее важных и частых операциях, которые выполняют пользователи, посмотреть планы выполнения запросов и вручную определить, какие индексы могут потребоваться дополнительно. Однако на практике при наличии нескольких сотен таблиц и представлений в рабочей базе данных и нескольких десятков стандартных запросов, которые выполняют пользователи (а код многих из них спрятан за хранимыми процедурами), рассчитывать оптимальную систему индексов вручную — это непроизводительная трата времени. Намного удобнее провести такой анализ в автоматическом режиме средствами программы Database Tuning Advisor. Это средство использует собранную информацию о выполненных запросах для того, чтобы определить наилучший набор индексов для базы данных.

Первое, что нужно сделать при применении Database Tuning Advisor, — собрать информацию о запросах пользователей. Проще всего это сделать средствами SQL Server Profiler (см. разд. 11.2.3), выбрав шаблон Tuning. Правильнее будет собирать информацию о работе пользователей в течение продолжительного времени, например, рабочего дня. Удобнее всего производить протоколирование в таблицу на сервере SQL Server, но при желании можно использовать и файлы трассировки на диске, и просто набор команд Transact-SQL в файле скрипта с расширением sql.

После того как исходная информация о командах, выполняемых пользователями, готова, можно запускать Database Tuning Advisor (меню Пуск | Программы | Microsoft SQL Server 2005 | Performance Tools | Database Engine Tuning Advisor). Первое, что вам потребуется сделать, — подключиться к серверу, для которого будет производиться оптимизация индексов. Database Tuning Advisor будет автоматически запрашивать с этого сервера необходимую служебную информацию. Кроме того, этот сервер будет использоваться для хранения временной информации Database Tuning Advisor, поэтому выполнение анализа лучше планировать на нерабочее время.

После подключения к серверу перед вами откроется интерфейс Database Tuning Advisor. В левой части расположено окно Session Monitor (Монитор сеанса) со списком сеансов оптимизации. Зеленым флажком при этом будут помечены те сеансы, для которых завершен анализ и созданы рекомендации. Из контекстного меню для объектов сеанса вы можете открыть сеанс, скопировать его, переименовать, удалить, запустить анализ и т. п. Можно использовать и еще одну интересную возможность: если воспользоваться командой Import Session Definition (Импортировать определение сеанса), то можно передать Database Tuning Advisor файл с расширением xml с параметрами сеанса, включая, например, информацию об индексах, влияние которых на производительность при их гипотетическом создании можно оценить. В результате у вас появляется возможность использовать средства Database Tuning Advisor для того, чтобы проверить, какое реальное влияние может оказать конкретный индекс, который вы планируете создать.

Справа в Database Tuning Advisor расположено основное окно с информацией о текущем сеансе. Изначально в нем есть две вкладки: General и Tuning Options (Параметры оптимизации). Первое, что необходимо сделать, — выбрать протокол рабочей нагрузки (workload). Им может быть таблица трассировки на SQL Server (созданная профилировщиком) или файл одного из трех типов:

q      двоичный файл трассировки, созданный профилировщиком (для таких файлов используется расширение trc);

q      файл трассировки в формате XML (собранный протокол можно сохранить в этом формате, использовав команду File | Save As (Файл | Сохранить как) в профилировщике);

q      просто текстовый файл с командами Transact-SQL (для него используется расширение sql).

Обратите внимание на параметр Database for workload analysis (База данных для анализа рабочей нагрузки). Он определяет базу данных, в которой будут создаваться временные объекты Database Tuning Advisor. Конечно, такие временные объекты не должны создаваться ни в базе данных master (что предлагается по умолчанию), ни в рабочей базе данных. Лучше всего использовать для временных объектов тюнинга новую базу данных, специально созданную для этой цели.

В нижней части вкладки General вы можете выбрать базы данных и таблицы, для которых будет производиться анализ системы индексов (чтобы открыть список таблиц, нужно щелкнуть на стрелку в строке для базы данных). Отличительной особенностью Database Tuning Advisor по сравнению с Index Tuning Wizard в SQL Server 2000 является то, что теперь можно производить анализ сразу нескольких баз данных.

Вкладка Tuning Options предназначена для настройки параметров тюнинга. В большинстве случаев оптимальными параметрами будут следующие:

q      не ограничивать время анализа (снять флажок Limit tuning time). Как уже говорилось, анализ лучше планировать на нерабочее время. Кроме снятия нагрузки с рабочей базы данных такое решение одновременно позволит отвести на анализ столько времени, сколько потребуется Database Tuning Advisor. Конечно же, снятие ограничения на время обеспечит максимальное качество анализа;

q      анализировать возможность применения индексов и индексированных представлений (установить переключатель Physical Design Structures to use in database (Структуры физического проекта для использования в базе данных) в положение Indexes and indexed views). Это обеспечит максимальную гибкость при проведении анализа. Все остальные положения переключателя (анализировать применение только индексов, только некластерных индексов, только индексированных представлений или только существующие индексы без выработки предложений по созданию новых) ограничивают варианты, которые будут рассматриваться при анализе;

q      использовать полные возможности секционирования (установить переключатель Partitioning strategy to employ (Стратегия секционирования для применения) в положение Full partitioning);

q      не сохранять какие-либо существующие физические структуры хранения (установить переключатель Physical Design Structures to keep in database (Физические структуры данных для сохранения в базе данных) в положение Do not keep any existing PDS). При этом будут генерироваться предложения не только по созданию новых индексов, но и по удалению существующих, если они практически не используются. Информация о том, какие индексы не используются и только замедляют операции по изменению данных, может оказаться очень полезной, а немедленно удалять индексы никто вас не заставляет.

После того как все настройки на этой вкладке произведены, можно нажать кнопку Start Analysis (Начать анализ) на панели инструментов. Если протокол рабочей нагрузки большой, а время для проведения анализа не ограничено, то для завершения этой операции может потребоваться несколько часов — еще один аргумент в пользу того, чтобы запускать его на ночь перед уходом с работы. Наблюдать за ходом выполнения вы можете на вкладке Progress (Прогресс).

После того как анализ завершен, откроется вкладка Recommendations (Рекомендации). На ней будет представлен список объектов (индексов, индексированных представлений, разделов), которые нужно создать или удалить. При этом названия объектов, которые нужно удалить, будут зачеркнуты. Однако не стоит спешить внедрять эти рекомендации сразу же. Намного правильнее будет вначале ознакомиться с результатами анализа на вкладке Reports (Отчеты). Кроме общей статистики анализа, на этой вкладке представлены отчеты, при помощи которых можно получить информацию:

q      о том, какие запросы выполнялись чаще всего (отчет Event frequency report);

q      насколько выиграет каждый запрос в производительности и какие индексы он использует или будет использовать в текущей и предлагаемой конфигурации индексов (отчеты Statement-index relations report (current) и Statement-index relations report (recommended));

q      о системе индексов в текущей и рекомендованной конфигурации (отчеты Index detail report (current) и Index detail report (recommended));

q      какие индексы используются активно, а какие совсем не используются (отчеты Index usage report).

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

Если после изучения результатов анализа вы придете к мнению, что предложенные рекомендации имеет смысл воплотить в жизнь, то в вашем распоряжении — меню Actions (Действия) Database Tuning Advisor. Вы можете использовать три пункта этого меню:

q      Apply recommendations (Применить рекомендации) — просто выполнить команды на создание или удаление объектов, немедленно или по расписанию, в зависимости от вашего выбора;

q      Save recommendations (Сохранить рекомендации) — рекомендации будут сохранены в файле скрипта. При этом вы сможете выбрать, использовать ли команды на создание и удаление объектов в оперативном режиме (с параметром ONLINE) или в автономном режиме (OFFLINE);

q      Evaluate recommendations (Оценить рекомендации) — это значит создать новый сеанс анализа, в котором предлагаемая конфигурация индексов, индексированных представлений и разделов будет считаться действующей, и провести анализ, приняв ее за исходную. Фактически в этом режиме вы проводите анализ типа "а что, если ...".

Database Tuning Advisor намного эффективнее своего предшественника Index Tuning Wizard. Часто на одной и той же базе данных для одной и той же нагрузки они дают совершенно разные рекомендации. Однако полностью полагаться на Database Tuning Advisor не стоит — ситуации, когда предлагаемые им рекомендации не оптимальны, иногда встречаются. Однако Database Tuning Advisor удобно использовать для получения информации, которая послужит отправной точкой для дальнейшего анализа, и для резкого сокращения трудоемкости при оптимизации системы индексов.

У Database Tuning Advisor есть и консольный вариант, представленный исполняемым файлом dta.exe. Возможности этой утилиты совпадают с возможностями графического варианта Database Tuning Advisor, поэтому консольный вариант используется в основном для автоматизации сеансов тюнинга. Например, если написать соответствующую команду на запуск dta.exe и сохранить ее в файле bat, то такой файл можно использовать для запуска сеанса анализа в ночное время.

Отметим и некоторые проблемы, которые связаны с применением Database Tuning Advisor:

q      Database Tuning Advisor иногда отказывается запускаться на относительно маломощных компьютерах (на которых, тем не менее, нормально работает SQL Server 2005). При попытке подключиться к серверу он может выдать ошибку 2812 "Could not find stored procedure 'msdb..sp_DTA_help_session'" (Не могу найти хранимую процедуру 'msdb..sp_DTA_help_session'). Такой хранимой процедуры действительно нет, но только потому, что Database Tuning Advisor должен создать ее вместе с другими необходимыми объектами в базе данных msdb. На маломощных компьютерах этого не происходит;

q      полные возможности Database Tuning Advisor доступны только в редакции SQL Server 2005 Enterprise Edition. Во всех остальных редакциях некоторые возможности будут недоступны.

Получить учебные материалы по этому курсу