Thursday, September 19, 2013

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:

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
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
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
How busy is my sql server due to incoming queries.
SQLServer: SQL Statistics - Compilations / sec
(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
    • 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 (
    • 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 (
    • 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.