sp_PressureDetector: Why You Need The Remote DAC Enabled

sp_PressureDetector: Why You Need The Remote DAC Enabled

---

Video Summary

In this video, I delve into the importance of enabling the remote DAC (Dedicated Admin Connection) in SQL Server when troubleshooting a server that’s experiencing issues. I demonstrate how to turn on the remote DAC and use it effectively by showing its impact during a simulated stress test with SQL Query Stress. This tool allows you to connect as a sysadmin using SSMS, ensuring you can access your server even under heavy load conditions where normal connections might fail. By enabling this feature, you’re prepared to quickly diagnose and resolve critical issues without the need for additional setup when problems arise.

Full Transcript

Erik Darling here with Darling Data. And here’s an unnatural Clark Kent curl going on in front. Can’t have that. Don’t want anyone getting me confused with the Man of Steel. But, uh, today, or rather, I don’t know, maybe we’ll talk about more stuff today, so it might be a little premature to just say the only thing we’re going to talk about today. because we’ll talk about some other stuff today, is, uh, we’re going to get back to talking about how I use various, uh, community tools related to SQL Server troubleshooting. And, uh, I believe so far, we’ve talked about SP Human Events and SP Quickie Store. Sorry, SP underscore Human Events and SP underscore Quickie Store. And, uh, this one I’m going to continue to talk about, uh, my stuff. Because my stuff is the most important. And, uh, we’re going to talk about the importance of turning on the remote DAC, the dedicated admin connection, when we might need to troubleshoot a SQL Server that’s, uh, having some issues. And, uh, the store procedure that we’re going to be talking about for the next couple of few videos is going to be SP underscore pressure detector, which is one of my favorites. It is nice, well, used to be short and sweet, and slowly has accumulated some, uh, some, some length to it. Not so much girth, just, just kind of length.

And, uh, but it’s all useful, useful length, and useful girth. So that’s good. I don’t want useless length or girth. I’ve got some useless girth. SP underscore. Pressure detector does not, though.

Uh, one thing I discovered recently is that if this whole SQL Server thing doesn’t work out, I’m going to get super into shadow puppets on the green screen. I’ve got to figure out the lighting a little bit better, but you get the idea.

You’re in for a real treat. Oh, Microsoft goes bankrupt. Shadow puppets it is. So the first, uh, thing, so, SP pressure detector has code similar or exactly like this baked into it.

Uh, so that it will tell you if the remote DAC is enabled or not. So it’ll, it’ll do a check to see if this is set correctly. When this is set correctly, we will have, uh, remote admin connections, but the value in use will be one instead of zero.

Right now it’s zero. We don’t have that turned on, and I don’t have that turned on, uh, because I want to show you how to turn it on. And then maybe even how to use it should be a very useful thing, wouldn’t it?

So right now, uh, this is not turned on. And one thing that is, is good to know about this, and I’m going to figure out the right way to move here, is that this is not an advanced option.

If this were an advanced option, you might on your server need to, uh, run SP configure to show advanced options before, uh, turning this on. So, uh, actually one thing that I do want to, that I’ve always found kind of interesting is if you execute SP configure with no parameters sent into it, it will just list out all of the configuration options.

Uh, it will not tell you all of the stuff that, uh, selecting from the sys.configurations dynamic management view returns, like if an option is advanced and stuff like that.

So that’s kind of weird, I think. Maybe, maybe SP configure could just do a select star from sys.configuration instead of messing around with this other stuff, but, uh, I digress.

Um, as I usually do. Uh, so let’s, uh, let’s run this, and let’s reconfigure our server, and say, see up here, uh, we have changed our configuration option.

Viva. Uh, it doesn’t tell you that you ran reconfigure. It says run the reconfigure statement to install, so, you know, done.

But when you run reconfigure, or it doesn’t say, like, reconfigure complete, or anything like that. It’s just a, just a silent yes. Um, I know that there’s also a reconfigure with override, uh, that you could do there, but that is, um, that is if you want to set settings that SQL Server maybe doesn’t agree with.

So, I would, uh, I would avoid using that, uh, as a practice. Just, just reconfigure like a normal person. Now, if we go back and look at this, the results here, we are all set.

Our value and value in use for our lovely, talented, remote admin connections is set to one. All right. All right.

Good stuff. Now, another thing that, um, SP underscore, pressure detector, will tell you, is, it’ll look to see if someone else out there connected to your SQL Server may have possibly, potentially, uh, taken the dedicated admin connection.

And the reason that’s important is because only one person at a time can use the dedicated admin connection. All right.

One at a time. Now, uh, some people, or I used to get some guff back when I used to, like, care about what people thought. Uh, they used to say things like, oh, it seems like a security risk.

Well, it’s not. Uh, you can only use this connection if your login has sysadmin. And, uh, quite frankly, uh, there’s just not a whole lot more interesting you can do with the DAC than you could do if you already have sysadmin.

Uh, I guess there’s some stuff with, like, the, uh, the resource database you could do, but that’s really not, uh, all that interesting compared to what you can do if you’re already a sysadmin.

All right. Groove is in the heart, so they say. So, uh, I’m gonna get GitHub open. So, I was doing some work on SP underscore blitzlock today.

Making some improvements. Always improving. That’s me. You can tell them. Nothing, just always getting better at things.

Uh, so what I’m gonna do is, uh, I’m going to, uh, I’m gonna really beat the crap out of my server. I’ve got two instances of SQL query stress open.

And the reason I’m using two instances of SQL query stress instead of one instance of O stress is, uh, I’m getting some weird ODBC errors from O stress that I just don’t feel like figuring out right now.

Uh, I’d rather record a video. Uh, I haven’t recorded in a week because I was on vacation. I was in Paris, France. And, uh, you know, you just don’t come back from France wanting to, like, dig into ODBC errors.

So, again, groove is in the heart. So what I’m gonna do is kind of show you why you want the DAC when the server is having issues.

And I’ve got this, you know, kind of crazy, quirky query going on in here. And I’m just gonna fire both of these off. And it’s gonna be, you know, kind of crappy. Like, I know this, these videos are supposed to be about SP underscore pressure detector, but, you know, even running SP underscore who is active has a tough time.

And even just trying to open a new query window is having a really tough time generating a connection, which is no bueno, right? We’re just gonna sit, this is gonna just, this will eventually time out.

But I’m not gonna sit there and make you watch it time out. That’s just kind of cruel and unusual. But if I kill this, and, uh, I’m gonna try to cancel these. Sometimes this doesn’t work as well as I’d like it to.

But lo and behold, uh, you know, that, that worked out pretty well. And, uh, one thing that I should show you here is that, uh, so SQL query stress has a default time of, I think, 15 seconds. So, uh, right here you can see that there are, like, you know, we, we, we, we completed some, but there, you know, we had some exceptions as well.

Rather, I think we just had all exceptions there. I’m not sure. I’m actually not sure how that’s getting measured, but we had exceptions on both of these, uh, equivalent to the number of iterations. So I think, I think we had a lot of stuff timing out, trying to, trying to connect.

We only have 200 threads, so if we completed 178 and had 178 exceptions, that’s twice as many as we could possibly have, almost twice as many as, uh, the threads that we have allocated here.

So, um, I don’t know. Math is hard. My friends, math is hard. So, now let’s, uh, let’s, let’s change things up a little bit. So, uh, SP who was active barely got any of the way through.

We ran for 23 seconds. Um, you’re just gonna have to take my word for it on that one. I guess I could move this way. There we go. And I could do a really careful zoom and hopefully I don’t catch any, any weird nether regions over there.

And there we go. there’s our 23 seconds of waiting for who is active to return results when it didn’t. Of course I killed it because, you know, the server is boned. And I don’t expect who, I don’t expect anything to be able to run when a server is really under a lot of CPU pressure like that because we need CPU threads to do things and we had like saturated the CPU threads on this one.

So, what I’m gonna do is show you a tiny itty bitty little shortcut. Very useful shortcut. Very useful engine. This is our top of hat would say.

And I’m gonna right click and I’m gonna choose connection and then I’m gonna choose change connection. And what I’m gonna do is I’m gonna use the SA account because that seems to be sensible.

And then I’m gonna come up to this line here where it says server name and I’m gonna add the word admin and I’m gonna make me do it again.

Thanks SSMS. I’m gonna add the word admin in here admin colon SQL2022 and I’m gonna put in my password and no one look.

Alright. If you saw what I typed please do your best to forget it. And I’m gonna hit connect.

Now there used to be a funny bug in management studio where you would do the admin connection thing and you would get an error message saying like it didn’t work.

But it did work. And we can validate that it worked. Again I’m gonna have to move to the side a little bit and again hopefully you know what I’m just gonna do something. I’m gonna do some clever navigating here.

I hope. Or try to. Let’s see. I gotta go this way and this thing has to go this way and then we will eventually see lo and behold that we do have the admin connection here.

So that’s good. We have the admin and spwhoisactive will work of course instantly because nothing’s going on. That’s needless to say.

But now let’s come back over to SQL query stress and let’s fire these two bad dogs off again. And now with the remote admin connection we get results back instantly.

Alright. We can run this a bunch of times. We’ll get all the stuff back. We’ll see that we’re supposed to have like I mean like under normal circumstances because we have 200 threads from each of these going in.

Like we would like expect to see like you know 400 rows in there because queries are running they’re running for a long time but under these circumstances there are a whole bunch of queries that can’t even get to the server to execute because we have the saturated worker threads all over the place.

So I’m going to cancel these again before I set my nice laptop on fire. And we’re going to close this one out without ever actually having looked at SP pressure detector.

This is going to be the importance of enabling the remote dedicated admin connection. So when your server is having problems you don’t have to worry about doing it then.

you’ll be all set. You’ll have your special SSMS login with the admin colon server name. You’ll have everything ready to go and you can fire up whatever tools, scripts you care about to figure out what the heck is going on with your server and why CPU is at 10 million percent or whatever.

Again, math is hard. Anyway, thank you for watching. I hope you learned something. I hope you enjoyed yourselves. If you enjoyed, if you did either, you should like and subscribe.

If you did neither, I don’t know, go get into my SQL. Who needs you? That’s how I feel about it. Go watch some green screen hand puppetry, I guess.

Anyway, thank you for watching. I’m going to record some other SP pressure detector videos today and hopefully if you are not enlightened in the least by this one, you will find yourselves greatly enlightened by future videos or at least enthralled, amazed.

My wife is texting me so I’m going to go pay attention to that for a minute. Upload this video. So, yeah, goodbye.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount 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. You can also get a quick, low cost health check with no phone time required.