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 Monday, July 6, 2009 1:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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.



John Sansom (@sqlBrit) | www.johnsansom.com
Post #747996
Posted Monday, July 6, 2009 1:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #748000
Posted Monday, July 6, 2009 1:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:26 PM
Points: 1,081, Visits: 1,504
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
Post #748002
Posted Monday, July 6, 2009 1:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 1,220, Visits: 3,227
-------------
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.
Post #748008
Posted Monday, July 6, 2009 2:04 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
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
Post #748036
Posted Monday, July 6, 2009 2:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
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….




Thanks Tim, appreciate it.



John Sansom (@sqlBrit) | www.johnsansom.com
Post #748042
Posted Monday, July 6, 2009 3:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
John Sansom (7/6/2009)

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.



You certainly took on a big topic for a first article.


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.


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.


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.


The original source for that query which I quote often in forums posts is:

SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog


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.


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.


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?


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 Professional SQL Server 2008 Internals and Troubleshootingwill be available in January, 2010 that has an entire chapter dedicated to memory in SQL Server.


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 #748094
Posted Monday, July 6, 2009 11:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
I decided to post a blog post to cover this in more depth:

Understanding the VAS Reservation (aka MemToLeave) in SQL Server

It's quite extensive an I hope it helps explain this in more detail.


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 #748316
Posted Tuesday, July 7, 2009 2:03 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
Good 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.



John Sansom (@sqlBrit) | www.johnsansom.com
Post #748356
Posted Monday, October 22, 2012 5:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 8:38 AM
Points: 2, Visits: 6
HI Jonathan,

I have one issue here, If nay idea please let me know

Some 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
Post #1375352
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse