DATA SQL Server 2005 Memory Internals.ppt

Technology is dominated by two types of people: those who understand what they do not manage, and those who manage what they do not understand

1. "Technology is dominated by two types of
people: those who understand what they do
not manage, and those who manage what
they do not understand.“
Putt's Law and the Successful Technocrat: How to
Win in the Information Age

2. DAT322: SQL Server
2005 Memory Internal
Geyzerskiy Dmitriy
Chief Architect Microsoft Technologies
[email protected]

3. Session Objectives and Agenda
•Windows Memory Management
•NUMA Architecture
•SQL Server Memory Management
•AWE vs. 64-bit

4. Myths

9. Windows Memory Overview
•Virtual Memory
•32 bits: 4GB
•64 bits: 17,179,869,184 GB
•VM states: Committed, Reserved, Free
•Page size
•4KB on x86, EM64T, AMD64
•8KB on Itanium
•Large pages: 4MB on x64, 16MB on Itanium
•Paging
•Page Faults
•Working Sets
•Kernel Memory And User Memory

10. Retrieving system information using
Win32 API

11. 3GB Process Space Option
Application Memory
2GB
System Memory
2GB
Application Memory
3GB
System Memory
1GB
•/3GB switch in BOOT.INI
•/USERVA (between 2048 and 3072, in 128MB increments)
•.EXE must be linked with LARGEADDRESSAWARE flag

12. 64 bit Address Space
8TB
User
6TB
System
•Map more data into the address space
•The application “speed” is the same on 32 bit and 64 bit
•OS needs 2GB of memory to hold pointers to 16GB or
more physical memory
x64
7TB
User
6TB
System
IA64

14. Paging Dynamics
Working
Sets
Standby
List
Modified
List
Free
List
Zero
List

16. Sizing the Page File
•More RAM should mean smaller page file!
•Crash dump settings affect
•Full: size of RAM
•Kernel dump: much smaller
•To size correctly, review what goes there
•Minimum should = commit charge peak
•Maximum could be a multiple of this

18. Measuring memory performance

19. Agenda
•Windows Memory Management
•NUMA Architecture
•SQL Server Memory Management
•AWE vs. 64-bit

20. NUMA

21. What is SMP
•SMP –Symmetric Multi-Processing
•Front-bus point of contention
•Difficult to scale beyond 32 CPU
Memory
CPU 0 CPU 1 . . . CPU n
Front-bus

22. What is NUMA
Memory
CPU 0CPU 1CPU 2CPU 3
Memory
CPU …CPU …CPU …CPU n
NUMA (Non-Uniformed Memory Access)
Local Memory
Access
Local Memory
Access

23. What is Interleaved-NUMA
•Enable NUMA hardware to behave as SMP
•Memory are used by all CPUs
•Each CPU’s cache line access slice of memory from all nodes
•SQL Server 2000 should use interleaved-NUMA
Local Memory Access Foreign Memory Access
Memory
CPU 0 CPU 1 CPU 2 CPU 3
Memory
CPU … CPU … CPU … CPU n

24. What is Soft-NUMA
•Activates custom NUMA configuration on top of
any of hardware
•Registry settings control final SoftNUMA
configuration
•Provides greater performance, scalability,
and manageability on SMP as well as on real
NUMA hardware

25. Soft-NUMA Configuration Example
We have:
NUMA system with 2 nodes and 4 CPU per
Node
We need:
2 CPUs for loading application and the rest
of CPUs for queries.

26. [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node0]
"CPUMask"=dword:0000000F
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node1]
"CPUMask"=dword:00000030
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node2]
"CPUMask"=dword:000000C0
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"="1433[0x3],2000[0x4]"
"TcpDynamicPorts"=""
"DisplayName"="Any IP Address"
Soft-NUMA Configuration Example

28. Agenda
•Windows Memory Management
•NUMA Architecture
•SQL Server Memory Management
•AWE vs. 64-bit

29. Server Architecture
Deadlock
Monitor
SQLOS Hosting API
Scheduling
Memory Manager
Buffer
Pool
Lock
Manager
Synchronization
Resource
Monitor
Lazy
Writer
SQLOS API
I/O
SQLOS API
External Components (CLR/MDAC)
SQLOS
Scheduler
Monitor
= thread
Parser Optimizer SQL Manager
Database
Manager
Query
Executor
Query Processor
Transaction Services
Lock Manager
File Manager
Buffer Manager
Utilities:
Bulk Load DBCC
Backup/Restore
Access Methods
Managers for:
Row Operations
Indexes
Pages
Allocations
Versions
StorageEngine
Protocols

30. SQL Server Memory Management
Resource
Monitor
Generic
Memory Clerk
Cache Memory
Clerk
Buffer Pool
Memory Clerk
CLR Memory
Clerk
Memory Node
Low Physical Internal/External Low VASHigh Physical Internal/External

31. CP CacheStore
(@1 numeric(2,1), @2 numeric(3,2))
insert [t] values(@1 , @2)
insert t values (2.1, 3.12)insert t values (3.33, 4.1)
(@1 numeric(3,2), @2 numeric(2,1))
insert [t] values(@1 , @2)
Auto-parameterization
Bucket 0 Bucket 1 Bucket 2
SQL Server2005 RTM SQL Server 2005 SP2

32. Monitoring Memory Pressure
Last notification RM broadcasted
select*
from sys.dm_os_ring_buffers
where ring_buffer_type=‘RING_BUFFER_RESOURCE_MONITOR ‘
BP/Single page allocator turns on/off internal memory pressure indicator
select *
from sys.dm_os_ring_buffers
where ring_buffer_type= ‘RING_BUFFER_SINGLE_PAGE_ALLOCATOR ‘
Single-page allocation vs. multi-page allocation
select *
from sys.dm_os_memory_cache_counters

33. SQL Server Memory DMVs

34. Agenda
•Windows Memory Management
•NUMA Architecture
•SQL Server Memory Management
•AWE vs. 64-bit

35. Address Windowing Extensions (AWE)
•Access more than 4GB of physical memory.
•Is ignored on systems with less than 3GB of physical memory.
•Is never swapped to disk.
Allocate the physical memory (Lock Pages in Memory)1
Create a region in the process address space to serve as
a window for mapping views of this physical memory
2
Map a view of the physical memory into the virtual
memory window
3

36. SQL Server Process Address Space with AWE
0xFFFFFFFF
0x00000000
0x80000000
0xC0000000
Operating System
SQL Server or OS
(/3GB switch)
SQL Server
MemToLeavearea
Thread stacks
Other Locks Query
Workspace Plan Cache
DB Page
Cache
(Buffer Pool)
AWE
Memory

37. SQL Server 2005 32-bit AWE Memory
•Right OS version
•Windows Server 2003 Standard and up
•/PAE in boot.ini enables 32-bit OS
to address more than 4GB memory
•SQL Server Edition
•Enterprise Edition
•Developer Edition
•sp_configure ‘awe enabled’

38. Lock Pages In Memory Option
•Entry in the SQLERROR log
•64 bit: Using locked pages for buffer pool
•32 bit: Address Windowing Extensions is enabled
•Discarded in Standard Edition
•The Local System account has the 'lock pages in
memory' privilege by default
A significant part of sqlserver process memory has been paged out.
This may result in a performance degradation. Duration: 0 seconds.
Working set (KB): 1086400, committed (KB): 2160928, memory
utilization: 50%.

40. SQL Server 2005 64 bit vs. 32 bit
•The only way to get virtual memory > 3GB
•What is different from 32-bit?
•All pointers are 64-bit
•SQL Server commits ‘min server memory’ memory at startup
•Some internal memory-related data-structure constants larger
•64-bit alignment of data structures

41. SQL Server 2005 64 bit vs. 32 bit
•What is the same?
•No on-disk database format changes
•No differences in buffer pool policy / algorithms from 32-bit
•All uses of memory can use additional 64-bit memory
•DB Page Cache, Query Workspace Memory, Plan Cache,
Locks, External uses, Utilities, …

42. 32-Bit and 64-Bit System Limitations
Architectural
component
64-bit Windows 32-bit Windows
Virtual memory 16 TB 4 GB
Paging file size 512 TB 16 TB
Hyperspace 8 GB 4 MB
Paged pool 128 GB 470 MB
Non-paged pool 128 GB 256 MB
System cache 1 TB 1 GB
System PTEs 128 GB 660 MB

43. Resources
•Blogs
•Slava Oks’ blog: http://blogs.msdn.com/slavao
•SQL Programmability & API Development Team Blog:
http://blogs.msdn.com/sqlprogrammability/
•External Links:
•NUMA FAQ: http://lse.sourceforge.net/numa/faq
•Books:
•Eldad Eilam: Reversing: Secrets of Reverse Engineering
•Ken Henderson: SQL Server 2005 Practical
Troubleshooting The Database Engine
•Kalen Delaney: Inside Microsoft SQL Server 2005 The
Storage Engine

44. Summary
•It pays to understand SQL Server memory management
•A number of performance issues either originate or manifest as
memory issues
•Memory-based performance tuning is a very useful technique
•Significant internal and external changes in SQL Server 2005
•Consider NUMA for your next large-scale project
•Upgrade your system to 64 bit

46. © 2007 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only.
MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

47. Backup Slides

48. Memory
Memory Subsystem
Procedure,
Http, etc.
Caches
Memory Clerk
Node
Memory
Subsystem
Memory
Object
Factory
CacheStore
Memory
Objects
SOS’ Memory Manager
SQL Memory Components
Buffer
Pool

49. Database Page Cache
•Most common use of memory -often referred to as
“Buffer Pool”
•Stores database pages –index, heaps
•Lazy writer thread sweeps across buffer pool to age
pages out of cache
•Uses a modified clock algorithm
•Each page has a reference count
•Reference count divided by 4 each time clock hand passes
•Pages with reference count 0 can be discarded
•A Dirty page needs to be written out first
•Favors keeping often-used pages in cache
•Higher level index pages naturally favored
•Full scans may do some damage to buffer pool
•Clock algorithm limits damage

50. Monitoring Database Page Cache
•Perfmon: SQL Server:Buffer Manager
•Buffer cache hit ratio: SQL 2000 SP3 onwards, this is “recent”
data (last 2K to 3K accesses)
•Page life expectancy: low value (< 300 seconds) indicates
“churn” in buffer pool
•Physical Disk: Avg Disk sec/read, write transfer
•Beware perfmon averaging
•Free list stalls/sec: another indication of memory pressure
•AWE counters –may correlate to kernel time
•Related: Per file I/O statistics obtained via
::fn_virtualfilestats(dbid, fileid)
•IoStallMS shows file-level I/O bottleneck

51. Plan Cache
•Caches plans for various types of batches
•Stored procedures, Triggers, Ad-hoc SQL, Auto-parameterized
SQL, Parameterized SQL (sp_executesql or via client APIs)
•Plans are of two types
•Compiled plan
•Read-only
•One per unique combination of statement, dbid, unique
combination of set options
•Executable plan / Execution Context
•Derived from compiled plan –points back to it
•One per concurrent execution
•Contains execution specific data –e.g. parameter/row values
•Not all executable plans cacheable –e.g. hash, parallel plans
•No pre-defined upper limit for size of plan cache
•Depends on buffer pool to manage space

52. Monitoring Plan Cache
•master.dbo.syscacheobjects
•Lists all items in plan cache
•Can aggregate this data to get use counts
•Very useful indicator of nature of application
•dbcc proccache
•High level summary data on plan cache
•dbcc cachestats
•Summary by cache object type
•Perfmon counters under Cache Manager
•Counts of cache pages, objects
•However, hit ratios are from instance startup
•Perfmon counters under SQL Statistics
•Can monitor compiles, recompiles, etc.
•Profiler Events
•SP:CacheHit, SP:CacheMiss, SP:CacheInsert

53. SQL Server 32-bit AWE Memory
•Mapping and Un-mapping AWE memory
•Mapping cost is small –equivalent to soft fault
•Un-mapping cost is substantial –need to update page tables
on all processors
•Pages mapped mostly 1 at a time
•Read-ahead may map multiple at a time
•Perfmon:
•Buffer Manager: AWE lookup maps/sec
•Pages un-mapped many at a time
•Up to 1 MB at a go
•Perfmon:
•Buffer Manager: AWE unmap calls/sec
•Buffer Manager: AWE unmap pages/sec
•Only DB Page Cache is able to use AWE Memory
•No virtual memory pointers within

54. Buffer Pool & AWE
•AWE enabled (system default) for 64-bit
environment
•When using AWE mechanism, buffer pool no
longer uses virtual memory committed
•dbcc memorystatus
Memory Manager KB
------------------------------ ----------------
VM Reserved 16979888
VM Committed 217928
AWE Allocated 14116272
Reserved Memory 1024
Reserved Memory In Use 0

55. Memory Pressure Comparison500 Pace databases, Thinktime 30sec
1000
1500
2000
2500
3000
32bit
32bit(AWE) 32bit(/3GB)
32bit(AWE/3GB)
64bit
Tpm
0
20
40
60
80
100
CPU%
Tpm
CPU% 500 Pace databases, Thinktime 30sec
10
15
20
25
30
32bit
32bit(AWE) 32bit(/3GB)
32bit(AWE/3GB)
64bit
Batch Req/sec
0
20
40
60
80
100
120
SQL comp/sec
Batch
Req/sec
SQL
comp/sec

56. Memory Pressure Comparison…500 Pace databases, Thinktime 30sec
0.0
20.0
40.0
60.0
80.0
100.0
32bit
32bit(AWE)32bit(/3GB)
32bit(AWE/3GB)
64bit
CPU%, SQL comp/sec 92.00
94.00
96.00
98.00
100.00
Proc plans hit ratio
CPU%
SQL
comp/sec
Proc plan hit
ratio 500 pace databases, Thinktime 30sec
115135
203234
1066
0
200
400
600
800
1,000
1,200
32bit
32bit(AWE) 32bit(/3GB)
32bit(AWE/3GB)
64bit
Proc cache K-pages 90.00
92.00
94.00
96.00
98.00
100.00
Proc plans hit ratio
Proc cache
pages
Proc plans hit
ratio