Sql-server – SQL Server 2008 R2 memory issue

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

• SQLServer:Memory Manager\Total Server Memory perfmon counter
• DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.

Knowing this is 64-bit SQL Server 2008 Enterprise simplifies the discussion, so thanks for that. The first thing to say is that it is almost never right to leave the max server memory configuration option at the default of 2147483647.

The max server memory option only limits the size of the buffer pool (used for all single-8KB allocations pre-2012). SQL Server can also require significant amounts of non-buffer pool memory (allocations > 8KB). Other instances, applications, and the OS also need room to work (and 2GB is not always a good rule of thumb there). Amit Banerjee, an Escalation Engineer with Microsoft PSS has three excellent posts on this topic, which I encourage you to read.

You will generally want to set max server memory to a fairly conservative maximum value to start with, monitor the actual amount of free memory on the server over a full load cycle, preferably over a decent period of time, and gradually increase it as appropriate and necessary.

The exact level to start at depends on how much non-buffer pool memory SQL Server might be expected to require, and how much memory will be needed by other instances, applications and the OS. On a server dedicated to a single instance of SQL Server, I might start max server memory around 160GB with 192GB installed. Your circumstances might dictate that a much lower starting point is indicated.

I also recommend you consider using the Lock Pages in Memory (LPIM) option (that link explains why in great detail). This will be enabled by default if the SQL Server service is running as LocalSystem (which is not a best practice). See this CSS article for details including how to tell if SQL Server is currently using LPIM or not.

Memory used via this mechanism is not visible to Task Manager; it is better to monitor SQL Server memory usage via Performance Monitor counetrs and/or the sys.dm_os_sys_memory DMV.