SQL Server를 통해 성능카운터 / sys.dm_os_performance_counters 읽기

SQL Server내에서 

SQL Server 관련 Perfmon Counter를 읽을 수 있는 카운터인 sys.dm_os_performance_counters 읽기

WITH CTE AS

(

SELECT ROW_NUMBER() OVER (ORDER BY object_name,instance_name,counter_name) AS RN

, RTRIM(object_name) AS object_name

, RTRIM(counter_name) AS counter_name

, RTRIM(instance_name) AS instance_name

, cntr_value

, cntr_type

, CASE cntr_type 

WHEN 65792 THEN '절대값 | Absolute Meaning' 

WHEN 65536 THEN '절대값 | Absolute Meaning' 

WHEN 272696576 THEN '초당 누적 카운터, 초기값으로부터 누적 | Per Second counter and is Cumulative in Nature'

WHEN 537003264 THEN '벌크값, 1073939712의 값으로 나누어준 후 % 연산  | Bulk Counter. To get correct value, this value needs to be divided by Base Counter value' 

WHEN 1073874176 THEN '벌크값, 1073939712의 값으로 나누어준다 초당 누적 카운터, 초기값으로부터 누적   | Per Second counter and is Cumulative in Nature. To get correct value, this value needs to be divided by Base Counter value' 

WHEN 1073939712 THEN '537003264의 Base Value | 537003264''s Base Counter value'

WHEN 0 THEN ' '

END AS counter_comments

FROM sys.dm_os_performance_counters

)

SELECT *

 FROM CTE

ORDER BY RN

GO

References......

http://troubleshootingsql.com/2011/03/03/what-does-cntr_type-mean/

http://blogs.msdn.com/b/psssql/archive/2013/09/23/interpreting-the-counter-values-from-sys-dm-os-performance-counters.aspx