[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-15
14
Medium Priority
?
1,032 Views
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.
0
Comment
Question by:hpradhan08
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 1500 total points
ID: 38297144
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

0
 

Author Comment

by:hpradhan08
ID: 38297236
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.
0
 
LVL 8

Expert Comment

by:Shahnawaz Ahmed
ID: 38297348
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:hpradhan08
ID: 38297393
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...
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38297431
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%?
0
 
LVL 8

Expert Comment

by:Shahnawaz Ahmed
ID: 38297895
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
0
 
LVL 8

Expert Comment

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

Author Comment

by:hpradhan08
ID: 38298202
Zberteoc,
No.

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

Expert Comment

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

Expert Comment

by:deighton
ID: 38299576
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.
0
 
LVL 18

Expert Comment

by:deighton
ID: 38299625
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.
0
 

Author Comment

by:hpradhan08
ID: 38416288
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...
0
 
LVL 18

Expert Comment

by:deighton
ID: 38416884
are there other apps running on the server?

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

Author Closing Comment

by:hpradhan08
ID: 38505805
ok, thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

834 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