September 8, 2010 at 8:15 am
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
September 8, 2010 at 8:26 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 9:11 am
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.
September 8, 2010 at 11:01 pm
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.
September 8, 2010 at 11:05 pm
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.
September 8, 2010 at 11:16 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 12:03 am
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.
September 9, 2010 at 12:23 am
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).
September 9, 2010 at 1:02 am
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.
September 9, 2010 at 1:32 am
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"
September 9, 2010 at 1:42 am
My server Ram is 8GB, I set in sql server memory minimum memory is '0',and maximum memory is 6144mb, and I am not enable the AWE because I am running on X64, Now my physical memory status is 7.30GB, its not releasing from last 5 hours when I was run
DBCC MEMORY STATUS the output is
memory manager KB
VM Reserved 8410776
VM Committed 69608
Locked Pages Allocated6399232
Reserved Memory 1024
Reserved Memory In Use 0
Memory node id=0
VM Reserved 8407128
VM Committed 66072
Locked Pages Allocated6399232
MultiPage Allocator 25896
SinglePage Allocator50520
September 9, 2010 at 1:47 am
memoryclerk_sqlbufferpool KB
VM Reserved 8347648
VM Committed 8192
Locked Pages Allocated 6399232
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 1488
I did the rebuild of index everything.
September 9, 2010 at 1:48 am
kommagoni (9/9/2010)
I am not able to identifing the problem from last 5 hours the memory consumes 7.22 Gb, not releasing the memory,
Once more with feeling....
SQL does not and will not automatically release memory once it has allocated it. That is normal, expected and documented behaviour.
If you are unhappy with the amount of memory SQL uses, reduce the max memory setting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 9:00 am
You can force SQL Server to release memory. Schedule a job which will run every 15 minutes with
commands given below.
DBCC FREESESSIONCACHE
go
DBCC FREESYSTEMCACHE ( 'ALL' )
go
dbcc dropcleanbuffers
--Note you can also free procedure cache but it will delete all stored execution plans.
September 9, 2010 at 9:05 am
That won't force it to release memory back to the OS. That will just force it to drop all pages out of the buffer pool (possibly hindering query performance for a while) and to clear all system caches. The memory will still be allocated to SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply