Статистика, собираемая SQL Server по наиболее тяжелым запросам

Административные скрипты MS SQL > Статистика, собираемая SQL Server по наиболее тяжелым запросам
05.07.2013 16:15:43



Статья:

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 - Имя объекта (процедуры или функции), содержащего запрос.
  •  

    получить план выполнения всего объекта (процедуры или функции) или пакета команд и искать сам запрос "внутри него":

    select 
    	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,
    	qp.query_plan
    from sys.dm_exec_query_stats  qs
    cross apply sys.dm_exec_sql_text(sql_handle) st
    cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
    where object_name(st.objectid, st.dbid) = 'SomeProcedure'