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.