SQL Server Memory Usage Query

By:   |   Comments (2)   |   Related: > 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.

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
-- =============================================
CREATE FUNCTION dbo.fn_CheckSQLMemory()
   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)
   declare @strtSQL datetime
   declare @currmem int
   declare @smaxmem int
   declare @osmaxmm int
   declare @osavlmm int 
   -- SQL memory
      @strtSQL = sqlserver_start_time,
      @currmem = (committed_kb/1024),
      @smaxmem = (committed_target_kb/1024)           
   FROM sys.dm_os_sys_info;
   --OS memory
      @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)

Here is how to use the function:

USE master 
select * from dbo.fn_CheckSQLMemory()

Here are the results:

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, September 8, 2023 - 7:49:47 AM - Tom Back To Top (91544)
sqlserver_start_time as SQLServer_Start_DateTime,
(committed_kb/1024) as SQL_current_Memory_usage_mb,
(committed_target_kb/1024) as SQL_Max_Memory_target_mb,
(total_physical_memory_kb/1024) as OS_Total_Memory_mb ,
(available_physical_memory_kb/1024) as OS_Available_Memory_mb
FROM sys.dm_os_sys_info, sys.dm_os_sys_memory;

Now run it against multiple instances from the registered servers section.

Tuesday, March 7, 2023 - 5:18:15 PM - Ray Goble Back To Top (90986)
I find that 'committed_target_kb' can be inaccurate. It can often only show the original MaxMem setting for SQL at the time of installation. I understand that this is a known bug that will not be resolved. I've verified this behavior with every instance on which I've tested this, and thus changed mine to call from sp_configure, so it works fine now.

get free sql tips
agree to terms