Freigeben über

Tracking VAS in Yukon

  • Artikel

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

  • Anonymous
    January 26, 2005
    > 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.

  • Anonymous
    January 26, 2005
    Slava Oks makes one heck of first impression.

  • Anonymous
    October 07, 2005
    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

  • Anonymous
    February 21, 2007
    Your articles related to VAS are very useful...I appreciate if you feed us more info. Thanks, MohammedU

  • Anonymous
    May 29, 2009
    PingBack from http://paidsurveyshub.info/story.php?title=slava-oks-s-weblog-tracking-vas-in-yukon