February 28, 2012 at 4:04 am
Hi
I am using a query from a blog to calculate the VAS usage. The calculation does not make sens as it is telling me I have 7086 gigs of VAS in my system. I only have 55 gigs allocated (64 total). Can someone shed some light on this.
Even just a sum on the region_size_in_bytes gives the same problem.
Here is the query.
With VASummary(Size,Reserved,Free) 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)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
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 VASummary
WHERE Free <> 0
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 28, 2012 at 5:05 am
64 bit?
If so, your calculation is about right, VAS is virtual address space, not physical memory and user VAS on 64 bit servers is 8 TB (iirc)
What are you trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2012 at 5:27 am
VAS that is full can indicate memory presure. But if you say that VAS is 8 Tera on 64 bit systems i suppose I do not understand the concept.
I have a system with 55Gig mem. Page Life expectancy is +- 17min. I want to find out if all the memory is used, thus 55gigs swapped every 17min.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
February 28, 2012 at 5:35 am
If you want to understand VAS, physical memory and the relationship, go find the videos by Mark Russinovich from CDC or TechEd. VAS pressure is more a 32-bit problem than 64-bit. It's hard to fill 64-bit VAS since I haven't yet seen a server with 8 TB of RAM + swap file.
If you're interested in SQL's buffer pool, then you shouldn't be looking at VAS size or usage, look at the SQL buffer pool.
Page life expectency is the average time that a page will live in memory, so if it's 17 minutes it means that on average a page will only remain in the buffer pool for 17 minutes, that's the definition of page life expectency.
You can check perfmon counters (total server memory) to see the total memory SQL is using and the dm_os_memory_clerks to see what's using it within SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2012 at 5:45 am
thanks for the reply, will look these things up to better understand.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply