How to use TSQL to find the size of total and free physical memory within the SQL Server?

Published 24 January 08 03:28 PM | SQL Master 

Recently I have to search for the information on the total size of free physical memory on the SQL Server which is causing lots of performance issues, when reported to CSS they have supplied the following TSQL to get more information in this regard:

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(Size*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS INT)/1024
    AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0

Comments

# Other SQL Server Blogs around the Web said on January 24, 2008 3:32 PM:

Recently I have to search for the information on the total size of free physical memory on the SQL Server

# SSQA.net - SqlServer-QA.net said on January 24, 2008 4:28 PM:

Recently I have to search for the information on the total size of free physical memory on the SQL Server

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.

Search

Go

This Blog

«January 2008»
SMTWTFS
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789

Syndication