InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 23:11:58 GMT20http://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxThere 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.Tue, 31 Jan 2012 12:13:29 GMTgvarolhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxNice script. Unfortunately, it does not work on 2005 and we still have some 2005 servers.Tue, 31 Jan 2012 11:44:22 GMTskronhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxI don't know sybaseTue, 31 Jan 2012 10:41:44 GMTgvarolhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxBy the way, your script makes me recall the "sp_sysmon" procedure in Sybase.:-DTue, 31 Jan 2012 10:31:39 GMTrw_eboxhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxJust ran your modified script. It works for me and the output is neat too.Thank you for your work,Tue, 31 Jan 2012 10:29:18 GMTrw_eboxhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxActually remove code "SELECT     @SQLServerCPU = SQLServerCPU,     @ServerCPU = ServerCPUFROM Util.dbo.GetCPUUsage(1)", it's redundant. See below.[font="Courier New"]USE [master]GOIF OBJECT_ID('dbo.sp_SysMon') IS NULL EXEC('CREATE PROCEDURE dbo.sp_SysMon AS SELECT 1 AS ID')GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE dbo.sp_SysMonAS SET NOCOUNT ONSET ANSI_WARNINGS OFFSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDDECLARE @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 TINYINTDECLARE @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 OFFSELECT @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 ONSELECT TOP (1) @SQLServerCPU = SQLProcessUtilization, @ServerCPU = 100 - SystemIdleFROM (SELECT TOP 1 cpu_ticks / cpu_ticks / ms_ticks AS ts_now FROM sys.dm_os_sys_info (NOLOCK)) AS aCROSS JOIN sys.dm_os_ring_buffers(NOLOCK)CROSS APPLY (SELECT CAST( record AS XML)AS rXML) AS rxCROSS 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 ndWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%&lt;SystemHealth&gt;%'ORDER BY timestamp DESCSET 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 &gt; 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 &gt; PLEThreshold * 100 THEN NULL WHEN PLEThreshold &lt;&gt; 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 &gt; 1 THEN DATEADD(DAY, DayDiff - 1, ServerStartTime) ELSE ServerStartTime END AS StartDateTime) AS b) AS ServerUpTime FROM rrGOEXEC sys.sp_MS_marksystemobject sp_SysMonGO[/font]Tue, 31 Jan 2012 06:45:53 GMTgvarolhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxSorry, 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 UtilGOCREATE FUNCTION dbo.GetCPUUsage (@TOP INT = 1)RETURNS TABLERETURN 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 '%&lt;SystemHealth&gt;%' ORDER BY [timestamp] DESCTue, 31 Jan 2012 06:40:44 GMTgvarolhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxI am having the same issue...Tue, 31 Jan 2012 06:34:27 GMTSean Smith-776614http://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxYour sp refers to a "Util.dbo.GetCPUUsage". Could you please post this script ?Tue, 31 Jan 2012 02:09:34 GMTSean O'Donovanhttp://www.sqlservercentral.com/Forums/Topic1244116-2887-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/System+health/87562/">System Proc sp_SysMon to monitor sql server health</A>[/B]Tue, 31 Jan 2012 01:50:46 GMTgvarol