메모리 병목 현상 해결 (버퍼 풀 / 논버퍼풀 메모리)

 

버퍼풀 논버퍼 풀 메모리는 우리가 흔히 알고 있는 페이징 풀 / 비페이징 풀 메모리랑은 다른거다.

페이징 풀 , 비페이징 풀은 참고로 다음과 같다.

 

• 페이징 풀, 비페이징 풀

	정의	개체란?
Paged pool	특정 개체들에 대한 시스템 메모리 영역	이들 개체들은 사용되지 않을 때 메모리상에 존재하던 자신의 (개체) 데이터를 디스크로 이동하여 기록할 수 있는 성질을 가진 개체들.
Nonpaged pool	특정 개체들에 대한 시스템 메모리 영역	이들 개체들에 대한 데이터는 디스크로 이동될 수 없는 성질의 개체들. 많은 양의 비페이징 풀 메모리를 요구하는 프로세스들을 주목할 필요가 있는데 서버상에 충분한 여유 메모리 공간이 없다면 이러한 프로세스들이 높은 수준의 페이지 폴트를 발생시키는 원인이 될 수 있다.

http://blog.daum.net/99lib/9

 

버퍼풀 메모리에 대해서 아무리 뒤져봐도 웹에 잘 안 나오길래,,

일단은 구글링해서 버퍼풀 메모리 랑 sql server 겹쳐 나오는 글을 스크랩 해 본다.

 

 

http://www.dbguide.net/db.db?cmd=view&boardUid=13978&boardConfigUid=9&categoryUid=216&boardIdx=75&boardStep=1

 

메모리 병목

이번 절은 특히 적은 메모리 구성에서 메모리에 대한 진단 분석, 메모리 관련 오류, 해당 원인 및 해결 방안 등을 다룹니다.

배경

서로 다른 메모리 리소스이지만 그냥 메모리라는 용어를 사용하는 것이 일반적입니다. 메모리 리소스의 유형이 여러가지이므로, 실제론 어떤 메모리 리소스를 말하는지 이해하고 구분하는 것이 중요합니다.

가상 주소 공간과 실제 메모리

마이크로소프트 Windows, 각 프로세스 단위로 가상 주소 공간(VAS)을 가집니로 VAS 크기를 구성합니다. VAS의 크기는 아키텍처(32 혹은 64비트)와 운영 시스템에 따라 결정됩니다. 메모리 문제 해결 측면에서 가상 메모리는 고갈될 수 있는 메모리 리소스이며 64비트 플랫폼에서 실제 메모리가 가용한 상황에서도 응용 프로그램이 메모리 부족을 경험할 수 있다는점을 이해하는 것이 중요합니다.

가상 주소 공간에 대한 추가 정보는 SQL Server 온라인 설명서의 “프로세스 주소 공간”에 대한 항목과 MSDN 기사 Virtual Address Space (영문) (http://msdn2.microsoft.com/en-us/library/aa366912.aspx[역주9])을 참조하십시오.

Address Windowing Extensions (AWE)과SQL Server

Address Windowing Extensions (AWE)는 32비트 응용 프로그램이 32비트 주소 한계를 초과하는 실제 메모리를 사용하도록 지원하는 API입니다. AWE 메커니즘은 기술적으로 64비트 플랫폼에서 필요치 않지만 계속 지원하고 있습니다. 64비트 플랫폼에서AWE 메커니즘을 통해서 할당된 메모리 페이지를 잠긴 페이지(locked page)라고 합니다.

32비트와 64비트 양쪽 플랫폼에서 AWE 메커니즘을 통해 할당된 메모리는 페이징되지 않습니다. 이것이 응용 프로그램에 이득이될 수 있습니다. (이 점이 64비트 플랫폼에서 AWE 메커니즘을 사용하는 이유 중의 하나입니다.) 그러나 이러한 동작이 시스템과 다른 응용 프로그램이 사용할 RAM 용량에 부정적인 영향을 줍니다. 그런 이유로, AWE를 사용하기 위해서는 SQL Server를 실행하는 Windows 계정에 대해 메모리 페이지 잠그기(Lock Pages in Memeory) 사용 권한이 부여되어야 합니다.

문제 해결 측면에서 본다면 SQL Server 버퍼 풀이 AWE 매핑 메모리를 사용한다는 점입니다; 그러나, 데이터베이스(해시) 페이지만이 AWE를 통해 할당된 메모리의 모든 이득을 얻을 수 있습니다. AWE 메커니즘을 통해 할당된 메모리 정보는 작업 관리자 혹은 Process: Private Bytes 성능 카운터를 통해서는 제공되지 않습니다. 이러한 정보를 얻기 위해서는 SQL Server 특정 카운터나 동적 관리 뷰(DMV)를 사용해야 합니다.

AWE 매핑 메모리에 대한 자세한 정보는 SQL Server 온라인 설명서의 목차에서 “큰 데이터베이스의 메모리 관리”, “메모리 아키텍처” 항목과 MSDN 기사 Large Memory Support (영문) (http://msdn2.microsoft.com/en-us/library/aa366718.aspx[역주10])를 참조하십시오.

다음 표는 SQL Server 2005의 각 구성별 지원 가능한 최대 메모리 옵션을 요약한 것입니다. (SQL Server 혹은 Windows 각 에디션별로 지원 메모리 크기에 대한 제한이 있음을 참고하십시오.)

표 1

구성	VAS	최대 실제 메모리	AWE/잠긴 메모리 지원
Native 32-bit on 32-bit OS	2 GB	64 GB	Yes
with /3GB boot parameter1	3 GB	16 GB	Yes
32-bit on x64 OS (WOW)	4 GB	64 GB	Yes
32-bit on IA64 OS (WOW)	2 GB	2 GB	No
Native 64-bit on x64 OS	8 terabyte	1 terabyte	Yes
Native 64-bit on IA64 OS	7 terabyte	1 terabyte	Yes

1 boot 매개변수에 대한 상세 정보는 SQL Server 온라인 설명서 목차의 “AWE 사용”을 참조하십시오.

메모리 부족

메모리 부족이란 가용 메모리가 제한된 상태를 말합니다. SQL Server가 메모리 부족 상태에서 실행되고 있음을 확인하는 것은 SQL Server는 메모리 부족 유형에 따라 다른 응답의 근본 원인을 요약합니다.

표 2

근본 원인

부족	외부	최대 실제 메모리
실제	실제 메모리(RAM)가 많지 않음. 이로 인해 현재 실행 중인 프로세스의 작업 메모리가 감소하게 되고 결국 성능 저하를 유발합니다. SQL Server는 이러한 상태를 감지하고 구성 옵션 설정 상태에 따라 버퍼 풀에 대상 메모리 크기를 줄이고 내부 캐시를 해제할 수 있습니다.	SQL Server는 내부 구성 요소간의 메모리 재분배를 유발하는 내부의 높은 메모리 소비를 감지합니다. 내부 메모리 부족 상태의 원인은: 외부 메모리 부족 상태를 반영. 메모리 설정 변경(예, “최대 서버 메모리”). 내부 구성 요소간의 메모리 분배 변경(버퍼 풀에서 높은 비율의 예약 공간과 stolen page로 인해).
가상	시스템 페이지 파일 공간이 부족한 상태로 실행. 이는 현재 할당된 메모리가 페이징될 수 없도록 만들고, 시스템에서 메모리 할당이 실패하는 원인이 됩니다. 이러한 상태가 결국 시스템 전체 응답을 매우 느려지게 만들거나 정지 상태로 만들 수 있습니다.	조각(많은 VAS가 가용하지만 작은 블록 단위), 혹은 메모리 소비(직접 할당, SQL Server VAS로 DLL 로드, 대량의 쓰레드)로 인한 낮은 VAS 상태로 실행. SQL Server가 이러한 상태를 감지하고 VAS에 예약된 영역 감소, 버퍼 풀의 대상 메모리 감소, 그리고 캐시를 축소 시킬 수 있습니다.

Windows는 실제 메모리가 높은 혹은 낮은 상태로 실행하면 알림을 주는 메커니즘를 가지고 있습니다. SQL Server는 이러한 메커니즘을 사용해서 메모리를 관리합니다.

표 3은 경우에 따른 일반적인 메모리 문제 해결 단계를 설명합니다.

표 3

표3

부족	외부	최대 실제 메모리
실제	실제 메모리(RAM)가 많지 않음. 이로 인해 현재 실행 중인 프로세스의 작업 메모리가 감소하게 되고 결국 성능 저하를 유발합니다. SQL Server는 이러한 상태를 감지하고 구성 옵션 설정 상태에 따라 버퍼 풀에 대상 메모리 크기를 줄이고 내부 캐시를 해제할 수 있습니다.	SQL Server는 내부 구성 요소간의 메모리 재분배를 유발하는 내부의 높은 메모리 소비를 감지합니다. 내부 메모리 부족 상태의 원인은: 외부 메모리 부족 상태를 반영. 메모리 설정 변경(예, “최대 서버 메모리”).
가상	시스템 페이지 파일 공간이 부족한 상태로 실행. 이는 현재 할당된 메모리가 페이징될 수 없도록 만들고, 시스템에서 메모리 할당이 실패하는 원인이 됩니다. 이러한 상태가 결국 시스템 전체 응답을 매우 느려지게 만들거나 정지 상태로 만들 수 있습니다.	조각(많은 VAS가 가용하지만 작은 블록 단위), 혹은 메모리 소비(직접 할당, SQL Server VAS로 DLL 로드, 대량의 쓰레드)로 인한 낮은 VAS 상태로 실행. SQL Server가 이러한 상태를 감지하고 VAS에 예약된 영역 감소, 버퍼 풀의 대상 메모리 감소, 그리고 캐시를 축소 시킬 수 있습니다.

도구

다음 도구와 정보들을 이용해서 문제를 해결할 수 있습니다.

• 메모리 관련 DMV
• DBCC MEMORYSTATUS 명령
• 성능 카운터: SQL Server 특정 개체에 대한 성능 모니터 혹은 DMV
• Task Manager 작업 관리자
• 이벤트 뷰어: 응용 프로그램 로그, 시스템 로그

메모리 부족 발견

메모리 부족 그 자체가 문제를 나타내지는 않습니다. 메모리 부족이란 서버의 메모리가 필요하지만 충분하지는 않은 상태로 나중에 메모리 오류가 발생할 수 있음을 말합니다. 메모리가 부족한 상태에서 작업하는 것이 서버에 정상적인 운영 상태일 수 있습니다. 그러나, 메모리 부족으로 인한 증상은 서버가 자신의 용량에 근접한 상태로 실행 중이며 잠재적인 메모리 부족 오류가 있음을 나타내는 것이기도 합니다. 정상적으로 운영 중인 서버의 경우엔 이러한 정보들이 차후 메모리 부족에 대한 판단 근거의 기준선으로 제공될 수 있습니다.

외부 실제 메모리 부족

작업 관리자를 열고 성능 탭에서 사용 가능 실제 메모리 항목의 사용 가능값을 검사합니다. 사용 가능 메모리 양이 작다면, 외부 메모리가 부족한 것입니다. 정확한 값은 여러가지 요소에 따라 달라지지만 그 값이 50-100MB로 떨어진 경우에 조사를 시작합니다. 이 값이 10MB이하로 떨어지면 분명히 외부 메모리가 부족한 상태입니다.

동일한 정보를 시스템 모니터의 Memory: Available Bytes 카운터를 통해서 얻을 수 있습니다.

외부 메모리 부족이 존재하고 메모리 관련 오류를 만난다면 시스템의 실제 메모리를 소비하는 주범이 누군인지 찾아내야 합니다. Process: Working Set 성능 카운터나 작업 관리자의 프로세스 탭에서 메모리 사용 열을 살펴보고 그 주범을 찾아냅니다.

다음 카운터들을 요약함으로써 시스템의 실제 메모리 전체 사용량을 대략적으로 구할 수 있습니다.

• 각 프로세스별 Process 개체, Working Set 카운터
• Memory 개체
• 시스템 작업 집합(working set)에 대한 Cache Bytes 카운터
• 비페이지 풀 크기에 대한 Pool Nonpaged Bytes 카운터
• Available Bytes (작업 관리자의 사용 가능 값과 동일)

외부 메모리 부족이의 가상 메모리 크기가 작업 집합(Process: Working Set 혹은 작업 관리자 사용 가능) 크기에 가깝습니다. 이는 메모리가 페이징되지 않음을 의미합니다.

작업 관리자의 메모리 사용 열 그리고 해당하는 성능 카운터는 AWE를 통해서 할당된 메모리는 계산하지 않음을 참고하십시오.

다음처럼 sys.dm_os_memory_clerks DMV를 사용해서 AWE 메커니즘을 통해서 할당된 SQL Server 메모리양을 알아낼 수 있습니다.

select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks

SQL Server는 현재 AWE 옵션이 활성화된 경우에 버퍼 풀 클럭(type = ‘MEMORYCLERK_SQLBUFFERPOOL’)에 대해서만 이 메커니즘을 사용한다는 점을 참고하십시오.

가능한 실제 메모리를 소비하는 주범을 식별하고 제거함으로써 외부 메모리 부족을 제거합니다, 혹은 더 많은 메모리를 추가함으로써 메모리 관련 문제를 해결할 수 있습니다.

외부 가상 메모리 부족
페이지 파일이 현재 메모리 할당을 수용할만한 충분한 공간이 있는지를 판단해야 합니다. 이를 위해, 작업 관리자를 열고 성능 탭에서 할당된 메모리 항목를 검사합니다. 전체가 한도에 가깝다면, 페이지 파일 공간이 부족할 가능성이 있습니다. 한도는 페이지 파일 공간을 확장하지 않고 할당될 수 있는 최대 메모리 크기를 나타냅니다. 작업 관리자의 할당된 메모리 전체는 페이지 파일 사용에 대한 잠재성이며, 실제 사용을 나타내지 않습니다. 실제 사용은 실제 메모리 부족 상태에서 증가합니다.

동일한 정보를 Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak 카운터에서 얻을 수 있습니다.

Process: Private Bytes카운터에서 Process: Working Set값을??적으로 예측할 수 있습니다.

Paging File: %Usage Peak(혹은 할당된 메모리 최고)가 높으면, 페이지 파일 증가 혹은 “running low on virtual memory” 알림을 나타내는 이벤트가 있는지 시스템 이벤트 로그를 확인합니다. 페이지 파일 크기를 증가시킵니다. 높은 Paging File: %Usage는 실제 메모리가 과도하게 소비되었으며 외부 실제 메모리의 부족 상태를 함께 고려해야 함을 나타냅니다.

내부 실제 메모리 부부족

내부 메모리 부족은 SQL Server 자체 설정에서 비롯되므로, 그 논리적인 단계는 SQL Server 내부의 메모리 분배를 살펴보고 버퍼 분배에 어떤 이상이 없는지를 검사하는 것입니다. 정상적으로는 버퍼 풀이SQL Server에 의해서 할당된 메모리의 대부분을 차지 위해 DBCC MEMORYSTATUS 출력을 살펴봅니다. Buffer Counts 부분에서 Target값을 찾습니다. 다음은 서버가 정상적인 부하를 가진 상태에서 DBCC MEMORYSTATUS 출력의 일부를 보여줍니다.

Buffer Counts Buffers
------------------------------ --------------------
Committed 201120
Target 201120
Hashed 166517
Reserved Potential 143388
Stolen Potential 173556
External Reservation 0
Min Free 256
Visible 201120
Available Paging File 460640

Target은 페이징 없이 할당될 수 있는 8KB 페이지 수로 SQL Server에 의해서 계산됩니다. Target은 Windows로부터 낮은/높은 메모리 알림에 응답하며 정기적으로 다시 계산됩니다. 정상적으로 부하를 가진 서버에서 target 페이지의 수가 감소되는 것은 외부 실제 메모리 부족을 나타낼 수 있습니다.

SQL Server가 많은 메모리를 소비한다면( Process: Private Bytes 혹은 작업 관리자의 메모리 사용 열에 의해서 판단할 수 있는), Target 카운트가 메모리의 상당 부분을 차지하는지 확인합니다. AWE 옵션이 활성화 상태라면 AWE 로 할당된 메모리는 sys.dm_os_memory_clerks나 DBCC MEMORYSTATUS 출력에서 계산해야 합니다.

위 예제(AWE는 비활성화)에서 Target * 8KB = 1.53GB라고 가정합니다, Process: Private Bytes가 대략 1.62GB라면 SQL Server가 소비하는 메모리의 94%가 버퍼 풀 target에 해당합니다. 서버가 정상적인 부하를 포함한 상태가 아니라면, 정상적인 상황일 때는 Target이 Process: Private Bytes성능 카운터 값을 초과할 수 있습니다.

Target이 낮은 반면, 서버의 Process: Private Bytes 혹은 작업 관리자의 메모리 사용열이 높다면, 이는 버퍼 풀 이외에 다른 메모리 구성 요소로부터 내부 메모리 부족을 나타낼 수 있습니다. SQL Server 프로세스 영역으로 로드되는 COM 개체, 연결된 서버, 확장 저장 프로시저, SQLCLR 및 기타 구성 요소들이 버퍼 풀 외부에서 메모리를 소비하는 메모리 구성 요소들입니다. 특히 이 구성 요소들이 SQL Server 메모리 인터페이스를 사용하지 않을 경우 소비되는 메모리를 추적하기가 어렵습니다.

SQL Server 메모리 관리 메커니즘을 사용하는 구성 요소들은 작은 메모리 할당을 위해 버퍼 풀을 사용합니다. 이 구성 요소들이 8KB이상을 할당할 경우, 다중 페이지 할당자(multi-page allocator) 인터페이스를 통해서 버퍼 풀 외부의 메모리를 사용합니다.

다음은 다중 페이지 할당자를 통해서 소비된 메모리 양을 확인할 수 있는 빠른 방법입니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

-- 다중 페이지 할당자 인터페이스를 통해서 할당된 메모리 크기
select sum(multi_pages_kb)
from sys.dm_os_memory_clerks

다음 코드로 다중 페이지 할당자를 통해서 할당된 메모리에 대한 분배 정보를 상세하게 알 수 있습니다:

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
type
------------------------------------------ ---------
MEMORYCLERK_SQLSTORENG 56
MEMORYCLERK_SQLOPTIMIZER 48
MEMORYCLERK_SQLGENERAL 2176
MEMORYCLERK_SQLBUFFERPOOL 536
MEMORYCLERK_SOSNODE 16288
CACHESTORE_STACKFRAMES 16
MEMORYCLERK_SQLSERVICEBROKER 192
MEMORYCLERK_SNI 32

다중 페이지 할당자를 통해 할당된 메모리 양이 크다면(100-200MB 이상), 추가 조사가 필요합니다.

다중 페이지 할당자를 통해서 할당된 대량 메모리가 보인다면, 서버 구성을 확인하고 이전 혹은 다음 쿼리를 사용해서 메모리 소비 주범이 어떤 구성 요소로는SQL Server 메모리의 대부분을 차지한다면, 이전(외부 실제 메모리 부족)에 소개한 외부 메모리 부족에 대한 원인을 찾아보거나 서버 메모리 구성 값을 확인해 봅니다.

최대 서? target 값과 비교해 봐야 합니다. 최대 서버 메모리는 버퍼 풀에 의해서 소비되는 최대 메모리를 제한합니다, 그러나 서버 전체적으로는 더 많은 메모리를 소비할 수 있습니다. 최소 서버 메모리는 버퍼 풀 메모리가 그 이하로는 내려가지 않도록 합니다. Target이 최소 서버 메모리 설정보다 작고 서버가 정상적으로 부하를 가진 상황이라면 이는 서버가 외부 메모리 부족 상태이며 옵션에 지정된 메모리 양만큼 확보할 수 없음을 나타냅니다. 더불어 위에서 언급한 내부 구성 요소의 메모리 부족도 의미하는 것입니다. Target 카운트는 최대 서버 메모리 옵션설정을 초과할 수 없습니다.

우선, DBCC MEMORYSTATUS 출력에서 stolen 페이지 카운트를 검사합니다.

Buffer Distribution111111111111111 Buffers
------------------------------ -----------
Stolen 1111111111111111111111111132871
Free 11111111111111111111111111117845
Cached111111111111111111111111 1513
Database (clean) 1111111111111111148864
Database (dirty)111111111111111111 259
I/O111111111111111111111111111111110
Latched 1111111111111111111111111110

stolen 페이지가 target에 비례해서 높은 백분율(75-80% 이상)을 가진다면 내부 메모리 부족을 나타냅니다.

서버 구성 요소별 메모리 할당에 대한 자세한 정보는 sys.dm_os_memory_clerks DMV를 참조합니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

-- 버퍼풀 이외의 구성 요소에의해 소비된 메모리 계산
-- single_pages_kb는BPool에 해당하므로 제외
-- BPool은 다음 쿼리에서 계산
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'
-- BPool에 의해 소비된 메모리 계산
-- 현재 AWE는 BPool에서만 사용
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'

각 구성 요소별 상세 정보는 다음 쿼리에서 구할 수 있습니다. (버퍼 풀과 그 이외에 구성 요소들 모두를 포함합니다.)

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- 정상적인 상황에서 버퍼 풀의committed memory는 대량 메모리 소비자를 나타내므로 계산에서 제외
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): ' + CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/
(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb
+ (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN
virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab

버퍼 풀은 단일 페이지 할당자를 통해서 다른 구성 요소에 메모리를 제공하므로 이전 쿼리에서는 다르게 처리합니다. 다음 쿼리를 사용해서 버퍼 풀 페이지(단?? 알 수 있습니다.

-- Bpool의 메모리 소비 상위 10위
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc

일반적으로는 내부 구성 요소가 소비하는 메모리에 대한 통제권이 없습니다. 그러나, 어떤 구성 요소가 메모리 소비 주범인지를 판단하는 것은 메모리 문제에 대한 조사 범위를 좁히는데 도움을 줄 것입니다.

시스템 모니터(Perfmon)

메모리가 부족한지 판단하기 위해서 다음 성능 카운터를 확인할 수 있습니다. (상세한 설명은 SQL Server 온라인 설명서를 참조하십시오.):

SQL Server: Buffer Manager개체

• Buffer cache hit ratio 가 작은 경우
• Page life expectancy 가 작은 경우
• Checkpoint pages/sec가 높은 경우
• Lazy writes/sec 가 높은 경우

메모리 부족과 I/O 오버헤드는 일반적으로 서로 관련된 병목입니다. 본 문서의 I/O 병목를 참조하십시오.

캐시와 메모리 부족

외부 및 내부 메모리 부족을 조사하는 또 다른 방법은 메모리 캐시 동작을 조사하는 것입니다.

SQL Server 2005의 내부 구현에 있어서, SQL Server 2000과 비교되는 차이점의 하나는 바로 캐싱(caching) 프레임워크의 단일화입니다. 프레임워크는 캐시에서 사용 빈도가 적은 항목들을 제거하기위해 클럭 알고리즘을 구현합니다. 현재는 내부 클럭 포인터와 외부 클럭 포인터로 두 개의 클럭 포인터[역주11] (clock hands)를 가집니다.

내부 클럭 포인터는 다른 캐시에 비례하여 캐시 크기를 조절합니다. 해당 캐시가 한계점에 다달았음을 프레임워크가 예상하게 되면 이동을 시작합니다.

외부 클럭 포인트는 SQL Server 메모리가 부족한 상태가 되면 이동을 시작합니다. 외부 클럭 포인터의 이동은 외부 및 내부 메모리의 부족이 그 원인이 될 수 있습니다. 내부 및 외부 클럭 포인터의 이동을 내부 및 외부 메모리 부족과같이 클럭 포인터 이동에 관한 정보는 sys.dm_os_memory_cache_clock_hands DMV를 통해서 알 수 있습니다. 각 캐시 항목은 내부 및 외부 클럭 포힌터별로 하나의 행을 가집니다. rounds_count와 removed_all_rounds_count 값이 증가한다면 서버의 내부/외부 메모리가 부족한 상태입니다.

select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb
as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch
on (cc.cache_address =ch.cache_address)
/*
--포인터가 이동된 캐시만을 볼 경우 이 블록 주석을 제거
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc

다음과 같이 sys.dm_os_cache_counters DMV와 조인해서 캐시 크기와 같은 추가 정보들을 얻을 수 있습니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0

USERSTORE 항목에 대해서는 사용 중인 페이지 정보가 출력되지 않으며 대신 NULL 이 됨을 참고하십시오.

링 버퍼

메모리 정보 진단을 위한 의미 있는 크기는 sys.dm_os_ring_buffers DMV에서 얻을 수 있습니다. 각 링 버퍼는 특정 유형에 마지막 알림 정보를 가지는 하나의 record를 유지합니다.

RING_BUFFER_RESOURCE_MONITOR

메모리 상태 변경을 식별하기 위해 리소스 모니터 알림 정보를 이용할 수 있습니다. 내부적으로 서로 다른 메모리 부족을 모니터하는 프레임워크를 가지고 있습니다. 메모리 상태가 변경되면, 리소스 모니터가 알림을 생성합니다. 이 알림은 메모리 상태에 따른 메모리 사용 조정을 위해서 해당 구성 요소가 내부적으로 사용하며 다음 코드와 같이 sys.dm_os_ring_buffers DMV를 통해서 사용자에 보여집니다.

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

record는 다음과 같습니다:

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

RESOURCE_MEMPHYSICAL_LOW<
/Notification>
2
0
1646380
432388
0
0
26592
17128
17624
50
3833132
3240228<
/AvailablePhysicalMemory>
5732340
5057100
2097024<
/TotalVirtualAddressSpace>
336760
0

이 레코드에서 서버가 실제 메모리 부족 알림을 받았음을 알 수 있습니다. 더불어 킬로바이트단위의 메모리 크기도 알 수 있습니다. SQL Server의 XML 기능을 활용해서 이러한 정보들을 쿼리할 수 있습니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int') as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int') as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR')
as R(x) order by
[Time Stamp] desc

메모리 부족 알림을 받았으므로, 버퍼 풀 target 크기를 다시 계산합니다. target 카운트는 최소 서버 메모리와 최대 서버 메모리 옵션에 의해서 지정된 범위 내에 있음을 참고하십시오. 버퍼 풀을 위해 새로 할당된 target 크기가 현재 크기보다 작은 경우, 버퍼 풀은 외부 실제 메모리 부족이 없어질 때까지 축소합니다. 참고로 SQL Server 2000에서는 AWE가 활성화된 경우 실제 메모리 부족에 반응하지 못했습니다.

RING_BUFFER_OOM

이 링 버퍼는 다음 예제 코드처럼 서버 메모리 부족(out-of-memory)를 나타내는 레코드를 포함합니다.

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'

record는 다음과 같습니다:

FAIL_VIRTUAL_COMMIT
4096

이 레코드는 실패한 작업의 유형(커밋, 예약, 혹은 페이지 할당)과 요청된 메모리 크기를 알려줍니다.

RING_BUFFER_MEMORY_BROKER 와 내부 메모리 부족

내부 메모리 부족이 감지되면, 메모리 할당 원본이 되는 버퍼 풀을 사용하는 구성 요소의 메모리 부족 알림(low memory notification)이 설정됩니다. 이는 캐시와 또 다른 구성 요소로부터 페이지 반환을 허용하는 것입니다.

내부 메모리 부족은 최대 서버 메모리 옵션을 조정하는 경우 혹은 stolen 페이지가 버퍼 풀의 80%를 초과하는 경우에도 발생할 수 있습니다.

내부 메모리 부족 알림(‘Shrink’)은, 다음 예제 코드와 같이 메모리 브로커(memory broker) 링 버퍼를 쿼리함으로써 알 수 있습니다.

select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)') as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
as R(x)
order by
[Time Stamp] desc
RING_BUFFER_BUFFER_POOL

이 링 버퍼는 버퍼 풀의 메모리 부족 상태를 포함한 중대한 버퍼 풀 실패를 나타내는 레코드를 포함합니다.

select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

record는 다음과 같습니다:

< BufferPoolFailure id="FAIL_OOM">
84344
84350
20
20345
64001
64001

이 레코드는 실패 내용(FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS)과 그 시점을 알려줍니다.

내부 가상 메모리 부족

VAS(가상 주소 공간) 소비는 sys.dm_os_virtual_address_dump DMV를 사용해서 추적할 수 있습니다. 다음과 같은 뷰를 사용해서 VAS 요약 정보를 구할 수 있습니다.

참고: 다음 표에 기술한 코드 중 일부는 가독성을 위해서 여러 줄에 표시되었습니다. 실제로는 한 줄로 입력합니다.

-- 가상 주소 공간 요약 뷰
-- SQL Server 영역에 대한 목록을 생성
-- 예약되거SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- 기준 주소 포인트가0인 경우를 제외하고,
--- 할당 기준 주소별로 영역 크기 집계
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- 기준 주소 포인트가0인 경우, 그룹화없이 개별 반환
SELECT
CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size

다음 쿼리로 VAS 상태를 평가할 수 있습니다.

-- 모든free 영역 가용 메모리 구하기
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
-- 최대 가용 메모리 크기 구하기
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0

최대 가용 메모리 크기가 4MB보다 작다면 VAS 부족을 경험할 수 있습니다. SQL Server 2005는 VAS 부족을 모니터하고 이에 반응합니다. SQL Server 2000은 VAS 부족 상태를 능동적으로 모니터하지 않지만, 가상 메모리 부족 오류가 발생할 때 캐시를 비우는 동작을 합니다.

메모리 오류에 대한 일반적인 문제 해결 단계
다음 목록은 메모리 오류 문제를 해결하는데 도움을 주는 일반적인 단계를 개략적으로 소개합니다.

1. 서버가 외부 메모리 부족 상태인지 확인합니다. 존재한다면 먼저 메 계속 발생하는지 확인합니다.
2. SQL Server: Buffer Manager, SQL Server: Memory Manger의 성능 모니터 카운터를 수집합니다.
3. 메모리 구성 매개 변수(sp_configure), 쿼리당 최소 메모리, 최소/최대 서버 메모리, awe enabled, 그리고 메모리 페이지 잠그기 사용 권한을 확인합니다. 잘못된 설정을 찾고 필요에 따라 수정합니다. SQL Server 2005에서 증가된 메모리 요구 사항을 검토합니다.
4. sp_configure 매개 변수 중 간적접으로 서버에 영향을 미칠 수 있는 비기본값이 있는지 확인합니다.
5. 내부 메모리 부족 여부를 확인합니다.
6. 메모리 오류 메시지를 만나면 DBCC MEMORYSTATUS 출력을 살펴봅니다.
7. 작업 부하(동시 세션 수, 실행 쿼리 수)를 확인합니다.

메모리 오류
701 - 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.

원인

가장 일반적인 서버 메모리 부족 오류입니다. 메모리 할당 실패를 나타내며 현재 작업 부하 상에서 메모리 한계를 벗어난 경우를 포함해서 다양한 원인들이 존재합니다. SQL Server 2005 메모리 요구 사항 증가와 특정 구성 설정( 최대 서버 메모리 옵션 같은)으로 사용자는 SQL Server 2000 보다 이 오류를 더 많이 만날 수 있습니다. 일반적으로 실패한 트랜잭션은 이 오류의 원인이 아닙니다.

문제 해결

오류의 일관성이나 반복성(동일 상태) 혹은 임시 발생(서로 다른 상태로 불특정 시점에 나타남) 여부에 관계 없이, 오류??. 이 오류가 발생하면 진단 분석 쿼리 또한 실패할 가능성이 있습니다. 외부 요소 평가에서부터 조사를 시작하십시오. 메모리 오류에 대한 일반적인 문제 해결 단계를 따릅니다.

해결 방안: 외부 메모리 부족 문제 제거. 최대 서버 메모리 설정 증가. 다음 명령 중 하나를 사용해서 캐시 비우기: DBCC FREESYSTEMCACHE, DBCC FREESESSIONCACHE, 혹은DBCC FREEPROCCACHE. 문제가 계속되면, 작업 부하를 줄입니다.

802 - 버퍼 풀에 사용할 수 있는 메모리가 부족합니다.

원인 이 오류가 반드시 메모리 부족 상태를 나타내는 것은 아닙니다. 버퍼 풀 메모리가 사용 중임을 나타냅니다. SQL Server 2005에서는 드물게 발생될 것입니다.

문제 해결

701 오류에서 소개한 일반적인 문제 해결 단계의 권장 사항을 따릅니다.

8628 - 쿼리 최적화를 기다리는 중 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.

원인

이 오류는 쿼리 컴파일 작업을 완료하는데 필요한 메모리 확보에 실패한 경우를 나타냅니다. 쿼리는 구문 해석, 대수 연산 처리, 그리고 최적화를 포함한 컴파일 처리 리소스 경합이 일어납니다. 쿼리가 리소스를 기다리는 동안 정의된 시간을 초과하면 오류가 반환됩니다. 이 오류의 주된 원인은 서버에서 대량의 쿼리 컴파일이 발생하는 경우입니다.

문제 해결

1. 서버 메모리 소비로 인한 영향인지 알아보기 위해 일반적인 문제 해결 단계를 따릅니다.

2. 작업 부하를 검사합니다. 각 구성 요소별 소비되는 메모리 양을 확인합니다. (본 문서의 내부 실제 메모리 부족 참조)

3. DBCC MEMORYSTATUS 출력에서 각 게이트웨이 상의 대기자(waiters) 수를 점검합니다. (이 정보는 많은 메모리를 소비하는 다른 쿼리가 실행 중인지를 알려줍니다).

Small Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6개 행 적용됨)
Medium Gateway Value
------------------------------ --------------------
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
(5 개 행 적용됨)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8

4. 할 수 있다면 작업 부하를 줄입니다.

8645 - 쿼리를 실행하기 위해 메모리 리소스를 기다리는 중 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.[역주12]

원인

이 오류는 메모리 집중적인 쿼리가 동시에 많이 실행되고 있음을 나타냅니다. 정렬(ORDER BY)과 조인을 사용하는 쿼리는 실행하는 동안 많은 메모리를 소비할 수 있습니다. 최대 병렬 처리 수준 설정 값이 높거나 쿼리가 정렬되지 않은 인덱스를 가진 분할 테이블 상에서 동작하는 경우 요구되는 메모리 양은 상당히 증가합니다. 쿼리에서 필요한 메모리 리소스를 기다리는 동안 정의된 시간을 초과하면(기본값은 sp_configure ‘query wait’ 설정 값이나 쿼리 예측 비용의 25배) 이 오류를 받게 됩니다. 일반적으로 이 오류를 받은 쿼리는 메모리를 소비하는 쿼리가 아닙니다.

문제 해결

1. 서버 메모리 상태를 평가하는 일반적인 단계를 따릅니다.
2. 의심되는 쿼리를 확인합니다: 분할 테이블을 사용하는 많은 수의 쿼리가 있다면, 정렬되지 않은 인덱스를 사용하는지 점검합니다, 조인 혹은 정렬를 포함한 쿼리가 많은지 점검합니다.
3. sp_configure 매개 변수인 최대 병렬 처리 수준과 쿼리 당 최소 메모리를 점검합니다. 최대 병렬 처리 수준을 낮추어 보거나 쿼리 당 최소 메모리가 높게 설정되어 있지 않는지 확인합니다. 높은 값이라면, 작은 쿼리 조차도 지정된 양의 메모리를 요구하게 됩니다.
4. 쿼리가 RESOURCE_SEMAPHORE로 대기 중인지 알아봅니다, 이는 이후에 차단에서 소개됩니다.

8651 - 최소 쿼리 메모리를 사용할 수 없어서 요청한 작업을 수행할 수 없습니다. ‘쿼리 당 최소 메모리’ 서버 구성 옵션의 구성 값을 줄이십시오.

원인

8645 오류와 유사한 원인입니다. 또한 일반적인 서버 메모리 부족 상태를 나타낼 수 있습니다. 쿼리 당 최소 메모리(min memory per query) 옵션이 너무 높은 경우 이 오류가 발생할 수 있습니다.

문제 해결

1. 일반적인 메모리 오류 문제 해결 단계를 따릅니다.
2. sp_configure의 쿼리 당 최소 메모리 옵션 설정을 확인합니다.