How do we find what queries are currently running in SQL Server?

How do we find what queries are currently running in SQL Server?
How do we find what queries are currently running in SQL Server?

Many times we will need to quickly find out what queries are running in an instance of SQL Server. In this article we will see a query that I have written that can directly extract all the information we need.

The query draws information from the system views dm_exec_requests, databases, dm_exec_sql_text dm_exec_sessions, dm_exec_query_memory_grants and dm_exec_query_plan.

Through this query we can see the following:

  • What database is it running on?
  • What type of command is it (select,update,delete,backup...)
  • The sql text
  • What is delaying the query
  • If another session blocks it
  • The memory it occupies
  • His plan
  • What machine is it running from?
  • What program is it running from?
  • Which user is it running from?

The query

All we have to do is run the following select query in a query window:

SELECT d.name,p.command,p.session_id,t.text,p.start_time,p.status,p.total_elapsed_time/1000 as elapsed_time_secs,wait_time/1000 as wait_time,last_wait_type,p.blocking_session_id,m.granted_memory_kb,m.grant_time,p.plan_handle,ph.query_plan,p.sql_handle,dr.host_name,dr.program_name,dr.login_name
FROM sys.dm_exec_requests p
inner join sys.databases d on d.database_id = p.database_id
outer apply sys.dm_exec_sql_text(p.sql_handle) t
outer apply sys.dm_exec_query_plan(p.plan_handle) as ph
inner join sys.dm_exec_sessions dr on dr.session_id = p.session_id
left join sys.dm_exec_query_memory_grants m on m.session_id = p.session_id
where 1=1
and text is not null
--and p.session_id in (select spid from sysprocesses where blocked<>0) --uncomment for only blocking queries
order by  p.start_time;

The result

Running the query will return the results as below. Also selecting double click on query_plan will open the graphic of each shot:

How do we find what queries are currently running in SQL Server?

Sources:

Share it

Leave a reply