SQL PERFORMANCE MONITOR – PART 5: Capturing Metrics

This is PART 5 in my series on Creating a SQL Performance Monitor that works across multiple AWS accounts and companies.

The first thing I want to do, is to grab performance metrics on a regular basis and store them in the PerfCounterLog table.

Remember that table: PerfCounterMetric? It holds all the definitions in the MetricTsql column and at what time intervals it should run in the MetricIntravalMinutes column.

I use the following stored procedure to grab said data. My procedure below will replace certain placeholders with the current date, time, and server name. The Sproc will pull a Tsql statement for each performance counter to be recorded at set interval. It will loop through those and then store the results in the PerfCounterLog table.

USE [ServerAnalysis]
GO
/****** Object:  StoredProcedure [Analysis].[PerfCounterMetricIntervalInsertJob]    Script Date: 8/23/2022 10:48:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	David Speight
-- Create date: 20220719
-- =============================================
CREATE OR ALTER PROCEDURE [Analysis].[PerfCounterMetricIntervalInsertJob]
@Interval int
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @iDate int;
	DECLARE @vTime char(6);
	DECLARE @Server varchar(150);
	DECLARE @tSQL varchar(max);
	DECLARE @iCount int=0;
	DECLARE @curMetricID int=0;
	DECLARE @tList TABLE (ID INT NOT NULL IDENTITY(1,1), MetricID INT);

	INSERT INTO @tList
	SELECT MetricID FROM [Analysis].[PerfCounterMetric] WHERE MetricIntervalMinutes =@Interval and MetricTsql is not null;
	SELECT @iCount = MAX(ID) FROM @tList;
	SELECT @Server = @@SERVERNAME, @iDate = CONVERT(VARCHAR(10), GETUTCDATE(),112), @vTime = REPLACE(CONVERT(VARCHAR(10), GETUTCDATE(),108),':','');

	WHILE @iCount > 0
	BEGIN
		SELECT @curMetricID = MetricID FROM @tList WHERE ID = @iCount;

		select @tSQL = REPLACE(REPLACE(REPLACE(MetricTsql,'[REPLACEiDate]',@iDate),'[REPLACEvTime]',@vTime),'[REPLACEserver]',@Server)
		FROM [Analysis].[PerfCounterMetric] WHERE MetricID = @curMetricID; 

		BEGIN TRY
			EXEC (@tSQL);
		END TRY
		BEGIN CATCH
			PRINT CONCAT('ERROR MetricID: ',@curMetricID,' at ',GetUTCDATE())
		END CATCH;

		SELECT @curMetricID=0, @iCount=@iCount-1;
	END;

END

NEXT, I want to set up SQL Agent Jobs to gather the data at those intervals. I will set up the following jobs (you may need more):

Agent Job	Code	Schedule
Performance Counter Get – Daily	EXEC Analysis.PerfCounterMetricIntervalInsertJob 1440;	Daily
Performance Counter Get – 6HR	EXEC Analysis.PerfCounterMetricIntervalInsertJob 360;	Every 6 hours
Performance Counter Get – 3HR	EXEC Analysis.PerfCounterMetricIntervalInsertJob 180;	Every 3 hours
Performance Counter Get – MIN	EXEC Analysis.PerfCounterMetricIntervalInsertJob 1;	Every 60 seconds

-- This code snippet shows all the SQL statements that will be executed for X interval.
select MetricID, MetricTsql
from Analysis.PerfCounterMetric 
where MetricIntervalMinutes = 1440 a
nd MetricTsql is not null;

The frequency in which you pull data into the local server tables has little effect on the final data warehouse reporting. You can pull data here every sixty (60) seconds and send all of it over there (to DW) every ten or fifteen minutes. Both parts are flexible.

Here is a breakdown of SAMPLE TSQL code stored in Metric ID 121 that runs every 180 minutes.

INSERT INTO ServerAnalysis.Analysis.PerfCounterLog (PerfLocationID,MetricID,DateKey,TimeKey,iVal,dVal,PartName,UtcDate) 
SELECT	s.PerfLocationID, 
		121, 
		[REPLACEiDate],			-- Value replaced with DATE by calling SPROC at runtime.
		'[REPLACEvTime]',		-- Value replaced with TIME by calling SPROC at runtime.
		(ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0)), -- This column stores BIGINT values.
		NULL,												-- This column stores DECIMAL(10,4) values.
		d.NAME,												-- This column stores VARCHAR(25) values.
		getutcdate() 
FROM ServerAnalysis.Analysis.Server AS s, 
sys.master_files AS mf INNER JOIN sys.databases AS d ON d.database_id = mf.database_id 
WHERE s.ServerNm = '[REPLACEserver]'   -- Value replaces with current @@SERVERNAME by calling SPROC at runtime.
GROUP BY s.PerfLocationID, d.NAME;

In the next section we will SSIS to periodically output this data to a CSV file that will travel to the main headquarters S3 bucket and loaded into a data warehouse table there.

All of the sample code and scripts to create the tables, views and procs will be available on my GITHUB https://github.com/LoveTheSql when the series is completed.