Script : Find Currently executing Queries, Blocking, Waits, Statement, Procedure, CPU
Posted by blakhani on July 17, 2014
This is one of my all time favorite script which I use most of the time in troubleshooting performance issues on live systems. This helps in findings below:
- What is the performance bottleneck?
- Is there any blocking? If yes, who is the blocker?
- What are the queries which are executing currently?
- What is the name of the stored procedure running currently?
- Which statement in stored procedure is getting executed right now?
- Who is consuming CPU right now? What are the high CPU queries?
- Who is doing lots of IO right now?
Column heading is self-explanatory. You need to adjust ORDER BY clause based on your need. In below query, I am showing top CPU consumers at the top. (Thanks for Ghufran (f) for adding more columns)
SELECT s.session_id ,r.STATUS ,r.blocking_session_id AS 'blocked_by' ,r.wait_type ,r.wait_resource ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time' ,r.cpu_time ,r.logical_reads ,r.reads ,r.writes ,CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time' ,CAST(( '<?query -- ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1) + CHAR(13) + CHAR(13) + '--?>' ) AS XML) AS 'query_text' ,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc' --,qp.query_plan AS 'xml_plan' -- uncomment (1) if you want to see plan ,r.command ,s.login_name ,s.host_name ,s.program_name ,s.host_process_id ,s.last_request_end_time ,s.login_time ,r.open_transaction_count FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st --OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if you want to see plan WHERE r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%' OR r.session_id != @@SPID ORDER BY r.cpu_time DESC ,r.STATUS ,r.blocking_session_id ,s.session_id
Here is the partial output
Note that since it shows currently executing request, it won’t show the request which are not doing anything like sleeping connection or awaiting command.
Hope this helps
Advertisements
bharathpammiramesh said
Thanks. It will be helpful to troubleshoot performance issues.
blakhani said
Thanks Bharath
Look what’s running in your sql server when facinf performance issue | Sqlinme said
[…] this excellent script posted by BalaMukund sir, which would give you an insight on whats running in your sql server when you […]
SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized | Journey to SQL Authority with Pinal Dave said
[…] job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means […]
subhash Yadav said
Thanks Bharath
blakhani said
Bharath?
SQL SERVER – SSMS Database Expand Hang – High waits on PREEMPTIVE_OS_LOOKUPACCOUNTSID « Help: SQL Server said
[…] profiler trace, and the query to capture currently running queries (refer this blog) I found that it was running sp_help_job stored procedure from MSDB database. This is the procedure […]
Anoop Kumar P said
Great Script Balmukund 🙂 .This one is really helpful to instantaneously identify the Sprocs which causes high CPU time and Elapsed time. Very good for real time troubleshooting.
blakhani said
Thanks Anoop.
Seetha rami reddy said
Thanks Bharath
SQL SERVER - FIX: ERROR : Msg 3023, Level 16, State 2 - Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized - Journey to SQL Authority with Pinal Dave said
[…] job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means […]
deepaktyagi83Deepak said
Hi Blakhani
I want to know which count we are getting in above query in r.logical_reads column its related to statement_text column or command_text column because if we are running procedure it will show procedure logical count or running statement part in batch.
Thanks
deepaktyagi83 said
Hi Blakhani
,r.logical_reads is related to statement_text or command_text ?
umasankar said
thanks