다음을 통해 공유

분류자 User-Defined 함수 만들기 및 테스트

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

다음 절차에 나와 있는 예제에서는 매우 복잡한 분류자 사용자 정의 함수를 만들 수 있는 가능성을 보여 줍니다.

이 예제에서

  • 지정된 시간 범위 동안 프로덕션 처리를 위해 리소스 풀(pProductionProcessing) 및 워크로드 그룹(gProductionProcessing)이 만들어집니다.

  • 프로덕션 처리 요구 사항을 충족하지 않는 연결을 처리하기 위해 리소스 풀(pOffHoursProcessing) 및 워크로드 그룹(gOffHoursProcessing)이 만들어집니다.

  • 로그인 시간에 대해 평가할 수 있는 시작 및 종료 시간을 유지하기 위해 마스터에 테이블(TblClassificationTimeTable)이 만들어집니다. 리소스 관리자가 분류자 함수에 스키마 바인딩을 사용하므로 마스터에서 만들어야 합니다.

    비고

    가장 좋은 방법은 자주 업데이트되는 큰 테이블을 마스터에 저장해서는 안 됩니다.

분류자 함수는 로그인 시간을 연장합니다. 지나치게 복잡한 함수로 인해 로그인 시간이 초과되거나 빠른 연결 속도가 느려질 수 있습니다.

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

  1. 새 리소스 풀 및 워크로드 그룹을 만들고 구성합니다. 각 워크로드 그룹을 적절한 리소스 풀에 할당합니다.

    --- 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

  2. 메모리 내 구성을 업데이트합니다.

    ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

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

    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

  4. 조회 테이블의 시간에 대해 평가할 수 있는 시간 함수 및 값을 사용하는 분류자 함수를 만듭니다. 분류자 함수에서 조회 테이블을 사용하는 방법에 대한 자세한 내용은 이 항목의 "분류자 함수에서 조회 테이블을 사용하는 모범 사례"를 참조하세요.

    비고

    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

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

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

리소스 풀, 워크로드 그룹 및 분류자 사용자 정의 함수를 확인하려면

  1. 다음 쿼리를 사용하여 리소스 풀 및 워크로드 그룹 구성을 가져옵니다.

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

  2. 다음 쿼리를 사용하여 분류자 함수가 존재하고 사용하도록 설정되어 있는지 확인합니다.

    --- 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

  3. 다음 쿼리를 사용하여 리소스 풀 및 워크로드 그룹에 대한 현재 런타임 데이터를 가져옵니다.

    SELECT * FROM sys.dm_resource_governor_resource_pools  
SELECT * FROM sys.dm_resource_governor_workload_groups  
GO

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

    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

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

    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

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

    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

분류자 함수에서 조회 테이블을 사용하는 모범 사례

  1. 반드시 필요한 경우가 아니면 조회 테이블을 사용하지 마세요. 조회 테이블을 사용해야 하는 경우 함수 자체에 하드 코딩될 수 있습니다. 그러나 분류자 함수의 복잡성 및 동적 변경 내용과 균형을 유지해야 합니다.

  2. 조회 테이블에 대해 수행되는 I/O를 제한합니다.

    1. TOP 1을 사용하여 하나의 행만 반환합니다.

    2. 테이블의 행 수를 최소화합니다.

    3. 테이블의 모든 행이 단일 페이지 또는 적은 수의 페이지에 있는지 확인합니다.

    4. 인덱스 검색 작업을 사용하여 찾은 행이 가능한 한 많은 검색 열을 사용하는지 확인합니다.

    5. 여러 테이블을 조인하여 사용하는 것을 고려하고 있다면, 단일 테이블로 비정규화합니다.

  3. 조회 테이블에서 차단을 방지합니다.

    1. NOLOCK 힌트를 사용하여 차단을 방지하거나 최대값이 1000밀리초인 함수에서 SET LOCK_TIMEOUT을 사용하십시오.

    2. 테이블은 master 데이터베이스에 있어야 합니다. (마스터 데이터베이스는 클라이언트 컴퓨터가 연결을 시도할 때 복구되도록 보장되는 유일한 데이터베이스입니다).

    3. 항상 스키마를 사용하여 테이블 이름을 정규화합니다. 데이터베이스 이름은 master 데이터베이스여야 하기 때문에 필요하지 않습니다.

    4. 테이블에 트리거가 없습니다.

    5. 테이블 내용을 업데이트하는 경우 기록기가 판독기를 차단하는 것을 방지하기 위해 스냅샷 격리 수준 트랜잭션을 사용해야 합니다. 힌트 NOLOCK 사용은 이를 완화할 수 있습니다.

    6. 가능하면 테이블 내용을 변경할 때 분류자 함수를 사용하지 않도록 설정합니다.

      경고

      이러한 모범 사례를 따르는 것이 좋습니다. 모범 사례를 따르지 못하는 문제가 있는 경우 향후 문제를 사전에 방지할 수 있도록 Microsoft 지원에 문의하는 것이 좋습니다.

또한 참조하십시오

리소스 관리자
리소스 관리자 사용
리소스 관리자 리소스 풀
리소스 관리자 워크로드 그룹
템플릿을 사용하여 Resource Governor 구성
리소스 관리자 속성 보기
ALTER RESOURCE GOVERNOR(Transact-SQL)
리소스 풀 만들기(Transact-SQL)
CREATE WORKLOAD GROUP(Transact-SQL)
함수 생성하기(Transact-SQL)
ALTER RESOURCE GOVERNOR(Transact-SQL)

  • Last updated on