Performance Counter 수집 - Transaction/sec

MS SQL 에서 Performance Counter 항목을 조회하고자 합니다.

그 중에 Transaction/Sec 를 알아보도록 하겠습니다.

 

값이 누적으로 밖에 쌓이지 않기 때문에 스냅샷을 뜨고, 일정 시간 후 데이터와 차이를 비교하여 값을 측정합니다.

DECLARE @TABLE TABLE(object_name VARCHAR(100),
        counter_name VARCHAR(100),
        instance_name VARCHAR(100),
        cntr_value BIGINT)
 
INSERT INTO @TABLE
SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters 
WHERE object_name='SQLServer:Databases' 
AND counter_name='Transactions/sec'

WAITFOR DELAY '00:01:00';

SELECT A.object_name,
        A.counter_name,
        A.instance_name,
        (B.cntr_value-A.cntr_value)/60 AS cntr_value
FROM @TABLE AS A
LEFT JOIN sys.dm_os_performance_counters AS B 
ON A.object_name=B.object_name 
	AND A.counter_name=B.counter_name 
	AND A.instance_name=B.instance_name
	AND B.object_name='SQLServer:Databases' 
	AND B.counter_name='Transactions/sec'

DECLARE @TABLE TABLE(object_name VARCHAR(100),
        counter_name VARCHAR(100),
        instance_name VARCHAR(100),
        cntr_value BIGINT)
  
INSERT INTO @TABLE
SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters 
WHERE object_name='SQLServer:SQL Statistics'
AND counter_name='Batch Requests/sec'
 
WAITFOR DELAY '00:01:00';
 
SELECT A.object_name,
        A.counter_name,
        A.instance_name,
        (B.cntr_value-A.cntr_value)/60 AS cntr_value
FROM @TABLE AS A
LEFT JOIN sys.dm_os_performance_counters AS B 
ON A.object_name=B.object_name 
    AND A.counter_name=B.counter_name 
    AND A.instance_name=B.instance_name
    AND B.object_name='SQLServer:SQL Statistics'    
    AND B.counter_name='Batch Requests/sec'