Click on the banner to learn about and purchase my database training on Azure

SQL Server and Azure SQL Database - How to return CPU and Memory usage using T-SQL

Reading Time: 3 minutes

A very common day-to-day need for a database administrator is to analyze the instance's CPU usage. For this reason, I will share in this article, a Transact-SQL (T-SQL) query to get this information from a SQL Server 2008+ database or Azure SQL Database.

Although it is possible to obtain the CPU usage using graphical tools, it is often interesting to be able to access this information using a query, as you can store this history in a table, generate alerts and custom actions according to the value obtained, create graphs in Power BI , etc…

How to return CPU usage using T-SQL

To return the server's CPU usage using T-SQL, use the script below:

Return example:

IMPORTANT
The above script uses the sys.dm_os_ring_buffers DMV and the Extended Event “SystemHealth”, enabled by default. You don't need to activate anything to use this script and it always returns the CPU usage of the last 256 minutes (a little more than 4h). If the instance is restarted, this data will be lost.

This script has been tested on Azure SQL Database and SQL Server 2008 and 2019.

How to return memory usage using T-SQL

To return the server's memory usage using T-SQL, use the script below. With it, it is possible to identify if there is memory pressure and if it is being caused by the OS or by the SQL Server itself.

The first point to note is the “RmNotification” column, which tells us the type of notification generated:

  • RESOURCE_MEMPHYSICAL_HIGH – SQL has room to allocate more memory
  • RESOURCE_MEMPHYSICAL_LOW – Low internal physical or system memory
  • RESOURCE_MEM_STEADY
  • RESOURCE_MEMVIRTUAL_LOW – Range of virtual addresses for SQL Server is running out

If the “RmNotification” column displays a “RESOURCE_MEMPHYSICAL_LOW” message, it means that the memory was low and this generated this alert in the Ring Buffer.

To understand what was down, let's look at the “IndicatorsProcess” and “IndicatorsSystem” columns.

If the “IndicatorsProcess” column returns 0 and the “IndicatorsSystem” column returns a value other than 0, it means that the memory pressure problem affected the entire system. But if the opposite happens, it means that only a process that suffered memory pressure and not the whole system (but it doesn't say which process it was).

Possible values ​​for the “IndicatorsProcess” and “IndicatorsSystem” columns:

  • IDX_MEMPHYSICAL_HIGH (High Physical Memory) = 1
  • IDX_MEMPHYSICAL_LOW (Low Physical Memory) = 2
  • IDX_MEMVIRTUALL_LOW (Low Virtual Memory) = 4

Result example:

If you need a simpler query, you can also use the “dm_os_process_memory” DMV:

Result:

I had already shared these scripts above in the article SQL Server - Useful DBA Queries You Always Have to Look for on the Internet, but most people ended up not finding it when they needed it and that's why I decided to create this article.

I hope you enjoyed this post.
A big hug and even more!