분류자 사용자 정의 함수 만들기 및 테스트Create and Test a Classifier User-Defined Function

이 항목에서는 분류자 사용자 정의 함수(Transact-UDF)를 만들고 테스트하는 방법을 보여 줍니다.This topic shows how to create and test a classifier user-defined function (UDF). 이 단계에는 Transact-SQLTransact-SQL 쿼리 편집기에서 SQL Server Management StudioSQL Server Management Studio 문을 실행하는 작업이 포함되어 있습니다.The steps involve executing Transact-SQLTransact-SQL statements in the SQL Server Management StudioSQL Server Management Studio Query Editor.

아래 절차에 나오는 예에서는 매우 복잡한 분류자 사용자 정의 함수를 만드는 경우에 대해 설명합니다.The example shown in the following procedure illustrates the possibilities for creating a fairly complex classifier user-defined function.

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

  • 지정한 시간 범위 동안의 프로덕션 처리를 위해 리소스 풀(pProductionProcessing) 및 작업 그룹(gProductionProcessing)을 만듭니다.A resource pool (pProductionProcessing) and workload group (gProductionProcessing) are created for production processing during a specified time range.

  • 프로덕션 처리에 대한 요구 사항에 맞지 않는 연결 처리를 위해 리소스 풀(pOffHoursProcessing) 및 작업 그룹(gOffHoursProcessing)을 만듭니다.A resource pool (pOffHoursProcessing) and workload group (gOffHoursProcessing) are created for handling connections that do not meet the requirements for production processing.

  • 로그인 시간에 대해 계산할 수 있는 시작 시간과 종료 시간을 보유하기 위해 마스터에 테이블(TblClassificationTimeTable)을 만듭니다.A table (TblClassificationTimeTable) is created in master to hold start and end times that can be evaluated against a login time. 리소스 관리자가 분류자 함수에 대한 스키마 바인딩을 사용하기 때문에 마스터에 이 테이블을 만들어야 합니다.This must be created in master because Resource Governor uses schema binding for classifier functions.

    참고

    가능하면 자주 업데이트하는 큰 테이블은 마스터에 저장하지 마십시오.As a best practice, you should not store large, frequently updated tables in master.

    분류자 함수는 로그인 시간을 확장합니다.The classifier function extends the login time. 과도하게 복잡한 함수를 사용하면 로그인을 수행할 때 시간이 초과되거나 빠른 연결 속도를 늦출 수 있습니다.An overly complex function can cause logins to time out or slow down fast connections.

분류자 사용자 정의 함수를 만들려면To create the classifier user-defined function

  1. 새 리소스 풀 및 작업 그룹을 만들고 구성합니다.Create and configure the new resource pools and workload groups. 각 작업 그룹을 적합한 리소스 풀에 할당합니다.Assign each workload group to the appropriate resource pool.

    --- 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. 메모리 내 구성을 업데이트합니다.Update the in-memory configuration.

    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    GO  
    
  3. 테이블을 만들고 프로덕션 처리 시간 범위의 시작 시간과 종료 시간을 정의합니다.Create a table and define the start and end times for the production processing time range.

    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. 조회 테이블의 시간에 대해 계산될 수 있는 시간 함수 및 값을 사용하는 분류자 함수를 만듭니다.Create the classifier function that uses time functions and values that can be evaluated against the times in the lookup table. 분류자 함수에서 조회 테이블을 사용하는 방법에 대한 자세한 내용은 이 항목의 "분류자 함수에서 조회 테이블을 사용하는 최선의 구현 방법"을 참조하십시오.For information about using Lookup Tables in a classifier function, see “Best practices for using Lookup Tables in a classifier function” in this topic.

    참고

    SQL Server 2008SQL Server 2008 에는 날짜 및 시간 데이터 형식 및 함수의 확장된 집합이 도입되었습니다. introduced an expanded set of date and time data types and functions. 자세한 내용은 날짜 및 시간 데이터 형식 및 함수(Transact-SQL)를 참조하세요.For more information, see Date and Time Data Types and Functions (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. 분류자 함수를 등록하고 메모리 내 구성을 업데이트합니다.Register the classifier function and update the in-memory configuration.

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

리소스 풀, 작업 그룹 및 분류자 사용자 정의 함수를 확인하려면To verify the resource pools, workload groups, and the classifier user-defined function

  1. 다음 쿼리를 사용하여 리소스 풀 및 작업 그룹 구성을 가져옵니다.Obtain the resource pool and workload group configuration by using the following query.

    USE master;  
    SELECT * FROM sys.resource_governor_resource_pools;  
    SELECT * FROM sys.resource_governor_workload_groups;  
    GO  
    
  2. 다음 쿼리를 사용하여 분류자 함수가 존재하며 설정되었는지 확인합니다.Verify that the classifier function exists and is enabled by using the following queries.

    --- 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. 다음 쿼리를 사용하여 리소스 풀 및 작업 그룹에 대한 현재 런타임 데이터를 가져옵니다.Obtain the current runtime data for the resource pools and workload groups by using the following query.

    SELECT * FROM sys.dm_resource_governor_resource_pools;  
    SELECT * FROM sys.dm_resource_governor_workload_groups;  
    GO  
    
  4. 다음 쿼리를 사용하여 각 그룹에 있는 세션을 확인합니다.Find out what sessions are in each group by using the following query.

    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. 다음 쿼리를 사용하여 각 그룹에 있는 요청을 확인합니다.Find out which requests are in each group by using the following query.

    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. 다음 쿼리를 사용하여 분류자에서 실행하는 요청을 확인합니다.Find out what requests are running in the classifier by using the following query.

    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  
    

분류자 함수에서 조회 테이블을 사용하는 최선의 구현 방법Best practices for using Lookup Tables in a classifier function

  1. 조회 테이블은 반드시 필요한 경우에만 사용합니다.Do not use a lookup table unless it is absolutely necessary. 조회 테이블을 사용해야 하는 경우에는 함수 자체에 테이블을 하드 코딩할 수 있습니다. 그러나 이 경우에는 분류자 함수의 동적 변경 및 복잡도 간에 균형을 조정해야 합니다.If you need to use a lookup table, it can be hard coded into the function itself; however, this needs to be balanced with the complexity and dynamic changes of the classifier function.

  2. 조회 테이블에 대해 수행되는 I/O를 제한합니다.Limit the I/O performed for lookup tables.

    1. TOP 1을 사용하여 행을 하나만 반환합니다.Use the TOP 1 to return only one row.

    2. 테이블의 행 수를 최소화합니다.Minimize the number of rows in the table.

    3. 테이블의 모든 행이 한 페이지 또는 소수의 페이지에 있도록 합니다.Make all rows of the table exist on a single page, or a small number of pages.

    4. Index Seek 작업을 사용하여 찾은 행이 최대한 많은 찾기 열을 사용하는지 확인합니다.Confirm that rows found using the Index Seek operations use as many seeking columns as possible.

    5. 조인을 통해 여러 테이블을 사용하려는 경우에는 단일 테이블로 비정규화합니다.De-normalize to a single table if you are considering using multiple tables with joins.

  3. 조회 테이블에 대한 차단을 방지합니다.Prevent blocking on the lookup table.

    1. NOLOCK 힌트를 사용하여 차단을 방지하거나 함수에서 SET LOCK_TIMEOUT (최대값 1000밀리초)을 사용합니다.Use the NOLOCK hint to prevent blocking or use SET LOCK_TIMEOUT in the function with a maximum value of 1000 milliseconds.

    2. 테이블은 반드시 master 데이터베이스에 있어야 합니다.Table(s) must exist in the master database. 클라이언트 컴퓨터가 연결을 시도할 때 복구가 보장되는 데이터베이스는 master 데이터베이스뿐입니다.(The master database is the only database that is guaranteed to be recovered when the client computers attempt to connect).

    3. 항상 스키마를 사용하여 테이블 이름을 정규화합니다.Always fully-qualify the table name with the schema. master 데이터베이스를 사용해야 하기 때문에 데이터베이스 이름은 필요하지 않습니다.The database name is not necessary since it has to be the master database.

    4. 테이블에 트리거를 사용하지 않습니다.No triggers on the table.

    5. 테이블 콘텐츠를 업데이트하는 경우에는 기록기가 판독기를 차단하지 않도록 분류자 함수에서 스냅숏 격리 수준 트랜잭션을 사용합니다.If you are updating the table contents, make sure to use a snapshot isolation level transaction in the classifier function to prevent Writer blocking Readers. NOLOCK 힌트를 사용해도 차단을 완화할 수 있습니다.Note that using the NOLOCK hint should also mitigate this.

    6. 가능한 경우 테이블 콘텐츠 변경 시 분류자 함수를 사용하지 않도록 설정합니다.If possible, disable the classifier function when changing the table contents.

      경고

      이러한 최선의 구현 방법을 따르는 것이 좋습니다.We highly recommend following these best practices. 문제가 발생하여 최선의 구현 방법을 따를 수 없는 경우에는 Microsoft 지원 센터에 문의하여 향후 발생할 수 있는 문제를 예방하는 것이 좋습니다.If there are issues that prevent you from following the best practices, we recommend that you contact Microsoft Support so that you can proactively prevent any future problems.

참고 항목See Also

리소스 관리자 Resource Governor
리소스 관리자 사용 Enable Resource Governor
리소스 관리자 리소스 풀 Resource Governor Resource Pool
리소스 관리자 작업 그룹 Resource Governor Workload Group
템플릿을 사용하여 리소스 관리자 구성 Configure Resource Governor Using a Template
리소스 관리자 속성 보기 View Resource Governor Properties
ALTER RESOURCE GOVERNOR(Transact-SQL) ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL(Transact-SQL) CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP(Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION(Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR(Transact-SQL)ALTER RESOURCE GOVERNOR (Transact-SQL)