T-SQL Script to Check MemToLeave’s Size

This below script is sourced from here. It checks not only the current size of MemToLeave area and also the size of the largest available memory block out of buffer pool.

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

And definition of 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.

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s