Availability Group DMVs consume too much memory.
This query joins to three Availability Group DMVs
SELECT name, ISNULL(SERVERPROPERTY('MachineName'), 'NA') AS [server]
WHERE database_id <> 2
AND name NOT LIKE '%temp[_]%'
AND recovery_model_desc = 'FULL'
AND state_desc = 'ONLINE'
AND name not in ('')
AND name in (
select DISTINCT ADC.database_name
from sys.availability_databases_cluster ADC
inner join sys.dm_hadr_availability_replica_states HARS on ADC.group_id = HARS.group_id
where is_local = 'TRUE'
and HARS.role_desc = 'PRIMARY'
select DISTINCT name
where name not in (
from sys.availability_databases_cluster ADC))
ORDER BY 1 DESC
When I run it on my server with 120 databases and 11 different availability groups, it runs for at least ten minutes.
During this time, I notice the size of ad hoc query plan cache increases fast in sys.dm_os_memory_clerks (The `CACHESTORE_SQLCP` category) The query plan size for this one query seems to be consuming all that space really large. It was 2GB before I cancelled it measured with:
select top 1 *
cross apply sys.dm_exec_query_plan(plan_handle)
where objtype = 'Adhoc'
order by size_in_bytes desc
The memory pressure increases the compilations per second and the CPU climbs dramatically and the database grinds to a halt.
I should be able to join to DMVs without taking a server down.
Michael J Swart commented
Build: 13.0.4474.0 (2016, SP1-CU8)