Sql-server – Do I need to add more memory to the SQL Server 2008 R2 instance

memorysql serversql-server-2008-r2

I've been asked if one of our SQL Server instances has sufficient memory. This is a SQL Server 2008 R2 Standard Edition running on a x64 machine with 40 CPUs and 64 GB of RAM.

The total size of all the databases running under this instance is slightly more than 300 GB.

The instance has been configured with 4096 MB for the Minimum Server Memory and 24000 MB for the Maximum Server Memory.

So far, I've managed to gather the below information:

Physical memory in use:

SELECT physical_memory_in_use_kb, 
   process_physical_memory_low, 
   process_virtual_memory_low
FROM sys.dm_os_process_memory 

physical_memory_in_use_kb process_physical_memory_low process_virtual_memory_low
------------------------- --------------------------- --------------------------
25657180                  0                           0

Performance Counters:

SELECT counter_name , cntr_value 
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)', 
'Target Server Memory (KB)', 'Granted Workspace Memory (KB)',
'Maximum Workspace Memory (KB)',
'Memory Grants Outstanding', 'Memory Grants Pending')

 counter_name                                cntr_value
-----------------------------------     --------------------
  Granted Workspace Memory (KB)                1024
  Maximum Workspace Memory (KB)                17714520
  Memory Grants Outstanding                    1
  Memory Grants Pending                        0
  Target Server Memory (KB)                    24576000
  Total Server Memory (KB)                     24576000

Buffer Pool:

select count(*) AS Buffered_Page_Count
 ,count_big(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Buffered_Page_Count Buffer_Pool_MB
------------------- --------------------
2499693             19528

Buffer Pool Size Graph:

BPHR:

PLE:

Plan Cache Size:

Page Reads Per Second

Page Writes Per Second

Total IO Wait Time:

My questions are:

What are the other things that I should be looking at?

If the above is sufficient, do I have to recommend adding more memory (maybe make it 50 GB) because PLE shows a degradation over time (while Total IO Wait Time shows going up)?

Target Server Memory is equal to the maximum memory set. Is it an indication that the instance can utilise more memory if it is configured with?

So, how do I know if my SQL Server needs more memory?
or maybe it doesn't

Best Answer

What are the other things that I should be looking at?

First few remarks

This is a SQL Server 2008 R2 Standard Edition running on a x64 machine with 40 CPUs and 64 GB of RAM.

Why is your max memory only at 24000MB when you have 64GB of phyisical memory? Are there other instances / services / ... running on it?

If the answer to this is no, then you could set max memory to about 56GB = 57344 MB.

As a side note, sql server 2008 R2 is out of extended support. It would be best to be looking towards migrating to a newer version.

Which objects are consuming buffer pool memory

Since your PLE has always been pretty low and has gotten even lower over time, you could look into what exactly is using this BP memory at different points during the day.

First of all look into which databases consume the most:

-- get memory usage per database
SELECT
[DatabaseName] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
COUNT_BIG(*) [Pages in Buffer],
COUNT_BIG(*)/128 [Buffer Size in MB]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id]
ORDER BY [Pages in Buffer] DESC;

Afterwards you can dig deeper on a database level to see which objects are in memory and how many pages these objects hold in memory.

-- get memory usage for objects in a database

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc;

If the more granular query reports less, you would have to remove and bd.page_type in ('data_page', 'index_page') to include LOB data pages.

Next steps

You should put this information together with queries running on your system, you might be able to optimize certain queries / add indexes so less data is needed in memory.

If you want to go even further, ROW / PAGE compression could be looked at if you have the CPU power to do so.

Compressed data remains compressed in memory.

Also note that if you are on sql server 2008 R2 standard edition, you could only go to 64GB of max memory. Starting from sql server 2014 this goes up to 128GB of max memory. This is also another reason to migrate.

If the above is sufficient, do I have to recommend adding more memory (maybe make it 50 GB) because PLE shows a degradation over time (while Total IO Wait Time shows going up)?

Solely based on PLE, which is not the only factor to look at, I would say that your pages get flushed too frequently out of memory. Either optimize your queries or increase your memory.

Target Server Memory is equal to the maximum memory set. Is it an indication that the instance can utilise more memory if it is configured with?

Target Server Memory (KB) is the amount of memory that SQL Server is willing (potential) to allocate to the buffer pool under its current load. Total Server Memory (KB) is what SQL currently has allocated.

Source

It has allocated all possible memory to the buffer pool as allowed by max memory, it will not go over this amount for buffer pool allocations.

So, how do I know if my SQL Server needs more memory? or maybe it doesn't

Again, looking at what objects are in memory and if the queries responsible for it can be optimized would be a good start.

Additionally I would increase either max memory if you have the room to do so (if you in fact have 64GB of memory on the machine) or increase the memory on the machine.

Again remember that there is a limit of 64GB of max memory for sql server 2008 R2 standard edition and that this is increased to 128GB starting from sql server 2014 standard edition.

There is no limit on enterprise edition.

Related Solutions

Sql-server – Plan Cache Pages by Database in SQL Server 2008R2

Here are queries that will give you buffer breakdown and plan cache breakdown per database. Note that plan cache is broken down by MB, not by pages - I don't know of a handy way, off the top of my head, to calculate the pages used by the plan cache (you'd think it's simple division, but not necessarily).

-- buffer pool breakdown

SELECT 
  db = DB_NAME(database_id),
  c = COUNT(DISTINCT page_id),
  size_MB = CONVERT(DECIMAL(12,2), COUNT(DISTINCT page_id)*8.0/1024) 
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY database_id
ORDER BY c DESC;

-- plan cache breakdown

SELECT 
  db = DB_NAME(t.[dbid]), 
  size_MB = CONVERT(DECIMAL(12,2), SUM(p.size_in_bytes)/1024.0/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY size_MB DESC;

On my test instance there are cases where plan cache is larger than the buffer pool for a given database.

The buffer pool contains both the data cache and the plan cache. But AFAIK sys.dm_os_buffer_descriptors only contains information about the data cache (index and data pages), so it does not necessarily reflect the entire buffer pool. The cases I saw above seem to support that theory.

Sql-server – SQL Server Dynamic Memory management

This isn't a complete answer, as I admit I don't know the complete answer, but can't fit into a comment.

Short detour - regarding SQLOS memory there is a wealth of information coming from a guy that worked on the internals of SQL Server storage team - Slava Oks. He has lots of articles on MSDN blogs regarding the SQLOS memory manager and they are all worth a read. Now I know we're on to SQL Server 2012, but the info is valuable nonetheless.

Now, regarding your first question, if there's a limit of max memory taken by db page cache before being cleared out and how is it controlled, I'll quote his word on it.

From article SQLOS's memory manager and SQL Server's Buffer Pool:

Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

From this article's comment I understand that SQL Server uses a LRU algorithm (LRU = least recently used). If you really need to go in depth, SQL Server's LRU algorithm in particular is described in the following research paper: The LRU-K Page Replacement Algorithm For Database Disk Buffering.

Now, for your second question, the log caches are not really exposed so they can't be changed/tuned, as they are a part of the SQLOS internal memory structure. Quoting from an answer from MSDN forum:

Essentially each database has a log buffer/cache for its transaction log which is a small contiguous memory allocation, that is 60K in size and used by active transactions, and flushed when a transaction commits, or when the buffer space fills up. Multiple active transactions can be interleaved in the log buffer, and if any one transaction commits, the buffer memory is written to the transaction log file by the log writer. If the space fills up, the contents get flushed to disk allowing the buffer/cache memory to be reused. This is why log IO can range for 512bytes to 64K (its actually 60K), but it is always sequential so varying write sizes doesn't matter that much. The format of the log records is different than data pages as documented in the BOL (http://msdn.microsoft.com/en-us/library/ms190925.aspx), so the size of a log record is variable, but the size of the log buffer/cache is consistent.

Best Answer

Related Solutions

Sql-server – Plan Cache Pages by Database in SQL Server 2008R2

Sql-server – SQL Server Dynamic Memory management

Related Question