SQL PERFORMANCE MONITOR – PART 8: Report Views and Procedures

In this section I will explore some example views that I will be using in my reports. In particular I would like to create a dashboard and I want to get the most recent data for that quickly and efficiently. Since my monitor DW table has millions of rows it is important that I place a usable index on the table and test different methods for performance.

After considering the above, I found that a combination of views within a stored procedure that uses a temp table variable gave me the best performance stats. Your data and scenario could be different, but I’ll share mine here.

Table Indexes

I have created the following INDEXES for my situation. The first INDEX was actually created to prevent SSIS (in the previous steps) from being able to insert duplicate rows into the table. The other two are performance related indexes.

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueConstraint] ON [Analysis].[PerfCounterStats]
(
	[LocationID] ASC,
	[ClusterID] ASC,
	[MetricID] ASC,
	[DateKey] ASC,
	[TimeKey] ASC,
	[PartName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueConstraint] ON [Analysis].[PerfCounterStats]
(
	[LocationID] ASC,
	[ClusterID] ASC,
	[MetricID] ASC,
	[DateKey] ASC,
	[TimeKey] ASC,
	[PartName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_PerfCounterStats_DateKey] ON [Analysis].[PerfCounterStats]
(
	[DateKey] ASC,
	[MetricID] ASC,
	[LocationID] ASC,
	[ClusterID] ASC,
	[PartName] ASC,
	[TimeKey] ASC
)
INCLUDE([iVal],[dVal],[UtcDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

The Views

One of the METRICS I capture is CPU USAGE PERCENT. I may wish to create a report that gives detail down to the minute or in another case over days or hours at ten minute intervals. I created these two views to handle each scenario.

CREATE VIEW [Analysis].[pcv_CpuUsagePercent_01]
AS

	-- Get CPU Percentage Use by minute interval
	-- MetricID 439 = OS Perf Counter: CPU usage % | 440 = OS Perf Counter: CPU usage % base
	SELECT	pc1.LocationID, 
			pc1.ClusterID, 
			pc1.DateKey, 
			pc1.TimeKey, 
			pc1.iVal AS iVal1, 
			pc2.iVal AS iVal2,
			(CASE WHEN pc2.iVal > 0 THEN 
				CONVERT(INT,((CAST(pc1.iVal AS FLOAT) / pc2.iVal))*100) 
				ELSE 0 END) AS iValResult
	FROM	Analysis.PerfCounterStats as pc1
			INNER JOIN Analysis.PerfCounterStats as pc2 ON 
					pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
					and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
	WHERE (pc1.MetricID = 439 and pc2.MetricID = 440)
	GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey, pc2.TimeKey, pc1.iVal, pc2.iVal;

CREATE VIEW [Analysis].[pcv_CpuUsagePercent_10]
AS

	-- Get CPU Percentage Use by ten (10) minute interval
	-- MetricID 439 = OS Perf Counter: CPU usage % | 440 = OS Perf Counter: CPU usage % base
	SELECT	pc1.LocationID, 
			pc1.ClusterID, 
			pc1.DateKey, 
			CONCAT(LEFT(pc1.TimeKey,3),'000') AS TimeKey, 
			AVG(pc1.iVal) AS iVal1, 
			AVG(pc2.iVal) AS iVal2,
			(CASE WHEN AVG(pc2.iVal) > 0 THEN
				CONVERT(INT,((CAST(AVG(pc1.iVal) AS FLOAT) / AVG(pc2.iVal)))*100) 
				ELSE 0 END)AS iValResult
	FROM	Analysis.PerfCounterStats as pc1
			INNER JOIN Analysis.PerfCounterStats as pc2 ON 
					pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
					and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
	WHERE (pc1.MetricID = 439 and pc2.MetricID = 440)
	GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, LEFT(pc1.TimeKey,3);

For my performance DASHBOARD, I want a view of just hte latest stat from each company location and for each server, so I created this view to be used within that STORED PROCEDURE that I will create here, lastly.

CREATE VIEW [Analysis].[pcv_CpuUsagePercent_Latest]
AS

	-- Get LATEST (only) CPU Percentage Use by Location/cluster
	-- MetricID 439 = OS Perf Counter: CPU usage % | 440 = OS Perf Counter: CPU usage % base
	SELECT *
	FROM (
			SELECT  pc1.LocationID, 
					pc1.ClusterID, 
					pc1.DateKey, 
					pc1.TimeKey,
					pc1.UtcDate,
					pc1.iVal AS iVal1, 
					pc2.iVal AS iVal2,
					(CASE WHEN pc2.iVal > 0 THEN 
						CONVERT(INT,((CAST(pc1.iVal AS FLOAT) / pc2.iVal))*100) 
						ELSE 0 END) AS iValResult,
						(ROW_NUMBER() OVER(PARTITION BY pc1.LocationID,pc1.ClusterID  ORDER BY pc1.DateKey DESC, pc1.TimeKey DESC)) AS RowCt
			FROM	Analysis.PerfCounterStats as pc1 WITH(NOLOCK)
					INNER JOIN Analysis.PerfCounterStats as pc2 WITH(NOLOCK) ON
							pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
							and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
			WHERE (pc1.MetricID = 439 and pc2.MetricID = 440) and (pc1.DateKey =  CONVERT(VARCHAR(10), GETDATE(),112))
			GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey, pc1.UtcDate, pc1.iVal, pc2.iVal) gr
	WHERE gr.RowCt = 1;

For my DASHBOARD, I also want LATEST views for the following:

• Signal Waits
• Disk Latency
• Memory Use
• Disk Use
• Server Up Time

CREATE VIEW [Analysis].[pcv_SignalWaits_Latest]
AS

	-- Get CPU Percentage Use Latest
	-- MetricID 447 =  OS Wait Stats: Signal Waits CPU %
	SELECT *
	FROM (
			SELECT	pc1.LocationID, 
					pc1.ClusterID, 
					pc1.DateKey, 
					pc1.TimeKey, 
					AVG(pc1.iVal) AS SignalWaits,
					(ROW_NUMBER() OVER(PARTITION BY pc1.LocationID,pc1.ClusterID  ORDER BY pc1.DateKey DESC, pc1.TimeKey DESC)) AS RowCt
			FROM	Analysis.PerfCounterStats as pc1
			WHERE	pc1.DateKey =  CONVERT(VARCHAR(10), GETDATE(),112) and pc1.MetricID = 447
			GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey) gr
	WHERE gr.RowCt = 1;

CREATE VIEW [Analysis].[pcv_IoDiskLatencyTotal_Latest]
AS

	-- Get Disk Latency Total (All)
	-- MetricID 351=reads  356=writes  371=stall
	SELECT *
	FROM (
			SELECT  pc1.LocationID, 
					pc1.ClusterID, 
					pc1.DateKey, 
					pc1.TimeKey, 
					pc1.iVal AS NumOfReads, 
					pc2.iVal AS NumOfWrites,
					pc3.iVal AS Stall,
					(CASE WHEN (pc1.iVal > 0 and pc2.iVal > 0)  THEN 
						CONVERT(INT,((CAST(pc3.iVal AS FLOAT) / (pc1.iVal + pc2.iVal)   ))) 
						ELSE 0 END) AS DiscLatencyMs,					
					(ROW_NUMBER() OVER(PARTITION BY pc1.LocationID,pc1.ClusterID  ORDER BY pc1.DateKey DESC, pc1.TimeKey DESC)) AS RowCt
			FROM	Analysis.PerfCounterStats as pc1  WITH(NOLOCK)
					INNER JOIN Analysis.PerfCounterStats as pc2 WITH(NOLOCK)
							ON pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
							and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
					INNER JOIN Analysis.PerfCounterStats as pc3 WITH(NOLOCK)
							ON pc1.LocationID = pc3.LocationID and pc1.ClusterID = pc3.ClusterID
							and pc1.DateKey = pc3.DateKey and pc1.TimeKey = pc3.TimeKey
			WHERE (pc1.MetricID = 351 and pc2.MetricID = 356 and pc3.MetricID = 371) and (pc1.DateKey =  CONVERT(VARCHAR(10), GETDATE(),112))
			GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey, pc1.iVal, pc2.iVal, pc3.iVal) gr
	WHERE gr.RowCt = 1;

CREATE VIEW [Analysis].[pcv_MemoryInstUsage_Latest]
AS

	-- Get CPU Percentage Use by one (1) minute interval
	-- MetricID 322 = SQL Memory: Total Size MBs | 321 = SQL Memory: Available Space MBs
	SELECT *
	FROM (
			SELECT	pc1.LocationID, 
					pc1.ClusterID, 
					pc1.DateKey,  
					pc1.TimeKey, 
					pc1.iVal AS MemoryInstAvailable, 
					pc2.iVal AS MemoryInstTotal,
					(pc2.iVal - pc1.iVal) AS MemoryInstInUse,
					(CASE WHEN pc2.iVal > 0 THEN
						CONVERT(INT,(((CAST(pc2.iVal AS FLOAT)-CAST(pc1.iVal AS FLOAT)) / pc2.iVal)*100)) 
						ELSE 0 END)AS MemoryInstPercentInUse,
					(ROW_NUMBER() OVER(PARTITION BY pc1.LocationID,pc1.ClusterID  ORDER BY pc1.DateKey DESC, pc1.TimeKey DESC)) AS RowCt
			FROM	Analysis.PerfCounterStats as pc1
					INNER JOIN Analysis.PerfCounterStats as pc2 ON 
							pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
							and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
			WHERE	(pc1.DateKey =  CONVERT(VARCHAR(10), GETDATE(),112)) and(pc1.MetricID = 322 and pc2.MetricID = 321) 
			GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey, pc1.iVal, pc2.iVal) gr
	WHERE gr.RowCt = 1;

CREATE VIEW [Analysis].[pcv_DiskUsage_Latest]
AS

	-- Get CPU Percentage Use by one (1) minute interval
	-- MetricID 311 = Server Drive: Total Size MBs   | 312 = Server Drive: Available Space MBs
	SELECT *
	FROM (
			SELECT	pc1.LocationID, 
					pc1.ClusterID, 
					pc1.DateKey,  
					pc1.TimeKey, 
					SUM(pc1.iVal) AS DiskSpaceAvailable, 
					SUM(pc2.iVal) AS DiskSpaceTotal,
					(SUM(pc2.iVal) - SUM(pc1.iVal)) AS DiskSpaceInUse,
					(CASE WHEN SUM(pc2.iVal) > 0 THEN
						CONVERT(INT,(((CAST(SUM(pc2.iVal) AS FLOAT)-CAST(SUM(pc1.iVal) AS FLOAT)) / SUM(pc2.iVal))*100)) 
						ELSE 0 END)AS DiskSpacePercentInUse,
					(ROW_NUMBER() OVER(PARTITION BY pc1.LocationID,pc1.ClusterID  ORDER BY pc1.DateKey DESC, pc1.TimeKey DESC)) AS RowCt
			FROM	Analysis.PerfCounterStats as pc1
					INNER JOIN Analysis.PerfCounterStats as pc2 ON 
							pc1.LocationID = pc2.LocationID and pc1.ClusterID = pc2.ClusterID
							and pc1.DateKey = pc2.DateKey and pc1.TimeKey = pc2.TimeKey
			WHERE	(pc1.DateKey =  CONVERT(VARCHAR(10), GETDATE(),112)) and (pc1.MetricID = 312 and pc2.MetricID = 311)
			GROUP BY pc1.LocationID, pc1.ClusterID, pc1.DateKey, pc1.TimeKey) gr
	WHERE gr.RowCt = 1;

CREATE VIEW [Analysis].[pcv_InstanceLastRestart]
AS
			SELECT	LocationID, ClusterID, MAX(UtcDate) AS UtcDate,
					MAX(CONVERT(datetime,PartName)) AS LastRestartDate,	
					CONCAT(
						((DATEDIFF(second,MAX(CONVERT(datetime,PartName)),GetUtcDate()))  / 3600 / 24)
						,' DAYS '
						,(DATEDIFF(second,MAX(CONVERT(datetime,PartName)),GetUtcDate()))  / 3600  % 24
						,' HRS '
						,(DATEDIFF(second,MAX(CONVERT(datetime,PartName)),GetUtcDate()))  / 60 % 60
						,' MIN '
						) AS RunDuration
			FROM	Analysis.PerfCounterStats WITH(NOLOCK)
			WHERE	(DateKey =  CONVERT(VARCHAR(10), GETDATE(),112)) and (MetricID = 201) 
			GROUP BY LocationID, ClusterID;

CREATE VIEW [Analysis].[pcv_DiskUsage_WatchList]
AS

	-- A list of drive letters to watch with less than 30% free
	-- MetricID 313
	SELECT q.LocationID, q.ClusterID, DateKey, STRING_AGG(PartName,', ') AS DiscWatchList
	FROM
			(select LocationID, ClusterID, DateKey, LEFT(PartName,1) AS PartName
			FROM [Analysis].[PerfCounterStats]  WITH(NOLOCK) 
			WHERE MetricID = 313 and Datekey = CONVERT(VARCHAR(10), GETDATE(),112)
			GROUP BY LocationID, ClusterID, DateKey, LEFT(PartName,1)
			HAVING MIN(dVal) < 30
			) as q
	GROUP BY LocationID, ClusterID, DateKey;

Stored Procedure

Finally, I will create a Stored Procedure that will pull together all the latest stats.

USE [ServerAnalysisDW]
GO
/****** Object:  StoredProcedure [Analysis].[PerfCounter_Rpt_Overview]    Script Date: 9/27/2022 8:22:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		David Speight
-- Create date: 20220901
-- =============================================
ALTER   PROCEDURE [Analysis].[PerfCounter_Rpt_Overview]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
		DECLARE @results as TABLE (	LocationID INT, 
						ClusterID INT, 
						ClusterName VARCHAR(250), 
						ClusterFullName VARCHAR(500),
						ClusterStatus VARCHAR(8),
						CpuPercent INT,
						SignalWaits INT,
						DiscLatencyMs INT,
MemoryInstPercentInUse INT,
DiskSpacePercentInUse INT,
RunDuration VARCHAR(36),
DiscWatchList VARCHAR(50))

	INSERT INTO @results (LocationID, CLusterID, ClusterName, ClusterFullName,
	ClusterStatus, CpuPercent, SignalWaits, DiscLatencyMs, MemoryInstPercentInUse, DiskSpacePercentInUse, RunDuration, DiscWatchList)
	SELECT	L.LocationID, 
			L.ClusterID, 
			L.ClusterName, 
			CONCAT(L.ClusterName,' (',
						(CASE L.LocationID	
WHEN 101111 THEN 'HEADQUARTERS'
WHEN 211222 THEN 'COMPANY A'
WHEN 211333 THEN 'COMPANY B'
WHEN 301444 THEN 'COMPANY C'
ELSE 'Undefined' END)    			,')') 	AS ClusterFullName,
'DOWN',0,0,0,0,0,'',''
	FROM	Analysis.PerfLocation as L WITH(NOLOCK)
	WHERE L.IsActive = 1
	GROUP BY L.LocationID, L.ClusterID, L.ClusterName;

	-- CPU
	UPDATE r
	SET CpuPercent = ISNULL(cpu.iValResult,0)
	FROM @results AS r
	INNER JOIN Analysis.pcv_CpuUsagePercent_Latest AS cpu on cpu.locationID = r.LocationID and cpu.ClusterID = r.ClusterID;

	-- Signnal Waits
	UPDATE r
	SET SignalWaits = ISNULL(sig.SignalWaits,0)
	FROM @results AS r
	INNER JOIN Analysis.pcv_SignalWaits_Latest sig  on sig.locationID = r.LocationID and sig.ClusterID = r.ClusterID;

	-- DiscLatency Ms
	UPDATE r
	SET DiscLatencyMs =  ISNULL(dlat.DiscLatencyMs,0)
	FROM @results AS r
	INNER JOIN Analysis.pcv_IoDiskLatencyTotal_Latest dlat on dlat.locationID = r.LocationID and dlat.ClusterID = r.ClusterID;
	
	--MemoryInstPercentInUse
	UPDATE r
	SET MemoryInstPercentInUse = ISNULL(mem.MemoryInstPercentInUse,0) 
	FROM @results AS r
	INNER JOIN Analysis.pcv_MemoryInstUsage_Latest mem on mem.locationID = r.LocationID and mem.ClusterID = r.ClusterID;

	--DiskSpacePercentInUse
	UPDATE r
	SET DiskSpacePercentInUse = ISNULL(dsk.DiskSpacePercentInUse,0) 
	FROM @results AS r
	INNER JOIN Analysis.pcv_DiskUsage_Latest dsk on dsk.locationID = r.LocationID and dsk.ClusterID = r.ClusterID;

	--RunDuration
	UPDATE r
	SET RunDuration = ISNULL(rst.RunDuration,'') 
	FROM @results AS r
	INNER JOIN Analysis.pcv_InstanceLastRestart rst on rst.locationID = r.LocationID and rst.ClusterID = r.ClusterID;

	--DiscWatchList
	UPDATE r
	SET DiscWatchList = ISNULL(duw.DiscWatchList,'')
	FROM @results AS r
	INNER JOIN Analysis.pcv_DiskUsage_WatchList duw on duw.locationID = r.LocationID and duw.ClusterID = r.ClusterID

	-- Cluster Status
	UPDATE @results
	SET ClusterStatus = 'UP'
	WHERE (LEN(RunDuration) > 1 AND CpuPercent > 0) or ((SignalWaits + DiscLatencyMs + MemoryInstPercentInUse) > 0);

	SELECT		LocationID, 
				ClusterID, 
				ClusterName, 
				ClusterFullName,
				ClusterStatus,
				CpuPercent,
				SignalWaits,
				DiscLatencyMs,
				MemoryInstPercentInUse,
				DiskSpacePercentInUse,
				RunDuration,
				DiscWatchList
	FROM		@results
	ORDER BY	LocationID,	
			ClusterID, 
			ClusterName;


END

No that I have my dataset, I can continue on to Part 9 and build my SSRS Dashboard.