SQL Server 2008 리소스 관리자 - REQUEST_MEMORY_GRANT_PERCENT

Memory Grant
쿼리 실행 시 특정 작업으로 인한 추가적인 메모리가 필요한 경우가 있습니다.
이러한 경우는, 큰 데이터에 대한 정렬이 필요한 경우 또는 hash 알고리즘을 사용해야 하는 경우입니다.
필요한 메모리의 크기는 통계 정보를 기반으로 산정됩니다.
하지만 통계는 항상 정확한 데이터를 보장하지 않기에 실제로 사용할 메모리보다 크게 메모리를 요구하거나 아니면 적게 요구할 수 있습니다.
크게 요구하는 경우는 메모리 리소스는 제한되어있기에, 다른 쿼리 또는 다른 개체에서 사용할 메모리 부족 현상으로 응답시간이 저하 될 수 있습니다.
작게 요구되는 경우는 관련 작업을 In-memory에서 처리 할 수 없기에 DISK IO를 유발시키는tempdb를 사용하게 됩니다.
당연히 tempdb를 사용하게 되면, 물리적인 IO가 발생하기에 메모리에서 처리되는 것보다 느릴 수 밖에 없습니다.

참고자료 BOL에서

REQUEST_MEMORY_GRANT_PERCENT: SQL Server 2005에서는 인덱스를 만들 때 향상된 성능을 위해 처음에 부여된 메모리보다 많은 작업 영역 메모리를 사용할 수 있습니다. 이러한 특별 처리는 SQL Server 2008의 리소스 관리자에서 지원됩니다. 그러나 초기 부여 및 추가 메모리 부여는 리소스 풀 및 작업 그룹 설정에 의해 제한됩니다.

 

쿼리당 최대 메모리 크기
이러한 작업에서 사용할 수 있는 메모리 크기는 어떻게 확인할 수 있을까?
Perfmon에서 Maximum Workspace Memory 카운터의 값으로 확인할 수 있습니다.
Maximum Workspace Memory는 전체 설정된 메모리의 75%를 사용할 수 있으며,
요청당 최대로 요구할 수 있는 크기는 SQL Server 2008의 경우 Maximum Workspace Memory의 25%입니다.
이 수치는 리소스 관리자의 기본 작업 그룹에 설정된 값이며, 이 값을 변경하게 되면 보다 많은 메모리를 사용 할 수 있습니다.
참고로 SQL Server 2005의 경우 x86은 20%, x64는 25%를 사용할 수 있으며, 사용자가 해당 수치를 변경 할 수 없습니다.
 

리소스 관리자의 REQUEST_MEMORY_GRANT_PERCENT 값을 변경하여 Sorting 쿼리 테스트
쿼리당 사용할 수 있는 Workspace Memory 값을 변경하여, 어떻게 달라지는지 확인해 보자.
 

/*
테스트 버전 정보
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
*/
  

첫 테스트는 기본 작업 그룹에서 테스트를 진행하는 경우입니다.
DMV를 통해서 기본 작업 그룹의 request_max_memory_grant_percent 를 확인
(25%로 설정됨)
select request_max_memory_grant_percent,* from sys.dm_resource_governor_workload_groups

대량의 데이터를 Sorting해야 하는 쿼리를 수행 시 perfmon의 Granted Workspace Memory카운터 값이 292MB정도를 사용했습니다.
Maximum Workspace Memory메모리 크기는 1229MB이기에 25%정도를 사용한 것을 알 수 있습니다.

   

두 번째로 작업 그룹의 REQUEST_MAX_MEMORY_GRANT_PERCENT를 100으로 변경하고 테스트를 진행해 보자.
ALTER WORKLOAD GROUP "default"
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 100)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
 

DMV를 통해서 기본 작업 그룹의 REQUEST_MAX_MEMORY_GRANT_PERCENT 를 확인.
(100%로 설정됨)
select request_max_memory_grant_percent,* from sys.dm_resource_governor_workload_groups

 

대량의 데이터를 Sorting해야 하는 쿼리를 수행 시 perfmon의 Granted Workspace Memory카운터 값이 1184MB정도를 사용했습니다.
당연히 많은 메모리를 사용함으로써 tempdb에 대한 IO횟수가 적어져서 전체적인 쿼리 응답시간은 두 번째 테스트가 더 좋은 결과를 보였습니다.

 

정리
이번 테스트를 통해서 Memory Grant를 하는 방법 및 요청 당 Workspace 메모리 설정을 변경하는 방법에 대해서 알아보았습니다.
OLTP환경보다DW환경과 같이 많은 정렬, 해쉬 작업이 빈번하게 사용되는 경우 적절한 설정을 통해서 보다 좋은 쿼리 응답시간을 가질 수 있을 것입니다.

By Hyok