Статистика по тяжелым запросам

Данный функционал доступен в версии SQL Server 2005 и выше, независимо от редакции (Express, Standard и т.д.), построенный на системном представлении sys.dm_exec_query_stats:

set transaction isolation level read uncommitted
select 
	top 100
	creation_time,
	last_execution_time,
	execution_count,
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
	total_logical_reads as [Reads],
	total_logical_writes as [Writes],
	total_logical_reads+total_logical_writes as [AggIO],
	convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
	case 
		when sql_handle IS NULL then ' '
		else(substring(st.text,(qs.statement_start_offset+2)/2,(
			case
				when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
				else qs.statement_end_offset    
			end - qs.statement_start_offset)/2  ))
	end as query_text,
	db_name(st.dbid)as database_name,
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AvgDur desc

DeColo®es писал(а):

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

Получаемые столбцы:

creation_time - Время, когда запрос был скомпилирован. Поскольку при старте сервера кэш пустой, данное время всегда больше либо равно моменту запуска сервиса. Если время, указанное в этом столбце позже, чем предполагаемое (первое использование процедуры), это говорит о том, что запрос по тем или иным причинам был рекомпилирован.
last_execution_time - Момент фактического последнего выполнения запроса.
execution_count - Сколько раз запрос был выполнен с момента компиляции. Количество выполнений позволяет найти ошибки в алгоритмах - часто в наиболее выполняемых запросах оказываются те, которые находятся внутри каких-либо циклов однако могут быть выполнены перед самим циклом один раз. Например, получение каких-либо параметров из базы данных, не меняющихся внутри цикла.
CPU - Суммарное время использования процессора в миллисекундах. Если запрос обрабатывается параллельно, то это время может превысить общее время выполнения запроса, поскольку суммируется время использования запроса каждым ядром. Во время использования процессора включается только фактическая нагрузка на ядра, в нее не входят ожидания каких-либо ресурсов. Очевидно, что данный показатель позволяет выявлять запросы, наиболее сильно загружающие процессор.
AvgCPUTime - Средняя загрузка процессора на один запрос.
TotDuration - Общее время выполнения запроса, в миллисекундах. Данный параметр может быть использован для поиска тех запросов, которые, независимо от причины выполняются "наиболее долго". Если общее время выполнения запроса существенно ниже времени CPU (с поправкой на параллелизм) - это говорит о том, что при выполнения запроса были ожидания каких-либо ресурсов. В большинстве случаев это связано с дисковой активностью или блокировками, но также это может быть сетевой интерфейс или другой ресурс. Полный список типов ожиданий можно посмотреть в описании представления sys.dm_os_wait_stats.
AvgDur - Среднее время выполнения запроса в миллисекундах.
Reads - Общее количество чтений.
Это пожалуй лучший агрегатный показатель, позволяющий выявить наиболее нагружающие сервер запросы. Логическое чтение - это разовое обращение к странице данных, физические чтения не учитываются. В рамках выполнения одного запроса, могут происходить неоднократные обращения к одной и той же странице. Чем больше обращений к страницам, тем больше требуется дисковых чтений, памяти и, если речь идет о повторных обращениях, большее время требуется удерживать страницы в памяти.
Writes - Общее количество изменений страниц данных. Характеризует то, как запрос "нагружает" дисковую систему операциями записи. Следует помнить, что этот показатель может быть больше 0 не только у тех запросов, которые явно меняют данные, но также и у тех, которые сохраняют промежуточные данные в tempdb.
AggIO - Общее количество логических операций ввода-вывода (суммарно) Как правило, количество логических чтений на порядки превышает количество операций записи, поэтому этот показатель сам по себе для анализа применим в редких случаях.
AvgIO - Среднее количество логических дисковых операций на одно выполнение запроса. Значение данного показателя можно анализировать из следующих соображений:
Одна страница данных - это 8192 байта. Можно получить среднее количество байт данных, "обрабатываемых" данным запросом. Если этот объем превышает реальное количество данных, которые обрабатывает запрос (суммарный объем данных в используемых в запросе таблицах), это говорит о том, что был выбран заведомо плохой план выполнения и требуется заняться оптимизацией данного запроса. В общем можно описать одну причину такого поведения сервера - вместо использования индекса сервер предпочитает сканировать таблицу или наоборот. Если объем логических чтений в разы превосходит общие объем данных, то это вызвано повторным обращениям к одним и тем же страницам данных. Помимо того, что в одном запросе таблица может быть использована несколько раз, к одним и тем же страницам сервер обращается например в случаях, когда используется индекс и по результатам поиска по нему, найденные некоторые строки данных лежат на одной и той же странице. Конечно, в таком случае предпочтительным могло бы быть сканирование таблицы - в этом случае сервер обращался бы к каждой странице данных только один раз. Однако этому часто мешают... попытки оптимизации запросов, когда разработчик явно указывает, какой индекс или тип соединения должен быть использован. Обратный случай - вместо использования индекса было выбрано сканирование таблицы. Как правило, это связано с тем, что статистика устарела и требуется её обновление. Однако и в этом случае причиной неудачно выбранного плана вполне могут оказаться подсказки оптимизатору запросов.
query_text - Текст самого запроса
database_name - Имя базы данных, в находится объект, содержащий запрос. NULL для системных процедур
object_name - Имя объекта (процедуры или функции), содержащего запрос.

Используя разные варианты сортировки, можно искать запросы, которые более всего расходуют процессорный ресурс, имеют самое долгое время выполнения (как в примере и т.д.)
Конечно, также можно фильтровать объекты по базе данных и/или имени объекта.
Однако в любом случае следует помнить, что данная статистика относится только к тем запросам, которые сохранены в процедурном кэше сервера. Если по каким-либо причинам (нехватка памяти, неявная рекомпиляция запросов или явные команды)

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

SELECT qs.plan_handle, a.attrlist
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('Имя_процедуры')
  AND  est.dbid = db_id('БД')

Примечание: для корректного запуска нужно указать свою БД и хранимую процедуру.

Из следующего запроса можно получить значения прослушанных параметров и планы выполнения для хранимой процедуры:

DECLARE @dbname    nvarchar(256), @procname  nvarchar(256)

SET @dbname = БД'
SET @procname = 'Имя_процедуры'

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
         CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
         CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
         CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
        CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter

Примечание: для корректного запуска нужно указать свою БД и хранимую процедуру.

Получаемые столбцы:

SET – атрибуты set_options для плана, битовая маска установленных параметров в клиенте. Чтобы перевести битовую маску, можно использовать ниже следующий запрос.
Pos – позиция запроса в хранимой процедуре, количество символов посчитанное от начала инструкции создающей процедуру, включая любой комментарии предшествующие CREATE PROCEDURE. Не особенно полезна сама по себе, но служит для сортировки инструкций в том порядке, в котором они появляются в процедуре.
Statement – инструкция. Инструкции повторяются по одной для каждого параметра в запросе.
Parameter – Имя параметра. Показываются только параметры, присутствующие в данной инструкции. Как следствие, инструкции, которые не используют параметров - не выводятся вовсе.
Sniffed Value – значение параметра в момент компиляции, это то значение, которое прослушал оптимизатор, когда строил план.
Query Plan – план запроса. Если установлен SQL Server Management Studio 2008, можно кликнуть на XML документ, и увидеть графический план непосредственно. Если установлен SSMS 2005, результат будет лишь XML документ. Его нужно сохранить с расширением .sqlplan, и потом открыть заново, чтобы увидеть графическое представление.

Функция для расшифровки битовой маски установленных параметров в клиенте:

-- Function to translate the set_options attribute in sys.dm_plan_exec_attributes.
-- Written by Erland Sommarskog, 2011-01-02.
CREATE FUNCTION dbo.setoptions (@setopts int) RETURNS TABLE AS
RETURN
WITH
  L0      AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1      AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2      AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3      AS(SELECT 1 AS c FROM L2 AS A, L0 AS B),
  PowsOf2 AS(SELECT power(convert(bigint, 2),
                       ROW_NUMBER() OVER(ORDER BY c) - 1) AS p2
             FROM L3)
SELECT CASE p2
          WHEN       1 THEN 'ANSI_PADDING'
          WHEN       2 THEN 'Parallel Plan'
          WHEN       4 THEN 'FORCEPLAN'
          WHEN       8 THEN 'CONCAT_NULL_YIELDS_NULL'
          WHEN      16 THEN 'ANSI_WARNINGS'
          WHEN      32 THEN 'ANSI_NULLS'
          WHEN      64 THEN 'QUOTED_IDENTFIER'
          WHEN     128 THEN 'ANSI_NULL_DFLT_ON'
          WHEN     256 THEN 'ANSI_NULL_DFLT_OFF'
          WHEN     512 THEN 'NoBrowseTable'
          WHEN    1024 THEN 'TriggerOneRow'
          WHEN    2048 THEN 'ResyncQuery'
          WHEN    4096 THEN 'ARITHABORT'
          WHEN    8192 THEN 'NUMERIC_ROUNDABORT'
          WHEN   16384 THEN 'DATEFIRST'
          WHEN   32768 THEN 'DATEFORMAT'
          WHEN   65536 THEN 'LanguageID'
          WHEN 1310722 THEN 'Force parameterization'
          ELSE 'Unknown, bit ' + str(p2, 10)
       END AS Set_option
FROM   PowsOf2
WHERE  p2 & @setopts <> 0

Примечание: функции нужно передать параметр битовой маски.

Прочли: 8003 | Изменено: 09 сентября 2011 | Добавлено: 09 июня 2011 | На главную