RE: SQL Server Memory Configuration, Determining MemToLeave Settings

One Orange Chip

Points: 26778

July 6, 2009 at 3:10 pm

#1020687

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.