Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* Create a dummy version of the procedure (so that we can use ALTER PROCEDURE below) */
IF object_id('dbo.p_queryProgress') IS NULL
BEGIN
EXEC('CREATE PROCEDURE dbo.p_queryProgress AS BEGIN RETURN END')
END
GO
/* GP 11/6/2015 Add a stored procedure to view live query progress on SQL 2014+
Note that only queries for which the actual execution plan is being captured will appear.
The easiest way to do this is to run the query in SSMS with "Include Actual Execution Plan" on.
Another way is to run an extended event session with the sqlserver.query_post_execution_showplan
event on, but be careful about this option because it does add significant overhead.
See http://sqlperformance.com/2013/03/sql-plan/showplan-impact for details on the overhead.
Example usages:
EXEC p_queryProgress
EXEC p_queryProgress @threadDetail = 1 -- Add pivoted columns showing the distribution of rows across each thread
EXEC p_queryProgress @threadDetail = 2 -- Expand the result set to have a separate row for each thread
EXEC p_queryProgress @session_id = 100 -- Filter results to a single session
EXEC p_queryProgress @node_id = 7 -- Filter results to a single node in the query plan (automatically expands result set to have a separate row for each thread)
*/
ALTER PROCEDURE dbo.p_queryProgress
@threadDetail TINYINT = 0, /* 0, 1, or 2; see example usages */
@session_id INT = NULL,
@node_id INT = NULL
AS
BEGIN
/**********************************************************************
Input validation and processing
**********************************************************************/
-- Confirm SQL 2014+
IF OBJECT_ID('sys.dm_exec_query_profiles') IS NULL
BEGIN
RAISERROR('p_queryProgress can only be run on a SQL 2014+ server.', 16, 1)
RETURN
END
-- If viewing results for just a single node, automatically maximize thread detail
IF @node_id IS NOT NULL
SET @threadDetail = 2
/**********************************************************************
Raw data collection
**********************************************************************/
-- Capture all the raw data by thread from sys.dm_exec_query_profiles
-- (See https://msdn.microsoft.com/en-us/library/dn223301.aspx for documentation)
-- GP 4/6/2016 APT-84606 We now capture database_id/object_id, which we'll use to update object_name
SELECT session_id, node_id, physical_operator_name, thread_id, row_count, estimate_row_count, elapsed_time_ms, cpu_time_ms,
database_id, object_id, CAST(NULL AS VARCHAR(256)) AS object_name,
scan_count, logical_read_count, physical_read_count, read_ahead_count, write_page_count, segment_read_count, segment_skip_count
INTO #tempQueryProgress
FROM sys.dm_exec_query_profiles WITH (NOLOCK)
WHERE session_id <> @@SPID /* Avoid the session running p_queryProgress! */
AND (@session_id IS NULL OR session_id = @session_id) /* If requested, filter to a single session */
AND (@node_id IS NULL OR node_id = @node_id) /* If requested, filter to a single node in the query plan */
-- Key the raw data
ALTER TABLE #tempQueryProgress ADD UNIQUE CLUSTERED (session_id, node_id, thread_id)
/**********************************************************************
Collect object names for any seeks/scans
**********************************************************************/
-- Populate object_name from object_id/database_id in a manner that is not susceptible to being blocked by a SCH-M lock.
-- Unfortunately, SQL Server does not provide support for invoking OBJECT_NAME() using the READ UNCOMMITTED isolation level.
-- Therefore, the otherwise-very-convenient OBJECT_NAME(object_id, database_id) API cannot be used because it will cause
-- p_queryProgress to block indefinitely if any of the ongoing queries access an object that has been modified (SCH-M lock) during the transaction.
-- See http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497 for a relevant enhancement request.
SELECT DISTINCT ISNULL(database_id, 0) AS database_id, ISNULL(object_id, 0) AS object_id, CAST(NULL AS VARCHAR(256)) AS object_name
INTO #distinctObjects
FROM #tempQueryProgress
-- Filter out any 0 values (e.g., for a spool, which uses 0 as a dummy value) as well as any NULL values
WHERE database_id <> 0
AND object_id <> 0
ALTER TABLE #distinctObjects ADD PRIMARY KEY (database_id, object_id)
-- Compute object_name from system catalog metadata so that NOLOCK can be used to prevent p_queryProgress from blocking
-- if any of the ongoing queries are accessing an object that has been modified (SCH-M lock) during the transaction.
DECLARE @dbId INT, @dbName VARCHAR(255), @sqlString VARCHAR(MAX)
DECLARE p_queryProgress_dbNameCursor CURSOR LOCAL FAST_FORWARD FOR
-- Set up a cursor over each distinct database so that we can target the system catalog tables on that database
SELECT DISTINCT d.database_id, d.name
FROM #distinctObjects o
JOIN master.sys.databases d WITH (NOLOCK)
ON d.database_id = o.database_id
OPEN p_queryProgress_dbNameCursor
FETCH NEXT FROM p_queryProgress_dbNameCursor INTO @dbId, @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Capture the full object name for all objects on a particular database that are accessed by one or more scans/seeks in the current p_queryProgress results
SET @sqlString = '
UPDATE do
SET do.object_name = ''' + @dbName + '.'' + s.name + ''.'' + o.name
FROM #distinctObjects do
JOIN ' + @dbName + '.sys.objects o WITH (NOLOCK)
ON o.object_id = do.object_id
JOIN ' + @dbName + '.sys.schemas s WITH (NOLOCK)
ON s.schema_id = o.schema_id
WHERE do.database_id = ' + CONVERT(VARCHAR(MAX), @dbId) + '
'
EXEC(@sqlString)
FETCH NEXT FROM p_queryProgress_dbNameCursor INTO @dbId, @dbName
END
CLOSE p_queryProgress_dbNameCursor
DEALLOCATE p_queryProgress_dbNameCursor
-- Now that we have all the distinct object_names, merge this information into the raw data we collected
UPDATE p
SET p.object_name = do.object_name
FROM #tempQueryProgress p
JOIN #distinctObjects do
ON do.database_id = p.database_id
AND do.object_id = p.object_id
/**********************************************************************
Build the final result set to display
**********************************************************************/
-- The default parameters, which include aggregating data across threads (to produce one row per query plan node)
-- and including a very basic amount of thread-level information (how many threads, how many processed 1+ rows, max % of all rows processed by any single thread)
DECLARE @threadDetailSelect VARCHAR(MAX) = '
, ISNULL(NULLIF(COUNT(CASE WHEN thread_id > 0 THEN 1 END), 0), 1) AS threads /* For nodes executing in parallel, only count parallel threads. For serial nodes (only thread_id 0), return 1. */
, COUNT(CASE WHEN row_count > 0 THEN 1 END) AS active_threads
, FORMAT(1.0*MAX(row_count)/NULLIF(SUM(row_count),0), ''#,##0.00%'') AS [Tmax_%]'
DECLARE @groupBy VARCHAR(MAX) = 'session_id, node_id, physical_operator_name'
DECLARE @orderBy VARCHAR(MAX) = 'session_id, node_id'
IF @threadDetail = 1 /* Add pivoted columns showing the % of rows that were processed by each thread */
BEGIN
SELECT @threadDetailSelect = @threadDetailSelect + '
, FORMAT(1.0*SUM(CASE WHEN thread_id = ' + CAST(thread_id AS VARCHAR(MAX)) + ' THEN row_count END)/NULLIF(SUM(row_count),0), ''#,##0.00%'') AS [T' + CAST(thread_id AS VARCHAR(MAX)) + '_%]'
FROM (
SELECT DISTINCT thread_id FROM #tempQueryProgress WHERE thread_id > 0
) t
-- Add columns in order by thread_id; technically undocumented SQL functionality, but we can do this easily by appending
-- to a variable in the SELECT statement and ensuring that the rows are processed serially and in the order we want.
ORDER BY t.thread_id
OPTION (MAXDOP 1)
END
ELSE IF @threadDetail = 2 /* Expand the result set to contain a row for each thread */
BEGIN
SELECT @threadDetailSelect = ''
-- It's not 100% clear whether callers will prefer thread_id to come before or after node_id in the ordering of rows.
-- For now, we've elected before since other use cases like not breaking out by thread or filtering to a specific node
-- likely already cover use cases in which it might be preferential to have thread_id after node_id in the sort order.
SELECT @groupBy = REPLACE(@groupBy, 'session_id', 'session_id, thread_id')
SELECT @orderBy = REPLACE(@orderBy, 'session_id', 'session_id, thread_id')
END
-- Build and execute the SQL statement that will present the results to the user
DECLARE @queryProgressResultsSql VARCHAR(MAX) = '
SELECT ' + @groupBy + ', MIN(object_name) AS object_name, SUM(row_count) rows, SUM(estimate_row_count) AS estimate_rows,
FORMAT(1.0*SUM(row_count)/NULLIF(SUM(estimate_row_count),0), ''#,##0.00%'') AS [%_complete],
SUM(elapsed_time_ms) AS elapsed_ms,
SUM(cpu_time_ms) AS cpu_ms,
SUM(scan_count) AS scans,
SUM(logical_read_count) AS logical_reads,
SUM(physical_read_count) AS physical_reads,
SUM(read_ahead_count) AS read_aheads,
SUM(write_page_count) AS spill_pages,
SUM(segment_read_count) AS segment_reads,
SUM(segment_skip_count) AS segment_skips'
+ @threadDetailSelect + '
FROM #tempQueryProgress
GROUP BY ' + @groupBy + '
ORDER BY ' + @orderBy + '
'
EXEC(@queryProgressResultsSql)
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.