Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

memory not releasing Expand / Collapse
Author
Message
Posted Wednesday, September 8, 2010 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:41 AM
Points: 28, Visits: 192
HI
I have a server with 8 Gb Ram I installed Sql server 2008 R2 on 64 bit platform Enterprise edition,in that for Sql server I allocated 6 GB of Ram I enable the AWE oprtion as well but in processing time it taking above 6 Gb ram like 7.gb or more after query completing its not release the memory how can I resolve this issue.
Actually when we running quries it taking the memory and memory increasing gradually after executing the query its not releasing the memory per example I set maximum 6 GB per Sql server then I run the query memory will increase gradually like 3.4 gb,3.7 gb, 4.5gb,and so on it will reach 7.56gb then after it steadily over there not come to down I am using X64 bit windows server 2008 sql server 2008 R2.
My system configuration is sql server 2008R2(10.0.3798) windows server 2008 X64 Ram is 8GB
Post #982342
Posted Wednesday, September 8, 2010 8:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 39,977, Visits: 36,340
SQL won't release memory unless the OS is under memory pressure. That's normal and that's how it works.

With 8GB on an x64 server you should set max memory no higher than 6GB. Disable AWE, it is not used on x64.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #982350
Posted Wednesday, September 8, 2010 9:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:59 PM
Points: 31,082, Visits: 15,529
I agree with Gail here. The server caches as much as it can, and holds memory as long as possible unless the OS has serious memory pressure. Even then it's slow to release.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #982393
Posted Wednesday, September 8, 2010 11:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:41 AM
Points: 28, Visits: 192
But when it not release the memory users get slow performance, recently I moved to live that time users said that very slow performance I thought because of Not releasing memory.
Post #982799
Posted Wednesday, September 8, 2010 11:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:41 AM
Points: 28, Visits: 192
But I set 6 GB for sql server it taking near about 7.6 gb, and no free memory available then how can I go to Live environment with this situation it is very stressfull its releasing the memory.
Post #982801
Posted Wednesday, September 8, 2010 11:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 39,977, Visits: 36,340
Max memory just controls the size of the buffer pool. SQL will use some memory beyond that, though it's usually not much.
Got CLR? Custom extended stored procs? Both use memory outside of the buffer pool.

If SQL's using more memory than you want, reduce the value of max server memory.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #982807
Posted Thursday, September 9, 2010 12:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:41 AM
Points: 28, Visits: 192
Thanks for your reply, but I am in still confusion if physical memory not releasing after execution of the query, here only one production server if I move to this in Live environment then approximately 500 users will connect to the server, then if not release the memory what I need to do, please provide the information I am going to move this server by tonight.
Thanking you in advance.
Post #982822
Posted Thursday, September 9, 2010 12:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:58 PM
Points: 20,705, Visits: 32,356
As Gail indicated, SQL Server will not release memory unless the OS is under memory pressure and forces SQL Server to releasse memory.

As she also said, you may want to reduce the MAX MEMORY setting from 6 GB to 4 or 5 GB and see what occurs.

Do not expect SQL Server to dynamically allocate and release memory. When first started, it may not take much, but as SQL Server caches data, query plans, etc, it will continue to use more memory until it reaches your max setting. Even then, it may still use some additional memory depending on what other components you are using, such as CLR ( as pointed out by Gail as well).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #982832
Posted Thursday, September 9, 2010 1:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:41 AM
Points: 28, Visits: 192
Actually nothing beyond running I reduce the memory size still problem going on I am not able to identifing the problem from last 5 hours the memory consumes 7.22 Gb, not releasing the memory, If application 400-500 users will connect at atime to the server, thats y I am worrying if not release the memory then its very hard to connect to the server or database, please provide what steps do I need to take to slove this problem.
Post #982851
Posted Thursday, September 9, 2010 1:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
Check the Total and Target memory counters?
As said SQL will not release memory until OS is under pressure. are you sure queries sre performing bad due to memory issue?

Did you checked if the index is proper?


"More Green More Oxygen !! Plant a tree today"
Post #982869
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse