Brent Ozar - How To Prove Your SQL Server Needs More Memory

I Really enjoyed watching this short 30 minute webcast from Brent Ozar that shows you how to check if your SQL Server needs more memory. Of course the relationship between SQL & SharePoint [xxxx] is clear and will directly impact the performance of any SharePoint implementation !

Thanks so much for sharing this info Brent !

The link to the webcast:
http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/

My personal notes from this webcast:
(I've put in the time frames of the video when Brent talks about that specific performance counter).

64 GB of memory costs less than your laptop.

• Create a business case to rectify the purchase of more memory for your SQL
• Less than one weekday of your salary.

Cost of your time tuning + changing VS Cost to upgrade RAM to the next level

Memory - Available Mbytes
SQLServer: Buffer Manager - Page life expectancy (18:41)	SQL keeps the grapes, but has to start making wine every time from scratch. How long can SQL keep the page-date in memory (cache), before turning to disk to get the data. (Measured in seconds). The higher this value, the better (how long can the data be kept in-memory).  Some say, 300 seconds is absolute lowest. Even if you are above 300..if the cache get's cleared very often you still have a problem!  (and putting more memory in won't solve this problem). Also see "Buffer pool questions" for details.
SQLServer: Memory Manager - Memory grants pending (13:10)	How many queries are waiting for memory before they can start. Should never be above 0. If above 0 , you should just add memory !
SQLServer: Memory Manager - Target Server Memory	Target = size of the barrel
SQLServer: Memory Manager  Total Server Memory	Total = how full is it right now
SQLServer: SQL Statistics - Batch Requests / sec (14:27)	How busy is my sql server due to incoming queries.
SQLServer: SQL Statistics - Compilations / sec (14:27)	(related to above) Building the execution plans #sec. There should already be execution plan ready, which lowers the CPU performance. If SQL Server is forced to compile >10% of queries [because it can't cache them] then we need more memory.

Avoiding buying RAM

• Parameterize queries
• Keep your ORM up to date (Nhibernate, LINQ, EF, etc)
• Keep these tools up to date ! (developers)

• Transition to stored procedures
• Consider OPTIMIZE FOR AD HOC
• Don't keep these in the cache !
• Only change this is this is causing the problem !

Buffer pool questions

• What part of our database is "active" ?
• Percentage ?
• Cache the active part; you have to figure this out
• Look at what data-pages are cached in memory ?
• Which databases are cached in memory ?
• Which tables ?

• What data pages are being cached in memory ?
• What queries are constantly running, thereby forcing those pages to be cached
• Analyse using: Sys.dm_exec_query_stats (BrentOzar.com/go/plans)
• Pulls out the top 10 / 20 queries & how much resources they use
• Make sure to order by TotalReads sec

• Analyse which pages are in memory (brentozar.com/go/pool)
• Which database & tables
• This is a one-time snapshot !

• It make a long time to run these queries.. Run them during a course of time.

From easy to hard (aka, cheap to expensive)

• Slow, and less than 64 GB ? Learn how to explain business costs: your time and risks vs $500 RAM.
• Memory Grants Pending >0 ? Queries can't start,  buy more memory
• Compiles / sec over 10% of Batch Requests / Sec ?
• SQL Server May not be able to cache plans.

• Buffer Page Life Expectancy > 300 ? You still may need more memory, but start with tuning indexes & queries.
• http://BrentOzar.com/blitzindex - sp_BlitzIndex
• http://BrentOzar.com/go/indextuning