USE master
GO
IF OBJECT_ID('sp_SysMon') IS NULL EXEC ('CREATE PROCEDURE sp_SysMon AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_SysMon
@ReturnInfo BIT = 0,
@InFo VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--#region How to build it
/*
DECLARE @SQL VARCHAR(MAX) ;
WITH c AS (SELECT counter_name,
OBJECT_NAME,
instance_name,
REPLACE(VariableName, '(KB)', '(MB)') AS VariableName,
CASE WHEN VariableName LIKE '%(KB)%' THEN 1024.0
END AS DivideBy
FROM ( VALUES ( 'Batch Requests/sec', '%SQL Statistics%', NULL, '@BatchRequestsPerSecond'),
( 'SQL Compilations/sec', '%SQL Statistics%', NULL, '@CompilationsPerSecond'),
( 'SQL Re-Compilations/sec', '%SQL Statistics%', NULL, '@ReCompilationsPerSecond'),
( 'Lock Waits/sec', '%Locks%', '_Total', '@LockWaitsPerSecond'), ( 'Page Splits/sec', '%Access Methods%', NULL, '@PageSplitsPerSecond'),
( 'Checkpoint Pages/sec', '%Buffer Manager%', NULL, '@CheckpointPagesPerSecond') ) AS dt (counter_name, OBJECT_NAME, instance_name, VariableName)),
d AS (SELECT counter_name,
OBJECT_NAME,
instance_name,
REPLACE(VariableName, '(KB)', '(MB)') AS VariableName,
CASE WHEN VariableName LIKE '%(KB)%' THEN 1024.0
END AS DivideBy
FROM ( VALUES ( 'Page life expectancy', '%Buffer Manager%', NULL, 'PageLifeExpectency'),
( 'Buffer cache hit ratio', '%Buffer Manager%', NULL, '[Buffer cache hit ratio]'),
( 'Buffer cache hit ratio base', '%Buffer Manager%', NULL, '[Buffer cache hit ratio base]'),
( 'Target Server Memory (KB)', '%:Memory Manager%', NULL, '[Target Server Memory (KB)]'),
( 'Total Server Memory (KB)', '%:Memory Manager%', NULL, '[Total Server Memory (KB)]'),
( 'Connection Memory (KB)', '%:Memory Manager%', NULL, '[Connection Memory (KB)]'),
( 'Granted Workspace Memory (KB)', '%:Memory Manager%', NULL, '[Granted Workspace Memory (KB)]'),
( 'Lock Memory (KB)', '%:Memory Manager%', NULL, '[Lock Memory (KB)]'),
( 'Maximum Workspace Memory (KB)', '%:Memory Manager%', NULL, '[Maximum Workspace Memory (KB)]'),
( 'Memory Grants Outstanding', '%:Memory Manager%', NULL, '[Memory Grants Outstanding]'),
( 'Memory Grants Pending', '%:Memory Manager%', NULL, '[Memory Grants Pending]'),
( 'Optimizer Memory (KB)', '%:Memory Manager%', NULL, '[Optimizer Memory (KB)]'),
( 'SQL Cache Memory (KB)', '%:Memory Manager%', NULL, '[SQL Cache Memory (KB)]'),
( 'User Connections', '%General Statistics%', NULL, '[User Connections]'),
( 'Processes blocked', '%General Statistics%', NULL, '[Processes blocked]'),
( 'Lock Blocks Allocated', '%:Memory Manager%', NULL, '[Lock Blocks Allocated]'),
( 'Lock Owner Blocks Allocated', '%:Memory Manager%', NULL, '[Lock Owner Blocks Allocated]'),
( 'Lock Blocks', '%:Memory Manager%', NULL, '[Lock Blocks]'), ( 'Lock Owner Blocks', '%:Memory Manager%', NULL, '[Lock Owner Blocks]') )
AS dt (counter_name, OBJECT_NAME, instance_name, VariableName))
SELECT @SQL = (SELECT 'DECLARE @BatchRequestsPerSecond BIGINT,
@CompilationsPerSecond BIGINT,
@ReCompilationsPerSecond BIGINT,
@LockWaitsPerSecond BIGINT,
@PageSplitsPerSecond BIGINT,
@CheckpointPagesPerSecond BIGINT,
@stat_date DATETIME2(2) = GETDATE()
SELECT
' + Util.dbo.StringConcat(VariableName + ' = MAX(CASE WHEN counter_name = ''' + REPLACE(counter_name, '''', '''''') + ''' AND object_name LIKE '''
+ REPLACE(OBJECT_NAME, '''', '''''') + '''' + CASE WHEN instance_name IS NOT NULL
THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ' THEN cntr_value END)' + ISNULL(' / ' + CAST(DivideBy AS VARCHAR), ''), ',
') + '
FROM sys.dm_os_performance_counters a (NOLOCK)
WHERE ' + Util.dbo.StringConcat('( counter_name = ''' + REPLACE(counter_name, '''', '''''') + ''' AND object_name LIKE ''' + REPLACE(OBJECT_NAME, '''', '''''')
+ '''' + CASE WHEN instance_name IS NOT NULL THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ')', '
OR
')
FROM c) + '
DECLARE @CPU_BUSY INT = @@CPU_BUSY,
@IDLE INT = @@IDLE,
@SQLCPU NUMERIC(6,2)
WAITFOR DELAY ''00:00:01''
SELECT @SQLCPU = (@@CPU_BUSY - @CPU_BUSY) / ((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) * 100
;WITH d as ('
+ (SELECT 'SELECT
' + Util.dbo.StringConcat('[' + counter_name + '] = MAX(CASE WHEN counter_name = ''' + REPLACE(counter_name, '''', '''''') + ''' AND object_name LIKE '''
+ REPLACE(OBJECT_NAME, '''', '''''') + '''' + CASE WHEN instance_name IS NOT NULL
THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ' THEN cntr_value END)' + ISNULL(' / ' + CAST(DivideBy AS VARCHAR), ''), ',
')
FROM c) + ',
'
+ (SELECT Util.dbo.StringConcat(VariableName + ' = MAX(CASE WHEN counter_name = ''' + REPLACE(counter_name, '''', '''''')
+ ''' AND object_name LIKE ''' + REPLACE(OBJECT_NAME, '''', '''''') + ''''
+ CASE WHEN instance_name IS NOT NULL THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ' THEN cntr_value END)' + ISNULL(' / ' + CAST(DivideBy AS VARCHAR), ''), ',
')
FROM d) + '
FROM sys.dm_os_performance_counters a (NOLOCK)
WHERE '
+ (SELECT Util.dbo.StringConcat('( counter_name = ''' + REPLACE(counter_name, '''', '''''') + ''' AND object_name LIKE ''' + REPLACE(OBJECT_NAME,
'''', '''''')
+ '''' + CASE WHEN instance_name IS NOT NULL
THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ')', '
OR
')
FROM c)
+ (SELECT '
OR ' + Util.dbo.StringConcat('( counter_name = ''' + REPLACE(counter_name, '''', '''''') + ''' AND object_name LIKE ''' + REPLACE(OBJECT_NAME, '''', '''''')
+ '''' + CASE WHEN instance_name IS NOT NULL THEN ' AND instance_name = ''' + REPLACE(instance_name, '''', '''''') + ''''
ELSE ''
END + ')', '
OR
')
FROM d) + ')
SELECT GETDATE() AS StatDate,
@@SERVERNAME as ServerName,
@SQLCPU AS SQLServerCPU,
([Buffer cache hit ratio] * 1.0 / [Buffer cache hit ratio base]) * 100.0 [BufferCacheHitRatio],
PageLifeExpectency,
m.available_physical_memory_kb / 1024 as [Available Physical Memory (MB)],
m.total_physical_memory_kb / 1024 as [Total Physical Memory (MB)],
[Total Server Memory (MB)],
[Target Server Memory (MB)],
([Batch Requests/sec] - @BatchRequestsPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [BatchRequestsPerSecond],
([SQL Compilations/sec] - @CompilationsPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [CompilationsPerSecond],
([SQL Re-Compilations/sec] - @ReCompilationsPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [ReCompilationsPerSecond],
([Lock Waits/sec] - @LockWaitsPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [LockWaitsPerSecond],
([Page Splits/sec] - @PageSplitsPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [PageSplitsPerSecond],
([Checkpoint Pages/sec] - @CheckpointPagesPerSecond) / (CASE WHEN DATEDIFF(ss, @stat_date, GETDATE()) = 0 THEN 1
ELSE DATEDIFF(ss, @stat_date, GETDATE())
END) AS [CheckpointPagesPerSecond],
'
+ (SELECT Util.dbo.StringConcat(VariableName, ',
')
FROM d
WHERE counter_name NOT IN ('Buffer cache hit ratio', 'Buffer cache hit ratio base', 'Page life expectancy', 'Target Server Memory (KB)',
'Total Server Memory (KB)')) + '
FROM d CROSS JOIN sys.dm_os_sys_memory m (NOLOCK)
'
EXEC Util.dbo.PrintLargeText
@SQL
*/
--#endregion
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
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 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 @SQLServerCPU = SQLServerCPU,
@ServerCPU = ServerCPU
FROM Util.dbo.GetCPUUsage (1)
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 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,
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) sd
CROSS JOIN sys.dm_os_sys_memory m (NOLOCK)
CROSS JOIN (SELECT COUNT(DISTINCT SPID) AS Sessions,
COUNT(DISTINCT CASE WHEN loginame = SQLServerServiceAccount THEN SPID
END) AS ServiceAccountSessions,
COUNT(DISTINCT CASE WHEN loginame NOT IN ('', 'sa', SQLServerServiceAccount) 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)
OUTER APPLY (SELECT SQLServerServiceAccount FROM Util.dbo.Servers WHERE SQLSERVERNAME = @@SERVERNAME) b) pr)
SELECT StatDate,
SERVERNAME AS SQLServerName,
MachineName,
SQLServerCPU,
ServerCPU,
PageLifeExpectency,
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,
Util.dbo.GetDateTimeDifference(ServerStartTime, StatDate) AS ServerUpTime
INTO #sp_SysMon
FROM rr
IF @ReturnInfo = 1
BEGIN
DECLARE @NSQL NVARCHAR(MAX)
SELECT @NSQL = 'SELECT
@InFo = ' + Util.dbo.StringConcat('''' + ColumnName + ': '' + ' + ColumnValue, ' + ''
'' + ') + '
FROM #sp_SysMon'
FROM (SELECT TOP 999999
c.name AS ColumnName,
C2 AS ColumnValue
FROM tempdb.sys.columns c (NOLOCK)
INNER JOIN tempdb.sys.types t ON t.user_type_id = c.user_type_id
CROSS APPLY (SELECT CASE WHEN t.name LIKE '%char%' THEN QUOTENAME(c.name)
ELSE 'CAST(' + QUOTENAME(c.name) + ' AS VARCHAR)'
END AS C1) d
CROSS APPLY (SELECT CASE WHEN c.is_nullable = 1 THEN 'ISNULL(' + C1 + ', '''')' ELSE C1 END AS C2) e
WHERE c.OBJECT_ID = OBJECT_ID('tempdb..#sp_SysMon')
ORDER BY c.name) k
EXEC sys.sp_executesql
@NSQL,
N'@InFo VARCHAR(MAX) OUTPUT',
@InFo OUTPUT
END
ELSE
SELECT *
FROM #sp_SysMon
GO
EXEC sys.sp_ms_marksystemobject sp_SysMon
GO