Current Running Queries

Following script is perhaps the most executed script in my 10 year Database Administration career. The script has been my first line of defense in any performance monitoring problem and has helped me narrow down most of the performance issues.

Many moons ago, I had posted a similar script over here. This is much more enhanced and powerful script with more detailed info. Works from SQL Server 2008 to SQL Server 2016.

SELECT   getdate ()   as  dt ,
ss . session_id ,
db_name ( sysprocesses . dbid )   as  dbname ,
er . status   as  req_status ,
ss . login_name ,
cs . client_net_address ,
ss . program_name ,
sysprocesses . open_tran ,
er . blocking_session_id ,
ss . host_name ,
ss . client_interface_name ,
[eqp] . [query_plan]  as  qplan ,
SUBSTRING ( est . text ,( er . statement_start_offset / 2 )+ 1 ,
CASE   WHEN  er . statement_end_offset =- 1  OR  er . statement_end_offset = 0 
THEN  ( DATALENGTH ( est . Text )- er . statement_start_offset / 2 )+ 1 
ELSE  ( er . statement_end_offset - er . statement_start_offset )/ 2 + 1
END )   as  req_query_text ,
er . granted_query_memory ,
er . logical_reads  as  req_logical_reads ,
er . cpu_time  as  req_cpu_time ,
er . reads  as  req_physical_reads ,
er . row_count  as  req_row_count ,  
er . scheduler_id ,
er . total_elapsed_time  as  req_elapsed_time ,
er . start_time  as  req_start_time ,
er . percent_complete ,
er . wait_resource  as  wait_resource ,
er . wait_type  as  req_waittype ,
er . wait_time  as  req_wait_time ,
wait . wait_duration_ms  as  blocking_time_ms ,
lock . resource_associated_entity_id ,
lock . request_status  as  lock_request_status ,
lock . request_mode  as  lock_mode ,
er . writes  as  req_writes ,
sysprocesses . lastwaittype ,
fn_sql . text   as  session_query ,
ss . status   as  session_status ,
ss . cpu_time  as  session_cpu_time ,
ss . reads  as  session_reads ,
ss . writes  as  session_writes ,
ss . logical_reads  as  session_logical_reads ,
ss . memory_usage  as  session_memory_usage ,
ss . last_request_start_time ,
ss . last_request_end_time ,
ss . total_scheduled_time  as  session_scheduled_time ,
ss . total_elapsed_time  as  session_elpased_time ,
ss . row_count  as  session_rowcount
FROM   sys . dm_exec_sessions  ss 
INNER   JOIN   sys . dm_exec_connections  cs 
ON  ss . session_id  =  cs . session_id 
OUTER   APPLY
fn_get_sql ( cs . most_recent_sql_handle )   as  fn_sql
INNER   JOIN   sys . sysprocesses  
ON   sys . sysprocesses . spid  =  cs . session_id
LEFT   OUTER   JOIN   sys . dm_exec_requests  [er] 
ON  er . session_id  =  ss . session_id
OUTER   APPLY   sys . dm_exec_sql_text   ( [er] . [sql_handle] )  [est]
OUTER   APPLY   sys . dm_exec_query_plan   ( [er] . [plan_handle] )  [eqp]
LEFT   OUTER   JOIN  
sys . dm_os_waiting_tasks  wait 
ON  er . session_id  =  wait . session_id
and  wait . wait_type  like   'LCK%'   and  
er . blocking_session_id  =  wait . blocking_session_id
LEFT   OUTER   JOIN   sys . dm_tran_locks  lock 
ON  lock . lock_owner_address  =  wait . resource_address
and  lock . request_session_id  =  er . blocking_session_id
WHERE  ss . status   !=   'sleeping'

Yes, a long query but perhaps the most useful one of all my scripts.

What does the script do?

Gives the details of about the active sessions and queries

Script gives us 2 types of information.  

1) Session related details - Information about the active sessions

2) Request related details - Information about the query that is active in each session

Request related details are lot more of interest. 

Key columns include

1) req_query_text - Query being run
2) req_status - Status of the query ( "Running" / "Suspended"/ "Runnable" )
3) Query Plan - Execution plan of the query
4) Cpu_time - Time spent by the query on CPU
5) req_elapsed_time - How long the has been running - in milliseconds
6) req_waittype - Wait type query was waiting for
7) req_wait_time - Waiting time in ms
8) blocking_session_id - Session Id blocking it
9) Connection details - IP Address, Application, hostname etc
10) Other Resource utilization details - reads, writes, query memory, row count etc.