• Главная
  •  > 
  • Настройка Windows 10
  •  > 
  • Как получить план выполнения запросов? Повышение производительности запроса План выполнения sql запроса.

Как получить план выполнения запросов? Повышение производительности запроса План выполнения sql запроса.

План выполнения SQL-запроса, или план запроса, - это последовательность шагов или инструкций СУБД, необходимых для выполнения SQL-запроса. На каждом шаге операция, инициировавшая данный шаг выполнения SQL-запроса, извлекает строки данных, которые могут формировать конечный результат или использоваться для дальнейшей обработки. Инструкции плана выполнения SQL-запроса представляются в виде последовательности операций, которые ВЫПОЛНЯЮТСЯ СУБД ДЛЯ предложений SQL SELECT, INSERT, delete и update. Содержимое плана запроса, как правило, представляется древовидной структурой и включает в себя следующую информацию:

  • порядок соединения источников данных (таблиц, представлений и т.п.);
  • метод доступа для каждого источника данных;
  • методы соединения источников данных;
  • операции ограничения выбора данных, сортировки и агрегирования;
  • стоимость и кардинальность каждой операции;
  • возможное использование секционирования и параллелизма. Информация, предоставляемая планом выполнения SQL-запроса, позволяет разработчику увидеть, какие подходы и методы выбирает оптимизатор для выполнения SQL-операций.

Интерпретация плана выполнения SQL-запроса

Визуализация плана выполнения SQL-запроса зависит от инструментов и средств разработки, которые могут как входить в состав СУБД, запрос которой представляет интерес для анализа, так и являться отдельными коммерческими или свободно распространяемыми программными продуктами, не имеющими прямого отношения к конкретному производителю СУБД. Использование того или иного инструмента визуализации плана выполнения запроса, как правило, существенно не влияет на восприятие того, что описывает представленный план запроса. Определяющей в процессе анализа того, каким путем пойдет оптимизатор при выполнении конкретного запроса, является способность верно интерпретировать информацию, которая представлена в плане запроса.

Как уже упоминалось, план SQL-запроса имеет древовидную структуру, которая описывает не только последовательность выполнения SQL-операций, но также и связь между этими операциями. Каждый узел дерева плана запроса - это операция, например сортировка, или метод доступа к таблице. Между узлами существует взаимосвязь родитель-потомок. Отношения родитель-потомок регулируются по следующим правилам:

  • родитель может иметь одного или нескольких потомков;
  • потомок имеет только одного родителя;
  • операция, не имеющая родительской операции, является вершиной дерева;
  • в зависимости от метода визуализации плана SQL-запроса потомок располагается с некоторым отступом относительно родителя. Потомки одного родителя располагаются на одинаковом расстоянии от своего родителя.

Рассмотрим более подробно информацию, представляемую планом выполнения SQL-запроса. Приведенные примеры выполнены в среде СУБД Oracle. В качестве инструмента выполнения запросов и визуализации плана SQL-запросов был использован Oracle SQL Developer. Фрагмент плана SQL-запроса представлен на рис. 10.11.

I Id I Operation

  • 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK PRODUCT INFORMATION

SELECT STATEMENT SORT ORDER BY NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL INDEX UNIQUE SCAN TABLE ACCESS BY INDEX ROWID

Рис. 10.11. Фрагмент плана выполнения SQL-запроса в среде СУБД Oracle

Используя правила отношения операций плана запроса, можно определить следующее их формальное описание.

Операция 0 - корень дерева плана запроса. Корень имеет одного потомка: операция 1.

Операция 1 - операция имеет одного потомка: операция 2.

Операция 2 - операция имеет двух потомков: операция 3 и операция 6.

Операция 3 - операция имеет двух потомков: операция 4 и операция 5.

Операция 4 - операция не имеет потомков.

Операция 5 - операция не имеет потомков.

Операция 6 - операция не имеет потомков.

Взаимодействие родитель-потомок между операциями плана запроса представлено на рис. 10.12.

Операции, выполняемые в плане запроса, можно разделить на три типа: автономные, операции не связанного объединения и операции связанного объединения (рис. 10.13).

Автономные

Операции несвязанного

Операции связанного

операции

объединения

объединения

Рис. 10.12.


Рис. 10.13.

Автономные операции - это операции, которые имеют не более одной дочерней операции.

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

  • 2. Каждая дочерняя операция выполняется только один раз.
  • 3. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.14 представлен план следующего запроса:

SELECT o.order_id ,о.order_status FROM orders о ORDER BY о.order_status

Данный запрос содержит только автономные операции.

Учитывая правила следования автономных операций, последовательность их выполнения будет следующая.

  • 1. В соответствии с правилом следования автономных операций № 1 первой будет выполнена операция с ID = 2. Выполняется последовательное чтение всех строк таблицы orders.
  • 2. Далее выполняется операция с ID = 1. Выполняется сортировка строк, возвращаемых операцией с ID = 2, по условию предложения сортировки ORDER BY.
  • 3. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

Операции несвязанного объединения

Операции несвязанного объединения - это операции, которые имеют более одной независимо выполняемой дочерней операции. Пример: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

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

  • 1. Дочерняя операция выполняется перед родительской операцией.
  • 2. Дочерние операции выполняются последовательно, начиная с наименьшего значения ID операции в порядке возрастания этих значений.
  • 3. Перед началом работы каждой следующей дочерней операции текущая операция должна быть выполнена полностью.
  • 4. Каждая дочерняя операция выполняется только один раз независимо от других дочерних операций.
  • 5. Каждая дочерняя операция возвращает свой результат родительской операции.

На рис. 10.15 представлен план следующего запроса:

SELECT o.order_id from orders о UNION ALL

SELECT oi.order_id from order_items oi

Данный запрос содержит операцию несвязанного объединения UNION all. Остальные две операции являются автономными.

Рис. 10.15. Операции несвязанного объединения, план запроса

1 SELECT STATEMENT I

Учитывая правила следования операций несвязанного объединения, последовательность их выполнения будет следующей.

  • 1. В соответствии с правилами 1 и 2 следования операций несвязанного объединения первой будет выполнена операция с ID = 2. Выполняется последовательное чтение всех строк таблицы orders.
  • 2. В соответствии с правилом 5 операция с ID = 2 возвращает считанные на шаге 1 строки родительской операции с ID = 1.
  • 3. Операция с ID = 3 начнет выполняться, только когда закончится операция с ID = 2.
  • 4. После окончания выполнения операции с ID = 2 начинает выполняться операция с ID = 3. Выполняется последовательное чтение всех строк таблицы order_items.
  • 5. В соответствии с правилом 5 операция с ID = 3 возвращает считанные на шаге 4 строки родительской операции с ID = 1.
  • 6. Операция с ID = 1 формирует результирующий набор данных на основе данных, полученных от всех ее дочерних операций (с ID = 2 и ID = 3).
  • 7. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

Таким образом, можно отметить, что операция независимого объединения последовательно выполняет свои дочерние операции.

Операции связанного объединения

Операции связанного объединения - это операции, которые имеют более одной дочерней операции, причем одна из операций контролирует выполнение остальных. Пример: nested loops, update.

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

  • 1. Дочерняя операция выполняется перед родительской операцией.
  • 2. Дочерняя операция с наименьшим номером операции (ID) контролирует выполнение остальных дочерних операций.
  • 3. Дочерние операции, имеющие общую родительскую операцию, выполняются, начиная с наименьшего значения ID операции в порядке возрастания этих значений. Остальные дочерние операции выполняются НЕ последовательно.
  • 4. Только первая дочерняя операция выполняется один раз. Все остальные дочерние операции выполняются несколько раз либо не выполняются совсем.

На рис. 10.16 представлен план следующего запроса:

FROM order_items oi, orders о

WHERE o.order_id= oi.order_id

AND oi.product_id>100

AND о.customer_id between 100 and 1000

Данный запрос содержит операцию связанного объединения NESTED LOOPS.

I Id I Operation

SELECT STATEMENT |

Рис. 10.16. Операции связанного объединения, план запроса

Учитывая правила следования операций связанного объединения, последовательность их выполнения будет следующей.

  • 1. В соответствии с правилами 1 и 2 следования операций связанного объединения первой должна быть выполнена операция с ID = 2. Однако операции с 1D = 2 и 1D = 3 являются автономными, и в соответствии с правилом 1 следования автономных операций первой будет выполнена операция с ID = 3. Выполняется просмотр диапазона индекса ORDCUSTOMERIX по условию: о. customer id between 100 and 1000.
  • 2. Операция с ID=3 возвращает родительской операции (с Ш=2) список идентификаторов строк Rowld, полученных на шаге 1.
  • 3. Операция с ID = 2 выполняет чтение строк в таблице orders, в которых значение Rowld соответствует списку значений Rowld, полученных на шаге 2.
  • 4. Операция с ID = 2 возвращает считанные строки родительской операции (с ID = 1).
  • 5. Для каждой строки, возвращаемой операцией с ID = 2, выполняется вторая дочерняя операция (с ID = 4) операции nested loops. То есть для каждой строки, возвращаемой операцией с ID = 2, выполняется полный последовательный просмотр таблицы order_items с целью найти соответствие по атрибуту соединения.
  • 6. Шаг 5 повторяется столько раз, сколько строк возвращает операция с ID = 2.
  • 7. Операция с ID = 1 возвращает результаты работы родительской операции (с ID = 0).
  • 8. Выполняется операция с ID = 0. Возвращается результирующий набор данных.

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

SELECT с. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) as product_qty,

SUM(oi.quantity* oi.unit_price) as total_cost FROM oe.orders о INNER JOIN customers c ON

о.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. cust_first_name

Последовательность операций плана данного запроса представлена на рис. 10.17.

SELECT STATEMENT I

SORT GROUP BY ЇГ

TABLE ACCESS FULL

INDEX RANGE SCAN

TABLE ACCESS BY INDEX ROWIDd

TABLE ACCESS FULL

Рис. 10.17. План запроса, последовательность выполнения операций

Опишем возможный подход к интерпретации плана выполнения 80Ь-запроса, представленного на рис. 10.17. Данный подход включает в себя два основных этапа: декомпозиция операций на блоки и определение порядка выполнения операций.

На первом этапе необходимо выполнить декомпозицию выполняемых операций на блоки. Для этого находим все операции объединения, т.е. операции, которые имеют более одной дочерней операции (на рис. 10.17 это операции 2, 3 и 4), и выделяем эти дочерние операции в блоки. В результате, используя пример на рис. 10.17, получаем три операции объединения и семь блоков операций.

На втором этапе определяется последовательность выполнения блоков операций. Для этого необходимо применить правила следования операций, описанные выше. Выполним ряд рассуждений по вопросу выполнения каждой операции относительно ее идентификационного номера (Ш).

Операция Ш = 0 - автономная и является родительской для операции сШ = 1.

Операция Ю = 1 тоже автономная; является родительской для операции Ш = 2 и выполняется перед операцией Ю = 0.

Операция ГО = 2 - операция несвязанного объединения и является родительской для операций Ю = 3, Ю = 8. Операция ГО = 2 выполняется перед операцией ГО = 1.

Операция ГО = 3 - операция связанного объединения, является родительской для операций ГО = 4, ГО = 7. Операция ГО = 3 выполняется перед операцией ГО = 2.

Операция ГО = 4 - операция связанного объединения, является родительской для операций ГО = 5, ГО = 6. Операция ГО = 4 выполняется перед операцией ГО = 3.

Операция ГО = 5 - автономная операция, выполняется перед операцией ГО = 4.

Операция ГО = 6 - автономная операция, выполняется перед операцией ГО = 5.

Операция ГО = 7 -автономная операция, выполняется после выполнения блока операций «С».

Операция ГО = 8 - автономная операция, выполняется после блока операций «Е».

На основе проведенных рассуждений и правил следования сформулируем последовательность выполняемых операций:

  • 1. Первой выполняется автономная операция ГО = 5, см. правила следования операций связанного объединения. Выполняется последовательное чтение всей таблицы.
  • 2. Результат операции ГО = 5 - считанные строки таблицы - передается операции ГО = 4.
  • 3. Выполняется операция ГО = 4: для каждой строки, возвращенной операцией ГО = 5, выполняется операция ГО = 6. То есть выполняется сканирование диапазона индекса по атрибуту соединения. Получение списка идентификаторов строк Яоу1с1.
  • 4. Результат операции ГО = 4 передается операции ГО = 3. То есть передается список идентификаторов строк Кош1с1.
  • 5. Выполняется операция ГО = 3: для каждого значения 11оу1с1, возвращенного в результате работы блока операций «С», выполняется операция ГО = 7, т.е. выполняется чтение строк таблицы по заданному списку идентификаторов строк ИтмЫ, полученных после выполнения операции Ш = 4.
  • 6. Выполняется автономная операция ГО = 8 - последовательное чтение всей таблицы.
  • 7. Выполняется операция несвязанного объединения ГО = 2: выполняется соединение хэшированием результатов работы блоков операций «Е» и «Е».
  • 8. Результат операции ГО = 2 передается операции ГО = 1.
  • 9. Выполняется операция несвязанного объединения ГО = 1: выполняется агрегирование и сортировка данных, полученных в результате работы операции ГО = 2.
  • 10. Выполняется операция ГО = 0. Возвращается результирующий набор данных.

Правила следования, сформулированные для основных типов операций, применимы для большинства планов выполнения БСГО-запроса. Однако существуют конструкции, используемые в БСГО-запросах, которые предполагают нарушение порядка выполнения операций, описанных в правилах следования. Такие ситуации могут появляться в результате использования, например, подзапросов или предикатов антисоединения. В любом случае процесс интерпретации плана выполнения БСГО-запроса не предполагает только использование ряда правил, которые обеспечат именно максимально верный анализ того, что собирается делать оптимизатор при выполнении 8СГО-запроса. Очередной БСГО-запрос - это всегда индивидуальный случай; и то, как он будет выполнен в СУБД, зависит от множества факторов, среди которых версия СУБД, версия и тип операционной системы, на которой развернут экземпляр СУБД, используемая аппаратная часть, квалификация автора 80Ь-запроса и т.д.

Александр Куклин написал отличную статью «Кэш планов и параметризация запросов. Часть 1. Анализ кэша планов «. Всем рекомендую к ознакомлению.

Вот небольшая вырезка из неё:

Процессор запросов (query processor), который и занимается выполнением SQL-запросов, поступивших на SQL-сервер, и выдачей их результатов клиенту, состоит из двух основных компонентов:

  1. Оптимизатор запросов (Query Optimizer).
  2. Исполнитель запросов (Relational Engine).

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

  1. Синтаксический анализатор (Parser) просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы, а также производит нормализацию запроса.
  2. Из синтаксического анализатора данные попадают на вход компонента Algebrizer, который выполняет семантический анализ текста. Algebrizer проверяет существование указанных в запросе объектов базы данных и их полей, корректность использования операторов и выражений запроса, и извлекает из кода запроса литералы, для обеспечения возможности использования автоматической параметризации.
    Например, именно поэтому запрос, имеющий в секции SELECT поля, не содержащиеся ни в агрегатных функциях, ни в секции GROUP BY, пройдёт в SQL Server Management Studio (SSMS) проверку по Ctrl+F5 (синтаксический анализ), но свалится с ошибкой при попытке запуска по F5 (не пройдёт семантический анализ).
  3. Далее Algebrizer строит дерево разбора запроса с описанием логических шагов, необходимых для преобразования исходных данных к желаемому результату. Для дерева запроса извлекаются метаданные объектов запроса (типы данных, статистика индексов и т.д.), производятся неявные преобразования типов (при необходимости), удаляются избыточные операции (например, ненужные или избыточные соединения таблиц).
  4. Затем оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. И выбирает ряд шагов, которые, по мнению оптимизатора, возвращают результаты быстрее всего и используют меньше ресурсов. В дерево запроса записывается последовательность этих полученных шагов и из конечной, оптимизированной версии дерева генерируется план выполнения запроса.

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

История стара как мир. Две таблицы:

  • Cities – 100 уникальных городов.
  • People – 10 млн. людей. У некоторых людей город может быть не указан.
Распределение людей по городам – равномерное.
Индексы на поля Cites.Id, Cites.Name, People .CityId – в наличии.

Нужно выбрать первых 100 записей People, отсортированных по Cites.

Засучив рукава, бодро пишем:

Select top 100 p.Name, c.Name as City from People p
order by c.Name

При этом мы получим что-то вроде:

За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)

Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.

Почему SQL сервер, обладая статистикой, не делает так:

Select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.

Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.

Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).

Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?

P.S.
CREATE TABLE . (


uniqueidentifier
ON
GO

CREATE TABLE . (
uniqueidentifier NOT NULL,
nvarchar(50) NOT NULL,
ON
GO

P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.

P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.

В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи)

P.P.P.P.S Попробовал. Сработало!

WITH Help AS
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
SELECT TOP 100 * FROM help

Дает 150 миллисекунд при тех же условиях! Спасибо

1 msdevcon.ru #msdevcon

3 Олонцев Сергей SQL Server MCM, MVP Лаборатория Касперского

4 Structured Query Language

5 Пример запроса select pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber from HumanResources.Employee as emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid where pers.firstname = N"John" and emp.hiredate >= " "

6 Логическое дерево запроса Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" and emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers Get Data Get Data HumanResources.Employee as emp

7 План запроса Показывает, как происходит исполнение T-SQL запроса на физическом уровне.

8 Несколько способов

9 DEMO Простой план Выбор всех данных из таблицы, как получить план запроса

11 Методы оператора Init() Метод Init() заставляет физический оператор инициализировать себя и подготовить все необходимые структуры данных. Физический оператор может получать много вызовов Init(), хотя обычно получает лишь один. GetNext() Метод GetNext() заставляет физический оператор получить первую или последующую строку данных. Физический оператор может получить много вызовов GetNext() или не получить ни одного. Метод GetNext() возвращает одну строку данных, а число его вызовов отображается значением ActualRows в выводе инструкции Showplan. Close() При вызове метода Close() физический оператор выполняет некоторые действия по очистке и закрывается. Физический оператор получает только один вызов Close().

12 Взаимодействие между операторами Operator 1 Operator 2 Operator 3

13 Взаимодействие между операторами 1. Request Row Operator 1 Operator 2 Operator 3

14 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3

15 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 3. Send Row

16 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 4. Send Row 3. Send Row

17 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 4. Send Row 3. Send Row

18 DEMO Оператор TOP Или почему лучше оператор называть итератором

19 Таблиц не существует!

20 HoBT Page 1 Page 2 Page 3 Page 4 Row 1 Row 3 Row 5 Row 7 Row 2 Row 4 Row 6 Row 8

21 HoBT Page Page Page Page Page Page Page

22 DEMO Операторы доступа к данным Scan, Seek, Lookup

23 У кого в базе данных есть только одна таблица?

24 Nested Loops, Hash Join и Merge Join

25 Операторы соединения Nested Loops inner join, left outer join, left semi join, left anti semi join Merge Join inner join, left outer join, left semi join, left anti semi join, right outer join, right semi join, right anti semi join, union Hash Join все типы логических операций

26 DEMO Операторы соединения, сортировки и первый оператор Nested Loops, Merge Join, Hash Join, Sort, First Operator

27 Предупреждения

28 DEMO Ошибки и предупреждения в планах запросов

29 Я знаю, что ничего не знаю. Сократ

30 DEMO Небольшой пример непонятного

31 Диагностика планов запросов -- TOP 10 запросов, которые потребляю больше всего CPU и их планы select top(10) substring(t.text, qs.statement_start_offset / 2, case when qs.statement_end_offset = -1 then len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast(qs.total_worker_time * 1. / qs.execution_count / as decimal(18, 2)) as avg_worker_time_ms, cast(p.query_plan as xml) as query_plan from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as t cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as p order by qs.total_worker_time desc; go

32 Техника чтения больших планов запросов Пробовать разбивать на логические блоки и анализировать постепенно. В SSMS при графическом отображении плана в правом нижнем углу появляется кнопка для более удобной навигации по плану запроса. Можно использовать XQuery\XPath.

33 DEMO Большой план запроса

35 DEMO SQL Sentry Plan Explorer

36 Подведем итоги Первый оператор Optimization level Compile time Size in cache Parameters, Compile Values Reason for Early Termination Стоимость итераторов Смотрите в первую очередь на операторы с самой высокой стоимостью. Не забывайте, что это всего лишь предполагаемые значения (даже в актуальных планах выполнения).

37 Подведем итоги Bookmark\Key Lookup Если их мало, то скорее всего проблемы нет. Если их много, создание покрывающего индекса поможет от них избавиться. Предупреждения Необходимо проверить, почему оно возникает и при необходимости принять меры.

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

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

40 Вопросы

41 Контакты Олонцев Сергей Лаборатория Касперского

42 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Оптимизация запросов в SQL Server 2005, статистика баз данных SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выполнения запросов, количество логических чтений (logical reads), хинты оптимизатора (optimizer hints), MAXDOP, OPTIMIZE FOR, руководства по планам выполнения (plan guides), sp_create_plan_guide

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

Отношение к оптимизации запросов у многих специалистов неоднозначное. С одной стороны, работа программного модуля Query Optimizer , который генерирует планы выполнения запросов, вызывает множество справедливых нареканий и в SQL Server 2000, и в SQL Server 2005. Query Optimizer часто выбирает не самые оптимальные планы выполнения запросов и в некоторых ситуациях проигрывает аналогичным модулям из Oracle и Informix . С другой стороны, ручная оптимизация запросов - процесс чрезвычайно трудоемкий. Вы можете потратить много времени на такую оптимизацию и, в конце концов, выяснить, что ничего оптимизировать не удалось: план, предложенный Query Optimizer изначально, оказался наиболее оптимальным (так бывает в большинстве случаев). Кроме того, может случиться так, что созданный вами вручную план выполнения запросов через какое-то время (после добавления новой информации в базу данных) окажется неоптимальным и будет снижать производительность при выполнении запросов.

Отметим также, что для выбора наилучших планов построения запросов Query Optimizer необходима правильная информация о статистике. Поскольку, по опыту автора, далеко не все администраторы знают, что это такое, расскажем о статистике подробнее.

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

Для баз данных SQL Server 2005 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS . При этом статистика для столбцов баз данных будет создаваться и обновляться автоматически. Для самых больших и важных баз данных может получиться так, что операции по созданию и обновлению статистики могут мешать текущей работе пользователей. Поэтому для таких баз данных иногда эти параметры отключают, а операции по созданию и обновлению статистики выполняют вручную в ночное время. Для этого используются команды CREATE STATISTICS и UPDATE STATISTICS .

Теперь поговорим об оптимизации запросов.

Первое, что необходимо сделать, - найти те запросы, которые в первую очередь подлежат оптимизации. Проще всего это сделать при помощи профилировщика, установив фильтр на время выполнения запроса (фильтр Duration в окне Edit Filter (Редактировать фильтр), которое можно открыть при помощи кнопки Column Filters на вкладке Events Selection окна свойств сеанса трассировки). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5секунд. Кроме того, можно использовать информацию о запросах, которая предоставляется Database Tuning Advisor .

Затем нужно проверить, устанавлен ли для ваших соединений, хранимых процедур и функций параметр NOCOUNT . Установить его можно при помощи команды SET NOCOUNT ON . При установке этого параметра, во-первых, отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (т. е. не отображается строка "N row(s) affected" на вкладке Messages (C ообщения) окна работы с кодом при выполнении запроса в Management Studio ). Во-вторых, отключается передача специального серверного сообщения DONE_IN_PROC , которое по умолчанию возвращается для каждого этапа хранимой процедуры. При вызове большинства хранимых процедур нужен только результат их выполнения, а количество обработанных строк для каждого этапа никого не интересует. Поэтому установка параметра NOCOUNT для хранимых процедур может серьезно повысить их производительность. Повышается скорость выполнения и обычных запросов, но в меньшей степени (до 10%).

После этого можно приступать к работе с планами выполнения запросов.

План выполнения запроса проще всего просмотреть из SQL Server Management Studio . Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если вы хотите узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 11.15).

Рис. 11.15. План выполнения запроса в SQL Server Management Studio

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

q вначале в окне Management Studio выполните команду SET STATISTICS IO ON . В результате после каждого выполнения запроса будет выводиться дополнительная информация. В ней нас интересует значение только одного параметра - Logical Reads . Этот параметр означает количество логических чтений при выполнении запросов, т. е. сколько операций чтения пришлось провести при выполнении данного запроса без учета влияния кэша (количество чтений и из кэша, и с диска). Это наиболее важный параметр. Количество физических чтений (чтений только с диска) - информация не очень представительная, поскольку зависит от того, были ли перед этим обращения к данным таблицам или нет. Статистика по времени также является величиной переменной и зависит от других операций, которые выполняет в это время сервер. А вот количество логических чтений - наиболее объективный показатель, на который в наименьшей степени влияют дополнительные факторы;

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

Хинтов оптимизатора в SQL Server 2005 предусмотрено много. Прочитать информацию о них можно в Books Online (в списке на вкладке Index (Индекс) нужно выбрать Query Hints [ SQL Server ] (Хинты запросов ), Join Hints (Хинты джойнов) или Table Hints [ SQL Server ] (Табличные хинты )). Чаще всего используются следующие хинты:

q NOLOCK , ROWLOCK , PAGLOCK , TABLOCK , HOLDLOCK , READCOMMITTEDLOCK , UPDLOCK , XLOCK - эти хинты используются для управления блокировками (см. разд. 11.5.7) ;

q FAST количество_строк - будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное количество строк (первых с начала набора записей). Если пользователю нужны именно первые записи (например, последние заказы), то для их максимально быстрой загрузки в окно приложения можно использовать этот хинт;

q FORCE ORDER - объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;

q MAXDOP (от Maximum Degree of Parallelism - максимальная степень распараллеливания запроса) - при помощи этого хинта указывается максимальное количество процессоров, которые можно будет использовать для выполнения запроса. Обычно этот хинт используется в двух ситуациях:

· когда из-за переключения между процессорами (context switching ) скорость выполнения запроса сильно снижается. Такое поведение было характерно для SQL Server 2000 на многопроцессорных системах;

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

q OPTIMIZE FOR - этот хинт позволяет указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, под значение фильтра для WHERE );

q USE PLAN - это самая мощная возможность. При помощи такого хинта можно явно определить план выполнения запроса, передав план в виде строкового значения в формате XML . Хинт USE PLAN появился только в SQL Server 2005 (в предыдущих версиях была возможность явно определять планы выполнения запросов, но для этого использовались другие средства). План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 11.15, и выбрав в контекстном меню команду Save Execution Plan As (Сохранить план выполнения как)).

В SQL Server 2005 появилась новая важная возможность, которая позволяет вручную менять план выполнения запроса без необходимости вмешиваться в текст запроса. Очень часто бывает так, что код запроса нельзя изменить: он жестко "прошит" в коде откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2005 появилась хранимая процедура sp_create_plan_guide . Она позволяет создавать так называемые руководства по планам выполнения (plan guides ), которые будут автоматически применяться к соответствующим запросам.

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

q насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы). Вполне может оказаться, что обращение к таблице при помощи индексов будет эффективнее;

q используются ли в коде курсоры. Курсоры - очень простое средство с точки зрения синтаксиса программы, но чрезвычайно неэффективное с точки зрения производительности. Очень часто можно избежать применения курсоров, используя другие синтаксические конструкции, и получить большой выигрыш в скорости работы;

q используются ли в коде временные таблицы или тип данных Table . Создание временных таблиц и работа с ними требуют большого расхода ресурсов, поэтому по возможности нужно их избегать;

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

q иногда запросы возвращают больше данных, чем реально требуется приложению (лишнее количество столбцов или строк). Конечно, это не способствует повышению производительности;

q если приложение передает на сервер команды EXECUTE , то имеет смысл подумать о том, чтобы заменить их на вызов хранимой процедуры sp_executesql . Она обладает преимуществами в производительности по сравнению с обычной командой EXECUTE ;

q повышения производительности иногда можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов. Нужно обратить внимание на применение параметров, постараться не смешивать в коде хранимой процедуры команды DML и DDL и следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS , SET ANSI_NULLS , SET ANSI_PADDING , SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными). Обычно проблема может возникнуть тогда, когда эти параметры устанавливаются на уровне отдельного запроса или в коде хранимой процедуры.

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