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 |
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
