Free up memory on SQL Server
- My SQL Server 2005 has 38Gb allocated to it and its currently using 42Gb, problem is it never frees up any of this memory for other processes and the app I have running on SQL is now becoming unusable...whats the best way to free up the memory, the system used to run on only 24Gb and started to have issue so we slowly increased the memory availbale until it was at 38Gb, each time I have increased the memory the app works fine for a day and by the next morning it has eaten up all the memory. I have 48Gb in the server but don't want to give out any more.
Can I set the memory to 24Gb again (without a restart) and go through the process of slowly increasing the memory again.
Cheers
Craig H
所有回覆
- Hi Craig
You need to set the Max Server Memory configuration option.
This limits the physical memory SQL will consume. If you set this to less than the currently used memory, you will see the memory reduce very quickly. No restart required.
In SSMS, you set this in Properties -> Memory
HTH
Ewan
If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).
If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution. - Craig
Also, SQL will consume all memory allocated to use as data cache. So you should expect SQL to consume any extra you allocate. However, it manages this sensibly, so you will not necessarily notice a performance hit if you restrict the max memory.
HTH
Ewan
If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).
If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution. Thanks, I actually did this at lunch time, I lowered the memory to 28Gb and let it settle down and then increased it to 30Gb and my app started working again, this memory has all been consumed now...
Is this really bad practice as it seems to be an answer until my developers write a new app.
Could I schedule a job do lower the memory each morning and then slowly increase it through out the day when my stock controllers encounter problems?
Cheers- I dont recommend you to bring down the memory allocated from time to time , just retain it at 28gb and watch it for few more days / hours . SQL Server 2005 will use as much memory as it's able to when necessary, but will respond to memory pressure requests from the OS to free up memory. The sudden memory usage you're seeing is probably the buffer pool expanding to allow more of the database to be held in memory.
Thanks, Leks - SQLLQS,Please make sure you have enabled Lock Pages In Memory option for windows.If you have 64-bit system then above mention article says its not required but there is knowledge base article which says that if you are having problem of buffer pool memory then you have to enable Lock Pages In Memory.Lets know if it helps.Thanks,Nimit
