Amount of Memory Available to SQL Server

thoughtful

You might have to answer the questions asking how you can determine the available memory versus the assigned memory for a SQL Server instance.  The script to 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, SQL Server 2012, and SQL Server 2014 is slightly different by SQL Server version.

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_kb / 1024 AS [Physical_Memory_In_MB]
      ,virtual_memory_kb / 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/2008 R2 and SQL Server 2012/2014 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)')
Advertisements

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