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

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.

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

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...

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%?

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

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

Zberteoc,
No.

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

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

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.

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.

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...

are there other apps running on the server?

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

ok, thanks.