i get lot of queries from dba’s asking what is the quickest way to figure out issue on sql server. for me almost every sql server performance troubleshooting starts with checking how sessions are doing, trying to find out which sessions are problem and needs quick fix…

in this article i will walk you through benefits of script and describe objects used to collect required information to quickly identify problem sessions and at the end i will explain how to analyses the output.


why script?

first and foremost reason is heavy oltp systems where any delay in finding and fixing an issue is critical, if we look at the current methods there are some deficiencies…

  • sp_who or any single dmv’s provides very limited information to make much sense
  • sp_who or dmv’s returns lot more rows and on heavily loaded systems and takes lot of time
  • several output values returned as numeric constraints, which needs to be manually converted into readable values for better understanding

this script is trying to address above issues

  • dmv’s are main source of information for troubleshooting in future
  • not all information required is available in one dmv and need to join multiple objects to make sense of information
  • constant values are converted in readable meaningful values
  • filtered out all inactive sessions and return below 3 types of session responsible for performance issues and require further troubleshooting
  1. any sessions which have an open transaction
  2. any sessions which has blocked by a session
  3. any sessions which have blocked another session
  • processing less number of records makes it faster and allows to run on heavily loaded systems with minimum footprint

process detail:

let’s go through the dmv’s, system tables and functions used to get information in one view:



provides details about every active sessions on sql server, almost same data as sys.dm_exec_sessions though provides some additional information which is not available in dmv’s


same session information as sys.sysprocessesthough takes precedence over sys.sysprocessesfor common data


provides you details about all actively running sessions on sql server (all awaiting sessions will not be returned), though whenever available this is most accurate data and takes precedence over sys.dm_exec_sessionsand sys.sysprocesses


a join between sessions and associated transaction for a session


details about the transactions


this is the new method introduced in sql server 2005 to return query executed by sessions, this replace dbcc inputbuffer which needs to be executed for each session manually before

output with interpretations

sql server process id, also known as spid, will be referred as “session” now onward

shows if parallel threads are used by session, if you are seeing it a lot check cxpacket, maxdop and parallelism

this is the session blocking sessionid, if this value is more than 0 then look what the blockingsession is doing

return query executed by a session


this value is generated runtime as diff between getdate() & transaction start time or last request end time for same session(usually applicable for session pooling scenarios)

any session having value more than 5 second is worth investigation

name of the host machine initiated session

this is the program executing the session, like sqlcmd, ssms, sql job, or the value configured at application level

name of the user/login executing this session


usually this value tells you whether session is actively doing something or not, any value other than running needs require close look on the value to find out why? look into waittype/waitresource for more info

if there are session in awaiting mode and idle for more than 5 seconds(sessionidlesec) check if they have an open transaction or not

type of command getting executed by session at that moment (usually any delete/insert/update command with sessionidlesec more than 10 require further troubleshooting)

this provides the info what session is currently waiting for, this is very important for debugging, see link for detailed explanation…

this work in conjunction with waittype

how long session is waiting for resource specified in waitresource & waittype to be available

value more than 0 with sessionidlesec more than 10 require more investigation, look into detailed about transaction in next 5 columns
if sessionstatus is showing “awaiting” then most probably transaction is waiting for some external process to finish or aborted though not communicated by app to sql server
check for the objects locked by this session and provide this info to app team for fix

what time transaction was started for this session, null for non-active transaction

if session is running in a transaction it populated transaction status otherwise value would be “not active”

what’s is the isolation level this session is using, affect the way sql server acquire locks on objects

user initiated transaction or system initiated transaction (also called implied transaction)

type of transaction is read/write, read-only, system or distributed

uniquely identifies a transaction, sometimes useful to map in application logs for troubleshooting

describes what transaction is currently doing

number of active requests in the session working on the transaction

provides information about how much work of the current command in session is completed, especially useful to see status for re-indexing & backup

provides information about how much time current command needs to complete in session, especially useful to see status for re-indexing & backup

total cpu consumed by current session

total time consumed by current session

total physical io consumed by current session

total memory allocated to current session

shows when last transaction was started by same session (usually applied to session pooling scenarios)

shows when last transaction was completed by same session (usually applied to session pooling scenarios)


this script works only sql 2005 and onwards

part 2:

will cover how to get extensive details about the locks and blocks obtained by these sessions…stay tuned!!

if you have any questions/suggestions feel free to comment

declare @activeprocess table (

session_id int not null,

kpid int null,

cmd varchar(2000) null,

open_tran tinyint,

lastwaittype varchar(2000) null,

waitresource varchar(2000) null,

blocked int,

sql_handle varbinary (4000) null,

stmt_start int,

stmt_end int,

waittime int,

physical_io bigint,

memusage int


insert into @activeprocess


distinct spid,kpid,cmd,open_tran,lastwaittype,waitresource,blocked,[sql_handle],stmt_start,stmt_end,waittime,physical_io,memusage

from sys.sysprocesses b with (nolock)

where (open_tran>0 

or blocked >0) and spid <> @@spid

or spid in (select blocked from sys.sysprocesses b with (nolock) where blocked >0)

-- select * from @activeprocess


s.session_id as sessionid, 

p.kpid as kpid,

blocked as blockingsession,

substring(qt.text, (p.stmt_start/2)+1, 

((case p.stmt_end

when -1 then datalength(qt.text)

when 0 then datalength(qt.text)

else p.stmt_end

end - p.stmt_start)/2) + 1) as queryexecuted,

datediff(ss, coalesce(t.transaction_begin_time,s.last_request_end_time,r.start_time), getdate()) as sessionidlesec,

s.host_name as hostname,

convert(varchar(2000),s.program_name) as programname, 

s.login_name as loginname,

convert(varchar(2000),s.status) as sessionstatus, 

convert(varchar(2000),p.cmd) as command,

convert(varchar(2000),coalesce(r.last_wait_type,p.lastwaittype)) as waittype, 

convert(varchar(2000),coalesce(r.wait_resource, p.waitresource)) as waitresource, 

p.waittime/1000 as waittimesec, 

convert(int, p.open_tran) as opentransactioncount,

t.transaction_begin_time as transactionbegintime,


when t.transaction_type <> 4 


case t.transaction_state 

when 0 then 'invalid' 

when 1 then 'initialized' 

when 2 then 'active' 

when 3 then 'ended' 

when 4 then 'commit started' 

when 5 then 'prepared' 

when 6 then 'committed' 

when 7 then 'rolling back' 

when 8 then 'rolled back' 


when t.transaction_type <> 4 


case t.dtc_state 

when 1 then 'active' 

when 2 then 'prepared' 

when 3 then 'committed' 

when 4 then 'aborted' 

when 5 then 'recovered' 



'not active' 

end as transactionstatus,


when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 0 then 'unspecified' 

when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 1 then 'readuncommitted' 

when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 2 then 'readcommitted' 

when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 3 then 'repeatable' 

when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 4 then 'serializable' 

when coalesce(r.transaction_isolation_level, s.transaction_isolation_level) = 5 then 'snapshot' 

end as transactionisolationlevel,

case st.is_user_transaction 

when 0 then 'user transaction' 

when 1 then 'system transaction' 

end as isusertransaction,

case t.transaction_type

when 1 then 'read/write transaction' 

when 2 then 'read-only transaction' 

when 3 then 'system transaction' 

when 4 then 'distributed transaction' 

end as transactiontype,

coalesce(r.transaction_id, st.transaction_id) as transactionid, 

case t.transaction_state

when 0 then 'the transaction has not been completely initialized yet' 

when 1 then 'the transaction has been initialized but has not started' 

when 2 then 'the transaction is active' 

when 3 then 'the transaction has ended. this is used for read-only transactions' 

when 4 then 'the commit process has been initiated on the distributed transaction' 

when 5 then 'the transaction is in a prepared state and waiting resolution' 

when 6 then 'the transaction has been committed' 

when 7 then 'the transaction is being rolled back' 

when 8 then 'the transaction has been rolled back' 

end as transactionstate,

st.enlist_count as enlistcount, 

r.percent_complete as percentcomplete,

r.estimated_completion_time as estimatedcompletiontime,

r.cpu_time/1000 as cpuconsumedsec,

r.total_elapsed_time/1000 as timeconsumedsec,

coalesce((r.reads+r.writes),p.physical_io) as physicalio,

coalesce(granted_query_memory,p.memusage) as memusage,

s.last_request_start_time as lastrequeststarttime,

s.last_request_end_time as lastrequestendtime

from @activeprocess p

left join sys.dm_exec_sessions s with (nolock) on s.session_id = p.session_id

left join sys.dm_exec_requests r with (nolock) on s.session_id = r.session_id

left join sys.dm_tran_session_transactions st with (nolock) on s.session_id = st.session_id

left join sys.dm_tran_active_transactions t with (nolock)on t.transaction_id = st.transaction_id

outer apply sys.dm_exec_sql_text(p.sql_handle) as qt