Смотрим текущую активность SQL Server

добавлено: 07 окт 14
понравилось:0
просмотров: 5275
комментов: 2

теги:

Автор: DeColo®es

Периодически публикую запрос, который сам использую для мониторинга того, что происходит на SQL Server.
Сегодня - очередное обновление.

Сам запрос:
set transaction isolation level read uncommitted;
go
select 
	sess.[login_name],
	sess.[host_name],
	qs.start_time,
	sess.last_request_start_time,
	datediff(ms, qs.start_time, getdate()) as duration,
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [Procedure],
	case when qs.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, 
	sess.cpu_time,
	qs.logical_reads,
	qs.writes,
	sess.session_id,
	case when qs.wait_type = 'CXPACKET' and qs.blocking_session_id = 0 then cp.[blocked] else qs.blocking_session_id end as blocking_id,
	isnull(nullif(qs.wait_resource, ''), cp.[waitresource]) as waitresource, 
	qs.wait_time, 
	qs.wait_type,
	convert(xml, pl.query_plan) _plan,
	rgwg.[name]
from sys.dm_exec_sessions sess
-- To get blocking info for parallel queries
outer apply (select top 1 blocked, esp.waitresource from  master.dbo.sysprocesses esp where esp.spid = sess.session_id and esp.blocked > 0 and esp.blocked <> esp.spid) cp
inner join sys.dm_exec_requests qs 
	on qs.session_id = sess.session_id
	and qs.[wait_type] not in ('BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP')
left join sys.dm_exec_procedure_stats ps 
	on ps.sql_handle = qs.sql_handle
	and ps.[plan_handle] = qs.[plan_handle]
outer apply master.sys.dm_exec_sql_text(qs.sql_handle) st
outer apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset	) pl
inner join sys.resource_governor_workload_groups as rgwg 	
	on rgwg.[group_id] = sess.[group_id]
where (sess.[host_name] is not null or sess.session_id>=49)
order by sess.session_id

В отличии от предыдущих версий
  • показывает план выполнения для конкретного запроса
  • показывает блокировки для параллельных запросов


    Что "может пойти не так":
    1. План выполнения запроса доступен не всегда - так уж бывает.

    2. Встречаются планы, которые невозможно прочитать из-за ограничений парсера xml - слишком сложные запросы, причем тут роль играет не только число таблиц, могут быть просто слишком запросы и без этого:
    Msg 6335, Level 16, State 101, Line 3
    XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

    3. Бывает, в процессе выполнения самого диагностического запроса, "диагностируемый" уже выполнился и более недоступен. Получим что-то вроде:
    Msg 8181, Level 16, State 1, Line 3
    Text for '' is missing from the system catalog. The object must be dropped and re-created before it can be used.
  • Комментарии


    • я бы добавил в "ИЛИ" is_user_process = 1
      и в дальнейшее "И" session_id <> @@spid

    • Запрос может вызвать разрушение работы планировщика, с необходимостью рестарта сервера.
      В MS об ошибке знают, но исправлять не хотят (т.к. есть решение - рестарт сервера :-) )
      https://connect.microsoft.com/SQLServer/feedback/details/700488/non-yielding-scheduler-when-outer-joining-dmvs-with-a-mismatched-on-clause



    Необходимо войти на сайт, чтобы оставлять комментарии