Background
Whenever users report that a system is slow, there can be many reasons. It can also be several different problems causing the system to be slow. However, more often than not, the problem is due to your code accessing data from something, and it is usually a database.
Even the best and most optimised database application can still have problems; perhaps too many users are running complex processes or just one long-running query impacting every other function.
How do we check who is running what?
There are lots of built-in SPs and Queries that allow you to view what is running right now, including: –
SELECT * FROM sys.dm_exec_requestsThis outputs something like this: –
or
SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this query
AND session_id > 50 -- don't show system queries
ORDER BY r.start_timeWhich outputs something like this: –
What is really running? What SQL?
The problem with the above scripts is that you can’t easily see the SQL being executed.
So, the SQL below can be helpful in such cases: –
SELECT
SessionId = ses.session_id
,[Database] = DB_Name(er.database_id)
,[Login] = ses.login_name
,Host = ses.host_name
,StartTime = er.start_time
,ClientAddress = con.client_net_address
,SQLStatement = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE ses.is_user_process = 0x1
AND ses.session_id != @@SPID
ORDER BY
ses.session_idIt produces this: –
You can see who is running what.
