June 25, 2009 at 11:50 am
Comments posted to this topic are about the item SQL Server Memory Configuration, Determining MemToLeave Settings
July 6, 2009 at 7:47 am
Just trying to clarify a little.....
Your results of "total Avail Mem, KB" is the total available free space in the Mem-to-Leave VAS? and the "Max Free Size KB" is the largest block available?
So, if I have a startup parm of -g512
your query shows approximately 328 meg total avail mem, can I then assume my system is currently using approx 184 meg of mem-to-leave?
Also, does the PerfMon flag; PROCESS - PRIVATE BYTES - sqlserver
show me the current used mem-to-leave? (Which is just a little bit off from the formula above)
Thanks for the query and article!
Tim White
July 6, 2009 at 8:35 am
I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.
July 6, 2009 at 9:16 am
2 Tim 3:16 (7/6/2009)
Just trying to clarify a little.....Your results of "total Avail Mem, KB" is the total available free space in the Mem-to-Leave VAS? and the "Max Free Size KB" is the largest block available?
So, if I have a startup parm of -g512
your query shows approximately 328 meg total avail mem, can I then assume my system is currently using approx 184 meg of mem-to-leave?
Also, does the PerfMon flag; PROCESS - PRIVATE BYTES - sqlserver
show me the current used mem-to-leave? (Which is just a little bit off from the formula above)
Thanks for the query and article!
Hi, thanks for your question.
You are correct in your understanding of VAS allocation.
With regard to the PerfMon counters I suggest reviewing the following MSDN article for clarification.
July 6, 2009 at 9:19 am
Pei Zhu (7/6/2009)
I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.
Hi, thanks for your question.
It is certainly not as large a concern however, it should be noted that you can still run out of VAS even on 64-bit if you run low on physical memory. This is why it is recommended that you configure the Maximum Server Memory Setting explicitly in SQL Server.
I hope this answers your question.
July 6, 2009 at 10:25 am
Is the query valid on a 64-bit system? The numbers retured were very large, something like several TB.
Beginning SQL 2005, we don't install 32-bit version anymore. We always set max server memory. But I don't know what's the best way to determine MemToLeave requirement on a particular instance.
July 6, 2009 at 10:40 am
Memtoleave has no effect on 64 bit instance. On my instance with 64 GB memory, I ran into VAS fragmentation few times in the past.
July 6, 2009 at 10:40 am
I am not really sure where to begin with commenting on this article. It has incorrect information in it, and the code provided for looking at VAS comes from Christian Bolton's blog, but the article doesn't provide credit.
The coverage of MemToLeave doesn't adequately address what the VAS reservation is for, what/how/why it gets used, or how it is calculated. I can see this article being a reference point for people adding the -g startup parameter to solve every problem and then being referenced in forums posts when the -g doesn't solve the issue. If you are going to cover something as complex as the MemToLeave area in SQL Server, you had better do it correctly, which this article doesn't do.
Now suppose your environment uses a fair amount of managed code such as .NET CLR or perhaps utilises SQL Server Integration Services (SSIS) quite heavily. These technologies naturally require memory just as SQL Server does however one critical point to note is that the memory for these resources is allocated from outside of the SQL Server Buffer, in a portion of memory known as MemToLeave.
This is very wrong. SSIS is its own process, and doesn't use SQL Server Memory allocations from MemToLeave.
MemToLeave is virtual address space (VAS) that's left un-used when SQL Server starts so that external components called by SQL Server are saved some address space. So in order for these technologies, .NET CLR and SSIS, to operate efficiently you must ensure that they too have access to sufficient memory.
This is why it is often recommended that you explicitly set the maximum amount of memory that SQL Server uses, as opposed to allowing it to consume all that is available on your server.
MemToLeave is a VAS Reservation on 32bit SQL Server systems that is reserved for multipage and external allocations and the SQL Server processes, again not SSIS which is its own process. The max server memory settings have no affect on the VAS reservation at all, they only affect the size of the buffer pool. The VAS reservation is calculated at startup time and uses a base 256MB reservation + the calculated additional workspace size. The -g parameter only applies to 32 bit instances of SQL Server and it only increases the 256MB base to values higher than 256MB, values lower than 256 are ignored for the parameter. It shouldn't just be used without understanding the impacts it has, like decreasing the size of the buffer pool.
If you are unsure of how much additional memory to allocate to MemToLeave, then it is best to increase the volume assigned in smaller increments of say 250MB, until your original indicators are no longer being raised i.e. your application domain unload messages are no longer appearing.
This is dangerous information to offer since increasing the VAS reservation decreases the size of the buffer pool, and there is a limit to how much VAS there is on a 32 bit server. You can't keep increasing the value. Anything beyond a 128 MB increase (which would be -g384) should be carefully considered. You likely have a bigger problem if you are increasing the base beyond this size that needs to be corrected.
If you are going to cover a topic like this one, you need to cover it in detail, and be accurate in what you write.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 6, 2009 at 10:42 am
John,
Could you clarify something?
I understand how .NET CLR and Linked Server queries (and extended stored procs) make use of the Virtual Address Space in the Database Engine's process. However, you mentioned SSIS and I'm not sure I understand how an SSIS package will put pressure on the Database Engine's VAS.
Please correct me if I'm wrong, but my understanding of an SSIS package execution flow is:
1) For ongoing permanent storage, an SSIS package is stored as XML on the hard drive, or in an internal format in an MSDB table, depending on where you "compile" it to via BIDS.
2) When your code calls the SSIS package, you tell the Integration Services service/process where your package is (on the hard drive, in MSDB, etc).
3) The Integration Services service grabs the package and loads it into its address space, and then begins execution.
4) Data obtained and manipulated by the package loads that information into the address space of the SSIS service as it moves through the various Flow tasks inside the package. Anytime the SSIS package accesses SQL Server, it is using standard SQL queries and thus behaves just like any client application would with respect to the DB Engine's memory utilization.
Thus, it is the SSIS service's address space that would be heavily affected by package execution, not the Database Engine's.
So might you mean possibly:
a) If the SSIS package is connecting through a "Shared Memory" protocal instead of TCP/IP or Named Pipes, memory inside the DB Engine's address space is necessary?
b) During execution of Maintenance Plans, which don't require an SSIS service on the machine to be running, the SSIS package supporting the maintenance plan must use the DB Engine's VAS?
Thanks,
Aaron M.
July 6, 2009 at 10:44 am
Jonathan thank you for confirmation and explanation.
July 6, 2009 at 1:19 pm
Hi Jonathan,
Thank you for your comments. I really appreciate you taking the time to provide feedback. I can certainly appreciate your frustrations and concerns. I will endeavour to alleviate them as best I can.
May I start by mentioning that this is my first attempt at writing an article and that being an experienced and talented author yourself, I am sure you can appreciate that the finer qualities of writing are honed in both time and practice. It goes without saying that there is still much I have to learn and I would certainly appreciate any guidance you have to offer.
The article I wrote is intended to raise people’s awareness to the fact that there is more to SQL Server Memory configuration than solely setting the maximum server memory or AWE settings etc. It is not intended to be a detailed walkthrough of SQL Servers memory architecture, which I’m sure you’ll agree, is a very in depth topic indeed.
With regard to your first point, concerning the source of the T-SQL code, to the best of my knowledge the code is both available and widely used in SQL Server circles. Perhaps I have mistakenly assumed that it is a standard DMV query script. Incidentally, this fact was raised with the Editor prior to publication of the article.
It of course goes without saying however that credit must be given where it is indeed due and so thank you for bringing this to my attention. If you could please provide me with the appropriate reference/s I will see to it that the article is amended accordingly and promptly.
Second, the statement regarding SSIS as being allocated from VAS is indeed inaccurate. Thank you for pointing this out and again I will see to it that the article is amended accordingly.
I will also look to incorporate your comments with regard to the applicability of the –g parameter to 32 bit instances, for improved clarity. In fairness however, the article does allude to using caution when applying the –g parameter however perhaps not sufficiently enough for all reader’s benefit.
Inexperience of writing aside, it is still not acceptable to include inaccurate information. As you very well point out, the topic of choice is complex and intricate. In future I will ensure to have work proof read by peers kind enough to do so and if per chance you were to offer that would be gratefully received.
You also kindly put forward some excellent suggestions for improvements to the article, such as additional background discussions surrounding VAS. Perhaps, time permitting you would like to engage in further discussions concerning this or even look to collaborate together on an improved version?
Once again thank you for your comments and feedback. I hope I have moved toward alleviating at least some of your concerns.
July 6, 2009 at 1:30 pm
Pei Zhu (7/6/2009)
I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.
depends
we have a 64bit SQL server that threw up some errors and turned out to be memory and SQL grabbing too much. had to put a max memory figure and leave 4GB to the OS. we have another SQL server that also runs a java app on the same server. that server has 64GB of RAM with 32GB for SQL and the rest for the OS and the application
July 6, 2009 at 1:32 pm
John,
Just wanted to say your last post took a lot of courage. I admire you admitting points where you were wrong and willing to move forward in a positive manner.
Nice guts….
Tim White
July 6, 2009 at 1:39 pm
-------------
depends
we have a 64bit SQL server that threw up some errors and turned out to be memory and SQL grabbing too much. had to put a max memory figure and leave 4GB to the OS. we have another SQL server that also runs a java app on the same server. that server has 64GB of RAM with 32GB for SQL and the rest for the OS and the application
--
That is not true. memtoleave is nothing to do with physical memory. It is about vas reservation.
July 6, 2009 at 2:04 pm
2 Tim 3:16 (7/6/2009)
John,Just wanted to say your last post took a lot of courage. I admire you admitting points where you were wrong and willing to move forward in a positive manner.
Nice guts….
++1
* Noel
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply