I’m gonna throw this at you fast, because it’s Friday. If you’re using sp_PressureDetector in a SQL Server performance crisis, there are two things you don’t want:
- Slow results from running diagnostic queries
- Diagnostic queries waiting in the same CPU and memory lines as user queries
That’s why one of the first things does is see if the Remote DAC is enabled, and urge you to turn it on, like so:
EXEC sp_configure 'remote admin connections', 1; RECONFIGURE;
Why bother? Well, the Remote DAC is sort of like a VIP entrance to Club SQL Server. You get your own little connection and set of resources that are unfettered by lowly user queries that are most-definitely-not-on-the-guest-list.
There are a couple caveats to being on the DAC list:
- You have to use an admin account to access it
- Only one person can use it at a time
So what is it, and how do you use it?
To use the DAC, all you have to do is add the prefix “ADMIN:” to your SSMS connection:
Once that’s done, you just connect as normal and you’re good to go. There was a longstanding bug fixed recently in SSMS, where an error message would show up that said you’d not connected using the Remote DAC, even though you were.
This makes running diagnostic queries when your server is Having A Bad Day© much less prone to either adding to or being trampled by performance issues.
And also, uh, it’s just generally a good thing to have enabled for situations where you can’t connect to SQL Server in any of the normal ways, and you maybe want to try to see what the problem is rather that just crawl out of bed to restart the server.
So yeah. Good, that. Enable it.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: sp_PressureDetector Doesn’t Show You Irrelevant Waits
- SQL Server Community Tools: sp_PressureDetector Shows You Running Queries Taking Up CPU And Memory
- SQL Server Community Tools: Detecting Memory Pressure In SQL Server With sp_PressureDetector