SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Sql 2008 R2 RAM occupying
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/19/2013 :  06:34:39  Show Profile  Reply with Quote
i am using sql 2008 r2 standard version when the system starts and sql services itself occupying 16 gb of ram, during running it is going upto 28 to 30 gb. our data base size is 10 GB APPROX May i know the reason for that? is there any tools to refresh the memmory? or if i upgade to sql 2012 is there any useful?

my hardware memmory is 32 gb

Siva

Edited by - pvssivakumar on 02/19/2013 06:51:46

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/19/2013 :  08:14:22  Show Profile  Reply with Quote
SQL Server by default takes up as much memory as it needs and then holds on to it. You can set the maximum amount of memory in the SSMS object explorer. Right click the server name, properties and then Memory tab. Reduce the maximum amount of memory. Given that you have 32 GB memory, assuming it is a dedicated server, you can set the max memory to 28000 MB. If you do it on a live system, the memory consumed by SQL Server may not come down immediately, but it eventually will.

Test in a development environment before you actually do it on the production system.

Edited by - James K on 02/19/2013 08:21:09
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/19/2013 :  11:34:07  Show Profile  Reply with Quote
Standard configuration is set to 90% of RAM for sql server memory and 1.5 times RAM for page memory.
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/19/2013 :  22:59:09  Show Profile  Reply with Quote
quote:
Originally posted by James K

SQL Server by default takes up as much memory as it needs and then holds on to it. You can set the maximum amount of memory in the SSMS object explorer. Right click the server name, properties and then Memory tab. Reduce the maximum amount of memory. Given that you have 32 GB memory, assuming it is a dedicated server, you can set the max memory to 28000 MB. If you do it on a live system, the memory consumed by SQL Server may not come down immediately, but it eventually will.

Test in a development environment before you actually do it on the production system.



Siva
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 02/20/2013 :  08:18:32  Show Profile  Visit jackv's Homepage  Reply with Quote
Before setting the max memory - create a memory profile of ALL the OS memory requirements , including other apps which may be running. Once you have this figure , you can set the SQL server max memory

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/20/2013 :  09:05:20  Show Profile  Reply with Quote
My query is if i set max memmory what happens about performance of sql is there option in sql to refresh in active memmory; if suppose sql needs beyond 28 gb system will hang? Can u pls explain clearly or should i increase physical memmory?


Siva
Go to Top of Page

jbates99
Constraint Violating Yak Guru

390 Posts

Posted - 02/26/2013 :  20:37:26  Show Profile  Reply with Quote
No, MSSQL will not hang. If it needs > 28 GB of RAM it may simply run a bit slower. Not to worry.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 02/27/2013 :  07:48:56  Show Profile  Visit jackv's Homepage  Reply with Quote
You may find that less data can be maintained in the memory , therefore more trips to disk. Of course , getting data from disk is generally slower than retrieving from memory

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/27/2013 :  08:18:56  Show Profile  Reply with Quote
Thanks for info but us there option to clear inactive menmory occupied by sql if there pls send that query kindly

Siva
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 02/27/2013 :  09:53:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
Restarting the SQL Server service is the only reliable way to release the memory it uses.
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/27/2013 :  13:10:16  Show Profile  Reply with Quote
Ya thanks dude, iam doing the same, but prodn server needs to be restarted na that is the only disadvantage i am feeling

Siva
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/27/2013 :  13:10:20  Show Profile  Reply with Quote
Ya thanks dude, iam doing the same, but prodn server needs to be restarted na that is the only disadvantage i am feeling

Siva
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 02/28/2013 :  01:49:15  Show Profile  Visit jackv's Homepage  Reply with Quote
SQL Server manages the buffer cache - depending on requests for data sets.Are you experiencing any memory pressure? Are queries running slower?



Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Edited by - jackv on 02/28/2013 01:50:46
Go to Top of Page

pvssivakumar
Starting Member

India
8 Posts

Posted - 02/28/2013 :  03:20:39  Show Profile  Reply with Quote

My RAM is 32 GB if the production servers is continuously up for more than 48 hours sql RAM is occupying total 32 GB and the server is hanging up, then the server should be restarted, this the problem i am
facing, I don t have much knowledge about sql but in task manager it
is showing that sql is occupying all the 32gb ram and hanging up.

Because of the above issue i am restarting sql services for every 24 hours.

If there is query in sql to clear unused buffer so that the above issue will be resolved for me.







quote:
Originally posted by jackv

SQL Server manages the buffer cache - depending on requests for data sets.Are you experiencing any memory pressure? Are queries running slower?



Jack Vamvas
--------------------
http://www.sqlserver-dba.com



Siva
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 02/28/2013 :  05:46:55  Show Profile  Reply with Quote
I know I am repeating myself - I had posted this a few days ago in this thread. Do the following:

1. Is the server a dedicated server? If it is not, how much memory is required for all your other applications not counting SQL Server? If it is a dedicated machine for SQL Server, this number would be 0. But let us say, for example, you need 4 GB for your other applications.
2. Add 3 Gigabytes to the number you calculated in step 1. So that is 7 Gigabytes.
3. Subtract this number from the installed memory. So in your case 32-7 = 25 GB.
4. In SQL Server Management Studio, right click on the server name, select properties, select Memory tab from the left panel and change the Maximum Server Memory to 25 Gigabytes. The number you need to enter there is in Mega Bytes. So enter 25000 there and click OK.
5. If you can, restart the server.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000