리소스 관리자를 사용하여 백업 압축으로 CPU 사용 제한(Transact-SQL)

아티클
08/21/2023

적용 대상:SQL Server

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

Important

주어진 리소스 관리자 시나리오에서 사용자 이름, 애플리케이션 이름을 비롯해 연결을 차별화하는 어떠한 요소라도 세션 분류의 기준이 될 수 있습니다. 자세한 내용은 Resource Governor 분류자 함수 및 리소스 관리자 워크로드 그룹을 참조하세요.

이 항목에는 순서대로 제공되는 다음 시나리오 집합이 포함되어 있습니다.

우선 순위가 낮은 작업에 대한 로그인 및 사용자 설정
CPU 사용량을 제한하도록 리소스 관리자 구성
현재 세션의 분류 확인(Transact-SQL)
CPU가 제한된 세션을 사용하여 백업 압축

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

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

다음 절차에서는 이 목적을 위해 로그인 및 사용자를 설정하는 단계와 Transact-SQL 예제인 "예제 A: 로그인 및 사용자 설정(Transact-SQL)"을 설명합니다.

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

로그인을 만들려면
- 로그인 만들기
- CREATE LOGIN(Transact-SQL)
필요에 따라 이 로그인에 VIEW SERVER STATE를 부여합니다.
- GRANT 시스템 개체 사용 권한(Transact-SQL)
자세한 내용은 GRANT 데이터베이스 보안 주체 권한(Transact-SQL)을 참조하세요.
이 로그인에 대한 SQL Server 사용자를 만듭니다.

사용자를 만들려면
- 데이터베이스 사용자 만들기
- CREATE USER(Transact-SQL)
이 로그인 세션과 사용자가 지정된 데이터베이스를 백업할 수 있도록 하려면 해당 데이터베이스의 db_backupoperator 데이터베이스 역할에 사용자를 추가합니다. 이 사용자가 백업할 각 데이터베이스에 대해 이 작업을 수행합니다. 필요에 따라 다른 고정 데이터베이스 역할에 사용자를 추가합니다.

고정 데이터베이스 역할에 사용자를 추가하려면
- sp_addrolemember(Transact-SQL)
자세한 내용은 GRANT 데이터베이스 보안 주체 권한(Transact-SQL)을 참조하세요.

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

Important

다음 예제에서는 domain_name 샘플 로그인 및 사용자 이름을 \MAX_CPU사용합니다. 우선 순위가 낮은 압축 백업을 만들 때 사용하려는 SQL Server 로그인 및 사용자의 이름으로 바꿉니다.

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

-- 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 AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO

[위쪽]

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

참고 항목

리소스 관리자가 사용하도록 설정되어 있는지 확인합니다. 자세한 내용은 리소스 관리자 사용을 참조하세요.

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

CPU 경합이 발생할 때 리소스 풀의 요청에 부여되는 최대 평균 CPU 대역폭을 제한하는 Resource Governor 리소스 풀을 만들고 구성합니다.
이 풀을 사용하는 Resource Governor 워크로드 그룹을 만들고 구성합니다.
분류자 함수를 만듭니다. 분류자 함수는 UDF(사용자 정의 함수)로, Resource Governor는 세션을 적절한 작업 그룹으로 라우팅되도록 분류하기 위해 이 함수의 반환 값을 사용합니다.
Resource Governor에 분류자 함수를 등록합니다.
리소스 관리자 메모리 내 구성에 변경 내용을 적용합니다.

참고 항목

Resource Governor 리소스 풀, 워크로드 그룹 및 분류에 대한 자세한 내용은 Resource Governor를 참조하세요.

이러한 단계에 대한 Transact-SQL 문은 프로시저의 Transact-SQL 예제 뒤에 "CPU 사용량을 제한하기 위해 Resource Governor를 구성하려면" 절차에 설명되어 있습니다.

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

CPU 사용량을 제한하도록 Resource Governor를 구성하려면(Transact-SQL)

CREATE RESOURCE POOL 문을 실행하여 리소스 풀을 만듭니다. 이 절차의 예제에서는 다음 구문을 사용합니다.
```
CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
```
값 은 최대 평균 CPU 대역폭의 백분율을 나타내는 1에서 100까지의 정수입니다. 적절한 값은 해당 환경에 따라 달라집니다. 이 항목의 예제에서는 그림의 목적을 위해 20%(MAX_CPU_PERCENT = 20)를 사용합니다.
CREATE WORKLOAD GROUP 문을 실행하여 CPU 사용량을 제어하려는 우선 순위가 낮은 작업에 대한 워크로드 그룹을 만듭니다. 이 절차의 예제에서는 다음 구문을 사용합니다.
```
CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];
```
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)
  
  Important
  
  SUSER_NAME은 분류자 함수에 사용할 수 있는 여러 시스템 함수 중 하나일 뿐입니다. 자세한 내용은 분류자 사용자 정의 함수 만들기 및 테스트를 참조 하세요.
- SET @local_variable (Transact-SQL).
ALTER RESOURCE GOVERNOR 문을 실행하여 리소스 관리자에 분류자 함수를 등록합니다. 이 절차의 예제에서는 다음 구문을 사용합니다.
```
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
```
다음과 같이 두 번째 ALTER RESOURCE GOVERNOR 문을 실행하여 Resource Governor 메모리 내 구성에 변경 내용을 적용합니다.
```
ALTER RESOURCE GOVERNOR RECONFIGURE;  
```

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

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

리소스 풀을 pMAX_CPU_PERCENT_20 만듭니다.
gMAX_CPU_PERCENT_20 작업 그룹을 만듭니다.
이전 예에서 만든 사용자 이름을 사용하는 rgclassifier_MAX_CPU() 분류자 함수를 만듭니다.
리소스 관리자에 분류자 함수를 등록합니다.

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

Important

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

-- Configure Resource Governor.  
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

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

참고 항목

이 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)을 선택합니다. 압축된 데이터베이스 백업을 만들려면 전체 데이터베이스 백업 만들기(SQL Server)를 참조하세요.

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

다음 BACKUP 예제에서는 새로 포맷된 백업 파일에 Z:\SQLServerBackups\AdvWorksData.bakAdventureWorks2022 데이터베이스의 압축된 전체 백업을 만듭니다.

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

[위쪽]

참고 항목

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

리소스 관리자를 사용하여 백업 압축으로 CPU 사용 제한(Transact-SQL)

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

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

CPU 사용량을 제한하도록 Resource Governor를 구성하려면(Transact-SQL)

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

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

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

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

참고 항목

피드백

피드백

추가 리소스

리소스 관리자를 사용하여 백업 압축으로 CPU 사용 제한(Transact-SQL)

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

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

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

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

CPU 사용량을 제한하도록 Resource Governor를 구성하려면(Transact-SQL)

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

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

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

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

참고 항목

피드백

피드백

추가 리소스