InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:10:59 GMT20http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]prash.k.r.p (10/23/2012)[/b][hr]HI ,Vas reaches up to 90 %, i mean when we check total memory is 108 mb and the free space would be below 20 mb like that. it is not coming down until it restarts SQl server.Thanks.KRP[/quote]That doesn't answer the question I asked. I am sorry, but unless you answer the specific questions I ask about this problem, I can't help you understand the causes beyond what is in the article. I don't have time to ask the same questions over and over and chase down your problem for you if you aren't going to answer the questions.Tue, 23 Oct 2012 06:12:50 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxHI ,Vas reaches up to 90 %, i mean when we check total memory is 108 mb and the free space would be below 20 mb like that. it is not coming down until it restarts SQl server.Thanks.KRPTue, 23 Oct 2012 03:15:44 GMTprash.k.r.phttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]prash.k.r.p (10/22/2012)[/b][hr]HI Jonathan,I have one issue here, If nay idea please let me knowSome of my SQL server are not releasing a VAS memory so we need to restart the server every time to release it. any cause or any solution / settings needed please. Thanks in advance.KRP[/quote]What VAS consumers exist in the instance? The only thing you can do is start removing them one at a time to find the one that is causing the problem. If you can't do that, upgrade to 64-bit, where you won't have problems caused by fragmentation of the VAS.Mon, 22 Oct 2012 12:09:25 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxHI Jonathan,I have one issue here, If nay idea please let me knowSome of my SQL server are not releasing a VAS memory so we need to restart the server every time to release it. any cause or any solution / settings needed please. Thanks in advance.KRPMon, 22 Oct 2012 05:39:48 GMTprash.k.r.phttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxGood Morning Everyone!Just to give you all a quick update, the incorrect statements referring to SSIS were amended promptly last night 22:30 GMT and so the corrected version is already live on the site and has replaced the prior version. Thanks go to Steve Jones for helping me via Twitter last night to get this sorted swiftly.Jonathan has also kindly provided what is believed to be the definitive original source of the T-SQL code in the article and so this reference has been incorporated too. This update is currently awaiting approval by the editor before being published shortly.I originally got into Blog authoring and then later writing articles because I love working with SQL Server technology but most of all because I enjoy helping others. Through collaboration and peer support we have now together produced an even better source of information for others to benefit from. This to me is a brilliant outcome and is in my opinion what makes SQL Server Central such a great community.Tue, 07 Jul 2009 02:03:41 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxI decided to post a blog post to cover this in more depth:[url=http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx]Understanding the VAS Reservation (aka MemToLeave) in SQL Server[/url]It's quite extensive an I hope it helps explain this in more detail.Mon, 06 Jul 2009 23:45:43 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]John Sansom (7/6/2009)[/b][hr]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.[/quote]You certainly took on a big topic for a first article. [quote]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.[/quote]Raising awareness of things is good, but putting advanced configuration information like using -g without explaining its impact in an article that is already one of the top 10 returns for a search on MemToLeave is open season for misuse of the startup option.[quote]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. [/quote]The original source for that query which I quote often in forums posts is:[url=http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx]SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog[/url][quote]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.[/quote]I've published incorrect information in the past, it happens. I try my best to avoid doing so, but I can misunderstand something, or leave out pertinent information as well. Correcting the incorrect information is always important so that it doesn't add wrong information to the community knowledge base. This however, happens less frequently than you'd expect, so as I'm sure you've seen in the forums, people make changes based on incorrect information and then at times have bigger problems. That is my big problem with misinformation going onto big sites like this one.[quote]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?[/quote]I have a quite extensive coverage of the VAS reservation that I wrote a while back in word to publish at some point after explaining it a few dozen times on the forums. I'll either put it in as an article here, or blog it later today so it can be referenced.The best information to date is available in Ken Henderson's books on SQL Server Internals (these can be hard to come by). However, a book by Christian Bolton titled [url=http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284/ref=sr_1_1?ie=UTF8&s=books&qid=1246914402&sr=8-1]Professional SQL Server 2008 Internals and Troubleshooting[/url]will be available in January, 2010 that has an entire chapter dedicated to memory in SQL Server.Mon, 06 Jul 2009 15:10:06 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]2 Tim 3:16 (7/6/2009)[/b][hr]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….[/quote]Thanks Tim, appreciate it.Mon, 06 Jul 2009 14:08:12 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]2 Tim 3:16 (7/6/2009)[/b][hr]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….[/quote][b][size="2"]++1[/size][/b]Mon, 06 Jul 2009 14:04:00 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx-------------dependswe 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.Mon, 06 Jul 2009 13:39:46 GMTPei Zhu-415513http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxJohn,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….Mon, 06 Jul 2009 13:32:01 GMT2 Tim 3:16http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]Pei Zhu (7/6/2009)[/b][hr]I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.[/quote]dependswe 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 applicationMon, 06 Jul 2009 13:30:19 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxHi 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.Mon, 06 Jul 2009 13:19:58 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxJonathan thank you for confirmation and explanation.Mon, 06 Jul 2009 10:44:45 GMTPei Zhu-415513http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxJohn,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.Mon, 06 Jul 2009 10:42:07 GMTammo-963576http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxI 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.[quote]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.[/quote]This is very wrong. SSIS is its own process, and doesn't use SQL Server Memory allocations from MemToLeave.[quote]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.[/quote]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. [quote]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.[/quote]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.Mon, 06 Jul 2009 10:40:11 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxMemtoleave has no effect on 64 bit instance. On my instance with 64 GB memory, I ran into VAS fragmentation few times in the past.Mon, 06 Jul 2009 10:40:04 GMTPei Zhu-415513http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxIs 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.Mon, 06 Jul 2009 10:25:06 GMTmojo-168709http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]Pei Zhu (7/6/2009)[/b][hr]I do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.[/quote]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.Mon, 06 Jul 2009 09:19:41 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspx[quote][b]2 Tim 3:16 (7/6/2009)[/b][hr]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 -g512your 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 - sqlservershow me the current used mem-to-leave? (Which is just a little bit off from the formula above)Thanks for the query and article![/quote]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.[url=http://msdn.microsoft.com/en-us/library/cc267846.aspx]http://msdn.microsoft.com/en-us/library/cc267846.aspx[/url]Mon, 06 Jul 2009 09:16:09 GMTJohn.Sansomhttp://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxI do not think it would be a concern for 64 bit sql instance. Correct me if I am wrong.Mon, 06 Jul 2009 08:35:54 GMTPei Zhu-415513http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxJust 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 -g512your 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 - sqlservershow me the current used mem-to-leave? (Which is just a little bit off from the formula above)Thanks for the query and article!Mon, 06 Jul 2009 07:47:34 GMT2 Tim 3:16http://www.sqlservercentral.com/Forums/Topic742071-1576-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Administration/67192/">SQL Server Memory Configuration, Determining MemToLeave Settings</A>[/B]Thu, 25 Jun 2009 11:50:16 GMTJohn.Sansom