memory not releasing

  • kommagoni

    SSCommitted

    Points: 1898

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714600

    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.

  • kommagoni

    SSCommitted

    Points: 1898

    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.

  • kommagoni

    SSCommitted

    Points: 1898

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

    SSCommitted

    Points: 1898

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    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).

  • kommagoni

    SSCommitted

    Points: 1898

    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.

  • Minaz Amin

    SSChampion

    Points: 11052

    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"

  • kommagoni

    SSCommitted

    Points: 1898

    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 Allocated 6399232

    Reserved Memory 1024

    Reserved Memory In Use 0

    Memory node id=0

    VM Reserved 8407128

    VM Committed 66072

    Locked Pages Allocated 6399232

    MultiPage Allocator 25896

    SinglePage Allocator 50520

  • kommagoni

    SSCommitted

    Points: 1898

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

    SSC Eights!

    Points: 810

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply