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

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

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
• If it's the Head Blocker
• 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.status
,p.session_id
,p.blocking_session_id
,
case
when p2.session_id is not null and (p.blocking_session_id = 0 or p.session_id IS NULL) then '1' else '0' end as head_blocker
,t.text
,p.start_time
,p.total_elapsed_time/1000 as elapsed_time_secs,p.wait_time/1000 as wait_time
,p.last_wait_type
,dr.host_name
,dr.program_name
,dr.login_name
,m.granted_memory_kb
,m.grant_time
,p.plan_handle
,ph.query_plan
,p.sql_handle
FROM sys.dm_exec_requests p
--left join sys.dm_os_waiting_tasks w on w.session_id = p.session_id
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
left join sys.dm_exec_requests p2 ON (p.session_id = p2.blocking_session_id)
where 1=1
and text is not null
--and p.session_id in (select spid from sysprocesses where blocked<>0)
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:

Sources:

• sys.dm_exec_requests (Transact-SQL)