SQL Memory Usage analysis

Analysing whether there is enough memory on a SQL Server is no easy task. Looking at task manager is of no real use, the only way to get meaningful figures is to use Performance. Here, knowing which metrics to record and how to interpret them is the complexity. This also needs to be combined with information that is provided by SQL Server.

This is not a comprehensive guide on what to do, see websites below for more detailed information. This is post is a starting point to hopefully get some figures up and running without too much investigation.

The main metrics to record are :

Memory: Available Bytes, Page Input/Sec, Pages/sec
Paging file: % Usage
Process: Page File Bytes Peak, Private Bytes, Working Set
SQLServer Memory Manager: Total Server Memory(KB)

Using these metrics, the page file can be calculated by taking the Page File Bytes Peak and * 0.7.
Page file usage can then be compared to the following thresholds:

Memory\\Available Bytes No less than 4 MB
Memory\\Pages Input/sec No more than 10 pages
Paging File\\% Usage No more than 70 percent
Paging File\\% Usage Peak No more than 70 percent

In addition, the working set can be compared to the maximum available bytes, and in turn the actual physical RAM on the system. If the physical RAM (after other processes are taken into account) is exceeded, then there is not enough memory.

If the working set is less that the Total Server Memory, then this is evidence that it is being trimmed.

To see if the swap file is being used, the following metrics need to be recorded:

Memory – Available Mbytes
Memory – Committed Bytes
Memory – Pages Output/Sec
Paging - %Usage
Working Set – Sqlservr

Of real interest, if the Memory Pages Output/Sec is not 0, then the swap file is being used.

To analyse what the memory usage is, the DBCC MemoryStatus can help to identify how efficiently the procedure cache is used. A large procedure cache may have an impact on SQL performance, and may result in less data being kept in memory force SQL to go to disk.

Useful sites for further information are :

(for 64bit SQL) - How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
Procedure Cache Tuning/Sizing
RAM, Virtual Memory, Pagefile and all that stuff