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.
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.
How to make good use of it is for another day in another post :)
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.
How to make good use of it is for another day in another post :)
