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.
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
- Any sessions which have an open transaction
- Any sessions which has blocked by a session
- 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
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
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,
lastwaittype varchar(2000) null,
waitresource varchar(2000) null,
sql_handle varbinary (4000) null,
insert into @ActiveProcess
from sys.sysprocesses b with (nolock)
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,
WHEN -1 THEN DATALENGTH(qt.text)
WHEN 0 THEN DATALENGTH(qt.text)
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
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
when 1 then 'Active'
when 2 then 'Prepared'
when 3 then 'Committed'
when 4 then 'Aborted'
when 5 then 'Recovered'
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,
when 0 then 'User Transaction'
when 1 then 'System Transaction'
end as IsUserTransaction,
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,
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