October 1, 2010 at 3:27 am
I need to write a statement to display a list of running sessions, and their sql texts (statements).
In Oracle an only thing I have to do, is to JOIN between v$session and v$sql views.
Any idea how to do it in SQL 2008?
Thought the lastwaittype will show, but it displays only SELECT word, without the actual statement.
Thanks in advance
October 1, 2010 at 5:25 am
from a similar post a while back, someone cobbled together an enhanced version of sp_who, which has an additional xml column of the command being executed.
try this version, named sp_who3:
/***************************************************************************************************************
*Routine Name: [dbo].[sp_who3]
*Purpose: Return SPID information from SQL Server
*Used by: Admins
*Inputs: @IsUserProcess (0,1),@CurrentSpid (0,Null)
*Returns:
*Test Code: EXEC sp_who3
***************************************************************************************************************/
--#############################################################################
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.* system views, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_who3'
--#############################################################################
CREATE Procedure sp_who3
@IsUserProcess TinyInt = 1
,@CurrentSpid TinyInt = 0
As
Begin
Set NoCount On
/*
Test Code
------------------------------------------
Declare @IsUserProcess TinyInt
,@CurrentSpid TinyInt
Select @IsUserProcess = 0
,@CurrentSpid = 0
*/
Select es.session_id As SPID
,ROW_NUMBER() over (partition by es.session_id order by es.login_time) As SubProcessID
,DB_NAME(Coalesce(er.database_id,tl1.resource_database_id,' - ')) As DBName
,Coalesce(ot.task_state,es.status,' - ') As TaskStatus
,es.login_name As LoginName
,Coalesce(ec.client_net_address,' - ') As IPAddress
,Coalesce((SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') As QueryText
,Coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ') As WaitType
,Coalesce(es.host_name,' - ') As HostName
,Coalesce(tl.request_session_id,'') As BlockedBy
,Coalesce((SELECT p.text
FROM (SELECT MIN(sql_handle) As sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = tl.request_session_id
) As rb
CROSS APPLY
(SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(rb.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
),'') As BlockingText
,Coalesce(es.program_name,' - ') As ProgramName
,Coalesce(es.client_interface_name,' - ') As ClientInterface
,Coalesce(es.host_process_id,' - ') As UserProcessID
,es.login_time As LoginTime
,es.last_request_start_time As LastBatch
,es.total_elapsed_time *.001 As SessionElapsedTime
,es.total_scheduled_time * .001 As CPUTime
,es.memory_usage As Num8kPages
,Coalesce(ec.num_reads,'') As NumReads
,Coalesce(ec.num_writes,'') As NumWrites
,Coalesce(er.open_transaction_count,st.TranCount,0) As OpenTranCount
,Coalesce(dt.lockcount,0) As LockCount
From sys.dm_exec_sessions es
Left Outer Join sys.dm_exec_connections ec
On ec.session_id = es.session_id
Left Outer Join sys.dm_os_waiting_tasks wt
On wt.session_id = es.session_id
Left Outer Join sys.dm_os_tasks ot
On es.session_id = ot.session_id
Left Outer Join sys.dm_tran_locks tl
On wt.blocking_session_id = tl.request_session_id
Left Outer Join sys.dm_tran_locks tl1
On ec.session_id = tl1.request_session_id
Left Outer Join sys.dm_exec_requests er
On tl.request_session_id = er.session_id
Left Outer Join (
Select request_session_id,COUNT(request_session_id) As LockCount
From sys.dm_tran_locks
Group By request_session_id
) dt
On ec.session_id = dt.request_session_id
Left Outer Join (
Select session_id,COUNT(session_id) As TranCount
From sys.dm_tran_session_transactions
Group By session_id
) st
On ec.session_id = st.session_id
Where es.is_user_process >= (Case when @IsUserProcess = 0 Then 0 Else 1 End)
And es.session_id <> (Case when @CurrentSPID = 0 Then 0 Else @@SPID End) --@@SPID if current Spid is to be excluded
End
Lowell
October 1, 2010 at 6:04 am
Fantastic!
Thanks a lot!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply