View memory usage by database on an SQL server

Question

My team (no DBAs in the team) is responsible for a database hosted on an SQL server with multiple other databases used by other areas of the business. Memory usage has recently been spiking to 100% on the server, causing performance issues for all the applications making use of this common database server.

We do not think our database is responsible for the spikes, but I lack the knowledge to prove it. Is there any way to check memory usage by database to identify the culprit?

What I tried:

• Microsoft support provides queries to see memory usage by memory-optimized objects, but following these steps show that we have no 0 MB. perhaps our database does not use memory-optimized objects? https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitor-and-troubleshoot-memory-usage?view=sql-server-ver16

• This page describes how to see memory usage at the server-level, but it does not help me to determine which database is utilizing the memory. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver16

Any pointers would be helpful, even if they are just questions I should direct to our IT group.

Answer 1

Memory usage has recently been spiking to 100% on the server, causing performance issues...

How do you know Memory pressure is the source of your performance issues? It is normal, by design, for SQL Server to utilize all of the Memory you allow for it on the server:

By default, a SQL Server instance may over time consume most of the available Windows operating system memory in the server. Once the memory is acquired, it will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process.

This is because SQL Server caches a multitude of things in Memory, including data statistics, query plans, and frequently used data pages, to improve performance and minimize how often it needs to read from disk.

If you want to determine if your server really is under Memory pressure, then you can use a tool such as sp_PressureDetector by Erik Darling. It's helpful by providing a distinction between how the Memory is being used - for its usual use cases of caching vs contention due to consumption by Memory grants for queries.

Answer 2

Is there any way to check memory usage by database

To provide answer to this specific question - to check how much memory each database is currently using in the buffer pool (how much of database's data is cached in memory) - use the BufferPoolSize procedure.

Deploy (create) it to any user database and run:

exec BufferPoolSize

Above should help to address your question "View memory usage by database on an SQL server", but this:

performance issues for all the applications making use of this common database server

may have much wider list of root cause reasons: blocking, competing for CPU, slow disk, excessive memory grants (memory used by queries), etc. and require deeper troubleshooting than simply checking memory used by databases on a server.