Do I need to add more memory to my SQL Server 2008 R2 instance?

Question

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

Answer 1

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.