Cтатистика запросов

Как известно выход MS SQL 2005 ознаменовался появлением большого числа новых системных каталогов и динамических административных представлений. Последние представляют особый интерес с точки зрения мониторинга состояния сервера, работы различных процессов. Через них можно отслеживать самую разнообразную информацию: задержки в операциях ввода-вывода дисковой подсистемы, структуру и распределение оперативной памяти, используемой сервером, архитектуру областей оперативной памяти, хранящей кэши планов выполнения, статистику работы запросов, типы ожиданий и многое другое. Я хотел поговорить о статистики выполнения запросов. Как известно, есть динамическое административное представление sys.dm_exec_query_stats, которое возвращает статистику по всем запросам, которые хранятся в кэше планов выполнения. В этом представлении есть информация о количестве запусков запроса, общем времени выполнения, числе логических и физических чтений и записей, времени последнего запуска. В частности, можно отслеживать информацию об используемых в настоящее время системой запросах, о наиболее ресурсоемких запросах с точки зрения операций ввода-вывода, процессорного выполнения и пр. Каталог также содержит сведения о базе данных и идентификаторе объекта (процедуры, функции, ...).

Теперь самое интересное. В каталоге нет текста запроса и плана выполнения, но есть столбцы sql_handle, plan_handle. От каталога можно сделать outer apply на функцию sys.dm_exec_sql_text, передав ей в качестве параметра sql_handle, и получив тем самым sql-текст. Однако этот текст является текстом всего пакета, который попал в кэш. Чтобы извлечь из него текст атомарной инструкции, необходимо воспользоваться столбцами statement_start_offset и statement_end_offset, которые показывают смещение инструкции относительно текста всего пакета.

План выполнения получается примерно таким же способом. Во-первых, можно сделать outer apply на функцию sys.dm_exec_query_plan, передав ей в качестве параметра столбец plan_handle. Функция вернет план выполнения в формате xml, который также можно посмотреть в Management Studio в графическом виде. Этот план соответствует всему пакету. Для получения плана только лишь одной инструкции требуется сделать outer apply на функцию sys.dm_exec_text_query_plan. У этой замечательной функции уже 3 параметра: описатель плана и смещения, благодаря которым она возвращает xml-план выполнения для отдельной инструкции.

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

select
       query.total_elapsed_time / query.execution_count / 1000 [Среднее время выполнения (мс)],
       query.total_elapsed_time / cast ( query.total_worker_time as float ) / 1000 [Среднее процессорное время (мс)],
       query.execution_count [Число запусков],
       quotename ( db_name ( txt.dbid ) ) + N'.' + quotename ( object_schema_name ( txt.objectid, txt.dbid ) ) + N'.' + quotename ( object_name ( txt.objectid, txt.dbid ) ) [Объект],
       substring
       (
             txt.text,
             1 + query.statement_start_offset / 2,
             ( isnull ( nullif ( query.statement_end_offset, -1 ), 2 * len ( cast ( txt.text as nvarchar ( max ) ) ) ) - query.statement_start_offset ) / 2
       ) [Текст инструкции],
       cast ( pltxt.query_plan  as xml ) [План выполнения инструкции],
       last_execution_time [Время последнего выполнения],
       pl.query_plan [План выполнения пакета],
       txt.text [Текст пакета],
       txt.encrypted [Объект зашифрован]
from
       sys.dm_exec_query_stats query
             outer apply
       sys.dm_exec_sql_text ( query.sql_handle ) txt
             outer apply
       sys.dm_exec_query_plan ( query.plan_handle ) pl
             outer apply
       sys.dm_exec_text_query_plan ( query.plan_handle, query.statement_start_offset, query.statement_end_offset ) pltxt
where db_name ( txt.dbid ) is not null
order by query.total_elapsed_time / query.execution_count desc