다음을 통해 공유


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

이 항목에서는 분류자 사용자 정의 함수(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에서는 날짜 및 시간 데이터 형식 및 함수의 확장된 집합을 제공합니다. 자세한 내용은 날짜 및 시간 함수(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