Menu
Currently I am facing a SQL memory pressure issue. i have run dbcc memorystatus, here is part of my result:
Memory Manager KB
---------------------------------------- -----------
VM Reserved 23617160
VM Committed 14818444
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 23613512
VM Committed 14814908
Locked Pages Allocated 0
MultiPage Allocator 387400
SinglePage Allocator 3265000
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 16809984
VM Committed 14184208
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 408
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 6311612
VM Committed 141616
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1456
MultiPage Allocator 20144
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3101784
MultiPage Allocator 300328
Buffer Pool Value
---------------------------------------- -----------
Committed 1742946
Target 1742946
Database 1333883
Dirty 940
In IO 1
Latched 18
Free 89
Stolen 408974
Reserved 2080
Visible 1742946
Stolen Potential 1579938
Limiting Factor 13
Last OOM Factor 0
Page Life Expectancy 5463
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 258572288
Available Virtual Memory 8771398631424
Available Paging File 16030617600
Working Set 15225597952
Percent of Committed Memory in WS 100
Page Faults 305556823
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 11382
TotalPages 430160
InUsePages 28
Can you lead me to analyze this result ?
Is it a lot execute plan have been cached causing the memory issue or other reasons?
467
You can also get these parameters from task manager - go into the process view, choose view columns and add the relevant columns. If no processes in task manager/perfmon show a memory leak, but the overall memory is still going up, then the leak must be down at the kernel level.
To determine if more memory is needed for SQL, monitor the following Performance Metrics: Buffer Manager/Buffer Node: Page Life Expectancy (PLE)PLE indicates the number of seconds a page in memory has to live if it isn't touched – if there is memory pressure, this value will decrease.
The reason for this is that SQL Server cache the data in the database in RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it.
This is a bit late, but perhaps it will help someone else who reads this.
From seeing Available Virtual Memory of 8 TB, I can tell this is a 64 bit system - along with the absence of any references to AWE allocation.
As Lette points out, the OS itself only has 256 MB of Available Physical Memory - but that's just what's remaining, not the total amount installed. SQL will try to use as much physical memory that's installed as possible for performance; accessing memory is by far faster than moving a disk head.
Going by VM Committed, SQL is using 14.1 GB of physical memory going by VM Committed - I'll guess that 16 GB total of physical memory is present, accounting for OS needs, available physical memory, and 16 being a good round number.
Memory pressure is coming from two primary areas: SQL buffer pool, and SQL Plan Cache.
SQL Buffer Pool
About 13.5 GB of memory is benig used for the buffer pool. Not atypical for SQL; it will try to use as much memory as it can.
SQL Plan Cache:
Aaccording to 11,382 ad-hoc queries query plans are cached. However, only 28 plans are in use - less than 1%. If we map this back to CACHESTORE_SQLCP, we see an interesting story - no memory is currently being used for these plans at this time, but I think at one point it had consuming 3.24 GB of memory. I must admit that I'm less sure of this, and would certainly appreciate a 2nd opinion on seeing 0 for VM Commmitted but values present for the allocators.
Summary Since you're running SQL 2008, consider enabling optimizing for ad hoc query plans. This will help quite a bit with memory pressure if your workloads are primarily ad hoc.
Reference
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
