Detailed Information with SQL Server (MSSQL)

The limits for warning and critical levels are mostly taken from Milena Petrovics article, but you will need to work out what is “normal” for some of your metrics.

There are sections in the page for Stored Procedure Plan Cache size and average execution times. These are not populated by the PowerShell script, but by the attached SQL Stored Procedures, that you will need to run from a SQL Server Agent Job.

Many of the panels have information tips explaining what their numbers mean. I made this dashboard to make my own life easier, but hopefully it will help others too.

Milena Petrovics article - https://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory-pagessec-memory-page-faultssec/

NB: Apologies for the formatting below, I can’t work out what editor this is and what the formatting rules are!

Powershell Script

$influxDB = “http://127.0.0.1:8086”; $serverName = “LIVEMSSQL”;

$valuesList = Get-Counter -Counter “\Processor(_Total)% Processor Time”,"\Memory\Pages/sec","\Memory\Pages Input/sec","\Memory\Pages Output/sec","\SQLServer:Buffer Manager\Page reads/sec","\SQLServer:Buffer Manager\Page writes/sec","\Memory\Page Faults/sec","\SQLServer:Memory Manager\Total Server Memory (KB)","\SQLServer:Memory Manager\Database Cache Memory (KB)","\SQLServer:Memory Manager\Free Memory (KB)","\SQLServer:Memory Manager\Stolen Server Memory (KB)","\SQLServer:Memory Manager\Lock Memory (KB)","\SQLServer:Memory Manager\Lock Blocks","\SQLServer:Memory Manager\Target Server Memory (KB)","\SQLServer:Buffer Manager\Buffer cache hit ratio","\SQLServer:Buffer Manager\Page life expectancy","\SQLServer:Buffer Manager\Lazy writes/sec","\SQLServer:Buffer Manager\Free list stalls/sec","\SQLServer:Memory Grants Pending","\SQLServer:Memory Manager\Memory Grants Outstanding","\SQLServer:Memory Manager\Granted Workspace Memory (KB)","\SQLServer:Memory Manager\Maximum Workspace Memory (KB)", “\SQLServer:SQL Statistics\Batch Requests/sec”; $timestamp = string).TotalMilliseconds) + “000000”; $processorActive = [math]::Round($valuesList.CounterSamples[0].CookedValue,3);

$pagesPerSec = [math]::Round($valuesList.CounterSamples[1].CookedValue,3); $pagesInputPerSec = [math]::Round($valuesList.CounterSamples[2].CookedValue,3); $pagesOutputPerSec = [math]::Round($valuesList.CounterSamples[3].CookedValue,3); $pageReadsPerSec = [math]::Round($valuesList.CounterSamples[4].CookedValue,3); $pageWritesPerSec = [math]::Round($valuesList.CounterSamples[5].CookedValue,3); $pageFaultsPerSec = [math]::Round($valuesList.CounterSamples[6].CookedValue,3); $softPageFaultsPerSec = [math]::Round($pageFaultsPerSec - $pagesInputPerSec,3); $hardPageFaultsPerSec = [math]::Round($pageFaultsPerSec - $softPageFaultsPerSec,3);

$memoryTotal = [math]::Round($valuesList.CounterSamples[7].CookedValue,3); $memoryUsed = [math]::Round($valuesList.CounterSamples[8].CookedValue,3); $memoryFree = [math]::Round($valuesList.CounterSamples[9].CookedValue,3); $memoryStolen = [math]::Round($valuesList.CounterSamples[10].CookedValue,3); $memoryLock = [math]::Round($valuesList.CounterSamples[11].CookedValue,3); $memoryLockBlocks = [math]::Round($valuesList.CounterSamples[12].CookedValue,3); $memoryLockBlocksPercent = [math]::Round(($memoryLock * 100) / $memoryFree,3); $targetServerMemory = [math]::Round($valuesList.CounterSamples[13].CookedValue,3); $memoryPressure = [math]::round(($targetServerMemory / $memoryTotal) - 1,3); $bufferCacheHitRatio = [math]::Round($valuesList.CounterSamples[14].CookedValue,3); $bufferPageLifeExpectancy = [math]::Round($valuesList.CounterSamples[15].CookedValue,3); $bufferLazyWritesPerSec = [math]::Round($valuesList.CounterSamples[16].CookedValue,3); $bufferFreeListStallsPerSec = [math]::Round($valuesList.CounterSamples[17].CookedValue,3); $memoryGrantsOutstanding = [math]::Round($valuesList.CounterSamples[18].CookedValue,3); $memoryGrantedWorkspace = [math]::Round($valuesList.CounterSamples[19].CookedValue,3); $memoryMaximumWorkspace = [math]::Round($valuesList.CounterSamples[20].CookedValue,3); $batchRequestsPerSec = [math]::Round($valuesList.CounterSamples[21].CookedValue,3);

$cpudatabinary = “db,server=$serverName,metric=cpu active=$processorActive $timestamp”; Invoke-WebRequest -Uri $influxDB/write?db=statistics -Method POST -Body $cpudatabinary;

$iodatabinary = “db,server=$serverName,metric=io pagespersec=$pagesPerSec,pagesinputpersec=$pagesInputPerSec,pagesoutputpersec=$pagesOutputPerSec,pagereadspersec=$pageReadsPerSec,pagewritespersec=$pageWritesPerSec,pagefaultspersec=$pageFaultsPerSec,hardpagefaultspersec=$hardPageFaultsPerSec,softpagefaultspersec=$softPageFaultsPerSec $timestamp”; Invoke-WebRequest -Uri $influxDB/write?db=statistics -Method POST -Body $iodatabinary;

$memorydatabinary = “db,server=$serverName,metric=memory memorytotal=$memoryTotal,memoryused=$memoryUsed,memoryfree=$memoryFree,memorystolen=$memoryStolen,memorypressure=$memoryPressure,memorylock=$memoryLock,memorylockblocks=$memoryLockBlocks,memorylockspercent=$memoryLockBlocksPercent,buffercachehitratio=$bufferCacheHitRatio,bufferpagelifeexpectancy=$bufferPageLifeExpectancy,bufferlazywritespersec=$bufferLazyWritesPerSec,bufferfreeliststallspersec=$bufferFreeListStallsPerSec,memorygrantsoutstanding=$memoryGrantsOutstanding,memorygrantedworkspace=$memoryGrantedWorkspace,memorymaximumworkspace=$memoryMaximumWorkspace $timestamp”; Invoke-WebRequest -Uri $influxDB/write?db=statistics -Method POST -Body $memorydatabinary;

$serverdatabinary = “db,server=$serverName,metric=server cpuactive=$processorActive,batchrequestspersec=$batchRequestsPerSec $timestamp”; Invoke-WebRequest -Uri $influxDB/write?db=statistics -Method POST -Body $serverdatabinary;

— Stored Procedures — — Log_SPAvgExecTime CREATE PROCEDURE [dbo].[Influx_LogSPAvgExecTime] @SchemaName NVARCHAR(128), @SPName NVARCHAR(128) AS BEGIN DECLARE @OutputTime NVARCHAR(MAX);

DECLARE @ServerName NVARCHAR(50);
SET @ServerName = @@SERVERNAME

EXECUTE [dbo].[GetSPAvgExecTime] @SchemaName, @SPName, @Time = @OutputTime OUTPUT;

IF (@OutputTime IS NOT NULL)
	BEGIN
		EXEC sp_configure 'xp_cmdshell', 1;
		RECONFIGURE;
		DECLARE @SQL NVARCHAR(MAX) = N'EXEC xp_cmdshell ''C:\Tools\curl -i -XPOST "http://127.0.0.1:8086/write?db=statistics" --data-binary "db,server=' + @ServerName + ',schema=' + @SchemaName + ',storedproc=' + @SPName + ' exectime=' + @OutputTime + '"'''
		EXEC(@SQL)
	END

END

— GetSPAvgExecTime — CREATE PROCEDURE [dbo].[GetSPAvgExecTime] @SchemaName NVARCHAR(128), @SPName NVARCHAR(128), @Time NVARCHAR(MAX) OUT AS BEGIN – SET NOCOUNT ON added to prevent extra result sets from – interfering with SELECT statements. SET NOCOUNT ON;

SELECT @Time = CONVERT(NVARCHAR, CONVERT(DECIMAL(30,2), SUM(total_worker_time) * 0.001 / SUM(execution_count)))
FROM MyDatabase.sys.dm_exec_procedure_stats AS stats
	LEFT JOIN MyDatabase.sys.procedures AS sp ON [stats].[object_id] = sp.[object_id]
	LEFT JOIN MyDatabase.sys.schemas AS schemas ON sp.[schema_id] = [schemas].[schema_id]
WHERE [schemas].[name] = @SchemaName
	AND sp.[name] = @SPName
	AND [stats].[database_id] = DB_ID('MyDataBaseNameOrVariableIfRunningOnSameDataBase')
RETURN

END

— Influx_LogSPCache CREATE PROCEDURE [dbo].[Influx_LogSPCache] AS BEGIN DECLARE @Total INT; DECLARE @Single INT; DECLARE @Multi INT;

SELECT @Single = SUM(size_in_bytes)/1048576 FROM sys.dm_exec_cached_plans WHERE usecounts = 1
SELECT @Multi = SUM(size_in_bytes)/1048576 FROM sys.dm_exec_cached_plans WHERE usecounts > 1
SELECT @Total = @Single + @Multi

DECLARE @ServerName NVARCHAR(50);
SET @ServerName = @@SERVERNAME

DECLARE @SQL NVARCHAR(MAX) = N'EXEC LogToInflux ''db'', ''server=' + @ServerName + ',metric=spcache'', ''total=' + CONVERT(VARCHAR, @Total) + ',singleuse=' + CONVERT(VARCHAR, @Single) + ',singleuseneg=-' + CONVERT(VARCHAR, @Single) + ',multiuse=' + CONVERT(VARCHAR, @Multi) + ''''
EXEC (@SQL)

END