March 15, 2018 at 9:26 am
We have a server with 512G of memory. We have a process that runs every night that loads millions of records into a different DB - same server. Problem is that the load process consumes about 200G of memory. The DB is not used at all during the day and is only really used for this nightly load.
I'm trying to figure out a way to release this 200G of memory during the day as it is not used.
What can I look for in the load process that would limit the memory used...or release it when it was done loading?
Guess I could create a new instance and put the DB in that instance and manage it's memory that way...? Sorry thinking out loud..
March 15, 2018 at 9:55 am
What else are you going to use the memory for? If the 200GB that is used for the load isn't used during the day, it'll soon get overwritten in memory by something fresher. Or do you have something other that SQL Server on the same server?
John
March 15, 2018 at 9:56 am
I would just like to free up some memory as it is getting close to full.
It never releases that 200G.
March 15, 2018 at 10:01 am
Indeed it doesn't - that's how SQL Server works. It grabs all the memory it needs (up to the max server memory) and doesn't give it back until the operating system asks for it. But if you've nothing else on the server, why do you need it to be given back? If you're worried that the OS doesn't have enough memory, set max server memory a bit lower.
John
March 15, 2018 at 10:07 am
Just curious, how do you know if the memory is not release when the load is done? Do you see 200gb of memory being used by that load after its done?
March 15, 2018 at 10:45 am
krypto69 - Thursday, March 15, 2018 9:26 AMWhat can I look for in the load process that would limit the memory used...or release it when it was done loading?
Just curious, how do you know if the memory is not release when the load is done? Do you see 200gb of memory being used by that load after its done?
If I restart the service then run the load process I can see the memory fill to 200G - and then never release. My normal every day use on that box is 280G (if I don't run the nightly load). It seems like it keeps the reserved memory for the next mornings load. I still have 30G free, but am concerned it doesn't release the 200G - then I will be writing out to disk.
It seems like it is using it for the load and never releasing it. Then is uses it again the next day.
Is there somthing I could add or take out that would force the memory to free up after the load completes every night?
March 15, 2018 at 10:49 am
NewBornDBA2017 - Thursday, March 15, 2018 10:07 AMkrypto69 - Thursday, March 15, 2018 9:26 AMWhat can I look for in the load process that would limit the memory used...or release it when it was done loading?Just curious, how do you know if the memory is not release when the load is done? Do you see 200gb of memory being used by that load after its done?
If I restart the service then run the load process I can see the memory fill to 200G - and then never release. My normal every day use on that box is 280G (if I don't run the nightly load). It seems like it keeps the reserved memory for the next mornings load. I still have 30G free, but am concerned it doesn't release the 200G - then I will be writing out to disk.
It seems like it is using it for the load and never releasing it. Then is uses it again the next day.
Is there somthing I could add or take out that would force the memory to free up after the load completes every night?
That is just how SQL server works, it's not a problem unless you have something else running on that server besides SQL Server(which is generally not recommended)
The memory is help by SQL Server and not released back to the OS but SQL Server will still have all of that 200GB to work with.
March 15, 2018 at 10:55 am
okay thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply