1

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.

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

7
  • Database don't use memory, unless you are specifically using in-memory databases; but you don't state that.
    – Thom A
    Aug 26, 2021 at 9:55
  • Ahh ok, fair enough so thats why everythihng is for server level.
    – gk1993_aal
    Aug 26, 2021 at 11:36
  • @Larnu, what about memory per query level, is that possible?
    – gk1993_aal
    Aug 26, 2021 at 11:49
  • 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?"
    – Thom A
    Aug 26, 2021 at 12:09
  • 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?
    – gk1993_aal
    Aug 26, 2021 at 12:56

2 Answers 2

3

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

2
  • Many thanks Aleksey. Is it possible to provide the sql for this task instead? I cannot install anything (privilidges).
    – gk1993_aal
    Aug 26, 2021 at 14:38
  • @gk1993_aal you can turn those stored procedures into sql scripts - not hard Aug 26, 2021 at 14:41
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.