SQL Server Memory Usage Query
By: Eli Leiba | Updated: 2021-04-15 | Comments (1) | Related: More > Monitoring
One of the biggest resources SQL Server likes to use is memory, so it can store as much data in memory and reduce reading from disk. There are many memory counters you can use to track memory usage and in this tip I provide a simplified method for checking memory usage for a SQL Server instance. This is a very simple function, but it can be used to monitor overall memory consumption (and this is the most common case).
My solution involves creating a SQL Server table function in the master database called dbo.fn_CheckSQLMemory.
The function fills the resulting table parameter with this data:
- SQL starting date and time
- Current memory used by SQL in MB
- Max memory that SQL can reach (target memory in MB)
- Maximum available memory on the server machine OS in MB
- Available memory at OS level in MB
All this information is gathered from two memory related DMVs (dynamic management views): sys.dm_os_sys_info and sys.dm_os_sys_memory.
Sys.dm_os_sys_info returns a miscellaneous set of useful information about the computer and about the resources available and consumed by SQL Server and sys.dm_os_sys_memory returns memory information from the operating system.
To learn more about SQL Server memory settings and usage, you can read all about it on this link.
Here is the T-SQL code for the multi statement table function, which I created in the master database.
-- ============================================= -- Author: Eli Leiba- Create date: 01-04-2021 -- Description: Check current SQL memory status compared to the OS status -- ============================================= SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION dbo.fn_CheckSQLMemory() RETURNS @Sql_MemStatus TABLE ( SQLServer_Start_DateTime datetime, SQL_current_Memory_usage_mb int, SQL_Max_Memory_target_mb int, OS_Total_Memory_mb int, OS_Available_Memory_mb int) AS BEGIN declare @strtSQL datetime declare @currmem int declare @smaxmem int declare @osmaxmm int declare @osavlmm int -- SQL memory SELECT @strtSQL = sqlserver_start_time, @currmem = (committed_kb/1024), @smaxmem = (committed_target_kb/1024) FROM sys.dm_os_sys_info; --OS memory SELECT @osmaxmm = (total_physical_memory_kb/1024), @osavlmm = (available_physical_memory_kb/1024) FROM sys.dm_os_sys_memory; INSERT INTO @Sql_MemStatus values (@strtSQL, @currmem, @smaxmem, @osmaxmm, @osavlmm) RETURN END GO
Here is how to use the function:
USE master GO select * from dbo.fn_CheckSQLMemory() GO
Here are the results:
Above you can see the 5 columns that are returned. This shows the current memory SQL Server is using as well as total memory on the machine and available memory on the machine.
- You can create and compile this multi statement table function in the master database of any of your SQL Servers in order to quickly monitor the memory current status of your SQL instances.
- This function was successfully tested using SQL Server 2019, but should work for SQL Server 2012 and later.
About the author
View all my tips
Article Last Updated: 2021-04-15