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 !

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.