As we know, to check whats going on at server level, we commonly use SP_WHO or SP_WHO2 but from both of these, there are no clear picture about exact commands which currently running on server, these shows the command, not exact full text string that’s currently running, Here is a some DMV’s manipulation to find the same.

SELECT SPID er.session_id
,BlkBy 
er.blocking_session_id
,ElapsedMS
er.total_elapsed_time
,CPU 
er.cpu_time
,IOReads 
er.logical_reads er.reads
,IOWrites 
er.writes
,Executions 
ec.execution_count
,CommandType 
er.command
,ObjectName 
OBJECT_SCHEMA_NAME(qt.objectid,dbid‘.’ + OBJECT_NAME(qt.objectidqt.dbid)
,SQLStatement 
= SUBSTRING(qt.text,er.statement_start_offset/2,
(
CASE WHEN er.statement_end_offset 1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE
                er.statement_end_offset 
END – er.statement_start_offset)/2)
,Status 
ses.status
,[Login] 
ses.login_name
,Host 
ses.host_name
,DBName 
DB_Name(er.database_id)
,LastWaitType 
er.last_wait_type
,StartTime 
er.start_time
,Protocol 
con.net_transport
,transaction_isolation 
=CASE ses.transaction_isolation_level 
                        WHEN 0 THEN ‘Unspecified’
                        WHEN 1 THEN 
‘Read Uncommitted’
                        WHEN 2 THEN ‘Read Committed’
                        WHEN 3 THEN ‘Repeatable’
                        WHEN 4 THEN ‘Serializable’
                        WHEN 5 THEN ‘Snapshot’
                        END
,ConnectionWrites 
con.num_writes
,ConnectionReads 
con.num_reads
,ClientAddress 
con.client_net_address
,Authentication 
con.auth_scheme
FROM 
sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id er.session_id
LEFT 
JOIN sys.dm_exec_connections con ON con.session_id ses.session_id
CROSS 
APPLY sys.dm_exec_sql_text(er.sql_handleas qt
OUTER APPLY SELECT execution_count MAX(cp.usecountsFROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle er.plan_handle ec
ORDER BY er.blocking_session_id DESCer.logical_reads er.reads DESCer.session_id

Advertisements

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