System Proc sp_SysMon to monitor sql server health

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gokhan Varol » System Proc sp_SysMon to monitor sql server...

System Proc sp_SysMon to monitor sql server health

Rate Topic

Display Mode

Topic Options

Author

Message

gvarol

Posted Tuesday, January 31, 2012 1:50 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:36 PM
Points: 20, Visits: 263

Comments posted to this topic are about the item System Proc sp_SysMon to monitor sql server health

Post #1244116

Sean O'Donovan

Posted Tuesday, January 31, 2012 2:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:05 AM
Points: 2, Visits: 303

Your sp refers to a "Util.dbo.GetCPUUsage". Could you please post this script ?

Post #1244128

Sean Smith-776614

Posted Tuesday, January 31, 2012 6:34 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 90, Visits: 369

I am having the same issue...

Post #1244316

gvarol

Posted Tuesday, January 31, 2012 6:40 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:36 PM
Points: 20, Visits: 263

Sorry, my original version uses many functions in my Util database, I thought replaced them all with the source code version. GetCPUUsage is an inline table valued function and needs to return single row back to the select statement, you can replace it with the select section of the query below by replacing @TOP with 1.

USE Util
GO
CREATE FUNCTION dbo.GetCPUUsage (@TOP INT = 1)
RETURNS TABLE
RETURN
SELECT TOP (@TOP)
SQLProcessUtilization AS SQLServerCPU,
100 - SystemIdle AS ServerCPU,
DATEADD(ms, -1 * (ts_now - [timestamp]), GETDATE()) AS [EventTime]
FROM (SELECT TOP 1 cpu_ticks/ (cpu_ticks/ ms_ticks) AS ts_now FROM sys.dm_os_sys_info (NOLOCK)) a
CROSS JOIN sys.dm_os_ring_buffers (NOLOCK)
CROSS APPLY (SELECT CAST( record AS XML) AS rXML) rx
CROSS APPLY (SELECT XmlInfo.Record.value('(./@id)[1]', 'int') AS record_id,
XmlInfo.Record.value('(./SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
XmlInfo.Record.value('(./SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
FROM rXML.nodes('./Record') AS XmlInfo (Record)) nd
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
ORDER BY [timestamp] DESC

Post #1244324

gvarol

Posted Tuesday, January 31, 2012 6:45 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:36 PM
Points: 20, Visits: 263

Actually remove code "SELECT
@SQLServerCPU = SQLServerCPU,
@ServerCPU = ServerCPU
FROM Util.dbo.GetCPUUsage(1)", it's redundant. See below.

USE [master]
GO
IF OBJECT_ID('dbo.sp_SysMon') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_SysMon AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_SysMon
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @BatchRequestsPerSecond BIGINT,
@CompilationsPerSecond BIGINT,
@ReCompilationsPerSecond BIGINT,
@LockWaitsPerSecond BIGINT,
@PageSplitsPerSecond BIGINT,
@CheckpointPagesPerSecond BIGINT,
@stat_date DATETIME,
@MachineName VARCHAR(128) = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),
@SQLServerCPU TINYINT,
@ServerCPU TINYINT
DECLARE @ServicePath NVARCHAR(156) = N'SYSTEM\CurrentControlSet\Services\' + CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'MSSQLSERVER'
ELSE 'MSSQL$' + @@SERVICENAME
END,
@MSSQLServiceAccountName VARCHAR(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@ServicePath,
N'ObjectName',
@MSSQLServiceAccountName OUTPUT,
N'no_output'
SET ANSI_WARNINGS OFF
SELECT @stat_date = GETDATE(),
@BatchRequestsPerSecond = MAX(CASE WHEN counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
@CompilationsPerSecond = MAX(CASE WHEN counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
@ReCompilationsPerSecond = MAX(CASE WHEN counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
@LockWaitsPerSecond = MAX(CASE WHEN counter_name = 'Lock Waits/sec'
AND object_name LIKE '%Locks%'
AND instance_name = '_Total' THEN cntr_value
END),
@PageSplitsPerSecond = MAX(CASE WHEN counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%' THEN cntr_value
END),
@CheckpointPagesPerSecond = MAX(CASE WHEN counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%' THEN cntr_value
END)
FROM sys.dm_os_performance_counters AS a (NOLOCK)
WHERE (counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'Lock Waits/sec'
AND object_name LIKE '%Locks%'
AND instance_name = '_Total')
OR (counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%')
OR (counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%')
WAITFOR DELAY '00:00:01'
SET ANSI_WARNINGS ON

SELECT TOP (1)
@SQLServerCPU = SQLProcessUtilization,
@ServerCPU = 100 - SystemIdle
FROM (SELECT TOP 1 cpu_ticks / cpu_ticks / ms_ticks AS ts_now FROM sys.dm_os_sys_info (NOLOCK)) AS a
CROSS JOIN sys.dm_os_ring_buffers(NOLOCK)
CROSS APPLY (SELECT CAST( record AS XML)AS rXML) AS rx
CROSS APPLY (SELECT XmlInfo.Record.value('(./@id)[1]', 'int') AS record_id,
XmlInfo.Record.value('(./SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
XmlInfo.Record.value('(./SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
FROM rXML.nodes('./Record') AS XmlInfo (Record)) AS nd
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
ORDER BY timestamp DESC
SET ANSI_WARNINGS OFF ;
WITH dd
AS (SELECT [Batch Requests/sec] = MAX(CASE WHEN counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
[SQL Compilations/sec] = MAX(CASE WHEN counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
[SQL Re-Compilations/sec] = MAX(CASE WHEN counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%' THEN cntr_value
END),
[Lock Waits/sec] = MAX(CASE WHEN counter_name = 'Lock Waits/sec'
AND object_name LIKE '%Locks%'
AND instance_name = '_Total' THEN cntr_value
END),
[Page Splits/sec] = MAX(CASE WHEN counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%' THEN cntr_value
END),
[Checkpoint Pages/sec] = MAX(CASE WHEN counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%' THEN cntr_value
END),
PageLifeExpectency = MAX(CASE WHEN counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%' THEN cntr_value
END),
[Buffer cache hit ratio] = MAX(CASE WHEN counter_name = 'Buffer cache hit ratio'
AND object_name LIKE '%Buffer Manager%' THEN cntr_value
END),
[Buffer cache hit ratio base] = MAX(CASE WHEN counter_name = 'Buffer cache hit ratio base'
AND object_name LIKE '%Buffer Manager%' THEN cntr_value
END),
[Target Server Memory (MB)] = MAX(CASE WHEN counter_name = 'Target Server Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Total Server Memory (MB)] = MAX(CASE WHEN counter_name = 'Total Server Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Connection Memory (MB)] = MAX(CASE WHEN counter_name = 'Connection Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Granted Workspace Memory (MB)] = MAX(CASE WHEN counter_name = 'Granted Workspace Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Lock Memory (MB)] = MAX(CASE WHEN counter_name = 'Lock Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Maximum Workspace Memory (MB)] = MAX(CASE WHEN counter_name = 'Maximum Workspace Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[Memory Grants Outstanding] = MAX(CASE WHEN counter_name = 'Memory Grants Outstanding'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END),
[Memory Grants Pending] = MAX(CASE WHEN counter_name = 'Memory Grants Pending'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END),
[Optimizer Memory (MB)] = MAX(CASE WHEN counter_name = 'Optimizer Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[SQL Cache Memory (MB)] = MAX(CASE WHEN counter_name = 'SQL Cache Memory (KB)'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END) / 1024.0,
[User Connections] = MAX(CASE WHEN counter_name = 'User Connections'
AND object_name LIKE '%General Statistics%' THEN cntr_value
END),
[Processes blocked] = MAX(CASE WHEN counter_name = 'Processes blocked'
AND object_name LIKE '%General Statistics%' THEN cntr_value
END),
[Lock Blocks Allocated] = MAX(CASE WHEN counter_name = 'Lock Blocks Allocated'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END),
[Lock Owner Blocks Allocated] = MAX(CASE WHEN counter_name = 'Lock Owner Blocks Allocated'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END),
[Lock Blocks] = MAX(CASE WHEN counter_name = 'Lock Blocks'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END),
[Lock Owner Blocks] = MAX(CASE WHEN counter_name = 'Lock Owner Blocks'
AND object_name LIKE '%:Memory Manager%' THEN cntr_value
END)
FROM sys.dm_os_performance_counters AS a (NOLOCK)
WHERE (counter_name = 'Batch Requests/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'SQL Compilations/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'SQL Re-Compilations/sec'
AND object_name LIKE '%SQL Statistics%')
OR (counter_name = 'Lock Waits/sec'
AND object_name LIKE '%Locks%'
AND instance_name = '_Total')
OR (counter_name = 'Page Splits/sec'
AND object_name LIKE '%Access Methods%')
OR (counter_name = 'Checkpoint Pages/sec'
AND object_name LIKE '%Buffer Manager%')
OR (counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%')
OR (counter_name = 'Buffer cache hit ratio'
AND object_name LIKE '%Buffer Manager%')
OR (counter_name = 'Buffer cache hit ratio base'
AND object_name LIKE '%Buffer Manager%')
OR (counter_name = 'Target Server Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Total Server Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Connection Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Granted Workspace Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Lock Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Maximum Workspace Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Memory Grants Outstanding'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Memory Grants Pending'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Optimizer Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'SQL Cache Memory (KB)'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'User Connections'
AND object_name LIKE '%General Statistics%')
OR (counter_name = 'Processes blocked'
AND object_name LIKE '%General Statistics%')
OR (counter_name = 'Lock Blocks Allocated'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Lock Owner Blocks Allocated'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Lock Blocks'
AND object_name LIKE '%:Memory Manager%')
OR (counter_name = 'Lock Owner Blocks'
AND object_name LIKE '%:Memory Manager%')),
rr
AS (SELECT GETDATE() AS StatDate,
@@SERVERNAME AS ServerName,
@MachineName AS MachineName,
@SQLServerCPU AS SQLServerCPU,
@ServerCPU AS ServerCPU,
[Buffer cache hit ratio] * 1.0 / [Buffer cache hit ratio base] * 100.0 AS BufferCacheHitRatio,
PageLifeExpectency,
CAST([Total Server Memory (MB)] / 4096 * 300 AS INT) AS PLEThreshold,
m.available_physical_memory_kb / 1024 AS AvailablePhysicalMemoryMB,
m.total_physical_memory_kb / 1024 AS TotalPhysicalMemoryMB,
[Total Server Memory (MB)] AS TotalServerMemoryMB,
[Target Server Memory (MB)] AS TargetServerMemoryMB,
([Batch Requests/sec] - @BatchRequestsPerSecond) / SecondsDiff AS BatchRequestsPerSecond,
([SQL Compilations/sec] - @CompilationsPerSecond) / SecondsDiff AS CompilationsPerSecond,
([SQL Re-Compilations/sec] - @ReCompilationsPerSecond) / SecondsDiff AS ReCompilationsPerSecond,
([Lock Waits/sec] - @LockWaitsPerSecond) / SecondsDiff AS LockWaitsPerSecond,
([Page Splits/sec] - @PageSplitsPerSecond) / SecondsDiff AS PageSplitsPerSecond,
([Checkpoint Pages/sec] - @CheckpointPagesPerSecond) / SecondsDiff AS CheckpointPagesPerSecond,
[Connection Memory (MB)] AS ConnectionMemoryMB,
[Granted Workspace Memory (MB)] AS GrantedWorkspaceMemoryMB,
[Lock Memory (MB)] AS LockMemoryMB,
[Maximum Workspace Memory (MB)] AS MaximumWorkspaceMemoryMB,
[Memory Grants Outstanding] AS MemoryGrantsOutstanding,
[Memory Grants Pending] AS MemoryGrantsPending,
[Optimizer Memory (MB)] AS OptimizerMemoryMB,
[SQL Cache Memory (MB)] AS SQLCacheMemoryMB,
[Processes blocked] AS Processesblocked,
[Lock Blocks Allocated] AS LockBlocksAllocated,
[Lock Owner Blocks Allocated] AS LockOwnerBlocksAllocated,
[Lock Blocks] AS LockBlocks,
[Lock Owner Blocks] AS LockOwnerBlocks,
[User Connections] AS UserConnections,
pr.Sessions,
pr.ServiceAccountSessions,
pr.UserAccountSessions,
pr.BlockingSessions,
pr.BlockedSessions,
pr.DormantSessions,
pr.RunningSessions,
pr.BackgroundSessions,
pr.RollbackSessions,
pr.PendingSessions,
pr.RunnableSessions,
pr.SpinloopSessions,
pr.SuspendedSessions,
pr.ServerStartTime
FROM dd
CROSS JOIN (SELECT DATEDIFF (millisecond, @stat_date, GETDATE ()) / 1000.0 AS SecondsDiff) AS sd
CROSS JOIN sys.dm_os_sys_memory AS m (NOLOCK)
CROSS JOIN (SELECT COUNT(DISTINCT spid) AS Sessions,
COUNT(DISTINCT CASE WHEN loginame = @MSSQLServiceAccountName THEN spid
END) AS ServiceAccountSessions,
COUNT(DISTINCT CASE WHEN loginame NOT IN ('', 'sa', @MSSQLServiceAccountName) THEN spid
END) AS UserAccountSessions,
COUNT(DISTINCT NULLIF(blocked, 0)) AS BlockingSessions,
COUNT(DISTINCT CASE WHEN blocked > 0 THEN spid
END) AS BlockedSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'dormant' THEN SPID
END) AS DormantSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'running' THEN SPID
END) AS RunningSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'background' THEN SPID
END) AS BackgroundSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'rollback' THEN SPID
END) AS RollbackSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'pending' THEN SPID
END) AS PendingSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'runnable' THEN SPID
END) AS RunnableSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'spinloop' THEN SPID
END) AS SpinloopSessions,
COUNT(DISTINCT CASE WHEN STATUS = 'suspended' THEN SPID
END) AS SuspendedSessions,
MIN(login_time) AS ServerStartTime
FROM master.sys.sysprocesses(NOLOCK)) AS pr)
SELECT StatDate,
ServerName AS SQLServerName,
MachineName,
SQLServerCPU,
ServerCPU,
PageLifeExpectency,
PLEThreshold,
CAST(CASE WHEN PageLifeExpectency > PLEThreshold * 100 THEN NULL
WHEN PLEThreshold <> 0 THEN PageLifeExpectency * 100.0 / PLEThreshold
ELSE 0
END AS NUMERIC(6, 2)) AS [PLE%],
BufferCacheHitRatio,
AvailablePhysicalMemoryMB,
TotalPhysicalMemoryMB,
TotalServerMemoryMB,
TargetServerMemoryMB,
ConnectionMemoryMB,
GrantedWorkspaceMemoryMB,
LockMemoryMB,
MaximumWorkspaceMemoryMB,
OptimizerMemoryMB,
SQLCacheMemoryMB,
MemoryGrantsOutstanding,
MemoryGrantsPending,
BatchRequestsPerSecond,
CompilationsPerSecond,
ReCompilationsPerSecond,
LockWaitsPerSecond,
PageSplitsPerSecond,
CheckpointPagesPerSecond,
LockBlocks,
LockBlocksAllocated,
LockOwnerBlocks,
LockOwnerBlocksAllocated,
Processesblocked,
BlockingSessions,
BlockedSessions,
UserConnections,
Sessions,
ServiceAccountSessions,
UserAccountSessions,
DormantSessions,
RunningSessions,
BackgroundSessions,
RollbackSessions,
PendingSessions,
RunnableSessions,
SpinloopSessions,
SuspendedSessions,
ServerStartTime,
(SELECT ISNULL(CAST(NULLIF(DATEDIFF(HOUR, ServerStartTime, StatDate) / 24, 0) AS VARCHAR) + ' days ', '') + RIGHT('0'
+ CAST(DATEDIFF(MINUTE,
StartDateTime,
StatDate) / 60
% 24 AS VARCHAR), 2) + ':'
+ RIGHT('0' + CAST(DATEDIFF(MINUTE, StartDateTime, StatDate) % 60 AS VARCHAR), 2) + ':' + RIGHT('0'
+ CAST(DATEDIFF(second, StartDateTime,
StatDate) % 60 AS VARCHAR),
2)
FROM (SELECT DATEDIFF (DAY, ServerStartTime, StatDate)AS DayDiff) AS dd
CROSS APPLY (SELECT CASE WHEN DayDiff > 1 THEN DATEADD(DAY, DayDiff - 1, ServerStartTime)
ELSE ServerStartTime
END AS StartDateTime) AS b) AS ServerUpTime
FROM rr
GO
EXEC sys.sp_MS_marksystemobject
sp_SysMon
GO

Post #1244333

rw_ebox

Posted Tuesday, January 31, 2012 10:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:20 AM
Points: 45, Visits: 178

Just ran your modified script. It works for me and the output is neat too.

Thank you for your work,

Post #1244586

rw_ebox

Posted Tuesday, January 31, 2012 10:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:20 AM
Points: 45, Visits: 178

By the way, your script makes me recall the "sp_sysmon" procedure in Sybase.

BigGrin

Post #1244592

gvarol

Posted Tuesday, January 31, 2012 10:41 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:36 PM
Points: 20, Visits: 263

I don't know sybase

Post #1244595

skron

Posted Tuesday, January 31, 2012 11:44 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 2:42 PM
Points: 227, Visits: 56

Nice script. Unfortunately, it does not work on 2005 and we still have some 2005 servers.

Post #1244652

gvarol

Posted Tuesday, January 31, 2012 12:13 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:36 PM
Points: 20, Visits: 263

There may be are pieces useful that works also in SQL 2005, I put bunch of counters from all over the place together to see all of them in one place.

Post #1244680

« Prev Topic | Next Topic »

Permissions