When does sql 2008 release "memory"?
-
We run a monthly process that causes sql to use about 11GB of RAM. The remainder of the month the server is lightly touched but sql memoery usage remains at 11gb.
Other than restarting sql server are there any other options for encouraging sql to release unused memory? (After I recycle sql the memory usage drops to about 1Gb and remains there until the big monthly job is run again. Clearly Sql is holding on for no apparent reason.)
TIA,
Barkingdog
-
Nothing (other than OS memory pressure) will make a running instance of SQL reduce it's memory utilisation. What you're seeing is normal, expected, documented behaviour.
Why is the large amount of used memory a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL should have a show on Discovery Channel called "Memory Hoarders"..once it gets the memory, it does not let go, up to the limit of what you allowed at set up...but the default is all the memory you've got. if it has the memory, it can save all the execution plans it encounters, which allow for faster subsequent queries that reuse those plans.
most recommendations i see are to to leave a gig or so for the operating system, and allow SQL to grab the rest...so if you've got 16 gig, change the value to be 14.5 or 15 gig for SQL, and leaving the rest for the OS.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yup, 2 Exabytes default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Good replies from everyone.. I don't think it is causing an issue but it just bothered me because that 11gb being held by sql can not be used by any other process on the server. (Sorry to say,some of our sql servers are NOT dedicated sql boxes.....but I guess if sql returned memory whenever it it was not needed the impact on performanne would be sever. Even sorrier to say that some of our sql servers are actually VM Guests running on a HOST so every GB given to any server is one that can not be used by another Guest.)
Barkingdog
-
If there are other processes then limit SQL's memory usage (via max server memory) in order to reserve some for them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
We have a few servers wearing multiple hats with SQL being one of them, and we've had instances where MS SQL does chew-up most of the available memory. As suggested you can adjust the Max Server Memory size or schedule the server or even just the SQL Server Engine service to restart every night or after your 11 Gig process is complete. This should bring the size back down if you absolutely need the Max Server Memory to be as high as it can for that one large process.
Just a suggestion --
Sam
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply