RESOURCE GOVERNOR로 tempdb 최대 사용량 제한
또 한 가지 국내 사용량이 극히 적어 매우 아쉬운 기능입니다. 리소스 관리자(Resource Governor)라는 기능은 단일 서버의 CPU, 메모리, IO와 같은 리소스를 서로 다른 주체를 기준으로 분할해서 사용하고 실 시간으로 사용량을 제어할 수 있는 아주 좋은 기능입니다 다만 이전까지는 Enterprise Edition 전용이어서 선택지가 아니었지만 드디어 SQL Server 2025부터 Standard Edition에서도 지원하도록 변경되었습니다. Microsoft의 훌륭한 선택에 감사^^
더불어 리소스 관리자에 제어 가능한 리소스 중의 하나로 tempdb 사용량이 추가되었습니다. 이번 글에서 간단하게 어떤 내용인지 간략하게 살펴보고 예제 결과로 확인하겠습니다.
1. tempdb 최대(총) 사용량 제한 및 조정
공동으로 사용하는 시스템 DB인 tempdb에는 여러가지 성능 관리 이슈가 있고 그 중 하나가 급격한 용량 증가입니다. SQL Server 개발팀은 매 버전 업그레이드에서 항상 tempdb 성능 개선 관련 기능을 추가하고 있으면 이번엔 최대 사용량 제한 및 조정을 할 수 있도록 기존 리소스 관리자에 옵션을 추가한 것입니다.
User, App 등의 다양한 주체를 기준으로 tempdb의 데이터 파일 용량 또는 백분율을 기준으로 최대치를 제한하고 필요 시 동적으로 조정할 수 있습니다. 물론 지정 용량을 초과하면 오류(1138)가 발생하며 사전 모니터링 및 후속 조치도 고려해야 합니다. 간단한 예제와 결과를 보겠습니다.
주의. 아래 예제는 SQL Server의 기본 생성 workload group인 ‘default’를 사용합니다. 과정을 단순화해서 이해를 목적으로 한 것이니 개별 테스트 또는 운영 시에는 별도의 사용자 workload group을 구성하고 이를 지정합니다.
1) 적용할 리소스 관리자의 대상 workload group(여기서는 ‘default’)에 tempdb 사용량 제한을 실제 크기 또는 백분율 단위로 지정할 수 있습니다.
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
— or GROUP_MAX_TEMPDB_DATA_PERCENT
GROUP_MAX_TEMPDB_DATA_MB는 실제 크기를 지정해서 제한할 수 있으며, 0 이면 공간 불가, NULL이면 초기화됩니다.
이 구성을 실제 적용하기 위해 RECONFIGURE가 필요합니다.
ALTER RESOURCE GOVERNOR RECONFIGURE;
2) 메타 데이터로 구성된 내용을 확인합니다.
SELECT group_id,
name,
group_max_tempdb_data_mb
FROM sys.resource_governor_workload_groups
WHERE name = 'default';
| group_id | name | group_max_tempdb_data_db |
| 2 | Default | 20480 |
3) 실시간으로 tempdb 공간의 사용량과 초과 횟수(오류 1138) 등을 확인할 수 있습니다.
SELECT group_id
, name
, tempdb_data_space_kb — 현재 사용량
, peak_tempdb_data_space_kb — 최고 사용량
, total_tempdb_data_limit_violation_count — 제한량 초과 횟수(1138 오류)
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';
| group_id | name | tempdb_data_space_kb | peak_tempdb_data_space_kb | total_tempdb_data_limit_violation_count |
| 2 | default | 0 | 720 | 0 |
4) tempdb를 사용하는 쿼리를 호출한 후 3)의 쿼리를 이용해서 사용량, 제한량 초과 횟수를 봅니다.
DROP TABLE IF EXISTS #t;
SELECT REPLICATE('A', 8000) AS c
INTO #t;
| group_id | name | tempdb_data_space_kb | peak_tempdb_data_space_kb | total_tempdb_data_limit_violation_count |
| 2 | default | 5120 | 5332 | 473 |
지정 용량 초과 재현을 위해 크기를 매우 작게(5MB) 한 뒤 SQLBigEyesHammer (또는 ostress나 기타 부하 테스트 도구)로 동시에 대량 호출해서 오류 발생 여부를 확인해 봤습니다.
아래 그림과 같이 오류가 발생합니다. 위 표와 같이 total_tempdb_data_limit_violation_count에도 누적이 됩니다.
[그림] tempdb 한도 초과 오류 1138
5) 설정 해제
ALTER WORKLOAD GROUP [default]
WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
2. 주의사항
- tempdb는 공유 DB이므로 초기 옵션, 설정 크기, 적용 workload group 등에 따라 영향의 정도가 달라질 수 있으므로 주의가 필요합니다.
- 변경하지 않은 한 tempdb의 초기 옵션은 크기 무제한입니다. 따라서 백분율 방식은 기본적으로 권장하지 않지만 사용한다면 주의해야 합니다.
- 리소스 관리자에 대한 충분한 학습, 테스트, 모니터링 준비를 해야 합니다.
- 기타 고려 사항, 주의, 보험 사례 등도 문서를 참조하세요.
마치면서
SQL Server 2025부터 드디어 Standard Edition에서도 리소스 관리자 기능이 지원됩니다, Standard Edition의 지원 하드웨어 크기가 커지는 만큼 사용자들을 위해 충분히 필요한 의사 결정이라 생각되었습니다. 리소스 관리자는 개인적으로 권장할 만큼 아주 좋은 기능입니다 다만 전체 리소스의 분할(수평, 수직)해서 제한하고 조장하는 기능인만큼 충분한 학습과 사전 테스트 및 실시간 모니터링과 관리가 필요함을 유념하시기 바랍니다.
감사합니다.
