sp_block查看数据库进程状态(是否阻塞,执行SQL等),可替代sp_who

 

   写这个存储过程的初衷就是当有业务部门反映系统慢时，能快速知道数据库是否有阻塞，blocking其他进程的SQL是什么。后来又有一个需求想实现：在停止数据库服务之前，能快速知道数据库当前运行着什么SQL，以决定是否可以立即停止数据库，还是需要等某些SQL运行完。

   写完这个存储过程，几经更改，在使用过程中，发现这个存储过程确实非常方便和好用，故分享给大家，希望对大家有用，也欢迎大家多提建议。

 

总结下这个存储过程的用途：

 1,查看数据库是否有阻塞；哪些进程在阻塞和被阻塞；阻塞了多少个进程和阻塞了多长时间？阻塞的SQL是什么？这个SQL当时的执行计划是什么？
 2,查看进程的常规信息，例如什么帐户登录，使用了哪个数据库，客户端名称和ip地址？
 3,查看进程运行状态，等待类型，等待资源，消耗的cpu，read，write等。
 4,通过查询全局临时表，可以统计当前服务器有多少个login在运行，多少个客户端在连接等。
 5,作为自动杀阻塞进程的sp的调用子sp。（这个后续推出）

 

 一些注释说明：

 1,为防止执行时间过长，仅显示blocking进程的执行计划。如果使用SQL Server2008的ssms，可以直接点击查询结果中的数据查看到图形化的执行计划；
 2,系统进程被阻塞,也会显示；（这个未经过充分验证）
 3,session_status 有时不匹配request_status,原因:存在脏读;或读取时间不一致
 4,当前自己进程的信息不显示（即执行这个sp的窗口），除非这个进程阻塞了其他进程；
 5,不论@status输入参数如何,blocking和blocked总是显示,可以用此特性只显示blocking和blocked

 

 结果集的一些说明：

 1,blocking_spid： 阻塞其他进程的进程号，如果某个进程正在阻塞其他进程，这个列显示为：kill spid，否则会显示被阻塞的层次关系；
 2,blocked_count: 本进程阻塞其他进程的个数；
 3,blocked_time: 本进程阻塞其他进程的最长时间；

 

 

use master --请修改db nameGO if object_id('sp_block') is not null drop procedure sp_block GOcreate procedure sp_block@status varchar(50)='%'--limit session status,default is all. where session_status like @statusas /**********************{VEREDITORDATECOMMENT}{ver 1.0summer.yang2009-02-27create procedure}{ver 1.1summer.yang2009-03-2add parameter,create table}{ver 1.2summer.yang2009-03-4add job name and step name, accelerate running time}{ver 1.3summer.yang2009-03-5add query plan}{ver 1.4summer.yang2009-03-6filted current spid}{ver 1.5summer.yang2009-03-9check deadlock and dropped primary key}{ver 1.6summer.yang2009-03-11set blocking session's wait_time as 0}{ver 1.7summer.yang2009-05-18fix bug "Difference of two datetime columns caused overflow at runtime."}{ver 2.0summer.yang2009-07-16replace the permanent table with viable table,for parallel execute }{ver 2.1summer.yang2009-07-21display the sys processes which are blocking other spids.}{ver 2.2summer.yang2009-09-11add ##block temp table}REMARK: 1. 系统进程本身没有connection,一个sesstion对应一个connection2. insert data有严格的顺序3. 如果sys.dm_exec_requests的列值为null,表明此requests已经不存在,session_status为sleeping4. if a spid is blocking and is blocked,it belong to blocked,but the blocking count has value.5. if the sp get error info like "The statement terminated. The maximum recursion 100 has been exhausted before statement completion.",then it indicate a deadlock happened,like this: "Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.6. sleeping: last_request_end_time>=last_request_start_time7. 有时查询结果的数据出现不一致的情况,比如type=sleeping,而status=running,原因是此sp内部多次查询的时间差8. 不论@status输入参数如何,blocking和blocked总是显示,可以用此特性只显示blocking和blocked9. Will only display the blocking spid's query plan.10. Will filter current spid,but if the current spid is blocking or blocked ,it will display.11. 系统进程被阻塞,也会显示12. session_status 有时不匹配 request_status,原因:存在脏读;或读取时间不一致EXAMPLE: exec sp_block '%running%' exec sp_block '%block%' ************************/set nocount ondeclare @spid_activity table (type nvarchar(50) not null,spid int not null ,blocking_spid int null,blocking_spid_sort varchar(1000) null,blocked_count int null,wait_time int null,blocked_time int null --seconds)--1,blocked sessionsinsert into @spid_activity(type,spid,blocking_spid,blocked_count,wait_time)select distinct '2_blocked', session_id,blocking_session_id,0,wait_timefrom sys.dm_exec_requests with (nolock) where blocking_session_id <>0--2,blocking sessionsinsert into @spid_activity(type,spid,blocking_spid,blocked_count,wait_time)select distinct '1_blocking', blocking_session_id,0,0,0from sys.dm_exec_requests with (nolock) where blocking_session_id <>0and blocking_session_id not in (select spid from @spid_activity)--消息 530，级别 16，状态 1，第 2 行--The statement terminated. The maximum recursion 100 has been exhausted before statement completion.--deadlock ,truncate table @spid_activity--BEGIN TRY--update @spid_activity--set blocked_count=fn3.blocked_count,--blocked_time=fn3.blocked_time,--blocking_spid_sort=fn3.blocking_spid_sort--from @spid_activity st3 cross apply dbo.fn_spid_blocked_count(spid) fn3--where st3.blocked_count=0--END TRY--BEGIN CATCH--if ERROR_NUMBER()=530--begin--print 'There is a deadlock! '--print 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'----truncate table @spid_activity--return -1--end--END CATCH;--------------------return block count and durationdeclare @spid intdeclare @count int,@blocked_time int ,@blocking_spid_sort varchar(1000);declare @cursor cursorset @cursor=cursor forward_only static forselect distinct spid from @spid_activity where blocked_count=0open @cursorfetch next from @cursor into @spidwhile @@fetch_status=0beginwith cte_blocked_count(spid ,blocking_spid ,blocked_time,level)as(-- Anchor Member (AM)select spid,blocking_spid,wait_time,1from @spid_activitywhere spid=@spidunion all-- Recursive Member (RM)select st1.spid,st1.blocking_spid,wait_time,level+1from @spid_activity st1 join cte_blocked_count co1on st1.blocking_spid=co1.spid)select @count=count(*)-1,@blocked_time=max(blocked_time) from cte_blocked_count ;with cte_blocked_sort(spid ,blocking_spid ,level,blocking_spid_sort)as(-- Anchor Member (AM)select spid,blocking_spid,1,convert(varchar(1000),+' -> '+ltrim(rtrim(str(blocking_spid))))from @spid_activitywhere spid=@spidunion all-- Recursive Member (RM)select st1.spid,st1.blocking_spid,level+1,convert(varchar(1000),ltrim(rtrim(co1.blocking_spid_sort))+' -> '+ltrim(rtrim(str(st1.blocking_spid))))from @spid_activity st1 join cte_blocked_sort co1on st1.spid=co1.blocking_spidwhere st1.blocking_spid<>0)select @blocking_spid_sort=blocking_spid_sort from cte_blocked_sort where level in (select max(level) from cte_blocked_sort)update @spid_activityset blocked_count=@count,blocked_time=@blocked_time,blocking_spid_sort=@blocking_spid_sortwhere blocked_count=0 and spid=@spid fetch next from @cursor into @spidend----------------------if object_id('tempdb.dbo.##block') is not null drop table ##block--10 show results*************select --st0.blocking_spid,blocking_spid_sort,case when st0.type is not null then st0.type when se1.status='running' then '3_Running' when se1.status='Sleeping' then '4_Sleeping' when se1.status='Dormant' then '5_Dormant' when se1.status='Preconnect' then '6_Preconnect' else 'unkown' end as type,se1.session_id as spid ,case when pr.dbid = 0 then ''else db_name(pr.dbid) end as DBname,se1.login_name/*,db_name(re2.database_id) as DBname--20090817yf*/,se1.[host_name] as client_name,co3.client_net_address as client_IP,case when charindex('Job 0x',se1.[program_name])>0 then (select 'SQLAgent Job: '+a.name+' Setp: '+ltrim(str(b.step_id))+' ('+b.step_name+')'from msdb.dbo.sysjobs a with (nolock) join msdb.dbo.sysjobsteps b with (nolock) on a.job_id=b.job_idwhere right(a.job_id,12) =substring(se1.[program_name],charindex('Job 0x',se1.[program_name])+26,12)and b.step_id=substring(se1.[program_name],charindex(' Step ',se1.[program_name])+6,charindex(')',se1.[program_name])-charindex(' Step ',se1.[program_name])-6))else se1.[program_name] end as client_program,te2.text as running_or_last_text, ( SELECT TOP 1 SUBSTRING(te1.text,statement_start_offset/2, ( (CASE WHEN statement_end_offset= -1 THEN (LEN(CONVERT(nvarchar(max),te1.text))*2) ELSE statement_end_offset END)-statement_start_offset)/2)) AS running_sql--,te1.text as running_text--running_sql是正在执行的SQL,属于running_text的一部分,case when st0.blocking_spid =0 then 'Kill '+ltrim(rtrim(str(st0.spid))) when st0.blocking_spid is not null then blocking_spid_sort else '' end blocking_spid,isnull(str(st0.blocked_count),'') as blocked_count,isnull(dbo.udf_Duration_IntToChar(blocked_time/1000),'') as blocked_time--fix bug of "Difference of two datetime columns caused overflow at runtime.",case when re2.wait_time is not null and se1.last_request_end_time>0 then 'wait '+dbo.udf_Duration_IntToChar(re2.wait_time/1000) when re2.wait_time is null and se1.last_request_end_time>0 then 'idle '+dbo.udf_Duration_IntToChar(datediff(ss,se1.last_request_end_time,getdate())) else 'idle from '+convert(char(20),se1.last_request_end_time,120) end as duration,se1.status as session_status ,re2.status as request_status,re2.command,re2.wait_type,re2.wait_resource,re2.open_transaction_count as open_tran,re2.row_count,local_net_address as Server_IP,local_tcp_port as Port--,se1.[program_name] as client_program --,user_name(re2.[user_id]) as UserName,se1.client_interface_name as client_interface,se1.host_process_id as client_process,case when st0.blocking_spid=0 then (select query_plan from sys.dm_exec_query_plan (re2.plan_handle))else '' end as query_plan,se1.login_time,se1.last_request_end_time /*,se1.last_request_start_time --20090817yf*/,se1.cpu_time ,se1.reads ,se1.logical_reads ,se1.Writes ,se1.is_user_process/*,se1.memory_usage,re2.cpu_time,re2.reads,re2.logical_reads,re2.Writes,re2.nest_level --20090817yf*/--,percent_complete--,re2.transaction_isolation_level --转换成文字说明--,re2.prev_error--,dbo.udf_Duration_IntToChar(re2.total_elapsed_time/1000)as total_elapsed_time--,re2.start_time--,re2.last_wait_type--,re2.open_resultset_count--,transaction_id---,se1.lock_timeout--,re2.granted_query_memory--,re2.executing_managed_code,blocked_time/1000 as blocked_time_ssinto ##blockfrom sys.dm_exec_sessions se1 with (nolock)inner join master.dbo.sysprocesses pr with (nolock) on se1.session_id=pr.spidleft join sys.dm_exec_connections co3 with (nolock) on se1.session_id=co3.session_idleft join sys.dm_exec_requests re2 with (nolock) on se1.session_id=re2.session_idleft join @spid_activity st0 on se1.session_id=st0.spidouter APPLY sys.dm_exec_sql_text(re2.sql_handle) AS te1 outer APPLY sys.dm_exec_sql_text(co3.most_recent_sql_handle) AS te2 where st0.type is not nullor (se1.is_user_process=1 and se1.status like @status and se1.session_id<>@@SPID)order by type,st0.blocked_count desc,st0.blocked_time desc,request_status desc,se1.session_idselect * from ##blockprint 'select * from ##block order by login_nameselect login_name,count(*) as count0 from ##block group by login_name order by login_name 'GOgrant execute on sp_block to publicGO

 

 下面是调用的函数：

if object_id('udf_Duration_IntToChar') is not null drop function udf_Duration_IntToCharGOcreate FUNCTION dbo.udf_Duration_IntToChar(@duration int)RETURNS varchar(50)AS /**********************{VEREDITORDATECOMMENT}{ver 1.0summer.yang2007-12-18create }{ver 1.1summer.yang2009-08-26add days}REMARK: 1. The duration must be secondsEXAMPLE: declare @duration intset @duration=3672+3600*100select dbo.udf_Duration_IntToChar(@duration)************************/BEGINdeclare @duration_char varchar(50),@days varchar(30)set @days=case when @duration>=86400 then convert(varchar(20),convert(int,@duration/86400))+'D 'else '' endset @duration=@duration%86400set @duration_char=@days+case when (@duration/3600)>9 then ltrim(str(@duration/3600)) else right('0'+ltrim(str(@duration/3600)),2) end +':'+right('0'+ltrim(str((@duration%3600)/60)),2)+':'+right('0'+ltrim(str((@duration%3600)%60)),2)RETURN @duration_charENDGO