12

I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup:

  • A 2-server SQL 2012 SP1 installation
  • SQL AlwaysOn HA has been enabled for a few databases
  • CPUs are 2.4GHz, 4 cores
  • RAM is 34 GB (it is an AWS instance, hence the odd number)
  • Resource utilization is relatively low - each server has 14+ GB memory free, and SQL is not capped on how much memory to use
  • Disk access time is fine - rarely going over 15ms/Read or Write
  • Databases are not big - 1 GB, 1.5 GB, 7.5 GB
  • SQL server process is using 16 GB Private Bytes, 15 GB Working Set

Overall, no resource issues are noted. Now for the odd part. SQL is not restarted (process has been running for almost 6 months) but it seems that every ~50 days, the Page Life Expectancy counter drops to (almost) 0. Up until that point it climbs steadily, no drops. Here is a perf graph:

enter image description here

When I look at the counter data (I don't have the exact number, just an hourly aggregation) it seems the PLE counter value reached about 4,295,000 sec (roughly 50 days) every time (at least every time I have data for).

My crazy theory is that the PLE number is held as milliseconds as an unsigned long int (which has a limit of 4,294,967,295) and at 49.71 days it resets, either by design, or because of a bug. This would explain the behavior of the two servers and the identical pattern they have. Or it could be something totally different and I am just not making any sense. :)

Has anyone seen anything like that, or can explain this behavior?

P.S. I saw this post, but my case seems slightly different.

P.P.S. This is a repost - I originally posted it here, but was advised the audience here is more appropriate.

Thanks!

3

I've seen this behaviour on a client site running SQL2012 SP1. The specifics here were NUMA, and PLE demonstrating a 'sawtooth' pattern but on an hourly cycle.

A couple of threads on SQLServerCentral discussed around this:

http://www.sqlservercentral.com/Forums/Topic1415833-2799-1.aspx http://www.sqlservercentral.com/Forums/Topic1424826-2799-1.aspx

the end result being that applying SP1 CU4 seemed to fix the problem.

CU4 contains the innocent looking fix An update is available for SQL Server 2012 Memory Management KB2845380

Worth a try?

  • Thanks for posting this (sorry for the delayed reply, for some reason I never got notification of your post). I took a look at the links - they do look somewhat similar, so I will try to dup the setup in QA, apply CU4 (or probably even go straight to SP2), then check again. With the pace I see this (every 50 days) it will be a while before I can confirm, but I will post back when I do have results. BTW, my patterns still holds - steady climb for ~50 days, then sharp drop to ~0, and climb again - total of 4 since the service was started in December. – CRCerr0r Jul 15 '14 at 21:18
  • Update... I have confirmed this in our QA environment. The stats I got from there are: Total Days between resets - 49.71 (+/- 1 minute); Max PLE value reached - 4,294,961 (at 1 minute samples, so it could have been slightly higher). That pretty much confirms the production observations. I will apply CU4 and report back in ~50 days... :) – CRCerr0r Sep 9 '14 at 15:37
  • 1
    Any news on this? – Michael Green May 31 '16 at 3:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.