1

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:

enter image description here

BPHR:

enter image description here

PLE:

enter image description here

Plan Cache Size:

enter image description here

Page Reads Per Second

enter image description here

Page Writes Per Second

enter image description here

Total IO Wait Time: enter image description here

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

  • "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 64 gb of memory? Are there other instances / services / ... running on it? – Randi Vertongen Nov 21 '19 at 8:52
2

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.

| improve this answer | |
  • Well.. 24000 MB is allocated to SQL Server out of 64GB but there is only ~ 12GB available physical memory, as the rest is shared with the application. I know this is not good practice but that's what iv'e inherited unfortunately.(I'm pretty new and wasn't here before when it was all designed this way maybe back in 2012-2013). Today I understood that actually they wanted to reduce memory by 2GB so as to get rid of system's memory alerts :) For now' i've convinced them that if they are not going to increase memory, at least should leave it as it is. – RGO Nov 22 '19 at 6:54
  • And yes, SQL Server 2008 R2 is out of support, and there is currently plan for upgrade early next year after change freeze. Anyways, thanks for your answer, and FYI, I have found two big objects collectively responsible for occupying almost half of the BPool. So now. I am looking for ways to see if there are any missing indexes that can help eliminate their full scans. – RGO Nov 22 '19 at 6:58
  • 1
    @RGO Great to hear! I have inherited servers like that too, completely understand :). Good luck & have a good day! – Randi Vertongen Nov 22 '19 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.