Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Memory Configuration, Determining MemToLeave Settings


SQL Server Memory Configuration, Determining MemToLeave Settings

Author
Message
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1558
Comments posted to this topic are about the item SQL Server Memory Configuration, Determining MemToLeave Settings


John Sansom (@sqlBrit) | www.johnsansom.com
2 Tim 3:16
2 Tim 3:16
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1083 Visits: 1547
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
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1510 Visits: 3980
I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1558
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.

http://msdn.microsoft.com/en-us/library/cc267846.aspx


John Sansom (@sqlBrit) | www.johnsansom.com
John.Sansom
John.Sansom
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 1558
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.


John Sansom (@sqlBrit) | www.johnsansom.com
mojo-168709
mojo-168709
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 831
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.
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1510 Visits: 3980
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.
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 1807
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
ammo-963576
ammo-963576
Mr or Mrs. 500
Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)Mr or Mrs. 500 (577 reputation)

Group: General Forum Members
Points: 577 Visits: 207
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.
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1510 Visits: 3980
Jonathan thank you for confirmation and explanation.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search