Performance Data Collection Viewer

This stored procedure queries the Maintenance Data Warehouse (MDW) snapshot tables to report notable query statistics over a time range: executions, average worker time, physical reads, logical reads and writes, elapsed time - plus a weighted score that blends them all into one number for ranking. Filter by application name and start/end time.

Example: EXEC GetNotableQueryStats @ApplicationName = 'Quote To Cash', @StartTime = '5/8/2012', @EndTime = '5/8/2012 23:59:59'

ALTER PROCEDURE GetNotableQueryStats
(
	@ApplicationName nVarChar(128) = NULL,
	@StartTime DateTime = NULL,
	@EndTime DateTime = NULL
) AS
BEGIN--PROCEDURE
	SET NOCOUNT ON;

	SELECT
		*
	FROM
	(
		SELECT
			[Application],
			Convert(Decimal(16,2), (WorkerTime + (PhysicalReads * 10.0) + LogicalWrites + LogicalReads + ElapsedTime) / 10.0) AS [Weight],
			Executions,
			WorkerTime,
			PhysicalReads,
			LogicalReads,
			LogicalWrites,
			ElapsedTime,
			[Query]
		FROM
		(
			SELECT
				asar.program_name AS [Application],
				SUM(qs.snapshot_execution_count) AS Executions,
				Convert(Decimal(16,2), (SUM(qs.snapshot_worker_time) / 1000.0) / (SUM(qs.snapshot_execution_count) + 0.0)) AS WorkerTime,
				Convert(Decimal(16,2), SUM(qs.snapshot_physical_reads) / (SUM(qs.snapshot_execution_count) + 0.0)) AS PhysicalReads,
				Convert(Decimal(16,2), SUM(qs.snapshot_logical_writes) / (SUM(qs.snapshot_execution_count) + 0.0)) AS LogicalWrites,
				Convert(Decimal(16,2), SUM(qs.snapshot_logical_reads) / (SUM(qs.snapshot_execution_count) + 0.0)) AS LogicalReads,
				Convert(Decimal(16,2), (SUM(qs.snapshot_elapsed_time) / 1000.0) / (SUM(qs.snapshot_execution_count) + 0.0)) AS ElapsedTime,
				nqt.sql_text AS [Query]
			FROM
				snapshots.notable_query_text AS nqt
			INNER JOIN snapshots.query_stats AS qs
				ON qs.[sql_handle] = nqt.[sql_handle]
			INNER JOIN core.snapshots_internal AS si
				ON si.snapshot_id = qs.snapshot_id
			INNER JOIN core.snapshot_timetable_internal AS sti
				ON si.snapshot_time_id = sti.snapshot_time_id
			INNER JOIN
					(
						SELECT DISTINCT
							asar.[sql_handle],
							asar.program_name
					FROM
							snapshots.active_sessions_and_requests AS asar
					WHERE
							asar.[sql_handle] <> 0x000000000000000000000000000000000000000000000000
							AND (asar.program_name LIKE @ApplicationName OR @ApplicationName IS NULL)
					) AS asar
				ON asar.[sql_handle] = nqt.[sql_handle]
			WHERE
				qs.snapshot_execution_count > 0
				AND (sti.snapshot_time >= @StartTime OR @StartTime IS NULL)
				AND (sti.snapshot_time <= @EndTime OR @EndTime IS NULL)
			GROUP BY
				asar.program_name,
				nqt.sql_text
		) AS Detail
	) AS Summary
	ORDER BY
		Summary.[Weight] DESC
	
END--PROCEDURE
GO

EXEC GetNotableQueryStats @ApplicationName = 'Quote To Cash', @StartTime = '5/8/2012', @EndTime = '5/8/2012 23:59:59'