October 17, 2011 at 1:11 pm
Hello SQL Performance Gurus,
I have a question about the behavior of SQL Server memory allocation with regards to how quickly the sql service can allocate the minimum memory that it is configured to use.
I already have the standard answer from Microsoft which is here:
The buffer pool does not immediately acquire the amount of memory specified in min server memory.
...
The amount of memory acquired by the Database Engine is entirely dependent on the workload placed on the instance. A SQL Server instance that is not processing many requests may never reach min server memory.
However, this does not help explain what I see across different environments we have...
The environment details:
PROD
-----
SQL Server 2005 SP4 x64 on Win 2003 x64
Min Memory = 80GB
Max Memory = 80GB
Total Server Memory = 95 GB
Lock Pages in Memory set for account that runs SQL Server
UAT
---
SQL Server 2005 SP4 x64 on Win 2003 x64
Min Memory = 60GB
Max Memory = 60GB
Total Server Memory = 76 GB
Lock Pages in Memory set for account that runs SQL Server
So, what we observe on the UAT and other non-production instances is that SQL Server will allocate all of the Memory we have configured for Min Memory within a couple of minutes. This occurs despite there being no activity on the SQL server instance. No workload to necessitate that SQL Server grab all of the min memory right away.
On Production, we see a very different pattern. When we restart the SQL service there is an initial quick grab of about 20GB of Memory, but then the total memory levels off and it can take several hours before SQL Server has reached the 80GB min memory. There are no other memory-intensive processes on the service that would be using Memory and preventing SQL from getting what it needs.
What can explain the differences in behavior? What can we do to force SQL to grab the 80GB as soon as it can? Thanks in advance for any experiences you can share on this subject.
October 17, 2011 at 1:32 pm
best is to set a different value for min and max server memory.
SQLserver can have issues when these values are equal.
If this is a dedicated box, why would you enforce the min memory to be that size ?
I would play around and monitor to what size it would grow itself over time and only then enforce a reasonable setting to be min server memory.
btw the max value is ok.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 17, 2011 at 1:37 pm
ALZDBA (10/17/2011)
best is to set a different value for min and max server memory.SQLserver can have issues when these values are equal.
If this is a dedicated box, why would you enforce the min memory to be that size ?
I would play around and monitor to what size it would grow itself over time and only then enforce a reasonable setting to be min server memory.
btw the max value is ok.
Same advice. Lower your min memory.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 1:40 pm
Hi Alzdba,
The question of why we have the memory configured this way is a valid one, which I do not know the answer to.
All I can say is that it has been configured like this for a long time (before my time here) and getting this changed is not easy due to strict change control. If you have any specific examples of issues that SQL server can have when these are set to equal, this would be helpful.
Placing that aside for the moment and with the assumption that these settings cannot be changed anytime soon, any ideas why in one environment the min memory gets allocated in a few minutes (remember, no major workload on the SQL Server), and in the other it takes hours?
Thanks again
October 17, 2011 at 2:07 pm
A few things could contribute. A dynamic swap file for the OS, Memory that is not fully seated, memory that has gone bad.
All of those things can cause delays like you are seeing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 18, 2011 at 7:31 am
Hi Jason,
Thanks for the additional suggestions.
I can rule out the first one, as the swap files are a fixed size (initial size and maximum size are set equal).
The other 2 I would consider as possibilities if there were other symptoms in addition to the slow memory grab. That is, if there were random crashes, distored video, problems booting up, problems with the OS seeing all of the memory, problems during intensive memory operations, etc, then maybe I would say that the memory could be bad or not seated properly on the mob. However, the only symptom is that the initial grab takes longer than we would expect. Once SQL has the 80GB, then the system performs great and there are no issues that suggest other problems.
Anyone else have experiences with SQL Server taking a long time to allocate memory the min memory?
Thanks again
October 18, 2011 at 8:28 am
My second place to check would also be io/ram related.
Any hints in the windows evenlog files ?
Are there other startup processes on these boxes ? (that have the need for big ram ?)
Did you check recovery intervals of you databases ?
- does their recovery time needed meet the expectations ?
can you post SQLServer errorlog file (or at least the startup part until all databases have been recovered )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 18, 2011 at 9:28 am
M_E_K (10/18/2011)
Hi Jason,Thanks for the additional suggestions.
I can rule out the first one, as the swap files are a fixed size (initial size and maximum size are set equal).
The other 2 I would consider as possibilities if there were other symptoms in addition to the slow memory grab. That is, if there were random crashes, distored video, problems booting up, problems with the OS seeing all of the memory, problems during intensive memory operations, etc, then maybe I would say that the memory could be bad or not seated properly on the mob. However, the only symptom is that the initial grab takes longer than we would expect. Once SQL has the 80GB, then the system performs great and there are no issues that suggest other problems.
Anyone else have experiences with SQL Server taking a long time to allocate memory the min memory?
Thanks again
We actually ran into memory issues with poorly seated modules that only exhibited itself when we tried to uninstall clustering. At no other time did the issue present itself. We reseated the modules and amazingly the uninstall worked. Yes, it was very weird and I would have never thought it, but it was a last ditch effort.
Just some food for thought.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 20, 2011 at 2:08 pm
Why do you expect SQL Server to allocate memory if it doesn't need it right now?
Do you experience any performance issues because of the Server not grabbing 80GB?
If no then why bother? If yes then why do you think that the real problem is "slow memory allocation"? It can be a result, not a root cause.
How fast do you think server can read 80 GB of data from IO subsystem to the memory? For example, if you have not optimized query which reads a lot of data but very inefficient, lets say, by doing single random extent reads from HDD? Even with the table scan it will take a while to populate 80GB of memory with some useful data.
Furthermore, how fast server can grab all the available memory after the restart should be the last thing in monitoring SQL Server performance assuming that there is nothing else than one SQL instance on this box.
October 20, 2011 at 3:18 pm
Hi,
ALZDBA: Thanks for your great suggestions and being so willing to help and even offering to look at log files.. much appreciated
Jason: That is one bizarre story wrt the cluster uninstall.. who would've thought?
Alexander: Thanks for jumping in. You raise some valid questions and I'll explain why we were worried about this below.
I *think* I'm pretty much ok now and I've figured a few things out and some of my misunderstanding was due to not comprehending what exactly different counters were capturing and the different information various people were looking at.
So to backtrack a bit, a concern was raised in our team about why SQL Server was behaving one way in PROD and a different way in other environments. In the end, though, it turned out to be an apples-to-oranges comparison.
For those interested in the details, open up the 2 attached graphs (graph1.jpg and graph2.jpg):
graph1.jpg:
This is an Available Memory graph from the PROD environment (~ 2 hours) where we have memory of SQL server set to min/max 80GB/80GB and LPIM is set. This was taken soon after a restart of SQL services (restarts do not happen often in production).
Basically this shows it took 1.5 hours to allocate 80GB of physical memory, which I agree on it's own does not generate any reason for concern.
graph2.jpg:
This is from a non-PROD environment with min/max memory set to 40GB/40GB. The counter being tracked here is SQLServer - Total Server Memory (KB)
Someone from our team did a test where they restarted SQL Server on a non-Prod box with no load and what we saw here was that all 40GB was allocated in about a minute. (duration of graph here is 1minute 40seconds)
SO like I said, this raised a lot of questions and left people wondering if we might have memory problems or performance issue we don't know about, but mainly people wanted to know why allocation was behaving so differently between environments.
Well, you probably get the picture by now.... SQL Server counter Total Server Memory is apparently capturing committed virtual memory, not necessarily physical memory. Obviously we can't just restart SQL in prod just for the sake of getting this counter for comparison, so we're waiting on the available memory graphs from non-PROD to justify our understanding of why it appeared so drastically at first.
However, I do have one lingering doubt about why so many people advise against having min/max set to the same value. What are potential problems of running with memory set this way?
Thanks again all... I'm hopeful this case can be closed 🙂
October 20, 2011 at 3:19 pm
Ooops... just realized that you can't uploaded images directly from laptop to this forum.. I don't have the images on a public server, but basically graphs are described in the post, so I hope it made sense...
October 20, 2011 at 6:07 pm
M_E_K (10/20/2011)
Hi,However, I do have one lingering doubt about why so many people advise against having min/max set to the same value. What are potential problems of running with memory set this way?
I don't see a big deal in having min=max if you don't have anything else on this server. Just leave some memory for OS, so min/max < total available memory on the server.
But you may want to set min < max when you have more than one SQL server instance on the same box.
For example:
Server has 40GB of RAM and there are 2 instances of SQL.
Now you have 2 options:
1. Set min=max = 16GB. But in this case after both of the instances grab 16GB they will never give it back to OS or other SQL, even if one of them is idle and second one experiencing memory pressure.
2. Set min = 8 GB, max = 24GB. In this configuration SQL Server can return memory to OS, and another instance can utilize it when needed. So it adds more flexibility and memory can be utilized better.
October 20, 2011 at 11:55 pm
M_E_K (10/20/2011)
Ooops... just realized that you can't uploaded images directly from laptop to this forum.. I don't have the images on a public server, but basically graphs are described in the post, so I hope it made sense...
you can just add them as an attachment with your reply. ( push "edit attachments" on the bottom ) 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply