PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

Checking who is connected to your server, with PowerShell.

There are many occasions when, as a DBA, you want to see who is connected to your SQL Server, along with how they are connecting and what sort of activities they are carrying out. I’m going to look at a couple of ways of getting this information and compare the effort required and the results achieved of each.

SQL Server comes with a couple of stored procedures to help with this sort of task – sp_who and its undocumented counterpart sp_who2. There is also the pumped up version of these called sp_whoisactive, written by Adam Machanic which does way more than these procedures. I wholly recommend you try it out if you don’t already know how it works. When it comes to serious interrogation of your SQL Server activity then it is absolutely indispensable.

Anyway, back to the point of this blog, we are going to look at getting the information from sp_who2 for a remote server. I wrote this PowerShell script a week or so ago and was quietly happy with it for a while. I’m relatively new to PowerShell so forgive both my rather low threshold for entertainment and the fact that something so simple is a moderate achievement for me.

So this is pretty straightforward, create an SMO object that represents our chosen server, define a connection to the database and a table object for the results when we get them, execute our query over the connection, load the results into our table object and then, if everything is error free display these results to the PowerShell grid viewer.

The query simply gets the results of ‘EXEC sp_who2’ for us. Depending on how many connections there are will influence how long the query runs. The grid viewer lets me sort and search the results so it can be a pretty handy way to locate troublesome connections.

SPWHO01.png

Like I say, I was quite pleased with this, it seems a pretty simple script and was working well for me, I have added a few parameters to control the output and give me more specific details but then I see a script that uses the $SMOServer object itself to provide the process information and saves having to define the connection object and query specifications.

Create the SMO object of our server and then call the EnumProcesses method to get all the process information from the server. Staggeringly simple! The results are a little different though.

SPWHO02.png

Some columns are the same and we can see the same basic information so my first thought was to which runs faster – so that I can get my results more quickly and also so that I place less stress on my server(s).

PowerShell comes with a great way of testing this – the Measure-Command function. All you have to do is wrap your piece of code in Measure-Command {[your code here]} and it will spit out the time taken to execute the code. So, I placed both of the above methods of getting SQL Server process connections in two Measure-Command wrappers and pressed F5! The PowerShell console goes blank for a while as the code is executed internally when Measure-Command is used but the grid viewer windows appear and the console shows this.

SPWHO03.png

You can take the output from Measure-Command and format it for easier reading but in a simple comparison like this we can simply cross refer the TotalMilliseconds values from the two result sets to see how the two methods performed.

The query execution method (running EXEC sp_who2 ) is the first set of timings and the SMO EnumProcesses is the second. I have run these on a variety of servers and while the results vary from execution to execution I have never seen the SMO version slower than the other. The difference has varied and the time for both has ranged from sub-second as we see above to almost 5 seconds on other systems. This difference, I would suggest is partly due to the cost overhead of having to construct the data connection and so on where as the SMO EnumProcesses method has the connection to the server already in place and just needs to call back the process information.

There is also the difference in the data sets to consider. Let’s take a look at what we get and where the two methods differ

Query execution method (sp_who2)

SMO EnumProcesses

Description

Urn

What looks like an XML or JSON representation of the server name and the process ID

SPID

Spid

The process ID

Status

Status

The status of the process

Login

Login

The login name of the user executing the command

HostName

Host

The name of the computer where the process originated

BlkBy

BlockingSpid

The SPID of a process that is blocking this one

DBName

Database

The database that this process is connected to

Command

Command

The type of command that is executing

CPUTime

Cpu

The CPU activity related to this process

DiskIO

The Disk IO activity related to this process

LastBatch

The time the last batch was executed from this process.

ProgramName

Program

The application that is facilitating the process connection to the SQL Server.

SPID1

In my experience this is always the same value as SPID.

REQUESTID

In my experience this is always 0

Name

In my experience this is always the same value as SPID and so could be seen as analogous to SPID1 from sp_who2

MemUsage

An indication of the memory used by this process but I don’t know what it is measured in (bytes, Kb, Mb…)

IsSystem

True or False depending on whether the process is internal to the SQL Server instance or has been created by an external connection requesting data.

ExecutionContextID

In my experience this is always 0 so could be analogous to REQUESTID from sp_who2.

Please note, these are my own very brief descriptions of these columns, detail can be found from MSDN for columns in the sp_who results here http://msdn.microsoft.com/en-GB/library/ms174313.aspx. Where the columns are common then I would use that description, in other cases then the information returned is purely for interpretation by the reader.

Rather annoyingly both result sets have useful information that the other doesn’t. sp_who2 returns Disk IO and LastBatch information which is really useful but the SMO processes method give you IsSystem and MemUsage which have their place in fault diagnosis methods too.

So which is better?

On reflection I think I prefer to use the sp_who2 method primarily but knowing that the SMO Enumprocesses method is there when I need it is really useful and I’m sure I’ll use it regularly. I’m OK with the fact that it is the slower method because Measure-Command has shown me how close it is to the other option and that it really isn’t a large enough margin to matter.