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

기본적으로 압축을 사용하여 백업하면 CPU 사용량이 크게 늘어나고 압축 프로세스로 사용되는 추가 CPU는 동시 작업에 악영향을 줄 수 있습니다. 따라서 CPU 충돌이 발생하면 CPU 사용량이 리소스 관리자에 의해 제한되는 세션에서 우선 순위가 낮은 압축 백업을 만들 수 있습니다. 이 항목에서는 이와 같은 경우에 CPU 사용량을 제한하는 리소스 관리자 작업 그룹에 특정 SQL Server 사용자의 세션을 매핑하는 방법으로 이러한 세션을 분류하는 시나리오를 제공합니다.

중요
주어진 리소스 관리자 시나리오에서 사용자 이름, 응용 프로그램 이름을 비롯해 연결을 차별화하는 어떠한 요소라도 세션 분류의 기준이 될 수 있습니다. 자세한 내용은 분류자 함수 작성 시 고려 사항 및 리소스 관리자 작업 관리 시나리오를 참조하십시오.

이 항목에서는 다음과 같은 시나리오를 순서대로 다룹니다.

1. 우선 순위가 낮은 작업에 대한 로그인 및 사용자 설정

2. CPU 사용량을 제한하도록 리소스 관리자 구성

3. 현재 세션의 분류 확인(Transact-SQL)

4. CPU가 제한된 세션을 사용하여 백업 압축

우선 순위가 낮은 작업에 대한 로그인 및 사용자 설정

이 항목의 시나리오에는 우선 순위가 낮은 SQL Server 로그인 및 사용자가 필요합니다. 사용자 이름은 이 로그인에서 실행되는 세션을 분류하고 CPU 사용량을 제한하는 리소스 관리자 작업 그룹으로 이러한 세션을 라우팅하는 데 사용됩니다.

다음 절차에서는 이러한 목적에 맞게 로그인 및 사용자를 설정하는 단계를 설명하며, 그 다음에는 Transact-SQL 예로 "예 1: 로그인 및 사용자 설정(Transact-SQL)"이 제공됩니다.

세션 분류를 위한 로그인 및 데이터베이스 사용자를 설정하려면

1. 우선 순위가 낮은 압축된 백업을 만들기 위한 SQL Server 로그인을 만듭니다. 

   로그인을 만들려면

   • 방법: SQL Server 로그인 만들기

   • CREATE LOGIN(Transact-SQL)

2. 필요에 따라 이 로그인에 VIEW SERVER STATE를 부여합니다.

   • GRANT 시스템 개체 사용 권한(Transact-SQL)

   자세한 내용은 고정 서버 역할의 권한(데이터베이스 엔진)을 참조하십시오.

3. 이 로그인의 SQL Server 사용자를 만듭니다.

   사용자를 만들려면

   • 방법: 데이터베이스 사용자 만들기

   • CREATE USER(Transact-SQL)

4. 이 로그인 및 사용자의 세션에서 지정된 데이터베이스를 백업하도록 하려면 해당 데이터베이스의 db_backupoperator 데이터베이스 역할에 사용자를 추가합니다. 이 사용자가 백업할 각 데이터베이스에 대해 이를 수행합니다. 필요에 따라 다른 고정 데이터베이스 역할에 사용자를 추가합니다.

   고정 데이터베이스 역할에 사용자를 추가하려면

   • sp_addrolemember(Transact-SQL)

   자세한 내용은 고정 데이터베이스 역할의 사용 권한(데이터베이스 엔진)을 참조하십시오.

예 1: 로그인 및 사용자 설정(Transact-SQL)

다음 예는 우선 순위가 낮은 백업을 위한 새 SQL Server 로그인 및 사용자를 만들도록 선택한 경우에만 해당합니다. 또는 적절한 기존의 로그인 및 사용자가 있는 경우 이를 사용할 수 있습니다.

중요
다음 예에서는 예제 로그인 및 사용자 이름인 domain_name\MAX_CPU을 사용합니다. 이를 우선 순위가 낮은 압축된 백업을 만들 때 사용할 SQL Server 로그인 및 사용자로 대체합니다.

이 예에서는 domain_name\MAX_CPU Windows 계정의 로그인을 만든 다음 이 로그인에 VIEW SERVER STATE 권한을 부여합니다. 이 권한을 통해 로그인 세션의 리소스 관리자 분류를 확인할 수 있습니다. 그런 다음 domain_name\MAX_CPU의 사용자를 만들어 AdventureWorks 예제 데이터베이스에 대한 db_backupoperator 고정 데이터베이스 역할에 추가합니다. 이 사용자 이름은 리소스 관리자 분류자 함수에 의해 사용됩니다.

-- Create a SQL Server login for low-priority operations
USE master;
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];
GO
-- Create a SQL Server user in AdventureWorks for this login
USE AdventureWorks;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[맨 위]

CPU 사용량을 제한하도록 리소스 관리자 구성

[!참고]

리소스 관리자가 설정되어 있는지 확인합니다. 자세한 내용은 방법: 리소스 관리자 설정 또는 해제(SQL Server Management Studio)을 참조하십시오.

이 리소스 관리자 시나리오는 다음과 같은 기본 단계로 구성됩니다.

1. CPU 충돌이 발생하면 리소스 풀의 요청에 지정되는 최대 평균 CPU 대역폭을 제한하는 리소스 관리자 리소스 풀을 만들고 구성합니다.

2. 이 풀을 사용하는 리소스 관리자 작업 그룹을 만들고 구성합니다.

3. 분류자 함수를 만듭니다. 분류자 함수는 UDF(사용자 정의 함수)로, 리소스 관리자는 세션을 적절한 작업 그룹으로 라우팅되도록 분류하기 위해 이 함수의 반환 값을 사용합니다.

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

5. 변경 내용을 리소스 관리자 메모리 내 구성에 적용합니다.

[!참고]

리소스 관리자 리소스 풀, 작업 그룹 및 분류에 대한 자세한 내용은 리소스 관리자 개념을 참조하십시오.

이러한 단계를 위한 Transact-SQL 문은 "CPU 사용량을 제한하도록 리소스 관리자를 구성하려면" 절차에 설명되어 있습니다. 그 다음에는 이 절차의 Transact-SQL 예가 나옵니다.

리소스 관리자를 구성하려면(SQL Server Management Studio)

• 방법: 템플릿을 사용한 리소스 관리자 구성(SQL Server Management Studio)

• 방법: 리소스 풀 만들기(SQL Server Management Studio)

• 방법: 작업 그룹 만들기(SQL Server Management Studio)

CPU 사용량을 제한하도록 리소스 관리자를 구성하려면(Transact-SQL)

1. CREATE RESOURCE POOL 문을 실행하여 리소스 풀을 만듭니다. 이 절차에 대한 예에서는 다음 구문을 사용합니다.

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

   Value는 최대 평균 CPU 대역폭의 비율을 나타내는 1에서 100까지의 정수입니다. 적절한 값은 해당 환경에 따라 달라집니다. 이 항목의 예에서는 설명을 위해 20% 비율을 사용합니다(MAX_CPU_PERCENT = 20).

2. CREATE WORKLOAD GROUP 문을 실행하여 CPU 사용량을 관리하려는 우선 순위가 낮은 작업에 대한 작업 그룹을 만듭니다. 이 절차에 대한 예에서는 다음 구문을 사용합니다.

   CREATE WORKLOAD GROUP group_name USING pool_name;

3. CREATE FUNCTION 문을 실행하여 이전 단계에서 만든 작업 그룹을 우선 순위가 낮은 로그인의 사용자에 매핑하는 분류자 함수를 만듭니다. 이 절차에 대한 예에서는 다음 구문을 사용합니다.

   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

   WITH SCHEMABINDING

   AS

   BEGIN

   DECLARE @workload\_group\_name AS sysname

   IF (SUSER_NAME() = 'user_of_low_priority_login')

   SET @workload\_group\_name = 'workload_group_name'

   RETURN @workload\_group\_name

   END

   이 CREATE FUNCTION 문의 구성 요소에 대한 자세한 내용은 다음을 참조하십시오.

   • DECLARE @local\_variable(Transact-SQL)

   • SUSER_SNAME(Transact-SQL)

     중요
     SUSER_NAME은 분류자 함수에 사용할 수 있는 여러 시스템 함수 중 하나일 뿐입니다. 자세한 내용은 분류자 함수 작성 시 고려 사항을 참조하십시오.

   • SET @local\_variable(Transact-SQL).

4. ALTER RESOURCE GOVERNOR 문을 실행하여 리소스 관리자에 분류자 함수를 등록합니다. 이 절차에 대한 예에서는 다음 구문을 사용합니다.

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.

5. 두 번째 ALTER RESOURCE GOVERNOR 문을 실행하여 다음과 같이 변경 내용을 리소스 관리자 메모리 내 구성에 적용합니다.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

예 2: 리소스 관리자 구성(Transact-SQL)

다음 예에서는 하나의 트랜잭션 내에서 다음과 같은 단계를 수행합니다.

1. pMAX_CPU_PERCENT_20 리소스 풀을 만듭니다.

2. gMAX_CPU_PERCENT_20 작업 그룹을 만듭니다.

3. 이전 예에서 만든 사용자 이름을 사용하는 rgclassifier_MAX_CPU() 분류자 함수를 만듭니다.

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

트랜잭션을 커밋한 후 예에서는 ALTER WORKLOAD GROUP 또는 ALTER RESOURCE POOL 문에 요청된 구성 변경 내용을 적용합니다.

중요
다음 예에서는 “예 1: 로그인 및 사용자 설정(Transact-SQL)"에서 만든 예제 SQL Server 사용자의 사용자 이름인 domain_name\MAX_CPU를 사용합니다. 이 이름을 우선 순위가 낮은 압축된 백업을 만드는 데 사용할 로그인 사용자의 이름으로 대체합니다.

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

[맨 위]

현재 세션의 분류 확인(Transact-SQL)

필요에 따라 분류자 함수에 지정한 사용자로 로그인한 후 개체 탐색기에서 다음 SELECT 문을 실행하여 세션 분류를 확인합니다.

USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name 
FROM sys.dm_exec_sessions AS sess 
JOIN sys.dm_resource_governor_workload_groups AS grps 
    ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

결과 창의 name 열에는 분류자 함수에서 지정한 작업 그룹 이름에 대한 하나 이상의 세션이 나열됩니다.

[!참고]

이 SELECT 문에 의해 호출되는 동적 관리 뷰에 대한 자세한 내용은 sys.dm_exec_sessions(Transact-SQL) 및 sys.dm_resource_governor_workload_groups(Transact-SQL)를 참조하십시오.

[맨 위]

CPU가 제한된 세션을 사용하여 백업 압축

최대 CPU가 제한된 세션에서 압축된 백업을 만들려면 분류자 함수에 지정된 사용자로 로그인합니다. 백업 명령에 WITH COMPRESSION(Transact-SQL)을 지정하거나 백업 압축(SQL Server Management Studio)을 선택합니다.

압축된 데이터베이스 백업을 만들려면

• 방법: 전체 데이터베이스 백업 만들기(Transact-SQL)

• 방법: 데이터베이스 백업(SQL Server Management Studio)

예 3: 압축된 백업 만들기(Transact-SQL)

다음 BACKUP 예에서는 새로 형식이 지정된 백업 파일인 Z:\SQLServerBackups\AdvWorksData.bak에 AdventureWorks 데이터베이스의 압축된 전체 백업을 만듭니다.

--Run backup statement in the gBackup session.
BACKUP DATABASE AdventureWorks TO DISK='Z:\SQLServerBackups\AdvWorksData.bak' 
WITH 
   FORMAT, 
   MEDIADESCRIPTION='AdventureWorks Compressed Data Backups'
   DESCRIPTION='First database backup on AdventureWorks Compressed Data Backups media set'
   COMPRESSION;
GO

[맨 위]

참고 항목

태스크

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

개념

리소스 관리자 소개

리소스 관리자 개념

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

리소스 관리자 문제 해결

관련 자료

리소스 관리자로 SQL Server 작업 및 리소스 관리

리소스 관리자 방법 도움말 항목