Recently in my trip to US for SQLPass, I had a unique opportunity to meet a number of attendees for my session. The session was well received and I got some great feedbacks. I loved the recent trip and it was a great experience for me to learn and prepare for the session. It was also an opportunity for me to learn from some of the deep conversations you folks brought during the session. If the session feedbacks and rating are any sense of how successful the session was, then I am glad to see they went really well. I look forward to many more sessions in the future too.
I met a number of you and you folks made even my lunch interesting. I thought I bring some conversations as blogs because these bring the simple nuances of SQL Server. I heard an interesting lunch time conversation when one of the attendees asked to how InMemory databases are using memory? Is there a way to find out which Resource Governor pool is using how much memory? In the past I wrote few simple blogs around these topics:
SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor
SQL SERVER – How to Bind Resource Governor for InMemory Enabled Databases?
I saw the usual answer from one of the individual that we have a number of DMVs that can help find this information. I thought of putting those DMVs here for quick reference:
-- Which resource pool is using how much of memory
SELECT pool_id
,NAME
,min_memory_percent
,max_memory_percent
,max_memory_kb / 1024 AS max_memory_in_MB
,used_memory_kb / 1024 AS used_memory_in_MB
,target_memory_kb / 1024 AS target_memory_in_MB
FROM sys.dm_resource_governor_resource_pools
Above DMV shows how the Resource Governor pools are configured and the values of memory currently. In case we have InMemory based databases, we need to understand which databases are configured to resource pools and what memory is currently mapped. This can also be got from the DMVs as shown below:
-- Check DB and pool binding
SELECT d.database_id
,d.NAME AS DbName
,d.resource_pool_id AS PoolId
,p.NAME AS PoolName
,p.min_memory_percent
,p.max_memory_percent
FROM sys.databases d
LEFT OUTER JOIN sys.resource_governor_resource_pools p
ON p.pool_id = d.resource_pool_id
The above shows the mapping of DB’s to resource pools. Do let me know if you have something similar used in your environments? If so, please share the same via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
