Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This morning at 4am we experienced a huge drop in Page Life Expectancy on a new sql server 2012 box. No jobs (backup etc) ran at this time and traffic was very light compared to say 20:00.

Current DB size is around 20gb but writing at 5gb per day. DB is heavy write (lots of xml data), very little read. Currently on a raid 10 disk with ssd for tempdb. The server has 32gb RAM.

What could be the cause in this massive drop?

enter image description here

[Update]

Current graph

enter image description here

Memory Stats DBCC MEMORYSTATUS

    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                5213323264
    Available Virtual Memory                 140684252233728
    Available Paging File                    9919574016
    Working Set                              25879891968
    Percent of Committed Memory in WS        100
    Page Faults                              6918560
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0

    (10 row(s) affected)

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              51371672
VM Committed                             25165812
Locked Pages Allocated                   0
Large Pages Allocated                    0
Emergency Memory                         1024
Emergency Memory In Use                  16
Target Committed                         25165824
Current Committed                        25165816
Pages Allocated                          24360576
Pages Reserved                           0
Pages Free                               33232
Pages In Use                             1486944
Page Alloc Potential                     22420592
NUMA Growth Phase                        2
Last OOM Factor                          0
Last OS Error                            0

(16 row(s) affected)

Memory node Id = 0                       KB
---------------------------------------- -----------
VM Reserved                              51371608
VM Committed                             25165792
Locked Pages Allocated                   0
Pages Allocated                          24360576
Pages Free                               33232
Target Committed                         25165800
Current Committed                        25165792
Foreign Committed                        0
Away Committed                           0
Taken Away Committed                     0

(10 row(s) affected)

Memory node Id = 64                      KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             20
Locked Pages Allocated                   0

(3 row(s) affected)

MEMORYCLERK_SQLGENERAL (node 0)          KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          7208

(6 row(s) affected)

MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
---------------------------------------- -----------
VM Reserved                              12980352
VM Committed                             640036
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          23648112

(6 row(s) affected)

MEMORYCLERK_SQLQUERYEXEC (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          320

(6 row(s) affected)

MEMORYCLERK_SQLOPTIMIZER (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          1160

(6 row(s) affected)

MEMORYCLERK_SQLUTILITIES (node 0)        KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          128

(6 row(s) affected)

MEMORYCLERK_SQLSTORENG (node 0)          KB
---------------------------------------- -----------
VM Reserved                              3968
VM Committed                             3968
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          8704

(6 row(s) affected)

MEMORYCLERK_SQLCONNECTIONPOOL (node 0)   KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          4192

(6 row(s) affected)

MEMORYCLERK_SQLCLR (node 0)              KB
---------------------------------------- -----------
VM Reserved                              4726784
VM Committed                             5712
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          30680

(6 row(s) affected)

MEMORYCLERK_SQLSERVICEBROKER (node 0)    KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          536

(6 row(s) affected)

MEMORYCLERK_SQLHTTP (node 0)             KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          8

(6 row(s) affected)

MEMORYCLERK_SNI (node 0)                 KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          376

(6 row(s) affected)

MEMORYCLERK_SNI (node 64)                KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          16

(6 row(s) affected)

MEMORYCLERK_SNI (Total)                  KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          392

(6 row(s) affected)

MEMORYCLERK_FULLTEXT (node 0)            KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          296

(6 row(s) affected)

MEMORYCLERK_SQLXP (node 0)               KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          16

(6 row(s) affected)

MEMORYCLERK_BHF (node 0)                 KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          304

(6 row(s) affected)

MEMORYCLERK_XE_BUFFER (node 0)           KB
---------------------------------------- -----------
VM Reserved                              4608
VM Committed                             4608
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
Pages Allocated                          0

(6 row(s) affected)
share|improve this question
    
Is this the first time it's happened or just the first time you've noticed it? –  swasheck Mar 1 at 14:13
    
@swasheck This has just happened this morning, only had this box 'live' for four days –  redsquare Mar 1 at 14:33
2  
If it's heavy write and very little read, why do you care about PLE? Is there actually a problem? I think people put a lot more stock in this counter than it deserves. –  Aaron Bertrand Mar 1 at 16:22
3  
Looking at the scale (y axis)... What is the current PLE? Looks like each minor gridline is 40,000. Which would put your current PLE roughly around 18,000 (it looks a few pixels below midway, so I'm being conservative). Is that correct? –  Thomas Stringer Mar 1 at 17:16
    
@Aaron Bertrand it might not be the most important stat but any drop like the above should raise concerns. We are a small team with no dba hence why I asked here. –  redsquare Mar 2 at 8:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.