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)
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:
