Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLFeatures

Prakash Heda works as Sr. Database Administrator at Advent Software. His expertise includes Performance Tuning/Troubleshooting, high availability, SAN configuration and windows administration. He has 13+ years of experience managing enterprise-wide data needs in both high volume transactional and data warehouse environments. Prakash holds certifications for MCDBA and MCITP. His background includes administration & development using VB/ASP/SSIS using SQL Server 2000, 2005 and 2008/R2 and SQL server 2012. He is an active member of PASS, SQL Saturday. He’s a frequent presenter at user groups, SQL Saturdays, and other community events. He is also an avid tweeter, blogger and runner.

Quick SQL Server Sessions Troubleshooting

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

 


Comments

Leave a comment on the original post [www.sqlfeatures.com, opens in a new window]

Loading comments...