분류자 사용자 정의 함수 만들기 및 테스트

  • 아티클

적용 대상:SQL ServerAzure SQL Managed Instance

이 항목에서는 분류자 사용자 정의 함수(Transact-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(10.0.x)에는 확장된 날짜 및 시간 데이터 형식 및 함수 집합이 도입되었습니다. 자세한 내용은 날짜 및 시간 데이터 형식 및 함수(Transact-SQL)를 참조하세요.

    CREATE FUNCTION fnTimeClassifier()  
RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
/* We recommend running the classifier function code under 
snapshot isolation level OR using NOLOCK hint to avoid blocking on 
lookup table. In this example, we are using NOLOCK hint. */
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable WITH(NOLOCK)
          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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 힌트를 사용하여 차단을 방지하거나 함수에서 SET LOCK_TIMEOUT(최댓값 1,000밀리초)을 사용합니다.

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

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

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

    5. 테이블 콘텐츠를 업데이트하는 경우에는 기록기가 판독기를 차단하지 않도록 분류자 함수에서 스냅샷 격리 수준 트랜잭션을 사용합니다. 힌트를 NOLOCK 사용하면 이를 완화해야 합니다.

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

      경고

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

참고 항목

리소스 관리자
리소스 관리자 사용
리소스 관리자 리소스 풀
리소스 관리자 작업 그룹
템플릿을 사용하여 리소스 관리자 구성
리소스 관리자 속성 보기
ALTER RESOURCE GOVERNOR(Transact-SQL)
CREATE RESOURCE POOL(Transact-SQL)
CREATE WORKLOAD GROUP(Transact-SQL)
CREATE FUNCTION(Transact-SQL)
ALTER RESOURCE GOVERNOR(Transact-SQL)