Find current active queries\sessions on a SQL instance

The following scripts find all running queries on a server – good start for troubleshooting long running queries

SELECT 
DB_NAME(req.database_id) as [Database],
sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Next query has more detail but without the sql text:

SELECT
r.session_id, d.name, r.start_time, s.login_name, s.login_time, s.status, 
r.status, s.last_request_start_time, r.command, r.wait_type, r.cpu_time, 
r.total_elapsed_time, r.reads, r.writes
FROM sys.dm_exec_sessions s
left join sys.dm_exec_requests r ON s.session_id = r.session_id
inner join sys.databases d ON d.database_id = r.database_id
WHERE r.session_id > 50

The following query finds all processes on the server for a particular user – whether they are currently running or not:

SELECT 
session_id, login_time, host_name, program_name, cpu_time, client_interface_name, 
login_name, status, last_request_start_time, last_request_end_time 
FROM sys.dm_exec_sessions s
WHERE login_name = 'my_user'
ORDER BY login_time DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: