1

The Question

What resources does the Dedicated Admin Connection reserve that I cannot reserve myself with Resource Governor? If I were a genius with Resource Governor, could I turn the Remote DAC off without consequence?

Context

The idea of the Dedicated Admin Connection is that SQL Server reserves a little bit of resources so that an administrator can always connect and solve big problems, most classically worker thread starvation, which breaks your monitoring tools.

I hate it when my monitoring tools break. If all of my monitoring tools could always use the Dedicated Admin Connection, then that would fix a lot of problems. This is, of course, an obviously stupid idea. However, it is not unfounded. For example, this article indirectly but correctly suggests that Extended Events still work while you're experiencing THREADPOOL waits and this video shows that Query Store survives.

In conclusion, SQL Server clearly has some internal way to protect monitoring from heavy workloads. I would like to use that myself. Resource Governor is the only idea I've thought of.

Improve this question

1 Answer 1

Reset to default
4

What resources does the Dedicated Admin Connection reserve that I cannot reserve myself with Resource Governor?

Memory node, memory, and scheduler.

Example:

select * from sys.dm_os_schedulers where parent_node_id = 64
select * from sys.dm_os_memory_nodes where memory_node_id = 64

If I were a genius with Resource Governor, could I turn the Remote DAC off without consequence?

Sure, but it wouldn't have the same resource access, there's a high chance when an instance is having issues you won't be able to even log in.

Trivial example is setting the user connections very low. You won't be able to log in, even with fancy RG setup. You can, however, connect to DAC. This was a common demo used in conjunction with a bunch of WAITFOR queries to eat up threads and requests.

For example, this article indirectly but correctly suggests that Extended Events still work while you're experiencing THREADPOOL waits and this video shows that Query Store survives.

Worker threads, as denoted as an issue you face, are needed for some features to work but many features do not use or have dedicated internal threads. Some items may continue to work while others don't.

In conclusion, SQL Server clearly has some internal way to protect monitoring from heavy workloads. I would like to use that myself.

There is no special protection, it's a byproduct of how each feature was implemented.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.