How much memory does the SQL need

memorysql-server-2012vmware-vspherewindows-server-2012-r2

I have a VM (SQL database server) that I think has more memory then it needs, however I'm not sure what I can reduce it to because I don't understand exactly what "In use", "Available", "Committed" and "Cached" memory mean.

The following is a screenshot of Task Manager after I did some load testing. Let's assume that the load won't get much more intense than this.

enter image description here

It seems obvious to me that 64GB of RAM is too much. I would like to understand how much I can take away without reducing performance.

Does this mean I only need 8GB because that is more than the "In use" amount? Or do i need to include the "cached" amount when I determine how much is needed?

Best Answer

SQL Server will hold on to the RAM it allocates, so since it doesn't seem to go above 6-7 GB, I would allocate 8GB for SQL and keep 2-4 GB extra for the OS in this case (SQL always does some tasks outside the memory it allocates for sqlserver.exe.

It would be a good idea to put this value (8 GB) in the min memory settings for your sql server instance. This way, when SQL needs the memory, it wouldn't loose time allocating it first because it 'takes' the 8GB when starting.

When playing around with the RAM, you can always check the Page Life Expectancy. This will tell you how long something stays in RAM. This is a value in seconds, as long as it keeps going up, you're golden.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

As long as it stays above 300, you're OK. Lower values would indicate some memory pressure. This could occure after doing large sorts, updating/rebuilding indexes, ... Don't freak out if this value is low after restarting the instance, it can never be greater then the time SQL is running.

A second interesting counter is the Buffer Cache Hit Ratio, this will tell you how many of the previous requests (last few seconds) were fetched from memory.

SELECT CAST(A.cntr_value1 AS NUMERIC) /
 CAST(B.cntr_value2 AS NUMERIC)* 100 AS Buffer_Cache_Hit_Ratio_Percentage, A.cntr_value1 As Cache_Hits, B.cntr_value2 AS Cache_Lookups
 FROM ( SELECT cntr_value AS cntr_value1
 FROM sys.dm_os_performance_counters
 WHERE object_name = 'MSSQL$SQL01:Buffer Manager' AND counter_name = 'Buffer cache hit ratio'
 ) AS A,
(SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'MSSQL$SQL01:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base'
) AS B;

In this example my SQL instance is a named instance SQL01, so change it to your instance name or change MSSQL$SQL01:Buffer Manager to MSSQLServer:Buffer Manager if you have a default instance.

The higher the better. In an ideal situation, you would get 100% here, this means the whole DB is in memory.

Related Topic