Mar 16, 2009

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 platform/s 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.

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 can add a comment using the form below, send me an email or contact me on Twitter @JohnSansom.

You may also find interesting…….

• How much memory is each SQL Server database using?
• The SQL Server Buffer Pool and Buffer Cache Hit Ratio
• How to identify the most costly SQL Server queries using DMV’s
• Top 10 Junior DBA Interview Tips
• SQL Server Essentials – Part 1: The Database Administrator’s Primary Responsibility