Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
admin:show_running_queries [2019/03/26 09:51]
dyson [Wide version]
admin:show_running_queries [2019/10/03 18:23] (current)
admin
Line 1: Line 1:
 ====== Show running queries ====== ====== Show running queries ======
  
-As featured on [[http://​www.sqlservercentral.com/​scripts/​Performance+Tuning/​123190/​|SQL Server Central]]! +The SSMS activity monitor provides a list of sessions with the text of the associated query //batch//. This script uses ''​statement_start_offset''​ and ''​statement_end_offset''​ to identify the currently executing statement within the batch and calculate its line number in the batch. Also includes a link to the selected execution plan, if available, and includes: 
- +  * start time and duration; 
-The SSMS activity monitor provides a list of sessions with the text of the associated query //batch//. This script uses ''​statement_start_offset''​ and ''​statement_end_offset''​ to identify the currently executing statement within the batch and calculate its line number in the batch. ​+  * information about wait duration, type and blocking processes (if any); 
 +  * query memory grants.
  
-Also includes a link to the selected execution plan, if available. 
  
 <code tsql>​WITH cte AS ( <code tsql>​WITH cte AS (
-  SELECT ​+  SELECT
     r.session_id     r.session_id
   , r.request_id   , r.request_id
   , r.database_id   , r.database_id
 +  , r.start_time
 +  , DATEDIFF(SECOND,​ r.start_time,​ GETDATE()) AS run_duration_s
   , t.objectid   , t.objectid
   , t.[text]   , t.[text]
Line 20: Line 22:
     END AS StatementEndOffset     END AS StatementEndOffset
   , p.query_plan   , p.query_plan
-  FROM sys.dm_exec_requests r +  FROM sys.dm_exec_requests r
     CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t     CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
     OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p     OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
Line 27: Line 29:
   AND t.[text] NOT LIKE '​%9ow34ytghehl3q94wg%' ​ -- grep -v grep!   AND t.[text] NOT LIKE '​%9ow34ytghehl3q94wg%' ​ -- grep -v grep!
 ), spaceUsage AS ( ), spaceUsage AS (
-  SELECT ​+  SELECT
     session_id     session_id
   , request_id   , request_id
   , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB   , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB
   , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB   , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB
-  FROM sys.dm_db_task_space_usage ​+  FROM sys.dm_db_task_space_usage
   GROUP BY   GROUP BY
     session_id     session_id
   , request_id   , request_id
 ) )
-SELECT ​+SELECT
   r.session_id   r.session_id
 , s.login_name , s.login_name
Line 44: Line 46:
     QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid,​ r.database_id)) + '​.'​ + QUOTENAME(OBJECT_NAME(r.objectid,​ r.database_id))     QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid,​ r.database_id)) + '​.'​ + QUOTENAME(OBJECT_NAME(r.objectid,​ r.database_id))
   , LEFT(LTRIM(r.[text]),​ 128)) AS QueryBatch   , LEFT(LTRIM(r.[text]),​ 128)) AS QueryBatch
-LEN(LEFT(r.[text], r.StatementStartOffset))  +, r.start_time as BatchStartDateTime 
-    ​- LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), ''​))  +CAST(r.run_duration_s / 3600 AS VARCHAR
-    + AS LineNumber+    ​+ ':'​ + RIGHT('​00'​ + CAST((r.run_duration_s % 3600/ 60 AS VARCHAR), 2
 +    + ':'​ + RIGHT('​00'​ + CAST((r.run_duration_s % 3600) % 60 AS VARCHAR), 2) AS RunTimeHMS
 , SUBSTRING( , SUBSTRING(
     r.[text]     r.[text]
Line 52: Line 55:
   , r.StatementEndOffset + 1 - r.StatementStartOffset   , r.StatementEndOffset + 1 - r.StatementStartOffset
   ) AS CurrentStatement   ) AS CurrentStatement
 +, LEN(LEFT(r.[text],​ r.StatementStartOffset))
 +    - LEN(REPLACE(LEFT(r.[text],​ r.StatementStartOffset),​ CHAR(10), ''​))
 +    + 1 AS [LineNo]
 +, wt.wait_type
 +, CAST((wt.wait_duration_ms / 1000) / 3600 AS VARCHAR)
 +    + ':'​ + RIGHT('​00'​ + CAST(((wt.wait_duration_ms / 1000) % 3600) / 60 AS VARCHAR), 2)
 +    + ':'​ + RIGHT('​00'​ + CAST(((wt.wait_duration_ms / 1000) % 3600) % 60 AS VARCHAR), 2) AS WaitTimeHMS
 +, wt.blocking_session_id AS BlockedBy
 +, mg.requested_memory_kb/​1024 AS MemoryRequestedMB
 +, mg.used_memory_kb/​1024 AS MemoryUsedMB
 , u.UserObjMB AS [UserObjMB*] , u.UserObjMB AS [UserObjMB*]
 , u.InternalObjMB , u.InternalObjMB
Line 57: Line 70:
 FROM cte r FROM cte r
   INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id   INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
 +  LEFT JOIN (
 +    SELECT TOP 1 WITH TIES *
 +    FROM sys.dm_os_waiting_tasks
 +    WHERE blocking_session_id <> session_id
 +    ORDER BY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY wait_duration_ms DESC)
 +  ) wt ON wt.session_id = r.session_id
   LEFT JOIN spaceUsage u   LEFT JOIN spaceUsage u
     ON r.session_id = u.session_id     ON r.session_id = u.session_id
     AND r.request_id = u.request_id     AND r.request_id = u.request_id
-/* +  LEFT JOIN sys.dm_exec_query_memory_grants mg ON mg.session_id = r.session_id 
-UNION ALL +</​code>​
-  +
-SELECT  +
-  9999 +
-, NULL +
-, '​tempdb'​ +
-, CAST(SUM(unallocated_extent_page_count)/​128 AS VARCHAR) + ' MB free'​ +
-, NULL +
-, NULL +
-, SUM(user_object_reserved_page_count) / 128 +
-, SUM(internal_object_reserved_page_count) / 128 +
-, NULL +
-FROM tempdb.sys.dm_db_file_space_usage +
- +
-ORDER BY 1 +
---*/</​code>​+
  
 ===== Notes ===== ===== Notes =====
  
-  * UserObjMB* typically relates to storage explicitly requested by the user (e.g. in temporary tables). The asterisk is intended to indicate that this is not always reliable - [[http://​sqlity.net/​en/​671/​deferred-deallocations-of-pages-in-tempdb/​|deferred deallocation]] in ''​tempdb''​ can make this appear to be higher than is the case.+  * UserObjMB* typically relates to storage explicitly requested by the user (e.g. in temporary tables). The asterisk is intended to indicate that this is not always reliable ​-- [[http://​sqlity.net/​en/​671/​deferred-deallocations-of-pages-in-tempdb/​|deferred deallocation]] in ''​tempdb''​ can make this appear to be higher than is the case.
   * InternalObjMB relates to storage used by the database engine in evaluating the batch (e.g. hash buckets, temporary sort runs etc)   * InternalObjMB relates to storage used by the database engine in evaluating the batch (e.g. hash buckets, temporary sort runs etc)
-  * The link to the query plan may be null if the plan is no longer in the cache or if the plan is too complex. In the latter case a text plan may still be available and can be obtained using the commented-out OUTER APPLY sys.dm_exec_text_query_plan. +  * The link to the query plan may be null if the plan is no longer in the cache or if the plan is too complex. In the latter case a text plan may still be available and can be obtained using the commented-out OUTER APPLY [[https://​docs.microsoft.com/​en-us/​sql/​relational-databases/​system-dynamic-management-views/​sys-dm-exec-text-query-plan-transact-sql|sys.dm_exec_text_query_plan]]
-  * The purpose of the NOT LIKE '​%9ow34ytghehl3q94wg%' ​clause ​is to exclude ​*thisquery from the output!+  * The purpose of ''​NOT LIKE '​%9ow34ytghehl3q94wg%%%'​%%'​' is to exclude ​//this// query from the output!
  
-===== Wide version ===== +===== Narrow ​version ===== 
-This wider version adds:+This narrower (featured on [[http://​www.sqlservercentral.com/​scripts/​Performance+Tuning/​123190/​|SQL Server Central]]!) omits:
   * start time and duration;   * start time and duration;
-  * information about wait duration, type and blocking processes ​(if any);+  * information about wait duration, type and blocking processes;
   * query memory grants.   * query memory grants.
 +
 <code tsql>​WITH cte AS ( <code tsql>​WITH cte AS (
-  SELECT+  SELECT ​
     r.session_id     r.session_id
   , r.request_id   , r.request_id
   , r.database_id   , r.database_id
-  , r.start_time 
-  , DATEDIFF(SECOND,​ r.start_time,​ GETDATE()) AS run_duration_s 
   , t.objectid   , t.objectid
   , t.[text]   , t.[text]
Line 105: Line 108:
     END AS StatementEndOffset     END AS StatementEndOffset
   , p.query_plan   , p.query_plan
-  FROM sys.dm_exec_requests r+  FROM sys.dm_exec_requests r 
     CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t     CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
     OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p     OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
Line 112: Line 115:
   AND t.[text] NOT LIKE '​%9ow34ytghehl3q94wg%' ​ -- grep -v grep!   AND t.[text] NOT LIKE '​%9ow34ytghehl3q94wg%' ​ -- grep -v grep!
 ), spaceUsage AS ( ), spaceUsage AS (
-  SELECT+  SELECT ​
     session_id     session_id
   , request_id   , request_id
   , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB   , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB
   , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB   , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB
-  FROM sys.dm_db_task_space_usage+  FROM sys.dm_db_task_space_usage ​
   GROUP BY   GROUP BY
     session_id     session_id
   , request_id   , request_id
 ) )
-SELECT+SELECT ​
   r.session_id   r.session_id
 , s.login_name , s.login_name
Line 129: Line 132:
     QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid,​ r.database_id)) + '​.'​ + QUOTENAME(OBJECT_NAME(r.objectid,​ r.database_id))     QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid,​ r.database_id)) + '​.'​ + QUOTENAME(OBJECT_NAME(r.objectid,​ r.database_id))
   , LEFT(LTRIM(r.[text]),​ 128)) AS QueryBatch   , LEFT(LTRIM(r.[text]),​ 128)) AS QueryBatch
-, r.start_time as BatchStartDateTime +LEN(LEFT(r.[text], r.StatementStartOffset))  
-CAST(r.run_duration_s / 3600 AS VARCHAR+    ​- LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), ''​))  
-    ​+ ':'​ + RIGHT('​00'​ + CAST((r.run_duration_s % 3600/ 60 AS VARCHAR), 2+    + AS LineNumber
-    + ':'​ + RIGHT('​00'​ + CAST((r.run_duration_s % 3600) % 60 AS VARCHAR), 2) AS RunTimeHMS+
 , SUBSTRING( , SUBSTRING(
     r.[text]     r.[text]
Line 138: Line 140:
   , r.StatementEndOffset + 1 - r.StatementStartOffset   , r.StatementEndOffset + 1 - r.StatementStartOffset
   ) AS CurrentStatement   ) AS CurrentStatement
-, LEN(LEFT(r.[text],​ r.StatementStartOffset)) 
-    - LEN(REPLACE(LEFT(r.[text],​ r.StatementStartOffset),​ CHAR(10), ''​)) 
-    + 1 AS [LineNo] 
-, wt.wait_type 
-, CAST((wt.wait_duration_ms / 1000) / 3600 AS VARCHAR) 
-    + ':'​ + RIGHT('​00'​ + CAST(((wt.wait_duration_ms / 1000) % 3600) / 60 AS VARCHAR), 2) 
-    + ':'​ + RIGHT('​00'​ + CAST(((wt.wait_duration_ms / 1000) % 3600) % 60 AS VARCHAR), 2) AS WaitTimeHMS 
-, wt.blocking_session_id AS BlockedBy 
-, mg.requested_memory_kb/​1024 AS MemoryRequestedMB 
-, mg.used_memory_kb/​1024 AS MemoryUsedMB 
 , u.UserObjMB AS [UserObjMB*] , u.UserObjMB AS [UserObjMB*]
 , u.InternalObjMB , u.InternalObjMB
Line 153: Line 145:
 FROM cte r FROM cte r
   INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id   INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
-  LEFT JOIN ( 
-    SELECT TOP 1 WITH TIES * 
-    FROM sys.dm_os_waiting_tasks 
-    WHERE blocking_session_id <> session_id 
-    ORDER BY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY wait_duration_ms DESC) 
-  ) wt ON wt.session_id = r.session_id 
   LEFT JOIN spaceUsage u   LEFT JOIN spaceUsage u
     ON r.session_id = u.session_id     ON r.session_id = u.session_id
     AND r.request_id = u.request_id     AND r.request_id = u.request_id
-  LEFT JOIN sys.dm_exec_query_memory_grants mg ON mg.session_id = r.session_id +/* 
-</​code>​+UNION ALL 
 +  
 +SELECT  
 +  9999 
 +, NULL 
 +, '​tempdb'​ 
 +, CAST(SUM(unallocated_extent_page_count)/​128 AS VARCHAR) + ' MB free'​ 
 +, NULL 
 +, NULL 
 +, SUM(user_object_reserved_page_count) / 128 
 +, SUM(internal_object_reserved_page_count) / 128 
 +, NULL 
 +FROM tempdb.sys.dm_db_file_space_usage 
 + 
 +ORDER BY 1 
 +--*/</​code>​ 
  
 {{tag>​running admin}} {{tag>​running admin}}
 ~~DISCUSSION~~ ~~DISCUSSION~~