Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

sp_now : rewrite sp_who2

Posted by Simon Cho on 08/03/2011

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
**
**Example: exec [sp_now]
**
*************************************************************************************************/
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′) 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

–exec sys.sp_MS_marksystemobject ‘sp_now’

Advertisements

One Response to “sp_now : rewrite sp_who2”

  1. Simon Cho said

    This query is very useful.
    Just run it and see the result.

    Simon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: