SQL Server Memory Manager 변화

SQL Server Memory Manager 변화

 

• Version : SQL Server 2012

 

SQL Server 2012에서 메모리 관련 DMV 및 DBCC MEMORYSTATUS, 성능모니터가 일부 재설계 되었다.

 

새롭게 디자인된 메모리 관리자를 그림으로 요약하면 다음과 같다.

 

[DMV]

Sys.dm_os_memory_nodes

단일 및 다중 페이지 할당 통합은 sys.dm_os_memory_nodes의 pages_kb 컬럼에 반영된다. 새로운 foreign_committed_kb 카운터는 SQL Server 2012에서 제공되는 NUMA 관리로 원격 메모리 노드를 식별한다. 원격 메모리 노드에 속한 느린 메모리 액세스 문제를 해결하는 유용하다.

SQL Server 2012	SQL Server 2008R2
Pages_kb	Single_pages_kb + multi_pages_kb
Foreign_committed_kb

 

 

Sys.dm_os_sys_info

메인 메모리 관리자 변경 메모리 카운터를 표준화 할 수 있다.

SQL Server 2012	SQL Server 2008R2
physical_memory_kb	physical_memory_in_bytes
virtual_memory_kb	virtual_memory_in_bytes
committed_kb	bpool_committed
committed_target_kb	bpool_commit_target
visible_target_kb	bpool_visible

 

 

Sys.dm_os_memory_cache_counters

SQL Server 2012	SQL Server 2008R2
Pages_kb	single_pages_kb + multi_pages_kb
pages_in_use_kb	single_pages_in_use_kb + multi_pages_in_use_kb

 

 

sys.dm_os_memory_cache_entries

SQL Server 2012	SQL Server 2008R2
pages_kb	pages_allocated_count

 

 

sys.dm_os_memory_clerks

SQL Server 2012	SQL Server 2008R2
pages_kb	single_pages_kb + multi_pages_kb
page_size_in_bytes	page_size_bytes

 

 

sys.dm_os_memory_objects

SQL Server 2012	SQL Server 2008R2
pages_in_bytes	pages_allocated_count
max_pages_in_bytes	max_pages_allocated_count

 

 

[DBCC MEMORYSTATUS]

이전에 있던 대부분의 DBCC 메모리 카운터는 수정 된 아키텍처를 반영하기 위해 Memory Manager에서 통합 되었다.

SQL Server 2012	SQL Server 2008R2
Memory Manager \Large Pages Allocated
Memory Manager \Emergency Memory	Memory Manager \Reserved Memory
Memory Manager \ EmergencyMemory In Use	Memory Manager \Reserved Memory In Use
Memory Manager \Target Committed (KB)	Buffer Pool \Target (Pages)
Memory Manager \Current Committed (KB)	Buffer Pool \Committed (Pages)
Memory Manager \Pages Allocated (KB)	Buffer Pool \Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \Pages Reserved (KB)	Buffer Pool \Reserved (Pages)
Memory Manager \Pages Free (KB)	Buffer Pool \ Free(Pages)
Memory Manager \Pages In Use
Memory Manager \Page Alloc Potential (KB)	Buffer Pool \Stolen Potential (Pages)
Memory Manager \NUMA Growth Phase
Memory Manager \Last OOM Factor	Buffer Pool \ LastOOM Factor
Memory Manager \Last OS Error
Memory Manager \Large Pages Allocated
Memory Manager \Emergency Memory	Memory Manager \Reserved Memory
Memory Manager \Emergency Memory In Use	Memory Manager \Reserved Memory In Use
Memory Manager \Target Committed (KB)	Buffer Pool \Target (Pages)
Memory Manager \ CurrentCommitted (KB)	Buffer Pool \Committed (Pages)
Memory Manager \Pages Allocated (KB)	Buffer Pool \Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \Pages Reserved (KB)	Buffer Pool \Reserved (Pages)
Memory Manager \Pages Free (KB)	Buffer Pool \ Free(Pages)
Memory Manager \Pages In Use
Memory Manager \Page Alloc Potential (KB)	Buffer Pool \Stolen Potential (Pages)
Memory Manager \NUMA Growth Phase
Memory Manager \Last OOM Factor	Buffer Pool \ LastOOM Factor
Memory Manager \Last OS Error
Memory node Id = n\ Pages Allocated	Memory node Id = n\ MultiPage Allocator + Memory node Id = n \ SinglePage Allocator
Memory node Id = n\ Target Committed
Memory node Id = n\ Current Committed
Memory node Id = n\ Foreign Committed
Memory node Id = n\ Away Committed
Memory Clerks \Pages Allocated	Memory Clerks \SinglePage Allocator + Memory Clerks \ MultiPage Allocator

 

 

[Perfmon Counter]

성능 모니터에서는 몇 가지 AWE 관련 카운터가 제거 되었다.

SQL Server 2012	SQL Server 2008R2
Object - SQLServer:MemoryManager	Object - SQLServer:BufferManager
Database Cache Memory (KB)	Database pages
Free Memory (KB)	Free pages
Reserved Server Memory (KB)	Reserved pages
Stolen Server Memory (KB)	Stolen pages
TargetServer Memory(KB)	Targetpages
TotalServer Memory (KB)	Totalpages
	AWElookup maps / sec
	AWE stolen maps / sec
	AWEwrite maps /sec
	AWEunmapcalls / sec
	AWEunmappages / sec
Object -SQLServer:Memory Node	Object -SQLServer:Buffer Node
Database NodeMemory (KB)	Database pages
Free Node Memory(KB)	Free pages
Foreign Node Memory(KB)	Foreign pages
Stolen Node Memory(KB)	Stolen pages
Target Node Memory(KB)	Target pages
Total Node Memory(KB)	Total pages

 

 

 

[참고자료]

http://blogs.msdn.com/b/sqlosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012.aspx

 

 

2014-01-16 / 강성욱 / http://sqlmvp.kr