Tracking VAS in Yukon
When we started working on the next release of SQL Server, Yukon, we realized that supportability features are keys to our success. Eventually I am planning on covering ones that are related to low level "stuff", something that many of you, I know, really interested in, i.e. memory management and scheduling. Here is the quick look at how you can monitor and diagnose Virtual Address Space, VAS, usage inside of SQL Server. In this example I assume your understanding of VAS and how it is handled by Windows. If you don't soon I will cover it so that we can talk about SQL Server's memory manager in full.
The query below can be executed against server periodically to identify if VAS keep on shirking. Constant VAS shrinkage could indicate potential problem. Even though Yukon can recover from VAS pressure much better than SQL2000, if there is a real leak coming from external components loaded into our VAS we can't do much....
(Disclaimer: Depending on Beta version of Yukon, you might need to change columns names. In addition I don't pretend to be a T-SQL guru, the example below is for demonstration purposes only. I am pretty sure that you guys can come up with better queries :-))
CREATE VIEW vasummary AS
select
Size = VaDump.Size,
Reserved = sum(case (convert (INT,VaDump.Base) ^ 0) when 0 then 0 else 1 end),
Free = sum(case (convert (INT,VaDump.Base) ^ 0x0) when 0 then 1 else 0 end)
from
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
---
select CONVERT (varbinary,sum(region_size_bytes)) AS Size,
allocation_base AS Base
from sys.dm_os_virtual_address_dump
where allocation_base <> 0x0
group by allocation_base
UNION
(
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
---
select CONVERT (varbinary,region_size_bytes), allocation_base
from sys.dm_os_virtual_address_dump
where allocation_base = 0x0)
)
as VaDump
group by Size
--- Get vasummary information: Number of regions of a given size in SQL Server Size and their status
---
select * from vasummary
--- Retrieve max available block
---
select max(size) from vasummary where Free <> 0
--- Get sum of all free regions
---
select sum(size*Free) from vasummary where Free <> 0
Here is example of output:
0x0000000000044000 1 0
0x0000000000048000 1 0
0x000000000004A000 1 0
0x000000000004F000 0 1
0x0000000000050000 0 1
0x0000000000052000 1 0
0x0000000000058000 2 0
0x000000000005A000 2 0
0x0000000000061000 1 0
0x0000000000070000 0 1
0x0000000000078000 1 0
0x0000000000079000 1 0
0x0000000000080000 325 1
0x0000000000082000 1 0
0x0000000000084000 1 1
0x000000000008B000 0 1
0x000000000008C000 1 0
0x0000000000090000 1 1
0x0000000000092000 2 0
0x0000000000093000 1 0
0x0000000000094000 1 0
0x0000000000097000 1 0
0x000000000009B000 1 0
0x000000000009E000 1 0
0x00000000000A0000 0 1
0x00000000000A7000 0 1
0x00000000000A9000 1 0
0x00000000000AC000 2 1
0x00000000000BC000 0 1
0x00000000000C0000 1 0
0x00000000000C4000 1 0
0x00000000000C6000 1 0
0x00000000000C8000 1 0
0x00000000000F0000 0 1
0x0000000000100000 16 0
0x0000000000102000 2 0
0x0000000000103000 1 0
0x000000000010A000 1 0
0x0000000000110000 8 0
0x0000000000134000 1 0
0x000000000015B000 1 0
0x000000000015E000 1 0
0x0000000000160000 4 0
0x00000000001B0000 1 0
0x00000000001DC000 1 0
0x0000000000220000 0 1
0x0000000000250000 0 1
0x0000000000260000 1 0
0x000000000026F000 0 1
0x0000000000270000 0 1
0x0000000000280000 1 0
0x00000000002C5000 1 0
0x00000000002F0000 0 1
0x0000000000340000 0 1
0x0000000000356000 1 0
0x0000000000360000 0 1
0x0000000000400000 338 0
0x0000000000800000 1 0
0x0000000000802000 1 0
0x0000000000900000 0 1
0x0000000001D29000 1 0
0x0000000004F56000 1 0
0x000000000F317000 0 1
(110 row(s) affected)
--------------------------------------------------------------
0x000000000F317000
(1 row(s) affected)
-----------
294117376
(1 row(s) affected)
The interesting point here is that we use this type of scripts to debug/diagnose VAS problems in our every day work This also means that we try to debug/diagnose SQL Server issues without attaching a debugger just relying on the dmvs and other supportability tools we have so that by the time we ship the product we will have the right set of tools for you :-).