Complete a Server Assessment – Memory Settings and Counters

Below you will find the code to get information about memory counters and settings.  Simply select the text and copy to the Clipboard.

SELECT
total_physical_memory_kb/1024 AS ‘Total Physical Memory MB’
, available_physical_memory_kb/1024 AS ‘Available Physical Memory MB’
, total_page_file_kb/1024 AS ‘Total Page File MB’
, available_page_file_kb/1024 AS ‘Available Page File MB’
, CONVERT(DECIMAL(9,2),100 – (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))))
AS ‘Percentage Used’
, system_memory_state_desc
FROM sys.dm_os_sys_memory;

SELECT Name
, value_in_use
FROM sys.configurations
WHERE Name IN (‘query wait (s)’, ‘min memory per query (KB)’, ‘Optimize for Ad Hoc Workloads’)
UNION
SELECT [name]
, value_in_use
FROM sys.configurations
WHERE [name] LIKE ‘max server memory%’
UNION
SELECT [name]
, value_in_use
FROM sys.configurations
WHERE [name] LIKE ‘min server memory%’

–Code below Source: Pinal Dave – blog.sqlauthority.com/2017/10/20/sql-server-turn-optimize-ad-hoc-workloads/

SELECT  AdHoc_Plan_MB, Total_Cache_MB,
CONVERT(DECIMAL(9,2),AdHoc_Plan_MB*100.0 / Total_Cache_MB) AS ‘AdHoc %’
FROM (
SELECT SUM(CASE
WHEN objtype = ‘adhoc’
THEN CONVERT(BIGINT,size_in_bytes)
ELSE 0 END) / 1048576.0  AS ‘AdHoc_Plan_MB’,
SUM(CONVERT(BIGINT,size_in_bytes)) / 1048576.0  AS ‘Total_Cache_MB’
FROM sys.dm_exec_cached_plans) T

–From https://www.sqlshack.com/insight-into-the-sql-server-buffer-cache/
–Number of database pages by database

SELECT
databases.name AS ‘DatabaseName’,
Count(*)  AS ‘NumberOfPages’,
COUNT(*) * 8 / 1024 AS ‘MBUsed’
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.databases
ON databases.database_id = dm_os_buffer_descriptors.database_id
GROUP BY databases.name
ORDER BY MBUsed DESC;

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.