MSSQL에서 리소스 풀 만들기 및 제어

Source: Dev.to

Introduction

서버 리소스를 직접 제어할 때가 왔습니다.

MS SQL 인스턴스에서는 모든 워크로드를 동일하게 취급하면 안 됩니다.
SQL Server Management Studio(SSMS)에서 실행되는 하나의 무거운 쿼리가 CPU와 메모리를 쉽게 소모해 프로덕션 워크로드에 영향을 줄 수 있습니다.

Resource Governor는 강력한 기능임에도 불구하고 활용도가 낮은 편입니다. 간단한 함수와 리소스 풀만으로도 다중 사용자 환경에서 안정성과 예측 가능성을 크게 향상시킬 수 있습니다.

What is Resource Governor?

Resource Governor는 서로 다른 워크로드가 사용할 수 있는 CPU와 메모리 양을 제어할 수 있게 해 주는 SQL Server 기능입니다. 이는 특히 프로덕션 안전성 확보나 DBA가 접근을 제어하고자 할 때 유용합니다.

설정이 완료되면 Microsoft SQL Server Management Studio에서 실행되는 모든 쿼리는 제한된 리소스 풀에 할당되고, 다른 모든 연결은 기본 풀에 남게 됩니다. 분류 함수(classifier function)를 수정하면 특정 요구에 맞게 리소스 풀을 커스터마이징할 수 있습니다.

일반적인 사용 시나리오에는 다음과 같은 경우가 포함됩니다:

• 특정 SQL 사용자
• 보고서 또는 BI 도구
• ETL 작업
• 임시 사용자(Ad‑hoc)
• 서드파티 애플리케이션
• 야간 또는 백그라운드 워크로드

Implementation

1. Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

2. Create the Classifier Function

세션이 사용할 워크로드 그룹을 결정하는 함수입니다.

CREATE FUNCTION [dbo].[ResourceGroup_Users]
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME;
    DECLARE @ProgramName SYSNAME;

    SET @ProgramName = CONVERT(SYSNAME, PROGRAM_NAME());

    IF @ProgramName LIKE 'Microsoft SQL Server Management Studio%'
    BEGIN
        SET @WorkloadGroup = 'LimitedResourceGroup';
    END
    ELSE
    BEGIN
        SET @WorkloadGroup = 'default';
    END

    RETURN @WorkloadGroup;
END
GO

Optional: 특정 SQL 사용자를 제한하려면 추가 조건을 넣습니다. 예:

IF SUSER_NAME() = 'testuser' THEN ...

3. Create a Resource Pool

이 풀은 SSMS 쿼리가 CPU와 메모리를 각각 30 % 이상 사용하지 못하도록 제한합니다.

CREATE RESOURCE POOL [LimitedResourcePool]
WITH
(
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 30,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 30
);
GO

4. Create a Workload Group

제한된 리소스 풀에 워크로드 그룹을 연결합니다.

CREATE WORKLOAD GROUP [LimitedResourceGroup]
USING [LimitedResourcePool];
GO

5. Enable the Classifier Function

Resource Governor에 함수를 사용하도록 지정하고 재구성합니다.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = [dbo].[ResourceGroup_Users]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Monitoring

다음 쿼리를 실행하면 각 세션이 어떤 리소스 풀을 사용하고 있는지 확인할 수 있습니다.

SELECT
    s.session_id,
    s.login_name,
    rg.name AS resource_pool_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_resource_governor_workload_groups AS wg
    ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools AS rg
    ON wg.pool_id = rg.pool_id
WHERE s.is_user_process = 1;

Notes

• 변경 사항은 새로운 세션에만 적용됩니다. 변경을 적용하려면 SQL 서비스를 재시작하거나 기존 세션을 종료하세요.
• Resource Governor는 SQL Server 2025부터 Standard Edition에서도 사용할 수 있으며, 이전 버전에서는 Enterprise 전용 기능이었습니다.

이제 리소스 풀을 활용해 보세요. 여러분의 SQL Server가 보다 원활하게 운영되길 바랍니다!