query_size.sql

query_size.sql

select
	 
	/*
		session values
	*/
	s.session_id,
	s.login_time, 
	s.host_name, 
	s.program_name, 
	s.login_name, 
	s.status session_status, 
	s.date_format, 
	s.quoted_identifier, 
	s.arithabort, 
	s.ansi_defaults, 
	s.ansi_null_dflt_on, 
	s.ansi_warnings,
	s.ansi_nulls, 
	case s.transaction_isolation_level
		when 0 then 'Unspecified'
		when 1 then 'ReadUncommitted'
		when 2 then 'ReadCommitted'
		when 3 then 'Repeatable'
		when 4 then 'Serializable'
		when 5 then 'Snapshot'
	end as transaction_isolation_level,
	/*
		request values 
	*/
	r.request_id, 
	r.status, 
	r.command,
	r.start_time request_start_time,	
	r.database_id request_database_id, 
	r.blocking_session_id request_blocking_session_id, 
	r.wait_type request_wait_type, 
	r.wait_time request_wait_time, 
	r.last_wait_type request_last_wait_type, 
	r.wait_resource request_wait_resource, 	
	r.open_transaction_count, 
	r.percent_complete, 
	dateadd(millisecond,estimated_completion_time, sysdatetime()) estimated_completion_time, 
	r.granted_query_memory * 8 / 1024. request_granted_query_memory_mb,
	r.nest_level,
	r.executing_managed_code,
	/*
		task values 
	*/
	t.task_address,
	t.task_state, 
	t.context_switches_count, 
	t.pending_io_count, 
	t.pending_io_byte_count, 
	t.scheduler_id, 
	t.exec_context_id, 
	/*
		memory grants values 
	*/
	mg.dop as degrees_of_parallelism, 
	mg.requested_memory_kb / 1024. requested_memory_mb,
	mg.required_memory_kb / 1024. required_memory_mb,
	mg.granted_memory_kb / 1024. granted_memory_mb,
	mg.used_memory_kb / 1024. used_memory_mb,
	mg.max_used_memory_kb / 1024. max_used_memory_mb,
	mg.query_cost, 
	dateadd(second,mg.timeout_sec,sysdatetime()) memory_grant_timeout, 
	mg.queue_id,
	mg.is_next_candidate,
	mg.wait_time_ms memory_grant_wait_time, 
	case mg.is_small
		when 1 then 'Small Resource Semaphore'
		else 'Regular Resource Semaphore'
	end as memory_grant_resource_semaphore_type,
	mg.resource_semaphore_id,
	mg.pool_id,
	/* 
		query stats values
	*/
	qs.creation_time plan_creation_time, 
	qs.last_execution_time, 
	qs.plan_generation_num,
	qs.execution_count, 
	qs.total_worker_time, 
	qs.last_worker_time, 
	qs.min_worker_time, 
	qs.max_worker_time, 
	qs.total_physical_reads, 
	qs.last_physical_reads, 
	qs.min_physical_reads,
	qs.max_physical_reads, 
	qs.total_logical_writes, 
	qs.last_logical_writes, 
	qs.min_logical_writes, 
	qs.max_logical_writes, 
	qs.total_logical_reads, 
	qs.last_logical_reads, 
	qs.min_logical_reads, 
	qs.max_logical_reads, 
	qs.total_clr_time, 
	qs.last_clr_time, 
	qs.min_clr_time, 
	qs.max_clr_time,
	qs.total_elapsed_time, 
	qs.last_elapsed_time, 
	qs.min_elapsed_time, 
	qs.max_elapsed_time,
	qs.total_rows,
	qs.last_rows, 
	qs.min_rows, 
	qs.max_rows,	
	(select       
			SUBSTRING(replace(replace(replace(text,CHAR(9),' '),char(10),' '),char(13), ' '),(r.statement_start_offset/2)+1,  
            (case when r.statement_end_offset = -1  
            then len(convert(nvarchar(max), qt.text)) * 2  
            else r.statement_end_offset end - r.statement_start_offset)/2+1) request_text, 
			text
			from sys.dm_exec_sql_text(r.sql_handle) qt
			for xml path('q'), type
			) as sql_text, 
	qp.query_plan
	from sys.dm_exec_sessions s 
	join sys.dm_exec_requests r 
		on s.session_id = r.session_id	
	left join sys.dm_os_tasks t 
		on r.task_address = t.task_address		
	left join sys.dm_exec_query_memory_grants mg
		on s.session_id = mg.session_id
		and r.scheduler_id = mg.scheduler_id	
	left join sys.dm_exec_query_stats qs 
		on mg.plan_handle = qs.plan_handle		
	--cross apply (

	--	select physical_memory_in_bytes / 1024. / 1024 server_memory_mb
	--		from sys.dm_os_sys_info
	--) mb
	--left join sys.dm_os_waiting_tasks wt
	--	on t.task_address = wt.waiting_task_address	
	--		and s.session_id = wt.session_id
	outer apply sys.dm_exec_query_plan(mg.plan_handle) qp
	outer apply sys.dm_exec_sql_text (mg.sql_handle) st
	
where s.session_id > 50
--order by s.session_id, t.exec_context_id
option (recompile, maxdop 1);