T-SQL Query To Find Memory Usage Per Resource Pool Per Database

Question

Post reply

T-SQL Query To Find Memory Usage Per Resource Pool Per Database

gs1975

Ten Centuries

Points: 1057
More actions
March 22, 2019 at 8:03 am

#371801

Hi,
I have just started using Resource governor in SQL Server 2016 and my manager has asked me to write a script to find out what the memory usage is per resource pool per database.
Is this possible?
I have been trying to write a query using some of the DMVs but I have not found anything suitable so far.
I have also searched Google and the search facility of this website, but found nothing that fits in with these requirements so far.
Any help would be appreciated.
Cheers,
George

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Wouldn't sys.dm_resource_governor_resource_pools be what you're looking for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gs1975 Ten Centuries Points: 1057 More actions · Answer 2

Hi Jeff,

Thanks for your reply.

The DMV 'sys.dm_resource_governor_resource_pools' gives me some of the information I need, but not everything.

I have incorporated some other DMVs into a script but I am not convinced that I have written it correctly and that memory is being returned correctly.

The script I have written is as below. If you or anyone has any ideas how this can be improved then that will be great.

SELECT DB_NAME(bd.database_id) AS [Database Name],
rg.name as ResourcePool,
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors bd
LEFT JOIN sys.dm_exec_requests r on bd.database_id = r.database_id
LEFT JOIN sys.dm_exec_sessions s
ON s.session_id = r.session_id
LEFT JOIN sys.dm_resource_governor_resource_pools rg
ON s.group_id=rg.pool_id
WHERE bd.database_id > 4 -- exclude system databases
GROUP BY rg.name, DB_NAME(bd.database_id)
order by [Cached Size (MB)] desc--rg.name, DB_NAME(bd.database_id)

Thanks in advance for any guidance.

Kind regards,
George

gs1975 Ten Centuries Points: 1057 More actions · Answer 3

The following script seems to return what I require.

SELECT DB_NAME(bd.database_id) AS [Database Name],
rg.name as ResourcePool,
--COUNT_BIG(*) [Pages in Buffer],
--COUNT_BIG(*)/128 [Buffer Size in MB]
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors bd
--LEFT JOIN sys.dm_exec_requests r on bd.database_id = r.database_id
LEFT JOIN sys.dm_exec_sessions s on bd.database_id = s.database_id
--ON s.session_id = r.session_id
LEFT JOIN sys.dm_resource_governor_resource_pools rg
ON s.group_id=rg.pool_id
WHERE bd.database_id > 4 -- exclude system databases
--and bd.database_id = 67
GROUP BY rg.name, DB_NAME(bd.database_id)
order by [Cached Size (MB)] desc, DB_NAME(bd.database_id)

Kind regards,
George