Checking who is connected to your server, with PowerShell.
- by Fatherjack
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.
$Server = 'SERVERNAME'
$SMOServer = New-Object Microsoft.SQLServer.Management.SMO.Server $Server
# connection and query stuff
$ConnectionStr = "Server=$Server;Database=Master;Integrated Security=True"
$Query = "EXEC sp_who2"
$Connection = new-object system.Data.SQLClient.SQLConnection
$Table = new-object "System.Data.DataTable"
$Connection.connectionstring = $ConnectionStr
try{
$Connection.open()
$Command = $Connection.CreateCommand()
$Command.commandtext = $Query
$result = $Command.ExecuteReader()
$Table.Load($result)
}
catch{
# Show error
$error[0] | format-list -Force
}
$Title = "Data access processes (" + $Table.Rows.Count + ")"
$Table | Out-GridView -Title $Title
$Connection.close()
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.
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.
$Server = 'SERVERNAME'
$SMOServer = New-Object Microsoft.SQLServer.Management.SMO.Server $Server
$Processes = $SMOServer.EnumProcesses()
$Title = "SMO processes (" + $Processes.Rows.Count + ")"
$Processes | Out-GridView -Title $Title
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.
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.
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.
