Find the (available) amount of memory available to SQL Server (instance / machine)

From time to time, I see the following question posted on various SQL forums asking how we can determine the (available / assigned) amount of memory for an SQL Server instance / machine. As we understand that there is no common script or procedure, which we can use in all versions of SQL Server to determine the amount of memory available and assigned to SQL Server instance and machine. Therefore, I decided to write this blog post, where I’ll share a script for each version of SQL Server that will help us determine the total amount of physical memory available on the operating system and the total memory available in SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2012.

SQL Server 2000 Script:

-- To get the total physical memory installed on SQL Server
CREATE TABLE #OS_Available_Memory (
	ID [int]
	,NAME [sysname]
	,Physical_Memory_In_MB [int]
	,Physical_Memory_In_Bytes [nvarchar](512)
	)

INSERT #OS_Available_Memory
EXEC [master]..[xp_msver]

SELECT [Name]
	,[Physical_Memory_In_MB]
	,[Physical_Memory_In_Bytes]
FROM #OS_Available_Memory
WHERE NAME = 'PhysicalMemory'
GO

DROP TABLE #OS_Available_Memory

--To get the minimum and maximum size of memory configured for SQL Server
SELECT *
FROM [master]..[sysconfigures]
WHERE [comment] IN ('Minimum size of server memory (MB)', 'Maximum size of server memory (MB)')

SQL Server 2005 Script:

-- To get the total physical memory installed on SQL Server
SELECT physical_memory_in_bytes / 1024 / 1024 AS [Physical_Memory_In_MB]
	,virtual_memory_in_bytes / 1024 / 1024 AS [Virtual_Memory_In_MB]
FROM [master].[sys].[dm_os_sys_info]

--To get the minimum and maximum size of memory configured for SQL Server
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

SQL Server 2008/200R2 and SQL Server 2012 Script:

-- To get the total physical memory installed on SQL Server
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
	,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
	,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
	,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
	,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
	,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

--To get the minimum and maximum size of memory configured for SQL Server.
SELECT [name] AS [Name]
	,[configuration_id] AS [Number]
	,[minimum] AS [Minimum]
	,[maximum] AS [Maximum]
	,[is_dynamic] AS [Dynamic]
	,[is_advanced] AS [Advanced]
	,[value] AS [ConfigValue]
	,[value_in_use] AS [RunValue]
	,[description] AS [Description]
FROM [master].[sys].[configurations]
WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')

For SQL Server 2000, I used internal system table and extended stored procedure to find out information about available memory to SQL Server instance and machine whereas for SQL Server 2005 and above, I used system view and dynamic management view to retrieve the same information.

About these ads

4 thoughts on “Find the (available) amount of memory available to SQL Server (instance / machine)

  1. Nice post Basit. I just notice one thing. For the SQL 2008/R2 query, the available_page_file_kb is listed as Available_Physical_Memory_in_MB. ->
    [available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB].

    Like

  2. In the ‘SQL Server 2008/200R2 and SQL Server 2012′ Script you have ‘[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]’ whereas I think it should be [available_physical_memory_kb] / 1024 AS [Available_Physical_Memory_In_MB]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s