리소스 관리자 작업 관리 시나리오

리소스 관리자를 사용하면 다양한 방법으로 SQL Server 시스템에서 작업을 모니터링하고 관리할 수 있습니다. 이 항목에서는 리소스 관리자를 구성하는 방법에 대해 간략하게 설명하고 리소스 관리자를 사용하는 방법을 보여 줍니다. 여기에는 작업 그룹과 리소스 풀을 만들고 변경하는 Transact-SQL 코드 예가 포함된 시나리오도 제공됩니다.

리소스 관리자 구성

SQL Server 2008을 설치하고 나면 리소스 관리자를 사용할 수 있지만 이를 위해서는 먼저 사용할 수 있도록 설정해야 합니다. 리소스 관리자에는 내부 및 기본 작업 그룹과 해당 리소스 풀이 포함되어 있습니다.

사용자 고유의 리소스 풀과 작업 그룹을 만들어서 사용하려면 다음 단계를 수행해야 합니다.

• 지정하는 제한이 있는 리소스 풀을 만듭니다.

• 지정하는 제한과 정책이 있는 작업 그룹을 만들고 작업 그룹이 속한 리소스 풀을 식별합니다.

• 들어오는 요청에 대해 사용할 분류자 함수를 정의하고 활성화합니다.

위의 단계가 모두 완료되면 활성 리소스 관리자 구성과 분류된 모든 활성 요청의 상태를 확인할 수 있습니다.

리소스 풀 및 작업 그룹 요구 사항 확인

SQL Server 환경에 사용할 작업 그룹과 리소스 풀 구성을 확인하려면 현재 응용 프로그램에서 SQL Server 리소스가 사용되는 방법을 알아야 합니다.

이를 위해서는 기본 리소스 풀에 작업 그룹을 만들어야 합니다. 며칠 동안 응용 프로그램을 실행하고 수집한 정보를 사용하여 다음 질문에 답합니다.

• CPU 요구 사항이란 무엇입니까?
  요청당 총 CPU 사용량의 평균을 초당 평균 요청 수로 곱하여 CPU 대역폭 예상치를 결정합니다.

  CPU 사용량의 최대, 평균 및 표준 편차는 최대 CPU 제한이 필요한지 여부를 결정하는 데 사용할 수 있습니다.

• 동시 요청 수란 무엇입니까?
  동시 요청 통계의 최대 및 평균 수를 사용하여 최소 동시성 요소를 확인할 수 있습니다.

• 필요한 총 메모리란 무엇입니까?
  사용된 최대 및 평균 총 메모리를 사용하여 메모리 요구 사항을 확인할 수 있습니다.

• 단일 쿼리에 얼마나 많은 메모리가 필요합니까?
  쿼리 통계당 최대 및 평균 메모리를 사용하여 필요한 메모리 양을 확인할 수 있습니다.

• 메모리 부족으로 인해 실행되는 않는 쿼리가 있습니까?
  메모리 큐 통계에서 대기하는 평균 시간을 사용하여 메모리 부족으로 인해 쿼리가 차단되는지 여부를 확인할 수 있습니다.

시나리오

다음은 예를 보여 주기 위한 목적으로 제공되는 일곱 개의 시나리오입니다. 시나리오 1-5는 누적되므로 각 시나리오는 이전 시나리오를 기반으로 만들어졌습니다. 시나리오 6은 이전 시나리오와 관련이 없고 시나리오 7은 시나리오 6을 기반으로 만들어졌습니다.

시나리오 1

Q: 방금 새 버전의 SQL Server를 설치했고 리소스 관리자를 사용하려고 합니다. 내 환경에서 리소스 관리자를 사용하려면 어떻게 해야 합니까?

A: 작업에 의한 리소스 소비를 모니터링하는 데 리소스 관리자를 사용할 수 있습니다. 리소스 관리자 환경을 설정하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 작업에 대한 작업 그룹을 만듭니다.

2. 분류 함수를 만듭니다.

3. 리소스 관리자로 분류 함수를 등록합니다.

4. 리소스 관리자를 사용하도록 설정합니다.

5. 리소스 관리자 성능 카운터를 모니터링하고 작업 그룹의 리소스 사용량에 대한 정보를 반환하는 DMV를 쿼리합니다.

예

[!참고]

다음 구성은 작업 그룹이 사용해야 하는 리소스 풀을 지정하지 않습니다. 기본적으로 작업 그룹은 기본 풀을 사용합니다.

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 2

Q: 시나리오 1의 모니터링 결과를 기반으로 임시 그룹(GroupAdhoc)의 쿼리가 30초 이상 실행될 경우 이벤트를 표시하려고 합니다.

A: 현재 리소스 관리자 구성을 변경하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 임시 그룹의 CPU 사용량에 제한을 설정합니다.

2. SQL Trace 이벤트(리소스 관리자 관리 클래스 이벤트)를 모니터링합니다.

3. 이벤트에 대한 동작을 수행합니다. 예를 들어 이벤트를 무시하거나, 전자 메일을 보내거나, 페이지를 보내거나, 요청에 대해 KILL 명령을 실행합니다.

예

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 3

Q: 모든 요청이 누적될 때 CPU 사용량의 50%를 넘지 않도록 임시 그룹을 더욱 제한하려고 합니다.

A: 이전 시나리오에서 기본 풀을 사용했으므로 새 리소스 풀을 만들어야 합니다. 현재 리소스 관리자 구성을 변경하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 새 리소스 풀을 만들고 CPU 제한을 구성합니다.

2. 새 리소스 풀을 사용하도록 임시 작업 그룹을 구성합니다.

예

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 4

Q: 이제 관리 그룹이 항상 진단 쿼리를 실행할 리소스를 갖도록 보장하려고 하지만 이러한 쿼리는 10% 이상의 서버 메모리 리소스를 사용하면 안 됩니다.

A: 이렇게 하려면 새 리소스 풀을 만들어야 합니다. 현재 리소스 관리자 구성을 변경하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 새 리소스 풀을 만들고 리소스 제한을 설정합니다.

2. 새 풀을 사용하도록 관리 작업 그룹을 구성합니다.

예

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 5

Q: 현재 보고서가 기본 리소스 풀에 있지만 별도의 작업 그룹(GroupReports)을 사용하고 있으므로 기본 리소스 그룹에서 보고서가 가장 높은 리소스 사용 우선 순위를 갖도록 하려고 합니다.

A: 이렇게 하려면 기본 그룹에서 IMPORTANCE 설정을 구성해야 합니다.

[!참고]

기본 그룹의 설정은 변경할 수 있지만 기본 풀은 변경할 수 없습니다. 따라서 기본 풀을 변경해야 하는 경우에는 새 리소스 풀을 만들어야 합니다.

현재 리소스 관리자 구성을 변경하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 기본 그룹의 설정을 변경합니다.

예

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 6

Q: 기존 리소스 풀을 사용하는 새 작업 그룹을 만든 다음 이 그룹이 새 리소스 풀로 이동하도록 분류자 함수를 변경하려고 합니다.

A: 이 새 리소스 관리자 환경을 설정하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 기본 설정을 사용하여 새 리소스 풀을 만듭니다.

2. 기존 풀에서 새 작업 그룹을 만듭니다.

3. 요청을 처리하는 새 분류자 함수를 만들고 등록합니다.

예

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

시나리오 7

Q: 시나리오 6에서 만든 작업 그룹이 더 이상 필요하지 않으므로 이 작업 그룹과 이 작업 그룹이 있는 리소스 풀을 삭제하려 합니다.

A: 리소스 관리자 환경을 변경하려면 다음 단계를 수행하십시오. 구성 예제가 제공됩니다.

1. 요청을 나머지 작업 그룹으로 분류하는 새 분류자 함수를 만들고 등록합니다.

2. 작업 그룹을 삭제합니다.

3. 리소스 풀을 삭제합니다.

4. 구성 변경 내용을 적용합니다.

예

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

참고 항목

참조

리소스 관리자 카탈로그 뷰(Transact-SQL)

리소스 관리자 관련 동적 관리 뷰(Transact-SQL)

개념

리소스 관리자 개념

리소스 관리자 DDL 및 시스템 뷰

방법: 리소스 관리자를 사용하여 백업 압축을 통해 CPU 사용량 제한(Transact-SQL)