Summary

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.

[adsenseyu5]

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:

Objects

Description/Usage

sys.sysprocesses

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

sys.dm_exec_sessions

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

sys.dm_exec_requests

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

sys.dm_tran_session_transactions

A join between sessions and associated transaction for a session

sys.dm_tran_active_transactions

Details about the transactions

sys.dm_exec_sql_text

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

SessionID

SQL server process ID, also known as SPID, will be referred as “session” now onward

Kpid

Shows if parallel threads are used by session, if you are seeing it a lot check CXPacket, MAXDOP and Parallelism

BlockingSession

This is the session blocking SessionID, If this value is more than 0 then look what the BlockingSession is doing

QueryExecuted

Return query executed by a session

SessionIdleSec

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

HostName

Name of the host machine initiated session

ProgramName

This is the program executing the session, like SQLCMD, SSMS, SQL JOB, or the value configured at application level

LoginName

Name of the user/login executing this session

SessionStatus

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

Command

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

WaitType

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

WaitResource

This work in conjunction with WaitType

WaitTimeSec

How long session is waiting for resource specified in WaitResource & WaitType to be available

OpenTransactionCount

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

TransactionBeginTime

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

TransactionStatus

If session is running in a transaction it populated transaction status otherwise value would be “Not Active”

TransactionIsolationLevel

What’s is the Isolation level this session is using, affect the way SQL server acquire locks on objects

IsUserTransaction

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

TransactionType

Type of transaction is Read/write, Read-only, system or distributed

TransactionId

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

TransactionState

Describes what transaction is currently doing

EnlistCount

Number of active requests in the session working on the transaction

PercentComplete

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

EstimatedCompletionTime

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

CpuConsumedSec

Total CPU consumed by current session

TimeConsumedSec

Total time consumed by current session

PhysicalIO

Total Physical IO consumed by current session

MemUsage

Total memory allocated to current session

LastRequestStartTime

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

LastRequestEndTime

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

Constraints

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
select 
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
select 
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,
case
when t.transaction_type <> 4 
then 
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' 
end 
when t.transaction_type <> 4 
then 
case t.dtc_state 
when 1 then 'Active' 
when 2 then 'Prepared' 
when 3 then 'Committed' 
when 4 then 'Aborted' 
when 5 then 'Recovered' 
end 
else 
'Not Active' 
end as TransactionStatus,
CASE 
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