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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
I've seen PLE way longer than 50 days, so unless there is a change in SP1 I doubt the size of the variable is the issue. – Max Vernon May 16 '14 at 22:26
Does this coincide with your patching of the cluster? I ask because the instance effectively restarts when you fail it over to a different node which is what you would likely do during maintenance to keep the data available. – James Anderson May 17 '14 at 12:17
Thanks for responding! @JamesAnderson - no, I have not restarted the nodes nor restarted SQL on either node since December. Both nodes were restarted back then within 30 min of each other, so I think #1 my theory has some merit, or #2 something resource-related happens to both of the nodes at the same time, or #3 a really bad query hits. #2 is not impossible, I suppose, but both are in two different AWS AZs, and resource issues VERY rarely happen in two AZs at the same time, not to mention 3 'synchronized' times. I would imagine the same would apply to a 'bad' query theory, correct? – CRCerr0r May 17 '14 at 13:19
So is there anything in the SQL logs from around the time the PLE drops? – James Anderson May 17 '14 at 13:27
There could be a variety of things scheduled for every ~50 days or so that is flushing the buffer pool. I'd check and see if you've had any jobs that have run at times that match the drops in PLE. Could be maintenance plans, automated testing, etc. that runs explicit DBCC DROPCLEANBUFFERS or other side-effecting operations. Note that the jobs might not originate on that server. Certainly isn't something SQL Server is just doing on its own because it gets bored after 50 days. – Aaron Bertrand May 17 '14 at 14:32

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:

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?

share|improve this answer
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
Any news on this? – Michael Green 20 hours ago

Your Answer


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

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