Run this script in your management studio as administrator.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SPID = er.session_id ,
DBName = DB_Name(er.database_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.objectid, qt.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 ,
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_handle) AS qt OUTER APPLY
( SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle) ec
ORDER BY er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
0 comments:
Post a Comment