Looking for SSMS Connections Directly from the Server

Leave a comment

February 19, 2015 by Mike Hillwig

My environment is 99% virtual. We hope to soon be 100% virtual, but I have that one physical cluster that we just can’t seem to retire.

With everything being virtual, we run pretty lean. None of our VMs have more than 32 GB of RAM, with most of them having 16 GB of RAM. This means there isn’t a lot of memory to waste. Recently, I was trying to figure out a problem with a server that was under constant memory pressure. In an act of desperation, I decided to reboot the server. I had no other idea what may be causing this. After we stopped all of the application engines for this particular client, I noticed something odd. We still had a lot of open sessions. They were all DBAs on my team. Worse yet, they were running directly from the server.

We have a rule that says you shouldn’t RDP directly into a server to run SSMS. We have management servers with SSMS installed, or people could run it from their own machines. Because of network connectivity issues or force of habit, we had seven people who all had RDP sessions to that server, and they were all running SSMS. Seven. Clearly our rule to prevent SSMS via RDP wasn’t working.

SSMS is built on the Visual Studio platform. This is a tool for developers, and it likes its resources. So when you have seven people all using it via RDP to the same server, you can imagine the drain on resources.

You’re probably thinking “But Mike, you can only have two RDP sessions on a server at a time.” And you’re correct. However, if someone closes the RDP session without logging off, it will let that session sit idle. And in that idle session, you have someone chewing up RAM inside of SSMS.

After this, I went on the proverbial witch hunt, running this query against every one of my servers to see how bad the damage was.

select @@servername, last_batch, hostname, loginame, datediff(hh, last_batch, current_timestamp) [Logged in Hours]
from sysprocesses
where hostname = SERVERPROPERTY('MachineName')
and program_name like 'Microsoft SQL Server Management %'
and datediff(hh, last_batch, current_timestamp) > 1

The last line of the WHERE clause allows us to give a little bit of leeway for someone who needed to hop on and off quickly. But if you have been using SSMS via RDP for more than an hour, it’s time to call that out.

I took the output, exported it to a spreadsheet and sent it to my team and associated groups. I gave them 48 hours before the public shaming started. We use this system that’s like twitter, only it shows on monitors all over the office. I threatened to start having alerts go there. So far, I haven’t had to make good on that threat. Yet.

My goal is to start introducing Windows Server Core Edition in our environment to help us get closer to avoiding this problem. My Windows team loves the idea. The hard part is now convincing our management team that this is the right thing to do.