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.