We help IT Professionals succeed at work.

how to correct the physical memory high on the DB Server?

Hi there,
We've 256GBs memory on the server. Out of that we provided 205GBs for SQL instance and rest for the OS. Now, we're seeing that the physical memory is 87% used. But CPU usage is way low. (note: we've 10 quad cores CPUs)  How I diagnose which processes using what and how I release the Memory?  Thank you in advance for your help.
Comment
Watch Question

prog
BRONZE EXPERT
Commented:
SQL caches data and other stuff for later use, so it is normal for the SQL instance to use the memory that you allocated to it.

Has SQL instance exceeded the 205GB you allocated to it?  

you can run various queries to see which database is using buffers, for example



-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767
       THEN 'Resource DB'
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 

Open in new window

Author

Commented:
Hi Deighton,

Yes, I see it's using 223GBs now out of 256GBs.

Here is the Query results:

db_name      db_buffer_pages      db_buffer_MB      db_buffer_percent
RPT                 9984311                       78002               37.158
Data                 4436061                       34656              16.509
KAW          2984635                        23317            11.108
tempdb          2758059                    21547              10.265
MDATA    2090773                         16334                7.781
OP                   1018826                     7959                3.792
Metrics            350076                          2734                1.303
msdb             62144                           485               0.231
Resource DB   2856                                   22               0.011
Utility              1114                           8      0.004
master               270                                     2      0.001
ReportServer         223                              1      0.001
ReportServerTempDB                            114      0      0.000
model                119                             0      0.000
MSTR                117                              0      0.000
Repository         119                           0      0.000

what options I have to resolve this? I use to put the lower number of the GBs like 15GB and that'll trigger the physical memory to go down... is that good idea to do it here? Note: this is our prod server. I  appreciate your help.
Shahnawaz AhmedCloud Migration Engineer
BRONZE EXPERT

Commented:
Hi Hpradhan08,

Try below Steps to resolve high memory utilization issue.

To add the HeapDecommitFreeBlockThreshold registry key:
1.Start Registry Editor (Regedt32.exe).
2.Locate and click the following key in the registry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
3.On the Edit menu, click Add Value, and then add the following registry value:
Value name:
HeapDecommitFreeBlockThreshold

Data type:
REG_DWORD

Radix:
Decimal

Value Data: The number of bytes

This value specifies the number of freed bytes above which the heap manager decommits the memory (instead of retaining and reusing the memory). If you set this registry key to a high value (for example, 262144), the heap manager is more effective when making sure that no bytes are decommitted. Therefore, virtual address fragmentation is lessened or even avoided.


Note This value is the number of bytes in decimal. The recommended value is 262144 which corresponds with a hex value of 0x00040000.
4.Quit Registry Editor.
5.Restart your computer.

Ref. KB - http://support.microsoft.com/kb/315407

Author

Commented:
Hi v_2shaha,
Thank you for your response.

This is our production server. I can't just go and edit the register.  This is:
Window's Server R2 SP1 64 bit  with 256 GBs RAM. So, I'm looking ways to release the buffer memory without having to reboot the server...
BRONZE EXPERT

Commented:
When you say 200GB of memory you are referring on space on hard drive, correct? That has nothing to do with the server memory. Which one is 87%?
Shahnawaz AhmedCloud Migration Engineer
BRONZE EXPERT

Commented:
Dear Hpradhan08,

I am pretty sure you are referring to the windows 2k8 R2 server and you are concerning about PF usage in the task manager. right? I have been tested this solution and its permanent fix. I do understand its production server but downtime needs to be taken to proceed. Please do so and it will resolve your issue hopefully.


Thanks
Shahnawaz AhmedCloud Migration Engineer
BRONZE EXPERT

Commented:
Another thing about registry editing. Article i have share with you is Microsoft Article. Kindly take a backup of registry before proceeding .

Author

Commented:
Zberteoc,
No.

The Memory we have on the db server is 256GBs .... and it's consuming 87% of it.
BRONZE EXPERT
Top Expert 2012

Commented:
and it's consuming 87% of it.
So why is that a problem?  By design, SQL Server will use all available memory.
deightonprog
BRONZE EXPERT

Commented:
so from the results of the query I gave you, 78GigaBytes of database RPT is cached in RAM - but why does SQL do this?  The answer is that the data can then be accessed much more quickly than if SQL has to go to the disk drive for all that data, accessing RAM is superbly faster than accessing disk drives, SQL loves RAM because it is fast.

The 256GB of memory you have in the server is paying off for you, you have asked SQL to ustilise large amounts of memory and it is doing this for you.
deightonprog
BRONZE EXPERT

Commented:
if you want to see all those readings go down you could run

checkpoint;
dbcc dropcleanbuffers;
GO
dbcc FREEPROCCACHE;
GO

to clear buffered data and buffered execution plans from memory.  

This might slow things down and the normal behaviour of re-buffering data in RAM may well re-occur.

*********************************************************************

why do you want to reduce this memory?  Is it because some other process needs it, at night for example?  You could probably schedule SQL statements to turn down the maximum RAM at some time, then increase it later.

Or is it just a 'housekeeping' exercise?  If the latter, I'd suggest you don't need to do this unless there is a definite perception of a problem, which you should explain to us.

Author

Commented:
That's because it's gobbling all the memory on the Server...  ok, how I turn down the maximum RAM at sometime?

It's a 'housekeeping exercise. The Server is responding slow and need to release some of the memory that SQL Server is using...
deightonprog
BRONZE EXPERT

Commented:
are there other apps running on the server?

http://msdn.microsoft.com/en-us/library/ms178067.aspx

Author

Commented:
ok, thanks.