November 12, 2009 at 9:09 am
I use the query below to determine the amount of fragmentation and free space I have in the MemToLeave area of memory in my SQL-2008 virtual-machine environment.
I'm getting non-sensical results.
RAM: 7.75 GB
Max buffer memory: 4.5 GB
1 SQL-2008 instance (64-bit Enterprise Edition).
Here is the query
-- from http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/
-- MemToLeave values for instance of SQL Server:
-- [Total avail mem, KB] : total memory assigned to MemToLeave
-- [Max free size, KB] : free memory available to MemToLeave
;WITH VAS_Summary AS
(
SELECT
[Size] = VAS_Dump.Size,
Reserved = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VAS_Dump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],
region_allocation_base_address [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) [Size],
region_allocation_base_address [Base]
FROM
sys.dm_os_virtual_address_dump
WHERE
region_allocation_base_address = 0x0
) AS VAS_Dump
GROUP BY [Size]
)
--Get size of all free mem. areas
--http://www.sqlservercentral.com/Forums/Topic624152-386-2.aspx?Update=1
--
SELECT
CAST(Size AS BIGINT)/1024 AS [Free size, KB]
FROM
VAS_Summary
WHERE
Free <> 0;
Here are the top 15 values (in ascending order);
the top 2 values are in the terabytes range!
Free size, KB
------------
80160
91108
97756
99968
139964
146868
256900
256928
345500
351780
611692
2072960
2095856
1863700460
6704040256
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 12, 2009 at 11:08 am
I realize now that this is because this query is measuring VIRTUAL memory, which in 64-bit is enormous.
What I'm actually after is the largest chunk of PHYSICAL memory available to the SQL CLR.
How can I get that?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply