February 25, 2011 at 2:43 pm
I'm using a SQL 2008 std database but I notice that the utilized memory on the box keeps increasing but never comes down unless I restart the SQL Sever service. I'm pretty much a novice on the Admin side so I was hoping someone could guide me in the right direction. Maybe I'm missing some basic understanding of how SQL utilizes memory.
What I do know is this:
1. When the SQL service starts up, it uses little memory and gradually increases till it hits the minimum memory setting.
2. As SQL needs memory, the service will allocate more memory as needed up to its max setting.
What I don't see happening when I run various processes is the memory ever coming down.
Any help would be appreciated.
February 25, 2011 at 2:59 pm
What you are describing is correct behavior. So you want to ensure that max server memory is set low enough so that other processes have enough memory for what they need to do. SQL will relinquish memory if necessary.
This post [/url]has some good information and references a couple of articles on there that would be good sources of additional information as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 3:08 pm
Thanks David for your quick response. The link you sent was interesting. So basically SQL will use as much memory as it wants unless an external process needs to borrow it.
February 25, 2011 at 3:21 pm
In a simple sense, yes, which is why setting max server memory appropriately is necessary. It would be good to read through the links posted in the link that I provided as there is a wealth of other information in there to consider based on your configuration.
Ultimately having "unfettered" access to a specific set of memory allows SQL Server to properly manage which data and exec plans to keep in cache for quick retrieval, along with other things as well. If it has to constantly release that memory then those plans and pages have to be "dumped" out and when they are needed again the plans for procedures / statements have to be recreated and data has to be pulled directly from disk. Both can be expensive operations so limiting the number of times that has to be done by keeping that information in cache is helpful to overall performance. My description is very basic due to a present time constraint so please take some time to read up on SQL Server memory usage when you have time.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 28, 2011 at 7:33 am
Thanks again David.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply