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 :-).

Comments (5)

  1. dls says:

    > 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….

    Thank you, comprehensive tool inclusion really helps learning curves. If I already don’t know the product, knowing that there’s an after-market tool to help with xyz is kind of unlikely.

  2. Slava Oks makes one heck of first impression.

  3. Peng Li says:

    Thank you very much and the information is very useful.

    BTW, in the current Yukon, the column names changed:

    allocation_base ==> region_allocation_base_address

    region_size_bytes ==> region_size_in_bytes

  4. Mohammedu says:

    Your articles related to VAS are very useful…I appreciate if you feed us more info.

    Thanks,

    MohammedU