Memory information through a few DMVs

One of my traits is that I am always trying to figure out how to get the same information in a variety of ways. When I go out to client sites to do performance assessments, often the DBAs are constrained in what they can do on their own boxes. For many this seems like a foreign concept, but many DBAs in very large companies do not have enough system privileges to run monitoring tools, even some of the tools we take for granted like Perfmon. Given this fact, I really like to have multiple methods to get as much information as I can in order to help assess the performance of database servers.

SQL Server 2005 really made my job a lot easier due to the dynamic management views and functions Microsoft put into it. If SQL Server 2008 is any indication, these DMV and DMFs will not only stay around, they will grow in number and become more complex over time.

While trying to redo the memory modules of my classes, I started playing around with some of the dynamic management views the other day and started to explore how I could get memory information from them. While there are a few new memory views in SQL Server 2008, I wanted a query that could work both in 2005 and 2008.

I noticed that I could quite a bit of basic memory information from the system information, so I thought I would show you a very basic query you could use for your own systems. This query attempts to show some of the basic memory areas in a SQL Server 2005 or 2008 installation.

–Memory columns of sys.dm_os_sys_info

SELECT

–Amount of physical memory on server

physical_memory_in_bytes

–Amount of physical memory available to the process in user mode

–Should be 2GB unless /3GB used

,virtual_memory_in_bytes

–Committed physical memory in buffer pool

–Does not include MemToLeave memory area

,bpool_committed AS ‘Number of 8KB buffers in buffer pool’

, bpool_commit_target AS ‘Number of 8KB buffers needed by the buffer pool’

,CASE

      WHEN bpool_commit_target > bpool_committed THEN ‘Extra memory needed from OS for Buffer Pool’

      WHEN bpool_commit_target < bpool_committed THEN ‘Memory may be released from Buffer Pool to OS’

      END AS ‘Status of Dynamic Memory’  

, bpool_visible AS ‘Number of 8KB Buffers in Buffer Pool that are directly accessible in the processes VAS.’

/* When AWE is not used. When memory target reached, the value will be the same as bpool_committed

When AWE is used on 32-bit. This value represents the size of the AWE mapping window used to access physical memory allocated by the buffer pool. Since the size of the AWE mapping window is bound by the process VAS the value of this column will be smaller than the value of bpool_committed.

If the value of this column becomes too low, you may receive out of memory errors.

*/

FROM sys.dm_os_sys_info

Some good information from this one DMV but it doesn’t show the memory utilized by the MemToLeave memory area. For this we need to look at another DMV.

Note:  The following CTE was stolen from Christian Bolton’s blog post: SQL Server memtoleave, VAS and 64-bit(http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx)  The CTE is a great adaptation of Slava Oaks view that he posted in one of his blog posts:  Tracking VAS in Yukon (http://blogs.msdn.com/slavao/archive/2005/01/26/360759.aspx).

–CTE built off of Slava Oaks view by Christian Bolton

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

So here are two DMVs which will give you another way to find out SQL Server memory information without going outside of SQL Server to do so. For those database administrators who do not have access to base server information, at least with sysadmin privileges or VIEW SERVER STATE permissions you can get enough information to start your memory baselines.