Общий план оптимизации и настройки запросов SQL Server

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

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

Большинство компаний обычно работают только с запросами после внедрения, когда они создают проблему и помещаются в список "багов" некоторого типа. Да, вы можете обнаружить проблемные запросы таким способом, но если вы будете дожидаться, пока они не появятся в списке "багов", вы попадете в цейтнот, "фиксируя" проблему, делая резервную копию приложения и запуская его. Если повезет, то вам хватит времени, чтобы применить новые знания по настройке производительности, которые вы приобрели за время, прошедшее с момента, когда в последний раз работали над запросом при решении новой проблемы. Этот способ настройки запросов по факту может позволить, а может и не позволить вам идентифицировать и исправить ключевой момент потери производительности ваших хранимых процедур, поскольку он имеет дело с теми запросами, которые настолько плохи, что привлекают к себе основное внимание. Лучший план позволил бы вам не только работать над проблемными запросами по мере их появления, но и получить списки запросов, которые демонстрируют проблемную производительность до того, как они попадают в список "багов". Тогда эти запросы могут оптимизироваться, когда у вас есть на это время или когда вы адаптируете их к новой версии приложения. Использование мощности SQL Profiler и выполнение простой трассировки для определения производительности ваших хранимых процедур может облегчить получение этих новых списков запросов. Такую трассировку просто создать, и, в зависимости от установки, запускать в моменты максимальной и минимальной загрузки в течение нескольких дней, чтобы получить хорошую выборку данных.

Создание списка запросов с помощью трассировки SQL Profiler

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

- EventClass
- EventSubClass
- TextData
- CPU
- Reads
- Writes
- Duration
- SPID
- StartTime
- EndTime
- NestLevel
- ObjectID
- ObjectName
- ObjectType

Такая трассировка может породить большое количество данных, поэтому может оказаться проще сделать так, чтобы информация помещалась в таблицу, из которой вы сможете извлекать ее с помощью запроса. Если можно найти способ фильтровать трассу, чтобы ограничить вывод данных, я предложил бы вам это сделать. Я обычно использую для имен моих хранимых процедур префикс 'spXXXX', поэтому я могу выполнить фильтрацию по имени объекта. Но поскольку вы работаете с уже имеющейся ситуацией, приходится что-то придумать, чтобы, допуская фильтрацию некоторых ненужных данных, получить необходимые данные и уменьшить трассу.

Теперь, когда вы сохранили ваши данные в таблице, создайте хранимые процедуры, которые выдадут самые плохие по производительности процедуры, учитывая длительность (длительность выводится в миллисекундах), SP:CacheMiss (отсутствие в кэше), SP:Recompile (перекомпиляция), Reads (чтения) и Write (записи). Создайте списки с наибольшими длительностями, наибольшим числом чтений, наибольшим числом записей и хранимыми процедурами, которые включают события SP:CacheMiss и SP:Recompile. Возможно, вам потребуется несколько попыток, чтобы понять, какое число операций чтения и записи считается чрезмерным в вашей базе данных, но если вы видите хранимую процедуру, которая превышает среднее значение этих показателей, с нее можно начать. Остальные списки проще для определения проблемных запросов, на которые можно воздействовать сразу.

Теперь, когда вы получили свои списки запросов, подлежащих переработке, - долго выполняющиеся запросы, запросы с чрезмерным числом чтений и записей, запросы, которые перекомпилируются, - что нужно делать, чтобы оптимизировать их?

Долго выполняющиеся и новые запросы

Первое, что нужно сделать для длительно выполняющихся запросов и запросов, которые вы только что создали, это получить их план выполнения. Используя план выполнения, вы должны:

- Найти то, что окрашено красным цветом. Анализатор запросов (Query Analyzer) окрасит значки красным, если определит, что там есть определенные проблемы. Обычно красный цвет означает, что статистические данные индексов, используемых данной частью запроса, устарели, однако, это может означать и другие вещи. Обращайте внимание на любые значки, которые имеют красный цвет, и думайте, как устранить проблемы.
- Разобраться, как вы получаете данные из таблиц. Выполняет ли запрос сканирование таблицы (Table Scan)? Можете ли вы сделать что-нибудь, чтобы заменить сканирование индекса или кластеризованного индекса (Index Scans или Clustered Index Scans) на поиск в индексе или поиск в кластерном индексе (Index Seeks или Clustered Index Seeks)? Можете ли вы переделать запрос, чтобы использовать кластеризованный индекс вместо некластеризованного индекса? Простое перемещение поиска базовых данных от сканирования таблицы на поиск в индексе или кластеризованном индексе, как правило, решает проблему медленно выполняющего запроса. В результате вы получаете очень быстрый способ улучшить работу большинства проблемных запросов.
- Обратите внимание на стоимость (Cost) каждого сегмента запроса. Следует знать, что стоимость каждого сегмента примерно соответствует процентной доле времени, которое потребуется для его выполнения. Выделите наибольшие процентные доли и подумайте, можете ли вы оптимизировать этот сегмент кода каким-либо способом. Это не означает, что вам нужно сделать так, чтобы максимальная стоимость перестала быть таковой; цель состоит в том, чтобы оптимизация сегмента просто сделала выполнение этого сегмента более быстрым, чем раньше.

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

• Есть ли в коде какие-нибудь курсоры? Курсоры - главная проблема производительности и обычно требуют обработки. Простая временная таблица с полем IDENTITY может обычно использоваться вместо необходимости применения курсора.
• Можете ли вы уменьшить использование временных таблиц или типа данных TABLE в запросе? Хотя эти объекты находят свои области применения, некоторые разработчики имеют привычку использовать их даже тогда, когда они на самом деле не нужны. Спросите себя, нельзя ли использовать JOIN вместо этого, а может другое предложение WHERE устранит необходимость в таблице. Если вы не можете обойтись без таблицы, посмотрите, можете ли вы уменьшить ее размер с помощью фильтров или использовать базовую таблицу, созданную в базе данных, чтобы вам не приходилось терять производительность на создании таблицы всякий раз, когда она потребуется.
• Имеются ли какие-нибудь операторы, которые изменяют структуру таблиц? Так же, как создание временной таблицы в запросе, модификация таблиц в запросах, также вызывает потерю производительности. Посмотрите, что можно сделать с этими операторами. Возможно, создание общей таблицы вас вполне устроит.
• Не возвращает ли запрос больше данных, чем это необходимо. Перегрузка сети может вызвать незначительные проблемы с хранимыми процедурами. Пробуйте не возвращать больше данных или столбцов, чем это необходимо для приложения. Также старайтесь использовать SET NOCOUNT ON, чтобы уменьшить обычно ненужный подсчет строк, который возвращается всеми запросами.
• Используете ли вы хранимую системную процедуру sp_executesql вместо команды EXECUTE, чтобы выполнить строку? Системная хранимая процедура sp_executesql имеет некоторое преимущество в производительности перед командой EXECUTE. Найдите возможность заменить EXECUTE на sp_executesql.
• Создаете ли вы транзакции в пределах запроса? Если вы создаете транзакцию в коде, удостоверьтесь, что вы можете оценить стоимость этой транзакции. Зная стоимость транзакции, вы сможете оценить, будет ли она обычно завершаться за 1 секунду, 1 минуту или 1 час. Можно принимать меры для упрощения транзакции, если оценка ее стоимости превышает несколько секунд. В противном случае вы рискуете получить очень медленные запросы, которые, помимо этого, могут вызвать проблемы блокировки базы данных в течение времени, необходимого для завершения транзакции.
• Старайтесь создавать процедуры как можно меньшего размера, устраняя ненужный код. Спросите себя, будет ли оператор IF когда-либо выполнять все свои переходы, - если нет, удалите их. Еще лучше создать другие хранимые процедуры, которые обрабатывают функцию каждого перехода в операторе IF. Также посмотрите, имеются ли части кода, которые используются много раз; в этом случае их можно убрать и создать из них отдельные хранимые процедуры. Наконец, разберитесь с использованием оператора CASE. Операторы CASE могут иногда устранить большое количество кода, если вы знаете, как использовать их должным образом.
• Используйте новый синтаксис ANSI JOIN вместо соединения в старом стиле. Новый синтаксис соединения имеет некоторое преимущество в производительности по сравнению со старым способом использования предложения WHERE для соединения. Новый синтаксис обладает также лучшей читаемостью и должен стать нормой в вашем кодировании на Transact-SQL.
• Иногда приходится слышать рекомендации о замене динамической части кода на параметризованные запросы. Это зависит от конкретной ситуации, т.к. я обнаружил, что в некоторых случаях, динамический запрос выполняет лучше, даже если не используется кэшируемый план. Чтобы сделать правильный выбор, вам нужно провести собственное исследование на основе конкретного запроса и базы данных.
• Ищите объекты, которые вызываются без уточнения имени владельца (owner). Каждый раз, когда запрос выполняется без уточнения имени владельца объектов, на которые имеются ссылки в запрос, оптимизатор должен удерживать блокировки компиляции на системных объектах, пока не определит, являются ли эти объекты теми же самыми, что и находящиеся в кэшируемом плане. Указание имени владельца решит эту проблему и поможет с проблемами производительности и блокировки.
• Обращайте внимание на типы данных, используемых в предложении WHERE операторе соединения. Любые сравнения данных или объединения должны быть выполнены с теми же самыми типами данных. Если типы данных различны, то незначительная потеря производительности возникает в связи с преобразованием типов данных, которое должно быть произведено до выполнения сравнения.

Перекомпиляция запросов

Даже если вы оптимизировали запросы, анализируя план выполнения и код, вы все еще можете иметь проблему, которую огромное большинство разработчиков даже не рассматривает. Эта проблема - стоимость работы, выполняемая всякий раз, когда для запроса должен быть создан новый план или выполнена повторная компиляция. Обычно повторной компиляции легко избежать, что дает значительный прирост производительности многих приложений. Следует иметь в виду, когда вы получаете список перекомпилирующихся хранимых процедур, что есть законные причины для того, чтобы перекомпилировать хранимые процедуры. Поэтому вы должны побеспокоиться в первую очередь о тех из них, для которых компиляция выполняется при каждом их исполнении. Другими вы можете заняться позже, решая, требуется ли для них перекомпиляция или нет.

• Ищите смесь операторов DDL и DML. Не перемешивайте создание объектов и использование этих объектов в вашем коде. Для этого переместите создание всех объектов в верхнюю часть вашего запроса.
• Ищите команды ANSI SET. Изменение состояния SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL приведет к перекомпиляции запроса при каждом его выполнении. Убедитесь в том, что вам действительно необходимо изменять эти настройки прежде, чем вы сделаете это внутри запроса.
• Если вам необходимо использовать курсор в коде, удостоверьтесь, что в курсоре нет ссылок на временные объекты. Ссылка на временный объект в курсоре приведет к повторной компиляции запроса при каждом его выполнении.

Итак, вы изучили план выполнения, проанализировали код и выявили повторную компиляцию; что теперь? Лишь только то, что все выглядит прекрасно, еще не означает, что запрос будет удовлетворительно выполняться в каждом отдельном сценарии. Вы должны придумать различные тесты для вашего запроса. Как он выполняется на наборах данных различного объема? Как он выполняется с различными наборами параметров? Что будет, если вы запустите запрос многократно, используя многочисленные соединения в Query Analyzer? Что бы вы ни думали о своем конкретном случае, требуется выполнять трассировку и анализ. Только не говорите: "Я настроил этот запрос, и он готов", - если вы запустили его только один раз. Проявите твердость и убедите ваших клиентов и менеджеров, что вам нужно время, чтобы полностью проверить новый или недавно переработанный запрос. Это более важно, чем быстрая установка заплаток, чтобы только выполнить работу в срок.

Как было сказано, это только общий план. Добавьте в него любые новые пункты, которые пришли вам в голову. Расширьте мои этапы своими наработками, накопленными за годы, и решениями общих проблем, которые вы обнаружили в своей базе данных. Как бы вы ни решили оптимизировать ваши запросы, запишите это, чтобы у вас было нечто "формальное", что можно передать другим в вашей группе или просто использовать, чтобы дать ответы на вопрос интервью, который захватил меня врасплох, если вам это когда нибудь понадобится.

Randy Dyess (оригинал: An Introduction to SQL Server Query Tuning)
Перевод: Моисеенко С.И.
Оригинал перевода