Windows Memory Management Basic Virtual Memory AWE SQL

목 차 몸풀기 – Windows Memory Management Basic : Virtual Memory & AWE 실 전 – SQL Server Memory Internals 마무리 – SQL PASS 2003 PSS Service Center Labs : Troubleshooting Memory

몸풀기 Windows Memory Management Basic : Virtual Memory & AWE

사전지식 Inside Microsoft Windows 2000, 3 rd Ed. Chap. 7 Memory Management (esp. pp. 389 -402) Programming Application for Microsoft Windows, 4 th Ed. Chap. 13 Windows Memory Architecture

메모리 공간(Address Space) Physical Memory 공간 - 하드웨어에 의해 지원됨 - 0 ~ MAXsys까지의 주소 Logical Memory 공간 - Process가 보는 자신의 주소 - 0 ~ MAXprog까지의 주소 Program은 Logical Address 만 볼 수 있음. - Physical Address는 볼 수 없음.

Virtual Memory(1) User Logical Memory를 Physical Memory와 분리 - 실행 시 program의 일부만이 메모리에 있다. - 따라서 Logical Address Space는 Physical Address Space보다 훨씬 클 수 있다. - Page가 SWAP될 수 있어야 한다.

Virtual Memory (2) 사용자에게 Memory의 Physical 한 측면을 모두 감춤. - Memory는 2 n Byte만큼의 Virtual Address Space를 가짐. - Virtual Address Space의 일부분만이 Physical Memory 에 존재함.

Paging 원리 - Process의 Physical Address Space는 연속적이지 않을 수 있음. - Physical Memory를 4 K 단위의 frame으로 나눔. - Logical Memory를 4 K 단위의 page로 나눔. - n page의 Program을 수행하려면, n개의 frame 이 필요함. - 모든 free frame에 대한 추적이 필요함. - page와 frame이 1: 1로 mapping됨. - page는 logical address에서 연속적이나 physical address의 frame은 임의의 위치에 존 재함.

Page와 frame의 mapping

Virtual Address Translation Page 테이블 통해 virtual page와 physical frame을 mapping함.

Heap(2) A Process’s Default Heap - Windows Function이 이 공간을 사용함 예) application의 ANSI 문자열을 UNICODE 문자열로 변환 시 변환 문자 저 장 Thread들의 Heap allocation은 serialize 됨 - 한번에 하나의 thread만이 Heap memory를 allocation할 수 있음. - corruption 방지

Address Windowing Extensions (AWE) 32 bit process에게 제한된 주소 공간(2 G /3 G)을 초과하는 physical memory를 할당 하고 접근할 수 있도록 하는 방법 3단계를 통해 AWE를 이용 - 사용할 physical memory 할당 - physical memory를 접근할 수 있는 window 역할을 할 virtual address space 지역 생성 - 생성된 window를 통해 physical memory 접근

Address Windowing Extensions (AWE)

실 전 SQL Server Memory Internals -이 세션은 PASS 2003에서 SQL Server Escalation Engineer인 Ken Henderson에 의해 발표된 내용입니다.

2003 PASS Community Summit November 11 -14, 2003 329/SQL Server Memory Internals Ken Henderson/Author Keith Elmore/Microsoft* *This presentation is based on internal training developed by Keith Elmore of Microsoft

Goals Describe the SQL Server memory management architecture Provide a brief overview of the implementation of this architecture. Teach you how to figure out how to solve memory-related problems

Agenda Windows memory management review Memory usage and SQL Server processes Memory usage by non-SQL Server processes Monitoring memory usage Troubleshooting memory-related errors

Memory Management Review

Virtual vs. physical memory Windows provides virtual memory services Committed virtual memory is backed by some type of physical storage – usually the page file or physical memory Windows handles translations between virtual memory and physical storage 32 -bit processes have a 4 GB virtual memory address space divided between kernel- and user-accessible areas

Address Space 0 x. FFFF Operating System 0 x. C 0000000 Application or OS (/3 GB or /USERVA switch) 0 x 80000000 Application 0 x 0000

Reserved vs. Committed Free – not used Reserved – address space set aside for later use; no physical storage used Committed – physical storage is being used to store something

Allocation Granularity Determines the smallest memory reservation Depends on the operating system Is 64 KB on 32 -bit Windows

System Page Size Determines the smallest memory allocation Depends on the processor architecture Is 4 KB on I 386

xp_sysinfo Custom xproc from my last book Uses Win 32 Get. System. Info API to retrieve useful system info Returns the system page size, allocation granularity, etc. exec xp_sysinfo Page. Size Allocation. Granularity Number. Of. Processors Processor. Type ---------------------4096 65536 2 Intel

Types of Win 32 memory Virtual memory – a range of addresses that can be committed or reserved and be noncontiguous Heap – a (typically) small contiguous memory region Shared memory – can share memory between processes and allow files to be treated as memory

Virtual Memory Virtual. Alloc -- only Win 32 allocator that differentiates between reserving and committing Reserve a memory region (multiples of 64 KB) Commit some or all of a previously reserved region (multiples of 4 KB) Can reserve and commit separately or at the same time

Heaps Heap. Alloc - used to allocate (typically) small chunks from the heap manager, which manages one or more regions that it allocated from virtual memory Each process has a default process heap (Get. Process. Heap) Applications can create their own heaps (Heap. Create)

Shared Memory Two uses: Allows multiple processes to share memory Allows a file to be treated as memory Create. File. Mapping and Map. View. Of. File set it up Used by SQL Server’s shared memory Net. Lib to avoid the network stack

Other allocators C++ new operator COM IMalloc, Co. Task. Mem. Alloc All allocators eventually resolve to Virtual. Alloc

C++ new operator The default Microsoft C++ implementation simply calls Heap. Alloc A class can overload new to do whatever it requires This operator is almost never used within SQL Server

IMalloc / Co. Task. Mem. Alloc IMalloc is a standard interface for COMbased code to allocate memory Co. Task. Mem. Alloc is semantically identical to IMalloc: : Alloc SQL Server uses an interface derived from IMalloc to manage internal memory allocations

Review 2 -3 GB memory available to application Memory can be free, reserved or committed All Win 32 allocators eventually end up using Virtual* APIs to do their work

SQL Server Buffer Pool

Buffer Pool Size SQL Server calculates max buffer pool size and reserves it at startup The computation is based on physical memory Lowering max server memory does not change the amount of memory reserved To change the reservation size, increase Mem. To. Leave (and shrink the maximum BPool size) via the -g parameter

Buffer Pool Allocations Up to 32 different regions can be allocated to set up the buffer pool The BPool maintains parallel arrays to track the start of each region and the number of pages in it, respectively SQL Server maintains a bitmap that tracks the committed pages in the BPool SQL Server maintains an array of structures (“BUFs”) that track allocated pages

Finding the BPool The BPool is exposed in a simple global variable that you can find with Win. Dbg Demo: Finding BPool

Buffer Pool & AWE Windows 2000 and later Involves mapping/unmapping a “view” of physical memory pages into the process virtual address space Limited amount of “visible” buffers at any given time (only what can be mapped into the process address space) May have to throw away procedure plans to clear space for mapping buffers

Review SQL Server dynamically adjusts the amount of memory it uses There is limited virtual address space left after the buffer pool reserves its maximum AWE helps some, but we’re still limited by virtual address space

Memory Allocation by SQL Server Processes

Memory Managers Connection – e. g. , PSS and SRVPROC Query Plan – compiled plans/execution plans Optimizer – metadata and tree structures Utility – e. g. , SQLTRACE, log manager, etc. General – e. g. , locks OS (Reserved) “Manager” “No Fail” Low Memory “Manager”

Direct Calls to Virtual. Alloc/Heap. Alloc Transaction Log Backup Profiler (7. 0) Net. Libs

Callbacks to Allocate Memory srv_alloc – (typically used by xprocs) callback routine that allocates from general pool or from OS buffers, depending on size MSXML

Lazywriter Two purposes: Tries to keep a minimum number of free buffers (freeing dirty buffers requires I/O) Keeps enough physical memory free on the machine to avoid paging Sweeps across BUFs to “age” them Many things that it can’t remove Demo: Watching Lazywriter in action

Review Five core memory managers OS memory “manager” for allocations greater than 8 KB; low memory mgr for emergencies Some places still do direct Virtual. Alloc srv_alloc -- callback into memory manager Lazywriter’s role in keeping memory available

Memory and External Components

Linked Servers Typically run in process Might be able to run out-of-process Even if run out-of-process, there are still some memory requirements to load the marshalling stubs

COM Objects Default sp_OACreate syntax creates objects in process Can force out of process even if app changes can’t be made (Q 198891) Marshalling required if out of process All in process allocations use address space from Mem. To. Leave

Extended Stored Procs Address space used for the DLL Memory allocations may come from buffer pool (srv_alloc < 8 KB), Mem. To. Leave (srv_alloc >= 8 KB, standard Heap. Alloc or Virtual. Alloc) Can be run from another server (Q 243428)

Review External consumers of SQL Server memory: Linked servers COM objects (sp_OA) Extended stored procs The first two always use Mem. To. Leave; the third may use buffer pool if using srv_alloc

Monitoring Memory Usage

Performance Counters Process Object Virtual Bytes – reserved virtual memory Private Bytes – committed virtual memory SQL Server: Buffer Partition Free list statistics SQL Server: Buffer Manager Stolen, Target, Total SQL Server: Memory Manager Memory manager statistics, etc.

DBCC MEMORYSTATUS • Documented in the KB (Q 271624) • Same info is produced for certain errors • PSS may ask you to run this in certain situations

vmmap, vmstat, tlist Command line tools vmmap and vmstat are variants of Richter-based internal tools that PSS may ask you to run vmmap dumps a row describing the attributes of each block of virtual memory vmstat displays summary info on the count, min, max and avg sizes of the different types tlist shows either a list of processes or details about threads/modules in a specific process

Review Performance Counters DBCC MEMORYSTATUS vmmap, vmstat, tlist

Troubleshooting

WARNING: Clearing procedure cache to free contiguous memory Allocation from OS Memory manager Normally out of address space in Mem. To. Leave Start by capturing Perf. Mon, DBCC MEMORYSTATUS, and the errorlog May need a userdump

WARNING: Failed to [reserve | commit] contiguous memory Shortage of contiguous address space in Mem. To. Leave (or physical memory on server)

Error 17803 Memory allocation from ODS component Issue may be in BPool or Mem. To. Leave —need more information to troubleshoot further May require a userdump to effectively troubleshoot

Error 17802/Unable to spawn xxx thread Address space is exhausted or heavily fragmented What is loaded in the process? Are there leaks? May require a userdump to effectively troubleshoot

Error 701 -y 701 is useful here to generate a stack dump when the error occurs (dbcc dumptrigger also works) Check sp_configure settings (e. g. , Open Objects) Demo: -y and dumptrigger

Error 7399 Shortage of memory in the Mem. To. Leave area Could be load related (OLE-DB provider is using lots of memory) and require a larger –g value May be a leak in the provider

Error 8645 Caused by many users running queries with sort or hash operators Goal is to find who is consuming all the workspace memory May be an indication that these queries are picking a bad plan or need some optimization

Further Reading Inside Windows 2000 (MS Press, 2000) by David Solomon and Mark Russinovich Programming Applications for Windows (MS Press, 1999) by Jeffrey Richter Inside SQL Server 2000 (MS Press, 2000) by Kalen Delaney The Guru’s Guide to SQL Server Architecture and Internals (Addison-Wesley, 2003) by Ken Henderson The BOL and various whitepapers on www. microsoft. com.

Updates Server: privatenews. microsoft. com Account name: privatenewsservicecenter Password: PASS

마무리 SQL PASS 2003 PSS Service Center Labs

Troubleshooting memory By using 4 Memory Senarios

Important Resources SQL Server Books Online “Monitoring with SQL Profiler” SQL Server Books Online “Memory Architecture” KB Article 293878 “INF: General Performance Troubleshooting Using SQL Profiler Traces” KB Article 230785 “INF: SQL Server 7. 0 and SQL Server 2000 Logging and Data Storage KB Article 321363 “INF: SQL Server Memory Usage” KB Article 243428 “HOW TO: Move Extended Stored Procedures Out of Process” KB Article 309256 “HOW TO: Troubleshoot SQL Server Error 8645”