CODE: counter

DECLARE @CounterPrefix NVARCHAR(30)

SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’
THEN ‘SQLServer:’
ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’
END ;
– Capture the first counter set
SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’
AND counter_name = ‘Full Scans/sec’
) OR ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’ AND counter_name = ‘Index Searches/sec’
) OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’ AND counter_name = ‘Lazy Writes/sec’
) OR ( OBJECT_NAME = @CounterPrefix + ‘Buffer Manager’ AND counter_name = ‘Page life expectancy’
) OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’ AND counter_name = ‘Processes Blocked’
) OR ( OBJECT_NAME = @CounterPrefix + ‘General Statistics’ AND counter_name = ‘User Connections’
) OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’ AND counter_name = ‘Lock Waits/sec’
) OR ( OBJECT_NAME = @CounterPrefix + ‘Locks’ AND counter_name = ‘Lock Wait Time (ms)’ )