Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Sql 2008 R2 RAM occupying

Author  Topic 

pvssivakumar
Starting Member

8 Posts

Posted - 2013-02-19 : 06:34:39
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 08:14:22
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.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-19 : 11:34:07
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

8 Posts

Posted - 2013-02-19 : 22:59:09
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-20 : 08:18:32
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

8 Posts

Posted - 2013-02-20 : 09:05:20
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

396 Posts

Posted - 2013-02-26 : 20:37:26
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-27 : 07:48:56
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

8 Posts

Posted - 2013-02-27 : 08:18:56
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

15732 Posts

Posted - 2013-02-27 : 09:53:58
Restarting the SQL Server service is the only reliable way to release the memory it uses.
Go to Top of Page

pvssivakumar
Starting Member

8 Posts

Posted - 2013-02-27 : 13:10:16
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

8 Posts

Posted - 2013-02-27 : 13:10:20
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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-02-28 : 01:49:15
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
Go to Top of Page

pvssivakumar
Starting Member

8 Posts

Posted - 2013-02-28 : 03:20:39

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 05:46:55
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
   

- Advertisement -