2013.05.04 DBNuni Study 에서 발표하였던 CLR을 이용하여 Performance Counter를 Database Query 문으로 SELECT하는 스크립트 입니다.
1. Visual C#의 SQL Server 프로젝트 또는 클래스 라이브러리 템블릿을 이용하여 DLL 파일을 만든다.
2. MSSQL Server 에서 CLR 을 활성화 한다.
3. CLR을 등록한다.
4. CLR을 호출하는 스칼라 함수를 생성한다.
5. Performance Counter 수치를 출력할 목록 테이블 생성 및 Counter Name을 등록한다.
4. CLR 함수를 이용하여 현재 서버의 Performance Counter를 확인한다.
먼저 Visual Studio를 이용하여 프로젝트를 만든다. (스크린샷 된 Visual Studio의 모습은 2012 모습입니다.)
Performance Counter를 출력하기 위한 코드를 만들고 컴파일을 합니다.
/*************************************************************************
** Desc :
** CLR 등록하기
*************************************************************************/
/*************************************************************************
** CLR 활성이 되어 있는지 SQL SERVER 구성 확인
*************************************************************************/
EXEC sys.sp_configure
SELECT * FROM sys.configurations
/*************************************************************************
** SQL SERVER 에서 T-SQL을 통한 CLR 통합 옵션 설정
*************************************************************************/
EXEC sys.sp_configure @configname = 'clr enabled', -- varchar(35)
@configvalue = 1 -- int
RECONFIGURE -- WITH OVERRIDE;
/*************************************************************************
** CLR 등록하기
*************************************************************************/
CREATE ASSEMBLY [SQLPerformance]
AUTHORIZATION [dbo]
FROM 'D:\SQLPerformance.dll'
WITH PERMISSION_SET = UNSAFE;
GO
-- ObjectName : ufn_clr_GetPerfCounterValue
/*************************************************************************
** Name : ufn_clr_GetPerfCounterValue
** Desc : 성능카운터 출력 스칼라 함수 (CLR)
** EXEC :
** Auth : 이승연(gamebible)
** Date : 2013.04.27
**************************************************************************
** Change History
**************************************************************************
** Date: Author: Description:
** ---------- -------- ---------------------------------------
** 2013.04.27 이승연(gamebible)
*************************************************************************/
CREATE FUNCTION [dbo].[ufn_clr_GetPerfCounterValue](
@CategoryName NVARCHAR(4000),
@CounterName NVARCHAR(4000),
@instanceName NVARCHAR(4000),
@MachineName NVARCHAR(4000)
)
RETURNS [float]
AS
EXTERNAL NAME [SQLPerformance].[UserDefinedFunctions].[ufn_clr_GetPerfCounterValue]
GO
/*************************************************************************
** Desc :
** 테이블을 생성하고 성능 기준치 값을 저장하는 T-SQL 코드
*************************************************************************/
USE DBA
GO
IF OBJECT_ID('dbo.AT_PerfCounters', 'U') IS NOT NULL BEGIN
DROP TABLE dbo.AT_PerfCounters;
END
CREATE TABLE dbo.AT_PerfCounters (
Seq INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Category NVARCHAR(4000) NOT NULL,
Counter NVARCHAR(4000) NOT NULL,
Instance NVARCHAR(4000) NOT NULL DEFAULT '',
IsSQLCounter BIT NOT NULL,
FriendlyName NVARCHAR(256) NOT NULL,
IsRatioBased BIT NOT NULL,
IsActive BIT NOT NULL,
BestPractice NVARCHAR(4000) NULL,
UpperLimit FLOAT NULL,
LowerLimit FLOAT NULL
);
GO
INSERT INTO dbo.AT_PerfCounters (
Category, Counter, Instance, IsSQLCounter, FriendlyName, IsRatioBased, IsActive, BestPractice, UpperLimit, LowerLimit
) VALUES (
'Processor', '% Processor Time', '_Total', 0, 'CPU', 0, 1, 'Should be less than 80%', 80, NULL
), (
'PhysicalDisk', 'Avg. Disk Queue Length', '_Total', 0, 'Avg. Disk Queue', 0, 1, 'Should not be permanently greater than 0', 1, NULL
), (
'SQLServer:Buffer Manager', 'Page life expectancy', '', 1, 'Page Life Expectancy', 0, 1, 'Should not drop below 1000', NULL, 1000
), (
'SQLServer:Databases', 'Percent Log Used', 'DBA', 1, 'DBA database - % log used', 1, 1, 'Should not fall below 90%', 90, NULL
), (
'SQLServer:Plan Cache', 'Cache hit ratio', '_Total', 1, 'Cache hit ratio', 1, 1, 'Should not fall below 90%', NULL, 90
);
GO
SELECT * FROM dbo.AT_PerfCounters
SELECT * FROM sys.dm_os_performance_counters
/*************************************************************************
** Desc :
** 성능 카운터 수치를 평가하기 위한 T-SQL
*************************************************************************/
;WITH PerfCTE AS (
SELECT
pc.FriendlyName,
pc.BestPractice,
CASE
WHEN pc.IsRatioBased = 1 AND r.cntr_type = 0 THEN 0
WHEN pc.IsRatioBased = 1 AND r.cntr_type <> 0 THEN 100. * c.cntr_value / r.cntr_value
WHEN c.cntr_value IS NULL THEN [DBA].[dbo].[ufn_clr_GetPerfCounterValue](pc.Category, pc.COUNTER, pc.Instance, '.')
ELSE c.cntr_value
END AS VALUE,
pc.UpperLimit,
pc.LowerLimit
FROM DBA.dbo.AT_PerfCounters AS pc
LEFT JOIN sys.dm_os_performance_counters AS c
ON pc.IsSQLCounter = 1
AND pc.Category = c.[object_name]
AND pc.COUNTER = c.counter_name
AND pc.Instance = c.instance_name
LEFT JOIN sys.dm_os_performance_counters AS r
ON c.object_name = r.object_name
AND r.counter_name LIKE RTRIM(c.counter_name) + '%'
AND c.instance_name = r.instance_name
AND r.cntr_type = 1073939712
WHERE
pc.IsActive = 1
)
SELECT
FriendlyName,
BestPractice,
CONVERT(NUMERIC(19,2), VALUE) AS VALUE,
CASE WHEN VALUE BETWEEN ISNULL(LowerLimit, VALUE) AND ISNULL(UpperLimit, VALUE) THEN 1
ELSE 0
END AS KPIValue
FROM PerfCTE
위와 같이 CLR을 등록하고 CTE를 사용하여 CLR함수를 호출하면 AT_PerfCounters 에 등록된 Counter의 수치가 출력이 되면서 KPI 지수를 출력 합니다. 해당 KPI 수치는 AT_PerfCounters 테이블 UpperLimit 와 LowerLimit 를 사용합니다.
해당 Query는 출력만을 하지만, 특정 테이블을 생성하여 해당 Counter를 저장하게 할 수도 있을 것이고, 조금 수정만 하면 다른 서버의 Counter도 출력할 수 있지 않을까 합니다. 해당 CLR 스크립트와 SQL Query 구문은 '실전 SQL Server MVP 61' 책의 12 챕터의 내용을 그대로 옮긴 것 입니다.
'Performance' 카테고리의 다른 글
| PowerShell을 이용하여 성능카운터 저장하기 (0) | 2013.05.11 |
|---|---|
| VB+WMI 로 Performance counter 를 DB에 저장하기 (0) | 2013.05.06 |
