Исследование планов запросов в SQL (перевод с англ.)

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

Dynamic Management View (DMV) sys.dm_db_index_usage_stats представление, которое хранит информацию об использовании индексов, можем использовать DMV для поиска индексов, у которых возможно есть проблемы. DMV предоставляет данные об итоговых показателях использования индексов, таких как сканирование, доступ или поиск, можем использовать DMV не только для поиска индексов, но и баз данных, на которые следует обратить внимание.
Давайте рассмотрим информацию, которую мы можем найти полезной в  DMV: 

• Сканирование (scan): приводит к снижению производительности, т.к. поиск идет по всему индексу. Вероятно, вам нужно оптимизировать запросы, которые используют индекс приводящий к большому количеству сканирований, путем изменения запроса или индекса. 

• Доступ (seek): в отличие от перебора является очень правильным использованием индекса, сравнивая пропорцию переборов и сканирований можем находить индексы, приводящие к проблемам с производительностью.

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

Пропускаю методику оптимизации, потому что эта статья сфокусирована на том как найти возможности для оптимизации.
sys.dm_db_index_usage_stats DMV содержит информацию о действиях пользователя и системых действиях, поэтому у нас два поля user_scans и system_scans, но мы можем не учитывать системную информацию.
Первый шаг заключается в том, чтобы определить базу данных, которая наиболее подвержена проблеме исследования. Нам нужен запрос для каждого вида проблемы, а именно сканирование или поиск (доступ не является проблемой, но соотношение сканирования и перебора - может помочь выявить проблемные запросы).

Идентификация проблемы перебора

Давайте рассмотрим запрос, для поиска базы данных с наибольшим количеством переборов:

select db_name(database_id),max(user_scans) bigger,
avg(user_scans) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average desc

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

Вот результат моего SQL Server:

 

Как видим у базы  adventureworks2012 много операций сканирования.
Только после того как мы выбрали базу данных мы можем получить имена индексов, в которых возможно есть проблемы. Для того, чтобы это сделать, нам нужно соединить информацию из DMV с sys.indexes для того, чтобы получить имя индекса.

Этот запрос нужно выполнить в выбранной базе данных (конечно, вы измените название базы данных в запросе):

Use adventureworks2012 /* <<<<------- вам нужно изменить название базы данных */
select object_name(c.object_id) as [table],
c.name  as [index],user_scans,user_seeks,
    case a.index_id
    when 1 then 'CLUSTERED'
    else 'NONCLUSTERED'
    end as type
from sys.dm_db_index_usage_stats a
      inner join sys.indexes c
on  c.object_id=a.object_id and c.index_id=a.index_id
    and database_id=DB_ID('AdventureWorks2012')   /*  <<<<------- вам нужно изменить название базы данных */
order by user_scans desc

Обратите внимание, что я также использовал  index_id для определения типа индекса: кластерный или не кластерный. Также я включил информацию по доступу (seek), теперь мы можем сравнивать сканирование и доступ для принятия решения о том какой индекс заслуживает нашего внимания. 

Можем четко отметить, что у таблицы 'bigproduct' и ее кластерного индекса есть много операций сканирования, на которых следует сосредоточиться.

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

Нахождение планов запроса, приводящих к сканированиям

Итак, мы определили базу данных и индексы, которые заслуживают нашего внимания, но теперь как найти те планы, по которым мы определим проблемные запросы?
Следующим этапом можем использовать SQL для поиска планов проблемных запросов в кэше, после этого мы сможем выяснить какой запрос нужно оптимизировать.
Используя DMV sys.dm_exec_query_stats  мы можем получить все запросы в кэше и идентифицировать те, у которых планы запросов с проблемой.
У DMV есть возможность получения плана запроса и текста запроса. Для этого будем использовать sys.dm_exec_query_plan и sys.dm_exec_sql_text (Dynamic Management Function, сокр. DMF), соответственно.

Запрос:

select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp

Поле query_plan это XML, но если вы посмотрите результат запроса, то SSMS распознает схему и покажет план графически, если вы нажмете на ссылку. Это удобно для исследования отдельных планов, но не для систематического поиска по планам запросов удовлетворяющим определенным критериям. Если не отфильтровать результаты основанные на XML, нам нужно будет рассматривать каждый. Наилучший вариант это использовать Xquery.

Если нажать на поле query_plan мы увидим план запроса графически

Для правильного поиска в XML используя XQuery, нам нужна информация о схеме, которую использует XML.

Схема этого XML опубликована по адресу http://schemas.microsoft.com/sqlserver/2004/07/showplan
Существует множество возможностей для поиска проблем выполнения используя этот метод, но я могу показать только несколько. Для того чтобы накладывать различные условия, вам нужно изучить схему.
Изучив схему, выяснили, что у нас есть элемент RelOp с атрибутом LogicalOp, который можно использовать для поиска всех планов, у которых происходит сканирование индекса или таблицы. Давайте рассмотрим запрос:

select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Scan"
            or @LogicalOp="Clustered Index Scan"
            or @LogicalOp="Table Scan"]')=1

XML плана запроса типизирован, значит можем описать пространство имен для того, чтобы использовать xquery надлежащим образом. Схема немного сложна и вам нужно быть осторожными при создании вашего запроса. Для примера, будет ошибкой поиск по елементу  IndexScan поскольку этот элемент используется для всех операций индексов, включая сканирование и поиск.
Следующий шаг это отобрать результат по конкретному индексу. Мы уже нашли индексы с наибольшим количеством сканирований, теперь можем выяснить какой план приводит к этому. Следуя схеме, имя индекса это атрибут элемента Object, внутри элемента IndexScan, который внтури элемента RelOp.

Итак, запрос:

select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp/qplan:IndexScan/qplan:Object[@Index="[pk_bigProduct]"]')=1

Если в результате этого запроса получаем очень много планов, то можно отсортировать по полям из sys.dm_exec_query_Stats для поиска плана запроса, который нужно оптимизировать. Для примера, можем использовать поле total_worker_time чтобы упорядочить результаты по времени нагрузки на процессор, вот так:

select qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Scan"
            or @LogicalOp="Clustered Index Scan"
            or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[@Index="[pk_bigProduct]"]')=1
order by total_worker_time desc  

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

 

Мы нашли именно тот запрос, который приводит к проблеме и...

 

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

Поиск проблем поиска

Другой пример использования DMVs это поиск плана с поиском по индексу. Если мы изменим первый запрос для вычисления количества поисков, мы отметим множество операций поиска в базе данных 'adventureworks2012':

select 
db_name(database_id),
max(user_lookups) bigger,
avg(user_lookups) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average desc

Ищем индекс, который является причиной избыточных (на наш взгляд) операций поиска:

use adventureworks2012 /* <<<<<------- you will need to change the database name */
select object_name(c.object_id) as [table],
c.name as [index],user_lookups,
     case a.index_id
     when 1 then 'CLUSTERED'
     else 'NONCLUSTERED'
     end as type
from sys.dm_db_index_usage_stats a
 inner join sys.indexes c
on  c.object_id=a.object_id and c.index_id=a.index_id
    and database_id=DB_ID('AdventureWorks2012')   /* <<<<<------- you will need to 
change the database name */
order by user_lookups desc

В Adventureworks2012 есть также проблема поиска

И наконец, чтобы найти планы запросов с операциями поиска, нужно отобрать по атрибуту поиска в элементе IndexScan:

select qp.query_plan,qt.text, plan_handle,query_plan_hash from 
sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

//AWMI:IndexScan[@Lookup]/AWMI:Object[@Index="[PK_TransactionHistory_TransactionID]"]')=1

В этом примере мы выясним, что если убрать два поля 'quantity' и 'actualcost' из запроса, то проблема поиска исчезнет. Конечно, это может быть возможно не во всех случаях, в некоторых случаях нужно искать другое решение, но эта статья не об этом.

Выделена причина поиска

Модификация запросов в практическое решение

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

Метод XQuery 'exist()' принимает константу как параметр. Единственное решение передавать имя индекса в переменную это использовать выражение sql:variable для доступа к переменным из внутреннего выражения xquery.

Вот то что получим:

Create FUNCTION [dbo].[FindScans] 
( 
 -- Add the parameters for the function here
 @Index varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
select qp.query_plan,qt.text,total_worker_time from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Scan"
            or @LogicalOp="Clustered Index Scan"
            or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1
)
GO

Обратите внимание, я использовал функцию 'fn:lower-case', иначе функция могла быть регистро зависима.
Теперь запрос для поиска плана в кеше, который выполняет сканирование индекса для конкретного индекса становиться простым select:

select * from dbo.FindScans('[pk_bigProduct]')

Аналогично для проблемы поиска:

CREATE FUNCTION FindLookups 
( 
 -- Add the parameters for the function here
 @Index varchar(50) 
)
RETURNS TABLE 
AS
RETURN 
(
select qp.query_plan,qt.text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //AWMI:IndexScan[@Lookup]/AWMI:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1

)
GO

select * from dbo.FindLookups('[PK_TransactionHistory_TransactionID]')

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

 Create FUNCTION [dbo].[FindScans] 
( 
 -- Add the parameters for the function here
 @Index varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
select qp.query_plan,qt.text, 
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time, total_rows,
last_rows, min_rows,
max_rows from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Scan"
            or @LogicalOp="Clustered Index Scan"
            or @LogicalOp="Table Scan"]/qplan:IndexScan/qplan:Object[fn:lower-
case(@Index)=fn:lower-case(sql:variable("@Index"))]')=1
)
GO

Create FUNCTION FindLookups 
( 
 -- Add the parameters for the function here
 @Index varchar(50) 
)
RETURNS TABLE 
AS
RETURN 
(
select qp.query_plan,qt.text, 
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time, total_rows,
last_rows, min_rows,
max_rows from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //AWMI:IndexScan[@Lookup]/AWMI:Object[fn:lower-case(@Index)=fn:lower-
case(sql:variable("@Index"))]')=1

)

GO

Источник: http://infostart.ru/public/146288/