Check

SELECT physical_memory_in_use_kb/1024    AS [Used by SQL (MB)],

       total_physical_memory_kb/1024     AS [Physical Memory (MB)], 

       available_physical_memory_kb/1024 AS [Available Memory (MB)], 

       total_page_file_kb/1024           AS [Total Page File (MB)], 

       available_page_file_kb/1024       AS [Available Page File (MB)], 

       system_cache_kb/1024              AS [System Cache (MB)],

       system_memory_state_desc          AS [System Memory State]

  FROM sys.dm_os_sys_memory,

       sys.dm_os_process_memory

  WITH (NOLOCK) OPTION (RECOMPILE);

SELECT c.name, c.value, c.value_in_use 

  FROM sys.configurations c 

 WHERE c.[name] IN ('max server memory (MB)','min server memory (MB)');

List Cached Data Per Object in Memory (1)...

SELECT COUNT (1) * 8 / 1024 AS MBUsed, 

    OBJECT_SCHEMA_NAME(object_id) SchemaName, 

name AS TableName, index_id   

FROM sys.dm_os_buffer_descriptors AS bd   

    INNER JOIN   

    (  

        SELECT object_name(object_id) AS name   

            ,index_id ,allocation_unit_id, object_id  

        FROM sys.allocation_units AS au  

            INNER JOIN sys.partitions AS p   

                ON au.container_id = p.hobt_id   

                    AND (au.type = 1 OR au.type = 3)  

        UNION ALL  

        SELECT object_name(object_id) AS name     

            ,index_id, allocation_unit_id, object_id  

        FROM sys.allocation_units AS au  

            INNER JOIN sys.partitions AS p   

                ON au.container_id = p.partition_id   

                    AND au.type = 2  

    ) AS obj   

        ON bd.allocation_unit_id = obj.allocation_unit_id  

WHERE database_id = DB_ID()  

GROUP BY OBJECT_SCHEMA_NAME(object_id), name, index_id   

ORDER BY COUNT (*) * 8 / 1024 DESC

GO

Buffer Pool

Here are a couple of useful scripts from Aaron Bertand...

Plan Cache

Min/Max Memory

Set min/max memory - calculate as below or leave at least 2GB for OS (skip this step on SQL Express)

Some settings for common configurations is documented in the "Change Max and Min Server Memory" section later.

VM Memory

SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)]

  FROM sys.dm_os_sys_memory

SQL Max Worker Threads

SELECT cpu_count AS [Logical CPU Count], 

       hyperthread_ratio AS [Hyperthread Ratio], 

       cpu_count/hyperthread_ratio AS [Physical CPU Count],

       RIGHT(SUBSTRING(@@VERSION, CHARINDEX('<',@@VERSION),4),3) Architecture

  FROM sys.dm_os_sys_info

OPTION (RECOMPILE);

The following table shows the automatically configured maximum number of threads...

ThreadStackSize

The following table shows the ThreadStackSize for each Architecture...

OS Mem

1024MB for every 4 CPU Cores

SELECT (cpu_count/4)*1024 as OS_Mem_MB

  FROM sys.dm_os_sys_info

VM Overhead

SELECT CEILING((20.29+((cpu_count-1)*3.99)+(((((total_physical_memory_kb/1024)+1)/256)-1)*1.89)) AS vm_overhead_MB

  FROM sys.dm_os_sys_info,

       sys.dm_os_sys_memory

Change Max and Min Server Memory

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

go

For a server with 4 vCPUs and 8GB of memory...

-- SQL Max Server Memory = 8192 - (512*2) - 1024 - 91 = 6053

-- SQL Min Server Memory = 6053 - 2048 = 4005

EXEC sys.sp_configure N'min server memory (MB)', N'4005'

go

EXEC sys.sp_configure N'max server memory (MB)', N'6053'

GO

RECONFIGURE WITH OVERRIDE

For a server with 4 vCPUs and 16GB of memory...

-- SQL Max Server Memory = 16384 - (512*2) - 1024 - 152 = 14184

-- SQL Min Server Memory = 14184 - 2048 = 12136

EXEC sys.sp_configure N'min server memory (MB)', N'12136'

go

EXEC sys.sp_configure N'max server memory (MB)', N'14184'

GO

RECONFIGURE WITH OVERRIDE

Memory in Virtual Environments

TODO

Bibliography & References