February 18, 2014 at 11:03 pm
Good Day all
I started receiving these alert messages, and after doing some re-search still can't figure out how to totally resolve it. From what I gather the value Microsoft stipulates 300 for PLE is not accurate if you running a 64 bit OS and dependent of the amount of RAM you allocate to SQL.
If I allocate 20 Gig of RAM to SQL, The PLE should not drop below 1500 - (PLE should be 300 for every 4 GB of RAM) (20/4)*300
During the course of the day it sometimes drops below 1500, so my question is how can I further see why and what query is causing this to happen???
I setup a monitoring job as mentioned by Steve Hood to capture results for me every 20 min.
February 19, 2014 at 12:36 am
Chapter 4 - http://www.red-gate.com/community/books/accidental-dba. All about memory, memory contention.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2014 at 9:09 am
I have no idea where you got that formula from, but I personally never use PLE any longer for monitoring. I would not be concerned at all about your occasional dips below 1500.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 19, 2014 at 9:17 am
From the author of the book recommended by Gail, here is an article that talks about the formula and such.
http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/
Best piece of advice there is this nugget
Don’t use the value 300 as any kind of Page Life Expectancy threshold. Anyone that continues to recommend doing so is doing you a disservice. Use an adaptive formula like (DataCacheSizeInGB/4GB*300). Even better – monitor the steady-state value of PLE and react when it dips *and stays* below your steady-state value.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 10:37 pm
Thanks a lot for all the assistance, that book that Gila mentioned is quite useful and not only for accidental DBA's :-D....
February 19, 2014 at 11:14 pm
SQL_Student (2/19/2014)
Thanks a lot for all the assistance, that book that Gail mentioned is quite useful and not only for accidental DBA's :-D....
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 20, 2014 at 12:33 am
SQL_Student (2/19/2014)
Thanks a lot for all the assistance, that book that Gila mentioned is quite useful and not only for accidental DBA's :-D....
Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy