[MSSQL] SQL Server의 메모리 관련 문제를 진단하는 1차적인 방법

시작하기에 앞서, 이 포스팅 하나만으로 SQL Server의 Memory Pressure (메모리 압박)을 진단하는 것은 섣부른 판단일 수 있다. 정확한 Memory Pressure를 진단하고 그것을 개선하는 프로세스는 상당히 디테일하게 진행이 되어야 하므로,

굉장히 긴 튜닝의 여정이 될 수 있다는 점을 미리 알아두자.

 

일단 Memory Alert 이 떴다든가, 유저가 쿼리 결과가 너무 늦는다거나와 같은 얘기를 들었을 때,

우리는 SQL Server 상에서 Memory Pressure가 일어났을 것이라는 짐작해볼 수 있다.

다음은 가장 먼저 시도해 볼 간단한 쿼리 3가지를 소개한다.

 

1. sys.dm_os_sys_memory

: OS level 에서의 설정된(?) 메모리 관련 정보를 반환하는 테이블

SQL Server는 기본적으로, OS level에 있는 external memory 조건과 하드웨어 기반의 물리적인 제한(물리적인 메모리 양 등)에 맞춰 제한되고 반응한다.

따라서 가장 첫 번째로, OS level 에서의 메모리 사용량이 어떻게 되는 지를 우선 체크해봐야 할 것이다.

SELECT [Total Physical Memory in MB] = total_physical_memory_kb/1024
	, [Physical Memory Available in MB] = available_physical_memory_kb/1024
	, system_memory_state_desc
FROM sys.dm_os_sys_memory

• total_physical_memory : OS가 사용할 수 있는 물리적인 메모리 전체 총량
• available_physical_memory : total_physical_memory 에서 가용 가능한 남은 용량
• system_memory_state_desc : system_high_memory_signal_state 와 system_low_memory_signal_state 의 값에 따라 문구가 정해지는 column
  • Available physical memory is high) system_high_memory_signal_state = 1 / system_low_memory_signal_state = 0 ⇒ 가용가능한 Physical Memory 양이 충분하다.
  • Available physical memory is low) system_high_memory_signal_state = 0 / system_low_memory_signal_state = 1 ⇒ 가용가능한 Physical Memory 양이 부족하다.
  • Physical memory usage is steady) system_high_memory_signal_state = 0 / system_low_memory_signal_state = 0 ⇒ 가용가능한 Physical Memory 가 충분하지도, 부족하지도 않게 적절하다.
  • Physical memory state is transitioning) system_high_memory_signal_state = 0 / system_low_memory_signal_state = 0 ⇒ 가용가능한 Physical Memory 가 급격하게 변화하는 순간에 이 쿼리가 수행되었다면 나올 수 있는 값이다.
  • 위 값에서 실제 메모리 문제로 인식되는 것은 ‘Available physical memory is low’ 라고 보면 될 것이다.

 

2. sys.dm_os_process_memory

: OS level 에서 프로세스에 할당된 메모리를 확인할 수 있는 테이블

SELECT [Physical Memory Used in MB] = physical_memory_in_use_kb/1024
	, [Physical Memory Low] = process_physical_memory_low
	, [Virtual Memory Low] = process_virtual_memory_low 
FROM sys.dm_os_process_memory

• physical_memory_in_use_kb : OS에 보고된, 프로세스에서 실제 사용하는 메모리 분량을 KB 단위로 나타낸 값
• process_physical_memory_low : 0 또는 1의 값으로, process 에서 사용 가능한 physical memory양이 부족할 때 1로 반응하는 값이다.
• process_virtual_memory_low : 0 또는 1의 값으로, process 에서 사용 가능한 virtual memory (Physical memory 가 아닌 영역, 페이지 파일 등) 의 양이 부족할 때 1로 반응하는 값이다.
  • process_physical_memory_low 값과 process_virtual_memory_low 값 중 둘 중 하나라도 1이 나온다면, Memory Pressure의 원인을 면밀히 조사해 볼 필요가 있다.

 

3. sys.dm_os_sys_info

: Computer(노드)에 대한 다양한 metric과 현재 가용 가능하거나 SQL Server에 의해 소비되고 있는 리소스에 대한 정보를 반환하는 테이블

SQL Server 2008 버전에서 사용)

SELECT [Physical Memory in MB] = physical_memory_in_bytes/1024/1024
	, [Virtual Memory in MB] = virtual_memory_in_bytes/1024/1024
	, [SQL Server Committed Memory in MB] = bpool_committed/1024
	, [SQL Server Target Committed Memory in MB] = bpool_commit_target/1024 
FROM sys.dm_os_sys_info

SQL Server 2012 이상 버전에서 사용)

SELECT [Physical Memory in MB] = physical_memory_kb/1024
	, [Virtual Memory in MB] = virtual_memory_kb/1024
	, [SQL Server Committed Memory in MB] = committed_kb/1024
	, [SQL Server Target Committed Memory in MB] = committed_target_kb/1024 
FROM sys.dm_os_sys_info

 

• physical_memory_kb | physical_memory_in_bytes : 해당 machine의 physical memory 총량
• virtual_memory_kb | virtual_memory_in_bytes : 해당 machine의 virtual memory 총량
• committed_kb | bpool_committed : SQL Server memory manger에 의해 커밋된(committed) 메모리 양
• committed_target_kb | bpool_commit_target : SQL Server memory manager에 의해 소비될 수 있는 메모리 총량으로, 예상치를 의미한다. 이 값에 따라 SQL Server memory manager가 committed 메모리 양을 늘릴지 또는 축소할 지를 결정한다.
  • committed_target_kb > committed_kb
    ⇒ SQL Server Memory Manager가 추가적인 메모리를 더 잡으려고 할 것이다. Memory Pressure 가 없이 아직 여유 있는 상태라고 봐도 무방하다.
  • committed_target_kb ≤ committed_kb
    ⇒ SQL Server Memory Manager가 현재 커밋된 메모리를 줄일려고 할 것이므로, 현재 가용가능한 Memory 양을 봐야 한다. 만약 (committed_kb - committed_target_kb) 값이 가용 가능한 Memory 양보다 크다면, Memory Pressure 가 일어나고 있다고 볼 수 있을 것이다.

 

만일 위 3개의 쿼리 수행 결과, 좋지 않은 징후를 발견했다면, 현재 서버가 Memory Pressure에

있다고 보고 면밀히 조사하는 것이 좋을 것이다.