Check configuration and usage of RAM and page file. Useful as quick overview for memory performance problem entry point. You will see if SQL Server using all memory according to configuration and how much memory is available on the server. Including information about Windows Page File usage.
SELECT c.value AS [max server memory (MB)], c.value_in_use AS [memory in use (MB)], m.total_physical_memory_kb/1024 AS [total RAM (MB)], m.available_physical_memory_kb/1024 AS [available RAM (MB)], m.total_page_file_kb/1024 AS [page file (MB)], m.available_page_file_kb/1024 AS [available pagefile (MB)] FROM sys.configurations c JOIN sys.dm_os_sys_memory m ON 1 = 1 WHERE c.name like '%max server memory%' ORDER BY name
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| max server memory (MB) | bigint | SQL Server max memory configuration limit in megabytes (MB). |
| memory in use (MB) | bigint | Size of memory in use by SQL Server, in MB. |
| total RAM (MB) | bigint | Total size of physical memory available to the operating system, in megabytes (MB). |
| available RAM (MB) | bigint | Size of physical memory available, in MB. |
| page file (MB) | bigint | Size of the commit limit reported by the operating system in MB. |
| available pagefile (MB) | bigint | Total amount of page file that is not being used, in MB. |