This SP is very helpful to troubleshooting concurrent issue.
use master
go
if object_id('sp_now') is not null
drop procedure sp_now
go
create procedure [dbo].[sp_now]
@session_id int = null
,@xml_on bit = 0
,@text nvarchar(500) =''
,@back_on bit = 0
as
/***********************************************************************************************
**Object Name: dbo.sp_now
**
**Description: Revise sp_who2
**
**Input Parameters:
@session_id int = null
, @text nvarchar(500) ='
, @back_on bit = 0
**
**Return Value: N/A
**
**Return Result Set: session info
**
**Creator: Simon Cho
**
**Create Date: 09/13/2011
**
**Example: exec [sp_now]
**
**Change History
**
**Change Date Changed By Reason
**9/24/2012 Simon Cho query_plan off, remove text field.
**9/25/2012 Simon Cho Change default value @xml_on=0
*************************************************************************************************/
begin
set nocount on;
set transaction isolation level read uncommitted;
select
sp.spid as s_id
, max(sp.blocked) as BlkBy
, substring(max(convert(varchar(23),r.start_time,121)+sp.status), 24, 50) status
, rtrim(max(sp.loginame)) as Login
, rtrim(ltrim(max(sp.hostname))) as hostname
, min(r.start_time) as st_time
, convert(numeric(10,2),max(r.total_elapsed_time/1000)/60.0) [run_time(min)]
--, r.total_elapsed_time
, GETDATE() as current_datetime
, max(sp.waittime) as waittime
, substring(max(convert(varchar(23),r.start_time,121)+r.wait_type), 24, 50) wait_type
, db_name(max(sp.dbid)) as db
, substring(max(convert(varchar(23),r.start_time,121)+command), 24, 50) command
, max(r.cpu_time) as cpu_time
, max(r.reads) as reads
, max(r.writes) as writes
, max(r.logical_reads) as logical_reads
, rtrim(max(sp.program_name)) as program_name
, max(c.client_net_address) ip
, min(sp.login_time) as login_time
, convert(numeric(10,2),max(r.granted_query_memory)/128.0) as granted_query_memory_MB
--, t.objectid
, OBJECT_NAME(t.objectid, t.dbid) objname
, convert(xml,'<?Q--'+CHAR(13)+CHAR(10)+substring(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)
, (case when (statement_start_offset/2)+1<len(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)) then (statement_start_offset/2)+1 else 1 end)
, (case statement_end_offset when -1 then datalength(max(case when text like 'Fetch%' then substring(text, 1,32) else text end)) else (statement_end_offset- statement_start_offset)/2 +1 end )
) +CHAR(13)+CHAR(10)+'--?>') as text_stmt_xml
--, max(t.text) as text
, case when @xml_on = 1 then (select query_plan from sys.dm_exec_query_plan(max(r.plan_handle)))
else convert(xml, '')
end as query_plan
from sys.sysprocesses sp with(nolock)
join sys.dm_exec_requests r with(nolock) on r.session_id=sp.spid
left join sys.dm_exec_connections c with(nolock) on r.connection_id=c.connection_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where 1=1
-- and sp.spid!=@@SPID
and case when @session_id is null then 1 else sp.spid end
= case when @session_id is null then 1 else @session_id end
and case when @text='' then '1' else text end
like case when @text='' then '1' else '%'+@text+'%' end
and case when @back_on = 0 then sp.spid else 51 end > 50
and case when @back_on = 0 then sp.status else 'all' end != 'background'
group by sp.spid
, sp.dbid, t.dbid
, t.objectid
, r.statement_start_offset
, r.statement_end_offset
, r.transaction_id
, c.connection_id
, sp.blocked
end
go
--exec sys.sp_MS_marksystemobject 'sp_now'
exec sp_now