sys.configurations contains various configuration parameters for SQL Server, including two that control the memory used by SQL Server:
SELECT c.name, c.value_in_use
FROM sys.configurations c
WHERE c.name like '%server memory%';
Results from my machine:
+------------------------+--------------+
| name | value_in_use |
+------------------------+--------------+
| min server memory (MB) | 256 |
| max server memory (MB) | 1024 |
+------------------------+--------------+
It will tell you the maximum and minimum amount of memory that SQL Server will use. If the number is 2147483647 that indicates SQL Server will use as much memory as it can get, assuming it needs it. This is the default value for max server memory (MB)
.
This query shows various memory quantities, including how much is actually in use by SQL Server:
SELECT upvt.Item
, upvt.Value
FROM (
SELECT *
FROM sys.dm_os_sys_info dosi
) src
UNPIVOT (
[Value] FOR Item IN (
committed_kb
, committed_target_kb
)
) upvt
committed_kb
represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager.
committed_target_kb
represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:
the current state of the system including its load
the memory requested by current processes
the amount of memory installed on the computer
configuration parameters
If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory.
sys.dm_os_sys_memory
shows the state of system memory; that is memory visible to Windows itself:
SELECT upvt.Item
, upvt.[Value]
FROM (
SELECT [Total size of physical memory available to the O/S, in (KB)] = CONVERT(varchar(50), total_physical_memory_kb)
, [Size of physical memory available, in KB] = CONVERT(varchar(50), available_physical_memory_kb)
, [Size of the commit limit reported by the operating system in KB] = CONVERT(varchar(50), total_page_file_kb)
, [Total amount of page file thatis not being used, in KB] = CONVERT(varchar(50), available_page_file_kb)
, [Total amount of system cache memory, in KB] = CONVERT(varchar(50), system_cache_kb)
, [Total amount of the paged kernel pool, in KB] = CONVERT(varchar(50), kernel_paged_pool_kb)
, [Total amount of the nonpaged kernel pool, in KB] = CONVERT(varchar(50), kernel_nonpaged_pool_kb)
, [Description of the memory state. See the table below] = CONVERT(varchar(50), system_memory_state_desc)
FROM sys.dm_os_sys_memory
) src
UNPIVOT (
[Value] FOR Item IN (
[Total size of physical memory available to the O/S, in (KB)]
, [Size of physical memory available, in KB]
, [Size of the commit limit reported by the operating system in KB]
, [Total amount of page file thatis not being used, in KB]
, [Total amount of system cache memory, in KB]
, [Total amount of the paged kernel pool, in KB]
, [Total amount of the nonpaged kernel pool, in KB]
, [Description of the memory state. See the table below]
)
) upvt;
Results on my system:
+-----------------------------------------------------------------+-----------------------------------+
| Item | Value |
+-----------------------------------------------------------------+-----------------------------------+
| Total size of physical memory available to the O/S, in (KB) | 16662488 |
| Size of physical memory available, in KB | 2496588 |
| Size of the commit limit reported by the operating system in KB | 33323116 |
| Total amount of page file thatis not being used, in KB | 17849648 |
| Total amount of system cache memory, in KB | 3447208 |
| Total amount of the paged kernel pool, in KB | 1186844 |
| Total amount of the nonpaged kernel pool, in KB | 257484 |
| Description of the memory state. See the table below | Available physical memory is high |
+-----------------------------------------------------------------+-----------------------------------+