Оптимизация производительности запросов SQL Server

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

Даже если сервер базы данных
употребляет самое массивное аппаратное обеспечение на свете, горсточка
плохо себя ведущих запросов может плохо отразиться на его
производительности. Практически, даже один плохой запрос (время от времени
их именуют «вышедшими из-под контроля») может вызвать суровое
понижение производительности базы данных.

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

Анализ планов
выполнения

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

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

Существует несколько методов извлечения плана
выполнения запроса:

В Management Studio есть функции отображения реального и
ориентировочного плана выполнения, представляющие план в
графической форме. Это более комфортная возможность
конкретной проверки и, по сути, более нередко
применяемый метод отображения и анализа планов выполнения
(примеры из этой статьи я буду иллюстрировать графическими
планами, сделанными конкретно таким методом).
Разные характеристики SET, к примеру, SHOWPLAN_XML и
SHOWPLAN_ALL, возвращают план выполнения в виде документа XML,
описывающего план в виде специальной схемы, либо набора строк с
текстовым описанием каждой операции.
Классы событий профайлера SQL Server, к примеру, Showplan XML,
позволяют собирать планы выполнения выражений способом
трассировки.

Хотя XML-представление плана выполнения не самый
удачный для юзера формат, эта команда позволяет использовать
без помощи других написанные процедуры и служебные программки для
анализа, поиска заморочек с производительностью и фактически
хороших планов. Представление на базе XML можно сохранить в файл
с расширением sqlplan, открывать в Management Studio и создавать
графическое представление. Не считая того, эти файлы можно сохранять для
следующего анализа без необходимости воспроизводить их всякий раз,
как этот анализ пригодится. Это в особенности полезно для сопоставления
планов и выявления возникающих с течением времени конфигураций.

Оценка цены
выполнения

1-ое, что необходимо осознать — это как генерируются
планы выполнения. SQL Server употребляет оптимизатор запроса на
базе цены, другими словами пробует сделать план выполнения с
малой оценочной ценой. Оценка делается на базе
статистики рассредотачивания доступных оптимизатору на момент проверки
каждой использованной в запросе таблицы данных. Если таковой
статистики нет либо она уже устарела, оптимизатору запроса не хватит
нужной инфы и оценка, вероятнее всего, окажется неточной. В
таких случаях оптимизатор переоценит либо недооценит цена
выполнения разных планов и изберет не самый лучший.

Существует несколько всераспространенных, но
неправильных представлений о ориентировочной цены выполнения.
В особенности нередко считается, что ориентировочная цена выполнения
является неплохим показателем того, сколько времени займет выполнение
запроса и что эта оценка позволяет отличить отличные планы от нехороших.
Это ошибочно. Во-1-х, есть много документов касающихся того, в
каких единицах выражается ориентировочная цена и имеют ли они
прямое отношение ко времени выполнения. Во-2-х,
так как значение это примерно и возможно окажется неверным,
планы с большенными оценочными затратами время от времени оказываются существенно
эффективнее исходя из убеждений ЦП, ввода/вывода и времени выполнения,
невзирая на предположительно высшую цена. Это нередко случается
с запросами, где задействованы табличные переменные. Так как
статистики по ним не существует, оптимизатор запросов нередко
подразумевает, что в таблице есть всего одна строчка, хотя их во много
раз больше. Соответственно, оптимизатор изберет план на базе
неточной оценки. Это означает, что при сопоставлении планов выполнения
запросов не следует полагаться лишь на ориентировочную цена.
Включите в анализ характеристики STATISTICS I/O и STATISTICS TIME, чтоб
найти настоящую цена выполнения в терминал ввода/вывода и
времени работы ЦП.

Тут стоит упомянуть об особенном типе плана
выполнения, который именуется параллельным планом. Таковой план можно
избрать при отправке на сервер с несколькими ЦП запроса,
поддающегося параллелизации (В принципе, оптимизатор запроса
рассматривает внедрение параллельного плана исключительно в том случае,
если цена запроса превосходит определенное настраиваемое
значение.) Из-за дополнительных расходов на управление несколькими
параллельными процессами выполнения, связанными с рассредотачиванием
заданий, выполнением синхронизации и сведением результатов,
параллельные планы обходятся дороже, что отражает их ориентировочная
цена. Тогда чем все-таки они лучше более дешевеньких, не
параллельных планов? Благодаря использованию вычислительной мощности
нескольких ЦП параллельные планы обычно выдают итог резвее
стандартных. Зависимо от определенного сценария (включая такие
переменные, как доступность ресурсов с параллельной нагрузкой других
запросов) эта ситуации для кого-либо возможно окажется желательной. Если
это ваш случай, необходимо будет указать, какие из запросов можно
делать по параллельному плану и сколько ЦП может использовать
каждый. Для этого необходимо настроить наивысшую степень параллелизма
на уровне сервера и по мере надобности настроить обход этого правила
на уровне отдельных запросов при помощи параметра OPTION (MAXDOP
n).

Анализ плана
выполнения

Сейчас разглядим обычный запрос, его план
выполнения и некие методы увеличения производительности.
Представим, что я выполняю этот запрос в Management Studio с
включенным параметром включения реального плана выполнения в примере
базы данных Adventure Works SQL Server 2005:

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

В конечном итоге я вижу план выполнения, изображенный на
рис. 1. Этот обычной запрос вычисляет
полное количество заказов, размещенных каждым клиентом в базе данных
Adventure Works. Смотря на этот план, вы видите, как ядро базы данных
обрабатывает запросы и выдает итог. Графические планы
выполнения читаются сверху вниз, справа влево. Каждый значок
соответствует выполненной логической либо физической операции, а
стрелки — потокам данных меж операциями. Толщина стрелок
соответствует количеству переданных строк (чем толще, тем больше).
Если поместить курсор на один из значков оператора, появится желтоватая
подсказка (такая, как на рис. 2) со
сведениями о данной операции.

Оптимизация производительности запросов SQL Server
Рис.
1 Пример плана выполнения

Оптимизация производительности запросов SQL Server
Рис.
2 Сведения об операции

Смотря на операторы, можно рассматривать
последовательность выполненных шагов:

Ядро базы данных делает операцию сканирования
кластеризированных индексов с таблицей Sales.Customer и возвращает
столбец CustomerID со всеми строчками из этой таблицы.
Потом оно делает сканирование индексов (не
кластеризированных) над одним из индексов из таблицы
Sales.SalesOrderHeader. Это индекс столбца CustomerID, но
предполагается, что в него заходит столбец SalesOrderID (ключ
кластеризации таблицы). Сканирование возвращает значения обоих
столбцов.
Результаты обоих сеансов сканирования соединяются воединыжды в столбце
CustomerID при помощи физического оператора слияния (это один из
3-х вероятных физических методов выполнения операции логического
объединения. Операция производится стремительно, но входные данные
приходится сортировать в объединенном столбце. В этом случае обе
операции сканирования уже вернули строчки, рассортированные в
столбце CustomerID, так что дополнительную сортировку делать не
необходимо).
Потом ядро базы данных делает сканирование
кластеризированного индекса в таблице Sales.SalesOrderDetail,
извлекая значения 4 столбцов (SalesOrderID, OrderQty,
UnitPrice и UnitPriceDiscount) из всех строк таблицы
(предполагалось, что возвращено будет 123,317 строк. Как видно из
параметров Estimated Number of и and Actual Number of Rows на рис. 2, вышло конкретно это число, так что
оценка оказалась очень четкой).
Строчки, приобретенные при сканировании кластеризованного индекса,
передаются оператору вычисления цены, умноженной на
коэффициент, чтоб вычислить значение столбца LineTotal для каждой
строчки на базе столбцов OrderQty, UnitPrice и UnitPriceDiscount,
упомянутых в формуле.
2-ой оператор вычисления цены, умноженной на
коэффициент, применяет к результату предшествующего вычисления функцию
ISNULL, как и подразумевает формула вычисленного столбца. Он
завершает вычисление в столбце LineTotal и возвращает его
последующему оператору вкупе со столбцом SalesOrderID.
Вывод оператора слияния с шага 3 соединяется воединыжды с выводом
оператора цены, умноженной на коэффициент с шага 6 и
внедрением физического оператора совпадения значений хэша.
Потом к группе строк, возвращенных оператором слияния по
значению столбца CustomerID и вычисленному сводному значению SUM
столбца LineTotal применяется другой оператор совпадения значений
хэша.
Последний узел, SELECT — это не физический либо логический
оператор, а местозаполнитель, соответственный сводным результатам
запроса и цены.

В сделанном на моем ноутбуке плане выполнения
ориентировочная цена равнялась 3,31365 (как видно на рис. 3). При выполнении с включенной функцией
STATISTICS I/O ON отчет по запросу содержал упоминание о 1,388
логических операциях чтения из 3-х задействованных таблиц.
Процентное значение под каждым оператором — это его цена в
процентах от общей ориентировочной цены всего плана. На плане
на рис. 1 видно, что большая часть общей
цены связана со последующими 3-мя операторами: сканирование
кластеризованного индекса таблицы Sales.SalesOrderDetail и два
оператора совпадения значений хэша. Перед тем как приступить к
оптимизации, хотелось отметить одно очень обычное изменение в моем
запросе, которое позволило на сто процентов убрать два оператора.

Оптимизация производительности запросов SQL Server
Рис. 3 Общая ориентировочная цена выполнения
запроса

Так как я возвращал из таблицы Sales.Customer
только столбец CustomerID, и тот же столбец включен в таблицу
Sales.SalesOrderHeaderTable в качестве наружного ключа, я могу
вполне исключить из запроса таблицу Customer без конфигурации
логического значения либо результата нашего запроса. Для этого
употребляется последующий код:

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Вышел другой план
выполнения, который изображен на рис.
4.

Оптимизация производительности запросов SQL Server
Рис.
4 План выполнения после устранения
из запроса таблицы Customer

Вполне устранены две операции — сканирование
кластеризированного индекса таблицы Customer и слияние Customer и
SalesOrderHeader, а совпадение значений хэша заменено на куда более
эффективную операцию слияния. При всем этом для слияния таблиц
SalesOrderHeader и SalesOrderDetail необходимо возвратить строчки обеих
таблиц, рассортированные по общему столбцу SalesOrderID. Для этого
оптимизатор кластера выполнил сканирование кластеризованного индекса
таблицы SalesOrderHeader заместо того, чтоб использовать
сканирование некластеризованного индекса, который был бы дешевле с
точки зрения ввода/вывода. Это неплохой пример практического
внедрения оптимизатора запроса, так как экономия, получающаяся
при изменении физического метода слияния, оказалась больше
дополнительной цены ввода/вывода при сканировании
кластеризованного индекса. Оптимизатор запроса избрал получившуюся
комбинацию операторов, так как она дает мало вероятную
примерную цена выполнения. На моем компьютере, невзирая на то,
что количество логических считываний возросло (до 1,941), временные
издержки ЦП стали меньше, и ориентировочная цена выполнения
данного запроса свалилась на 13 процентов (2,89548).

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

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Направьте внимание, что в
сделанном индексе есть вычисленный столбец. Это может быть не всегда —
все находится в зависимости от определения такового столбца.

Создав этот индекс и выполнив тот же запрос, я
получил новый план, который изображен на рис.
5.

Оптимизация производительности запросов SQL Server
Рис.
5 Оптимизированный план выполнения

Сканирование кластеризованного индекса таблицы
SalesOrderDetail заменено некластеризованным сканированием с приметно
наименьшими затратами на ввод/вывод. Не считая того, я исключил один из
операторов вычисления цены, умноженной на коэффициент,
так как в моем индексе уже есть вычисленное значение столбца
LineTotal. Сейчас ориентировочная цена плана выполнения
составляет 2,28112 и при выполнении запроса делается 1,125
логических считываний.

Упражнение. Запрос заказа покупателя

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

Ответ. Я предложил высчитать лучший индекс
покрытия для сотворения таблицы Sales.SalesOrderHeader на примере
запроса из моей статьи. При всем этом необходимо сначала отметить,
что запрос употребляет только два столбца из таблицы: CustomerID и
SalesOrderID. Если вы пристально прочитали эту статью, то увидели,
что в случае с таблицей SalesOrderHeader индекс покрытия запроса уже
существует, это индекс CustomerID, который косвенно содержит столбец
SalesOrderID, являющийся ключом кластеризации таблицы.

Естественно, я разъяснял и то, почему оптимизатор запроса не стал
использовать этот индекс. Да, можно вынудить оптимизатор запроса
использовать этот индекс, но это решение было бы наименее действенным,
чем имеющийся план с операторами сканирования кластеризованного
индекса и слияния. Дело в том, что оптимизатор запроса пришлось бы
приневолить или выполнить дополнительную операцию сортировки,
нужную для использования слияния, или откатиться вспять, к
использованию наименее действенного оператора совпадения значений хэша.
В обоих вариантах ориентировочная цена выполнения выше, чем в
существующем плане (версия с оператором сортировки работала бы
в особенности плохо), потому оптимизатор запроса не будет их
использовать без принуждения. Итак, в данной ситуации лучше
сканирования кластеризованного индекса будет работать только
некластеризованный индекс в столбцах SalesOrderID, CustomerID. При
этом необходимо отметить, что столбцы должны идти конкретно в таком
порядке:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

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

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

Индекс покрытия

Индекс, сделанный из таблицы SalesOrderDetail,
представляет собой так именуемый «индекс покрытия». Это
некластеризованный индекс, где содержатся все столбцы, нужные
для выполнения запроса. Он избавляет необходимость сканирования всей
таблицы при помощи операторов сканирования таблицы либо
кластеризованного индекса. На самом деле индекс представляет собой
уменьшенную копию таблицы, где содержится подмножество ее столбцов.
В индекс врубаются только столбцы, которые нужны для ответа на
запрос либо запросы, другими словами только то, что «покрывает» запрос.

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

Не страшитесь создавать индексы с несколькими
столбцами. Они бывают существенно полезнее индексов с одним
столбцом, и оптимизатор запросов почаще их употребляет для покрытия
запроса. Большая часть индексов покрытия содержит несколько
столбцов.

С моим примером запроса можно сделать еще
кое-что. Создав индекс покрытия таблицы SalesOrderHeader, можно
дополнительно улучшить запрос. При всем этом будет применено
сканирование некластеризованного индекса заместо кластеризованного.
Предлагаю вам выполнить это упражнение без помощи других. Попытайтесь
получить определение индекса: выясните, наличие каких столбцов
превратит его в индекс покрытия данного запроса и воздействует ли
порядок столбцов на производительность. Решение см. в боковой панели
«Упражнение. Запрос заказа покупателя».

Индексированные представления

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

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Направьте внимание на параметр WITH SCHEMABINDING,
без которого нереально сделать индекс такового представления, и
функцию COUNT_BIG(*), которая будет нужно в этом случае, если в нашем
определении индекса содержится обобщенная функция (в этом случае
SUM). Создав это представление, я могу сделать и индекс:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID
ON vTotalCustomerOrders(CustomerID)

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

Если перезапустить запрос, то итог будет
зависеть от применяемой версии SQL Server. В версиях Enterprise либо
Developer оптимизатор автоматом сравнит запрос с определением
индексированного представления и употребляет это представление,
заместо того чтоб обращаться к начальной таблице. На рис. 6 приведен пример получившегося плана
выполнения. Он состоит из одной-единственной операции — сканирования
кластеризованного индекса, который я сделал на базе представления.
Ориентировочная цена выполнения составляет всего 0,09023 и при
выполнении запроса делается 92 логических считывания.

Оптимизация производительности запросов SQL Server
Рис.
6 План выполнения при
использовании индексированного представления

Это индексированное представление можно создавать
и использовать и в других версиях SQL Server, но для получения
аналогичного эффекта нужно поменять запрос и добавить прямую
ссылку на представление при помощи подсказки NOEXPAND, приблизительно
так:

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

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

Поиск запросов,
нуждающихся в настройке

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

К огорчению, самый надежный способ достаточно сложен
и предугадывает отслеживание всех выполненных запросов к серверу с
следующий группировкой по подписям. При всем этом текст запроса с
реальными значениями характеристик заменяется на замещающий текст,
который позволяет избрать однотипные запросы с различными значениями.
Подписи запроса сделать тяжело, так что это непростой процесс. Ицик
Бен-Ган (Itzik Ben-Gan) обрисовывает решение с внедрением
пользовательских функций в среде CLR и постоянных выражений в собственной
книжке «Microsoft SQL Server 2005 изнутри: запросы T-SQL».

Существует очередной способ, куда более обычной, но
не настолько надежный. Можно положиться на статистику всех запросов,
которая хранится в кэше плана выполнения, и опросить их с
внедрением динамических административных представлений. На рисунке 7 есть пример запроса текста и плана
выполнения 20 запросов из кэша, у каких полное количество
логических считываний оказалось наибольшим. При помощи этого
запроса очень комфортно стремительно отыскивать запросы с наибольшим
количеством логических считываний, но есть и некие ограничения.
Он показывает только запросы с планами, кэшированными на момент
пуска. Не кэшированные объекты не показываются.

Рис. 7  Поиск 20 самых дорогих исходя из убеждений ввода/вывода при считывании запросов.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

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

Успешной опции!

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.

Информация поездки автобусом