April 23, 2012 at 2:10 pm
the answer isnt very often direct or clear. neither is the info on the internet, which in alot of cases is contradictory or vague...
Something I've run into alot is metrics that paint an inconsistent picture of how SQL Server is doing for Memory resources...
in cases, PLE will show very low, while cache hit ratio is very high.
seems on most servers I have analyzed 'Target Memory' just looks like the Max Memory value thats configured, and in alot of cases so does 'Total Memory'. but meanwhile, pages/second is still very low...
I guess my question isnt very direct or clear either, so here it is...
what techniques do YOU use for wading through these metrics, and answering the simple question... "does my SQL Server need more or less memory?"
one other more specific question... I've spoken with MSFE's who state that PLE is the one-stop-shop go-to memory metric, and it shouldnt hover below 300.
how bout when it is spikey... a saw-tooth pattern when graphed out over a day, with it growing steadily, then dropping drastically to say 1000 or 1500 seconds, then repeating every few hours. sometimes these drops correlate with an OLAP cube loading or some other event, other times its not as easy to correlate to something specific. usually, accompanying those drops, I dont see any impact to disk I/O, buffer hit ratios and other metrics, which relates to my above question.
Thanks!
April 23, 2012 at 2:30 pm
http://www.sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx
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
April 23, 2012 at 2:35 pm
LAW1143 (4/23/2012)
one other more specific question... I've spoken with MSFE's who state that PLE is the one-stop-shop go-to memory metric, and it shouldnt hover below 300.
To be completely blunt, anyone who says that today is parroting 7 year old information and does not understand what PLE is.
http://www.sqlskills.com/BLOGS/PAUL/post/Page-Life-Expectancy-isnt-what-you-think.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/Do-yourself-a-favor-Trust-No-One.aspx
And on buffer cache hit ratio:
Lastly, I will recommend chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
April 24, 2012 at 6:21 am
sorry for taking a while to reply...
thanks for the links and feedback! 🙂
dont know why I didnt start with sqlskills... invaluable resources over there!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply