VAS Summary

Forrás/Source: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

 1WITH VASummary(Size,Reserved,Free) AS
 2(SELECT
 3    Size = VaDump.Size,
 4    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
 5    WHEN 0 THEN 0 ELSE 1 END),
 6    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
 7    WHEN 0 THEN 1 ELSE 0 END)
 8FROM
 9(
10    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
11    AS Size, region_allocation_base_address AS Base
12    FROM sys.dm_os_virtual_address_dump 
13    WHERE region_allocation_base_address <> 0x0
14    GROUP BY region_allocation_base_address 
15 UNION  
16    SELECT CONVERT(VARBINARY, region_size_in_bytes),
17                   region_allocation_base_address
18    FROM sys.dm_os_virtual_address_dump
19    WHERE region_allocation_base_address  = 0x0
20)
21AS VaDump
22GROUP BY Size)
23 
24 
25SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,
26CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 
27FROM VASummary 
28WHERE Free <> 0