SQL Server Memory Configuration, Determining MemToLeave Settings

Determining the appropriate memory configuration for a SQL Server platform is a task that all database administrators are required to perform. It is essential to ensuring that an appropriate level of performance can be provided.

I am going to discuss some of the additional memory configuration tweaking that you may wish to undertake so that your environment can provide adequate support for workloads involving managed code, .NET CLR, Linked Servers and extended stored procedures.

Establishing a good foundation

Before considering additional tweaking of your SQL Server platform, it is wise to have already established a good foundation and to have addressed the basics.

To begin with you will want to ensure that the correct settings for both the hardware and software (Windows operating system version and SQL Server Edition) for your particular environment have been applied. You will no doubt also wish to ensure that your choice of configuration makes the maximum possible use of the resource available to you.

There are a number of excellent resources and references regarding how to configure SQL Server that can be found in the SQL Server Administration area.

Additional considerations when configuring SQL server memory

Having established a good foundation, in addition to this you may find that the specific requirements of your individual platform require you to carry out further tweaking.

For example, your environment may dictate that SQL Server share the available server memory with other applications, in which case you will need to consider the distribution of memory resource between these. The ideal configuration choice is that SQL Server runs on its own dedicated server however this may not always be practical.

Now suppose your environment uses a fair amount of managed code such as .NET CLR. 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.

What is 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, Linked Servers and extended stored procedures, 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.

How do I determine my MemToLeave usage?

To assist in assessing your current utilisation, the T-SQL script below can be used to identify the largest available block of virtual address space (MemToLeave) outside of the buffer pool and is sourced from Christian Bolton’s Blog Post – SQL Server memtoleave, VAS and 64-bit

The query utilises the Dynamic Management View (DMV) sys.dm_os_virtual_address_dump which returns information about pages in the virtual address space of the calling process.

WITH VAS_Summary AS
(
	SELECT Size = VAS_Dump.Size,
	Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 0 ELSE 1 END),
	Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base) ^ 0) WHEN 0 THEN 1 ELSE 0 END)
	FROM
	(
		SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
			region_allocation_base_address [Base]
			FROM sys.dm_os_virtual_address_dump
		WHERE region_allocation_base_address <> 0
		GROUP BY region_allocation_base_address
		UNION
		SELECT
			CONVERT(VARBINARY, region_size_in_bytes) [Size],
			region_allocation_base_address [Base]
		FROM sys.dm_os_virtual_address_dump
		WHERE region_allocation_base_address = 0x0 ) AS VAS_Dump
		GROUP BY Size
	)
SELECT
	SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],
	CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]
FROM VAS_Summary WHERE FREE <> 0

How can I tell if I need to allocate more memory to MemToLeave?

There are two key indicators that express a need to assign more memory to MemToLeave.

  1. If the above T-SQL script shows that the amount of available memory is small for the requirements of your platform. (For example, your application/development team may be able to advise on the expected memory requirements of the managed code components that have been developed).
  2. A more pressing indicator takes the form of a variety of warning/error messages raised by either SQL Server or the specific managed code component.

For example, if the MemToLeave region is too small for .NET managed code, a common indicator of this will be the appearance of frequent “Application Domain Unload” messages appearing in the SQL Server Error log. An example message is provided below:

AppDomain 8 (DatabaseName.dbo[runtime].7) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.

Another indicator is an error message that occurs when using Linked Server queries, that states:

“There is insufficient system memory to run this query.”

If you encounter any of these indicators then you almost certainly need to evaluate your SQL Server usage of VAS.

How do I allocate more resources to MemToLeave?

In the event that you determine that you require to increase the size of the MemToLeave area, this can be done by defining the -g startup option for the SQL Server Service.

The value that you assign to the parameter will determine the size of the MemToLeave area of memory.

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 for example 64MB (although dependent on your specific platform), until your original indicators are no longer being raised i.e. your application domain unload messages are no longer appearing.

Detailed instruction for using the -g startup option can be found in the books online article, Using the SQL Server Service Startup Options.

It is important to note that increasing the size of the MemToLeave area will reduce the amount of available memory to the SQL Server Buffer Pool. For this reason it is wise to iterate your tweaking of this parameter in small increments.

Summary

A SQL Server DBA is responsible for ensuring the performance of the platforms they administer.

After completing an initial SQL Server configuration you may need to tweak your memory configuration further in order support managed code. Where appropriate, this can be achieved by using the SQL Server -g startup parameter on 32bit systems, and reducing the max server memory setting in 64bit systems.

I hope you have enjoyed reading this article and that it proves fruitful in your administration of SQL Server. If you have any questions about MemToLeave or anything whatsoever about SQL Server then please feel free to ask.

You may also find interesting…….

 

  • Prem

    Best article on ‘MemToLeave’ ever I read. Thanks.

  • Craig

    Great article. It would be nice to have some examples of -g values though as it’s not included by default in the startup options. I set it to 1024 and now SQL thinks my desktop has 890Gb of available memory. Yes, I did say Gb!

  • Nitin

    Hi,

    I am facing the same issue but in SQL 2008 ->

    Could you please help me in getting rid over following error that I am getting :-

    2011-04-05 08:16:32.31 spid1s AppDomain 70 (SMS_K01.dbo[runtime].69) is marked for unload due to memory pressure.
    2011-04-05 08:16:32.49 spid15s AppDomain 70 (SMS_K01.dbo[runtime].69) unloaded.
    2011-04-05 08:17:02.70 spid243 AppDomain 71 (SMS_K01.dbo[runtime].70) created.

    Details of the server are as below :-

    It’s a SCCM server.

    SQL 2008 SP2 64 Bit.

    Microsoft SQL Server 2008 (SP2) – 10.0.4272.0 (X64)
    Jan 5 2011 22:23:43
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)

    Total Physical Memory – 6GB
    Memory allocated for SQL Server – 2.5GB

    Please help me.

  • http://dirkhondong.wordpress.com Dirk Hondong

    Hi John,

    although it´s quite an old post I have to ask a question here.
    To quote you:
    “Determining the appropriate memory configuration for a SQL Server platform is a task that all database administrators are required to perform”

    Do you have some kind of guideline for “the appropriate” mem config?
    If you take a look on the internet, you often get the advice: get as much memory as possible for your server.
    But sometimes this is not possible (e.g. virtual system where the hosts are running out of mem sooner or later) and you have to suggest a minimum configuration.

    Regards
    Dirk

  • Pingback: SQL Server 2005: There is insufficient system memory to run this query. Drija

  • Humberto

    Hi, John
    In your post “SQL Server Memory Configuration, Determining MemToLeave Settings”, the link “Using the SQL Server Service Startup Options.” doesn’t work.

    Could you fix it? because I’m facing the error “There is insufficient system memory to run this query”.

    Thanks.

  • Manishkumar1980

    Great indeed !

  • James Lupolt

    Thanks for this post. There’s a small error in your transcription of the query you got from Christian Bolton. On the last line, I think you mean ‘WHERE Free 0′.

  • James Lupolt

    Sorry, I mean ‘WHERE Free != 0′. (The SQL ‘not equals’ symbol is being sanitized out of my comment.)

    • http://www.johnsansom.com John Sansom

      The formatting on this post gets out of whack sometimes. I’ve corrected the query syntax so you should be good to go sir.

  • Pingback: The SQL Server Buffer Pool and Buffer Cache Hit Ratio

  • http://www.sunnylandfarms.com Wallace

    John, my Tot.Avail.Mem is 8574878024 and Max Free Size is 6706867200. Is the Max Free Size the MemToLeave?

    • http://www.johnsansom.com John Sansom

      Hi Wallace, based on those numbers sir it looks like you’re using a 64bit server, in which case this post(which is getting old now) is most likely not relevant for you.

      What’s the problem that you are trying to troubleshoot?

  • Pingback: SQL Server CDC; 4 Lessons Learned | Russ Thomas - SQL Judo

  • http://www.sunnylandfarms.com Wallace Houston

    in the SQL Server logs I’m seeing…
    AppDomain 5 (SFIData.dbo[runtime].4) is marked for unload due to memory pressure.
    followed by……
    AppDomain 5 (SFIData.dbo[runtime].4) unloaded.

    Could this be due to MemToLeave settings? this is SQL Server 2008 standard 64-bit
    8 GB total RAM, 6.5 GB allocated to SQL

    • http://www.sunnylandfarms.com Wallace Houston

      We’ve been getting time-out errors from apps which use the database. This was in the SQL Agent error log from last night…..
      Message
      [165] ODBC Error: 0, Unable to complete login process due to delay in opening server connection [SQLSTATE 08001]

      A CLR process was implemented a year ago and runs 4 times per hour. We didn’t seem to have the connection timeouts until it was implemented. I researched the error and talked to the programmer which also researched and set up “garbage collection” on the CLR. It has helped, since we don’t see the problem as often, but it seems the timeouts happen when things are busiest. I’ve never been able to categorically blame it on the CLR. I’ve used sp_who2 and sp_who3 to try and determine the holdup. I’ve yet to see the CLR block another process, but I believe it sucks the life out of the server when it runs.

    • http://www.johnsansom.com John Sansom

      You need to understand how VAS allocations are being consumed within your environment. It could be CLR that is a large consumer yes but you should look to prove this is the case. If proven, the only thing you can do on the SQL Server side of things is to reduce the max server memory setting, effectively reducing the size of your buffer pool and leaving it available for VAS allocations. From a CLR/development standpoint, you could look at reducing the memory overhead of the CLR component.

      See: Understanding the VAS Reservation (aka MemToLeave) in SQL Server for an excellent explanation of the differences between 32bit and 64bit VAS Sizing in SQL Server.

      You can use the query in my post above, as well those in the referenced article to identify what the consumers of your VAS allocations are.

  • Pingback: Less Server Memory? – Technical Steps to avoid increasing more memory | SQL DBA Support . COM - Knowledge Centre for SQL DBAs