Follow SQLMaster on Twitter
Welcome to SqlServer-QA.net Sign in | Help

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

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

Published Thursday, January 24, 2008 3:28 PM by SQL Master

Comments

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

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

Thursday, January 24, 2008 3:32 PM by Other SQL Server Blogs around the Web

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

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

Thursday, January 24, 2008 4:28 PM by SSQA.net - SqlServer-QA.net
Anonymous comments are disabled