4

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?

2 Answers 2

5

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.

2
  • Excellent answer, thanks for the advice. The only question I still have is what does the 3.5 number under cached mean? Does this mean that in addition to the 6.7GB being used there is another 3.5 GB of RAM being used for caching?
    – kralco626
    Apr 24, 2015 at 11:21
  • Cached is the sum of standby memory (cached data not in use) and modified pages (must be written to disk before it can be used again by another resource). It's not a metric I use often or know a lot more about. More info: blogs.technet.com/b/askperf/archive/2013/08/09/… Apr 24, 2015 at 22:57
0

Great definitions in the link below. One strategy is to guesstimate as the first answer and then to keep an eye on page faults. As your page faults increase and as your available memory decreases you know you need to increase the amount of memory available to the VM. VMware should also allow you to set whether or not a VM is limited to the amount of memory specified or if it is allowed to creep over that limit into a shared resource pool.

http://blogs.technet.com/b/askperf/archive/2008/01/25/an-overview-of-troubleshooting-memory-issues.aspx

2
  • I have seen upto 192GB of RAM. It was using biz logic software though.
    – mikedopp
    Apr 23, 2015 at 17:13
  • depends on the application. Providing VDI to several thousand users for a simple browser-based application ( required VDI for compliance reasons ) takes around 500MB - 1GB per user using Remote Desktop Services. Using Xen Desktop or VMware is not much better. Huge memory consumption. Even spread across a cluster it takes around 256GB per server. Apr 23, 2015 at 17:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .