아티클
09/27/2010

방법: 분류자 사용자 정의 함수 만들기 및 테스트(Transact-SQL)

이 항목에서는 분류자 UDF(사용자 정의 함수)를 만들고 테스트하는 방법을 보여 줍니다. 이 단계에는 SQL Server Management Studio 쿼리 편집기를 사용하여 Transact-SQL 문을 실행하는 작업이 포함되어 있습니다. 계속하기 전에 분류자 함수 작성 시 고려 사항을 읽어보는 것이 좋습니다.

아래 절차에 나오는 예에서는 매우 복잡한 분류자 사용자 정의 함수를 만드는 경우에 대해 설명합니다.

이 예에서는 다음과 같은 작업이 수행됩니다.

지정한 시간 범위 동안의 프로덕션 처리를 위해 리소스 풀(pProductionProcessing) 및 작업 그룹(gProductionProcessing)을 만듭니다.
프로덕션 처리에 대한 요구 사항에 맞지 않는 연결 처리를 위해 리소스 풀(pOffHoursProcessing) 및 작업 그룹(gOffHoursProcessing)을 만듭니다.
로그인 시간에 대해 계산할 수 있는 시작 시간과 종료 시간을 보유하기 위해 master에 테이블(TblClassificationTimeTable)을 만듭니다. 리소스 관리자가 분류자 함수에 대한 스키마 바인딩을 사용하기 때문에 마스터에 이 테이블을 만들어야 합니다.

[!참고]

가능하면 자주 업데이트하는 큰 테이블은 master에 저장하지 마십시오.

분류자 함수 작성 시 고려 사항에 언급했듯이 분류자 함수는 로그인 시간을 확장합니다. 과도하게 복잡한 함수를 사용하면 로그인을 수행할 때 시간이 초과되거나 빠른 연결 속도를 늦출 수 있습니다.

분류자 사용자 정의 함수를 만들려면

새 리소스 풀 및 작업 그룹을 만들고 구성합니다. 각 작업 그룹을 적합한 리소스 풀에 할당합니다.

--- Create a resource pool for production processing
--- and set limits.
USE master
GO
CREATE RESOURCE POOL pProductionProcessing
WITH
(
     MAX_CPU_PERCENT = 100,
     MIN_CPU_PERCENT = 50
)
GO
--- Create a workload group for production processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gProductionProcessing
WITH
(
     IMPORTANCE = MEDIUM
)
--- Assign the workload group to the production processing
--- resource pool.
USING pProductionProcessing
GO
--- Create a resource pool for off-hours processing
--- and set limits.

CREATE RESOURCE POOL pOffHoursProcessing
WITH
(
     MAX_CPU_PERCENT = 50,
     MIN_CPU_PERCENT = 0
)
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gOffHoursProcessing
WITH
(
     IMPORTANCE = LOW
)
--- Assign the workload group to the off-hours processing
--- resource pool.
USING pOffHoursProcessing
GO

메모리 내 구성을 업데이트합니다.
```
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
```

테이블을 만들고 프로덕션 처리 시간 범위의 시작 시간과 종료 시간을 정의합니다.

USE master
GO
CREATE TABLE tblClassificationTimeTable
(
     strGroupName     sysname          not null,
     tStartTime       time              not null,
     tEndTime         time              not null
)
GO
--- Add time values that the classifier will use to
--- determine the workload group for a session.
INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM')
go

조회 테이블의 시간에 대해 계산될 수 있는 시간 함수 및 값을 사용하는 분류자 함수를 만듭니다.

[!참고]

SQL Server 2008에서는 날짜 및 시간 데이터 형식 및 함수의 확장된 집합을 제공합니다. 자세한 내용은 날짜 및 시간 데이터 형식 및 함수(Transact-SQL)를 참조하십시오.

CREATE FUNCTION fnTimeClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
     DECLARE @strGroup sysname
     DECLARE @loginTime time
     SET @loginTime = CONVERT(time,GETDATE())
     SELECT TOP 1 @strGroup = strGroupName
          FROM dbo.tblClassificationTimeTable
          WHERE tStartTime <= @loginTime and tEndTime >= @loginTime
     IF(@strGroup is not null)
     BEGIN
          RETURN @strGroup
     END
--- Use the default workload group if there is no match
--- on the lookup.
     RETURN N'gOffHoursProcessing'
END
GO

분류자 함수를 등록하고 메모리 내 구성을 업데이트합니다.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

리소스 풀, 작업 그룹 및 분류자 사용자 정의 함수를 확인하려면

다음 쿼리를 사용하여 리소스 풀 및 작업 그룹 구성을 가져옵니다.

USE master
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups
GO

다음 쿼리를 사용하여 분류자 함수가 존재하며 설정되었는지 확인합니다.

--- Get the classifier function Id and state (enabled).
SELECT * FROM sys.resource_governor_configuration
GO
--- Get the classifer function name and the name of the schema
--- that it is bound to.
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

다음 쿼리를 사용하여 리소스 풀 및 작업 그룹에 대한 현재 런타임 데이터를 가져옵니다.
```
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups
GO
```

다음 쿼리를 사용하여 각 그룹에 있는 세션을 확인합니다.

SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
          FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
          ON g.group_id = s.group_id
ORDER BY g.name
GO

다음 쿼리를 사용하여 각 그룹에 있는 요청을 확인합니다.

SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
           FROM sys.dm_exec_requests r
     INNER JOIN sys.dm_resource_governor_workload_groups g
            ON g.group_id = r.group_id
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO

다음 쿼리를 사용하여 분류자에서 실행하는 요청을 확인합니다.

SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name 
           FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
           ON g.group_id = s.group_id
                 AND 'preconnect' = s.status
ORDER BY g.name
GO
 
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
           FROM sys.dm_exec_requests r
     INNER JOIN sys.dm_resource_governor_workload_groups g
           ON g.group_id = r.group_id
                 AND 'preconnect' = r.status
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO

참고 항목

개념

리소스 관리자 개념

분류자 함수 작성 시 고려 사항

리소스 관리자 문제 해결

방법: 분류자 사용자 정의 함수 만들기 및 테스트(Transact-SQL)

분류자 사용자 정의 함수를 만들려면

리소스 풀, 작업 그룹 및 분류자 사용자 정의 함수를 확인하려면

참고 항목

개념

추가 리소스