SQL Server. Тяжелые запросы

Данный функционал доступен в версии 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

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

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

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

Автор: Дмитрий Костылёв

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube