June 18, 2014 at 9:11 am
Is my server under memory pressure? This is a standard edition, 64 GB of memory in 64-bit environment. Here is part of the dbcc memorystatus result. Thanks in advance!
Memory Manager KB
---------------------------------------- -----------
VM Reserved 80473532
VM Committed 55684316
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 30976720
MultiPage Allocator 1008
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 12624832
VM Committed 29916
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1520
MultiPage Allocator 78680
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 6710784
Target 6710784
Database 2566579
Dirty 41624
In IO 0
Latched 8
Free 19072
Stolen 4125133
Reserved 0
Visible 6710784
Stolen Potential 2250111
Limiting Factor 17
Last OOM Factor 0
Page Life Expectancy 632
(14 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 6524350464
Available Virtual Memory 8712661204992
Available Paging File 75180728320
Working Set 57238368256
Percent of Committed Memory in WS 100
Page Faults 3212391179
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 4048
TotalPages 66525
InUsePages 764
(3 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 2004006
Current Max 2004006
Future Max 2004006
Physical Max 4937436
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Optimization Queue (internal) Value
---------------------------------------- --------------------
Overall Memory 44065685504
Target Memory 41080938496
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 4124791
Predicted 5359477
Private Target 0
Private Limit 0
Total Target 6375244
Total Limit 6375244
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 126129
Rate -5546
Target Allocations 1141896
Future Allocations 0
Overall 5379112
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 3998662
Rate 334
Target Allocations 5014763
Future Allocations 0
Overall 5379112
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate -51459
Target Allocations 2250119
Future Allocations 1234359
Overall 5379112
Last Notification 1
(6 row(s) affected)
June 18, 2014 at 10:55 am
I'm not superman at this, but it doesn't look like your server is under memory pressure based on what you have provided.
What is the trend for Page Life Expectancy (shows how long pages stay in the cache and should be rising/steady)?
What are the symptoms that your applications are showing that has you looking into this?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 19, 2014 at 1:31 am
Value of stolen pages looks quite high to me.Stolen=4125133.Lots of hash and sort operations ?
Value of PLE also seems quite low,only 632 for a 64 GB memory.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 7:06 am
Yes, on the sql default trace file, I see a lot of hash and sort operations. How should I solve it? Yes, the PLE seems to be low too. The MEMORYCLERK_SQLOPTIMIZER consumes a lot of memory too.
June 19, 2014 at 7:52 am
You can try to identify the queries that are causing hashes and sorts and tune them. There may be query and/or index changes you can make to reduce hashes and sorts.
As I said earlier you need to look at the trend for PLE. You could have had an event that flushed the cache and it is rising. It could be in the thousands now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply