Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Server Memory Configuration, Determining MemToLeave Settings Expand / Collapse
Author
Message
Posted Thursday, June 25, 2009 11:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:15 AM
Points: 344, Visits: 1,530
Comments posted to this topic are about the item SQL Server Memory Configuration, Determining MemToLeave Settings


John Sansom (@sqlBrit) | www.johnsansom.com
Post #742071
Posted Monday, July 06, 2009 7:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 9:40 AM
Points: 1,081, Visits: 1,473
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
Post #747728
Posted Monday, July 06, 2009 8:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,156, Visits: 3,061
I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.
Post #747786
Posted Monday, July 06, 2009 9:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:15 AM
Points: 344, Visits: 1,530
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
Post #747813
Posted Monday, July 06, 2009 9:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:15 AM
Points: 344, Visits: 1,530
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
Post #747819
Posted Monday, July 06, 2009 10:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, 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.
Post #747873
Posted Monday, July 06, 2009 10:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,156, Visits: 3,061
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.
Post #747878
Posted Monday, July 06, 2009 10:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Post #747879
Posted Monday, July 06, 2009 10:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 4:09 PM
Points: 577, Visits: 197
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.
Post #747883
Posted Monday, July 06, 2009 10:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,156, Visits: 3,061
Jonathan thank you for confirmation and explanation.
Post #747887
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse