SQL Server - memory usage per database level and query level

Question

I am trying to find a query for two things:

1. Get Current Memory Usage per database

There are different databases at the same server so I need to get Memory Usage for specific database, not for all dbs.

2. Get Current Memory Usage per query

I want to compare different queries, how much memory they consume so I can evaluate if some queries needs to be optimized in a meaning that they consume too much memory and not whether they take too much time.

• Thom A: Database don't use memory, unless you are specifically using in-memory databases; but you don't state that.
• gk1993_aal: Ahh ok, fair enough so thats why everythihng is for server level.
• gk1993_aal: @Larnu, what about memory per query level, is that possible?
• Thom A: Have a look at Monitor memory usage, which might answer some of your questions. It's far from as simple as "how much memory did a query use?"
• gk1993_aal: Had already been looking at this but its per server level. I actually can see that in the activity monitor , there is all the information, as well as memory info per query but I cannot find the sql behind this "Activity monitor" and I dont have privilidges to get Activity monitor info(I saw the results only on my local instance) . Any idea how to find this sql behind activity monitor dashboard in SQL Server?

Answer 1

Get Current Memory Usage per database

Use below link, download "Memory - BufferPoolSize.sql" and install stored procedure BufferPoolSize on any database. Run it and it will show current Buffer Pool size used for each database

https://github.com/aleksey-vitsko/Database-Administrator-Tools

BufferPoolSize

Get Current Memory Usage per query

Use below link, download "First Responder Kit", unzip it and install sp_BlitzCache into any database

https://www.brentozar.com/first-aid/

Run it with below parameter

exec sp_BlitzCache @SortOrder = 'memory grants'

It will give you a list of queries (that are present in a Plan Cache currently) that used most of memory, when they ran

• gk1993_aal: Many thanks Aleksey. Is it possible to provide the sql for this task instead? I cannot install anything (privilidges).
• Aleksey Vitsko: @gk1993_aal you can turn those stored procedures into sql scripts - not hard

Answer 2

-- Memory used by each database

SELECT DB_NAME(database_id),
COUNT (1) * 8 / 1024 AS MBUsed
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

Answer 3

I found this sample, and it works well

https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
 AND counter_name = 'Database Pages';
;WITH src AS
(
 SELECT database_id, db_buffer_pages = COUNT_BIG(*)
 FROM sys.dm_os_buffer_descriptors
 WHERE database_id BETWEEN 5 AND 32766
 GROUP BY database_id
)
SELECT 
 [db_name] = DB_NAME(database_id),
 db_buffer_pages,
 db_buffer_MB = db_buffer_pages / 128,
 db_buffer_percent = CONVERT(DECIMAL(6,3), 
 db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;