The memory space and ram space allocated to my SQL Server

Question

How do I find out how much memory space and RAM has been allocated to my SQL Server. SQL Server version is SQL Server 2012 11.0.5343.0.

Please help. I have searched online and all i can find is how much memory is being used by my SQL Server.

I need to find out how much memory and RAM have been allocated to my SQL Server already. So, that I can figure out how much memory and RAM are not being used.

Thanks!

Answer 1

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 |
+-----------------------------------------------------------------+-----------------------------------+