| 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 gbSiva |
|
|
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. |
 |
|
|
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. |
|
|
|
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 |
|
|
|
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 memoryJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 memoryJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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 kindlySiva |
|
|
|
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. |
|
|
|
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 feelingSiva |
|
|
|
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 feelingSiva |
|
|
|
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 |
|
|
|
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 amfacing, I don t have much knowledge about sql but in task manager itis 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 |
|
|
|
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. |
|
|
|
|
Sql 2008 R2 RAM occupying