<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
memory not releasing
20 posts, Page 1 of 2
1
2
»»
memory not releasing
Rate Topic
Display Mode
Topic Options
Author
Message
kommagoni
kommagoni
Posted Wednesday, September 08, 2010 8:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 12:25 AM
Points: 25,
Visits: 173
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
GilaMonster
GilaMonster
Posted Wednesday, September 08, 2010 8:26 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,738,
Visits: 30,011
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, September 08, 2010 9:11 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 8:32 AM
Points: 31,433,
Visits: 13,747
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
kommagoni
kommagoni
Posted Wednesday, September 08, 2010 11:01 PM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 12:25 AM
Points: 25,
Visits: 173
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
kommagoni
kommagoni
Posted Wednesday, September 08, 2010 11:05 PM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 12:25 AM
Points: 25,
Visits: 173
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
GilaMonster
GilaMonster
Posted Wednesday, September 08, 2010 11:16 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 37,738,
Visits: 30,011
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
kommagoni
kommagoni
Posted Thursday, September 09, 2010 12:03 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 12:25 AM
Points: 25,
Visits: 173
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
Lynn Pettis
Lynn Pettis
Posted Thursday, September 09, 2010 12:23 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 21,625,
Visits: 27,471
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
kommagoni
kommagoni
Posted Thursday, September 09, 2010 1:02 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, April 13, 2013 12:25 AM
Points: 25,
Visits: 173
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
Minaz
Minaz
Posted Thursday, September 09, 2010 1:32 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Sunday, April 21, 2013 10:57 PM
Points: 535,
Visits: 1,423
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 »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
