SQL Server Memory Usage Query

By: Eli Leiba   |   Updated: 2021-04-15   |   Comments   |   Related: More > Monitoring

Problem

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).

Solution

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.

T-SQL Function

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.

Next Steps

• 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