SQL Server Memory Configuration, Determining MemToLeave Settings

Question

SQL Server Memory Configuration, Determining MemToLeave Settings

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

John.Sansom SSCertifiable Points: 7335 More actions · Answer 1

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

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 One Orange Chip Points: 26778 More actions · Answer 3

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.

John.Sansom SSCertifiable Points: 7335 More actions · Answer 4

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

prash.k.r.p SSC Enthusiast Points: 126 More actions · Answer 5

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

prash.k.r.p (10/22/2012)
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

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.

prash.k.r.p SSC Enthusiast Points: 126 More actions · Answer 7

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

prash.k.r.p (10/23/2012)
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

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.

Duran SSChampion Points: 13259 More actions · Answer 9

Hey Everyone,

Interesting thread, and I have ended up here via various articles because I am seeing alot of the following messages 2-3 times a minute in the logs for a number of databases...,

AppDomain 161 (DatabaseName.dbo[runtime].174) is marked for unload due to memory pressure.

...sometimes it will be just one database several times in one minute, then other times all three will appear. There are more than 3 DB's on the server (11 user DBs in total), but these ones are the only ones to appear with the error. The entire screen can be full of nothing but these errors.

I thought I found my answer here, but it raises more questions. Higher up in the thread I read that if I am on 64bit it's not relevant, we are running Windows 2012 and SQL Server 2012 SP1 build 11.0.3000.0, so perhaps none of this really relates.

As for the physical memory, which reading earlier also does not seem relevant (but I'll include it here anyway), is set at

MIN 61440

MAX The default of 2147483647

...not set by me, when I am in charge I set max appropriately.

I ran the VAS query and got the following results...

Total Available Mem: 137145368572

Max Free Size : 137109232840

...here I am just totally confused. This is in the TBs. The box is physical, not virtual.

I'd love a bit of guidence in getting this cleared up. I'm not sure what way to turn as this point, and the box is critical. Obviously I am more than happy to provide more information.

Thank you for reading, and your time.

Regards,

D

PS: As an aside, I just ran the following query in case it revealed anything extra, from the bottom line onwards it was the same...

SELECT type, virtual_memory_committed_kb, pages_kb

FROM sys.dm_os_memory_clerks

WHERE virtual_memory_committed_kb > 0 OR pages_kb > 0

order by virtual_memory_committed_kb desc;

type virtual_memory_committed_kbpages_kb

MEMORYCLERK_SQLCLR 123612 24712

MEMORYCLERK_SQLBUFFERPOOL89204 978872

OBJECTSTORE_LOCK_MANAGER65536 1160

MEMORYCLERK_SOSMEMMANAGER20912 0

MEMORYCLERK_XE_BUFFER 4608 0

MEMORYCLERK_SQLSTORENG1344 11552

USERSTORE_TOKENPERM 0 8

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Duran (8/18/2015)
I'd love a bit of guidence in getting this cleared up. I'm not sure what way to turn as this point, and the box is critical. Obviously I am more than happy to provide more information.

How good are you at profiling .NET memory usage and reading/changing .NET code? Or even better, what do they need to use SQLCLR in the database for exactly? A lot of cases of app domain unloads can simply be memory leak detection in the .NET hosted API's and SQLOS is unloading the AppDomain as a part of those policies. It's not difficult to trigger that with just a basic DataSet object that you populate with some data. Or use String.Split with a big string as explained by Adam Machanic here http://sqlblog.com/blogs/adam_machanic/archive/2009/04/26/faster-more-scalable-sqlclr-string-splitting.aspx

The simple answer is that you might not be able to figure it out from a SQL Server standpoint, you would have to be reviewing the .NET code and using a performance profiler as well to see what's going on there too.

Duran SSChampion Points: 13259 More actions · Answer 11

Hi Jonathan,

Thank you for replying, well Ive not profiled .Net memory usage before, is this done using SQL profiler/Performance monitor? I can change the code, but I can work with the developers are writing/updating it. Can you recommend a performance profiler that would be good for me, I understand you get what you pay for, but it would need to be free, and that's if I am allowed to install it on a machine in the first place.

I did run perfmon the last time we did a test run, the pain is that the test box is VM running on an already stressed host, but within the test window I saw regular and significant spikes with Granted Workspace Memory and Memory Grants Outstanding (which hit 100, sometimes momentarily, sometimes for a few seconds) at the same time. The process hits two databases on different instance (on different hosts) and I saw the same activity on both). The PLE was quite low as well, but I'd expect that under heavy querying.

I'll have a read of the blog, thanks for the link.

Regards,

Paul.