Query data gathered with Performance Monitor and loaded with Relog to a SQL Database

query-perfmon-relog-data.sql

WITH cte AS
(
	SELECT
		YEAR(CONVERT(DATETIME, CAST(C1.CounterDateTime AS VARCHAR(19))) ) AS sample_year
	,	MONTH(CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_month
	,	DAY(CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_day
	,	DATEPART(HH,CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_hour
	,	DATEPART(mi,CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_minute
	,	REPLACE(C2.MachineName, '\\', '') AS machine_name
	,	CASE
		WHEN LEFT(C2.ObjectName, 6) = 'MSSQL$'
		THEN SUBSTRING(C2.ObjectName, 7, CHARINDEX(':', C2.ObjectName, 1) - 7)
		WHEN LEFT(C2.ObjectName, 10) = 'SQLServer:'
		THEN 'MSSQLSERVER'
		ELSE NULL
		END AS sql_instance_name
	,	CASE
		WHEN LEFT(C2.ObjectName, 6) = 'MSSQL$'
		THEN 'SQLServer:' + SUBSTRING(C2.ObjectName, CHARINDEX(':', C2.ObjectName, 1) + 1, LEN(C2.ObjectName) - 6)
		ELSE C2.ObjectName
		END AS perf_object_name
	,	C2.InstanceName AS perf_instance_name
	,	C2.CounterName AS perf_counter_name
	,	C2.InstanceIndex AS perf_instance_index
	,	C1.CounterValue AS perf_counter_value
	FROM CounterData AS C1
	JOIN CounterDetails AS C2
	  ON C1.CounterID = C2.CounterID
)
SELECT	
	sample_year
,	sample_month
,	sample_day
,	sample_hour
,	sample_minute
,	machine_name
,	sql_instance_name
,	perf_object_name
,	perf_instance_name
,	perf_counter_name
,	perf_instance_index
,	COUNT(*) AS num_samples
,	MIN(perf_counter_value) AS min_perf_counter_value
,	AVG(perf_counter_value) AS avg_perf_counter_value
,	MAX(perf_counter_value) AS max_perf_counter_value
FROM	cte
GROUP BY
	sample_year
,	sample_month
,	sample_day
,	sample_hour
,	sample_minute
,	machine_name
,	sql_instance_name
,	perf_object_name
,	perf_instance_name
,	perf_counter_name
,	perf_instance_index