How do we find what queries are currently running in SQL Server?
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:
Certifications:
Latest posts by Stratos Matzouranis (see all)
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 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
, [statement_text] = Substring(t.TEXT, (p.statement_start_offset / 2) + 1,
( ( CASE p.statement_end_offset WHEN - 1 THEN Datalength(t.TEXT)
ELSE p.statement_end_offset
END - p.statement_start_offset ) / 2 ) + 1)
,[command_text] =Coalesce(Quotename(Db_name(t.dbid)) + N'.' + Quotename(Object_schema_name(t.objectid, t.dbid)) + N'.' + Quotename(Object_name(t.objectid, t.dbid)), '')
,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:
