Актиные сесии

Административные скрипты MS SQL > Актиные сесии
26.11.2019 13:29:05



Статья:

--Актиные сесии 
--exec [dbo].[sp_active];
--exec [dbo].[sp_WhoIsActive];
/*%work, rowcount, cpu_time, programName, LogicalReads, %для_alter,backup, objName, user_id*/
 
 
DECLARE @SPID int = 50
DECLARE @status varchar(100) = 'background'
 
select  ';kill '+cast(r.session_id as varchar(3)) as text_kill,
		/*r.session_id,*/
		r.percent_complete "%  work  ",        
		r.row_count,  
         case r.statement_end_offset    
              when -1 then s2.text      
              else substring(s2.text, r.statement_start_offset/2, 
                     (r.statement_end_offset/2) - (r.statement_start_offset/2))      
         end as [SQLText],
		 CASE 
			WHEN CHARINDEX('SQLAgent - TSQL JobStep', s1.program_name) = 1
			THEN 
				(SELECT 
					[name] 
				FROM 
					msdb.dbo.sysjobs 
				WHERE 
					job_id = CAST(CONVERT(binary(16), SUBSTRING(s1.program_name, 30, 34), 1) as uniqueidentifier)) + '  /' + s1.program_name + '/'
			ELSE
				s1.program_name
		END as program_name
		--,s3.query_plan
		,r.start_time,	
		DATEDIFF(day,         r.start_time, getdate()) days,
		CAST(DATEADD(Second, r.total_elapsed_time / 1000 , 0) as time(0)) as [duration] ,     		
		r.status,
		r.wait_type,
		r.wait_resource, 
		r.wait_time as [wait_time ms/1000] ,	
		r.cpu_time,
		r.reads,
		r.logical_reads,
		r.writes,
		--(r.granted_query_memory*8)/1024 as [requested_granted_memory(Mb)],
		mg.requested_memory_kb/1024 as [requested_memory(Mb)],
		mg.granted_memory_kb/1024 as [granted_memory(Mb)],
		mg.max_used_memory_kb/1024 as [max_used_memory(Mb)],       	
		tdb_alloc.tempdb_allocations,
		tdb_alloc.tempdb_current,
		r.command, 
		r.blocking_session_id,
        s1.host_name,   
		s1.login_name, 
        db_name(r.database_id) as db,
		USER_NAME(r.user_id) as user_name,  
		case r.statement_end_offset      when -1 then NULL                	   
		else object_name(s2.objectid, s2.dbid)      end [ObjnameName],
		--r.blocking_session_id,      r.wait_type,  
 
		r.percent_complete "% для alter,backup e.t.c.",    
        r.estimated_completion_time,
		r.total_elapsed_time,  
        r.scheduler_id,
		r.reads,
		r.writes,
	    --	r.logical_reads,
		r.row_count,  	
		r.plan_handle,
 
         r.user_id, (r.estimated_completion_time /1000)/60 as 'мин' 
from 
	sys.dm_exec_requests r   
	inner join sys.dm_exec_sessions s1 on r.session_id=s1.session_id
	left join sys.dm_exec_query_memory_grants mg on r.session_id=mg.session_id and r.request_id=mg.request_id
	outer apply sys.dm_exec_sql_text(r.sql_handle) as s2  
	LEFT OUTER JOIN 
	(SELECT 
		t_info.session_id,  
		SUM(t_info.tempdb_allocations) AS tempdb_allocations,  
		SUM(t_info.tempdb_current) AS tempdb_current  
	FROM  
	(  
		SELECT 
			tsu.session_id,  
			tsu.user_objects_alloc_page_count + 
				tsu.internal_objects_alloc_page_count AS tempdb_allocations, 
			tsu.user_objects_alloc_page_count + 
				tsu.internal_objects_alloc_page_count -  
				tsu.user_objects_dealloc_page_count -  
				tsu.internal_objects_dealloc_page_count AS tempdb_current  
		FROM sys.dm_db_task_space_usage AS tsu  
		UNION ALL  
		SELECT 
			ssu.session_id,  
			ssu.user_objects_alloc_page_count + 
				ssu.internal_objects_alloc_page_count AS tempdb_allocations,  
			ssu.user_objects_alloc_page_count + 
				ssu.internal_objects_alloc_page_count -  
				ssu.user_objects_dealloc_page_count -  
				ssu.internal_objects_dealloc_page_count AS tempdb_current  
		FROM sys.dm_db_session_space_usage AS ssu  
	) AS t_info  
	GROUP BY  
		t_info.session_id
	) tdb_alloc ON r.session_id = tdb_alloc.session_id
 
    --outer  apply sys.dm_exec_query_plan (r.plan_handle) as s3                   
   where 
		1 = 1
		and r.status <> @status
		and r.command <> 'task manager'      
		and r.session_id <> @@SPID
		and r.session_id > @SPID
      -- and r.database_id <> db_id('msdb') 
 
--   order by  r.session_id,r.cpu_time desc  
--order by login_time 
--	order by start_time 
--order by r.cpu_time desc 
order by r.logical_reads desc 
 
--exec sp_whoisactive @not_filter = 'ReportServer',    @not_filter_type = 'database', @get_plans=1, @find_block_leaders = 1, @get_task_info = 2, @get_additional_info=1--, @get_locks = 1
 
/*
select * from sys.dm_exec_query_memory_grants
 
DECLARE @DBTrouble varchar(300);
DECLARE @strProc varchar(max);
 
SELECT DISTINCT DB_NAME(database_id) as DB, /*', ' ,*/redo_queue_size,last_commit_time FROM sys.dm_hadr_database_replica_states (nolock)
where redo_queue_size is not null*/