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

Posted on 2012-08-15
Last Modified: 2012-10-17
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.
Question by:hpradhan08
    LVL 18

    Accepted Solution

    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
           database_id, db_buffer_pages = COUNT_BIG(*)
           FROM sys.dm_os_buffer_descriptors
           --WHERE database_id BETWEEN 5 AND 32766
           GROUP BY database_id
       [db_name] = CASE [database_id] WHEN 32767
           THEN 'Resource DB'
           ELSE DB_NAME([database_id]) END,
       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 Comment

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

    Expert Comment

    by:Shahnawaz Ahmed
    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:

    Data type:


    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 -

    Author Comment

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

    Expert Comment

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

    Expert Comment

    by:Shahnawaz Ahmed
    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.

    LVL 7

    Expert Comment

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

    Author Comment


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

    Expert Comment

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

    Expert Comment

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

    Expert Comment

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

    dbcc dropcleanbuffers;

    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 Comment

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

    Expert Comment

    are there other apps running on the server?

    Author Closing Comment

    ok, thanks.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
    The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now