PowerShell is handy for a lot of quick checks when working with SQL Server. A good friend of mine, Keith Nicholson, came up with a one-line script which became quite handy when doing some performance testing at a client this week.
The issue is that when there are multiple instances of SQL Server running on a system and you want to use perfmon to gather some performance statistics on the amount of processor time used by a particular instance (% Processor Time under Process when you Add Counters), for example. How do you know which instance corresponds to which "sqlservr" process? Let's say for example you have three instances running. In perfmon they're listed as sqlservr, sqlservr#1 and sqlservr#2. This isn't very intuitive.
Keith developed a one line script which answers this question. I'll break it down into its component parts. First, get the processes using the Get-Process cmdlet where the process name is sqlservr:
Get-Process -name "sqlservr"
Simple enough. Next sort those results on the ID property.
Sort-Object -Property ID
Great, now we need to iterate through the results, and for each one use the Get-WMIObject cmdlet to call the Win32_Service class, filtering on the current ID value. The Win32_Service class will return the service name for the identified process.
ForEach-Object -process { Get-WMIObject Win32_Service -filter ("ProcessID =" + $_.Id)}
Finally, we can format the results into tabular form, specifying the Autosize parameter to make it easy to read, and return just the ProcessID and Name properties.
Format-Table -autosize -Property ProcessID, Name
OK, now we have all the pieces and we can just put them together, using the pipe symbol (|) to send the results from each step to the next, and here's our script.
Get-Process -name "sqlservr" | Sort-Object -Property ID | ForEach-Object -process
{ Get-WMIObject Win32_Service -filter ("ProcessID =" + $_.Id)} | Format-Table
-autosize -Property ProcessID, Name
When we run the script we get results like this:
ProcessId Name
--------- ----
1296 MSSQL$INST01
1332 MSSQL$INST02
1352 MSSQLSERVER
Because perfmon displays the three sqlservr processes in order by their Process ID values we know that sqlservr is the INST01 instance, sqlservr#1 is the INST02 instance, and sqlservr#2 is the default instance. But there's MORE!
Let's say you've had to shut down one of the instances, we'll pick INST02 for example. When it starts back up Windows assigns it a new Process ID value. If you were actively monitoring the % Processor Time for all three instances your counter data would be useless. Here's what happens.
When Windows stops the INST02 process that leaves just two instances of the sqlservr process running. Since INST02 was "process sqlservr#1", the default instance now moves from sqlservr#2 to sqlservr#1. See the problem? Your counter data just switched from INST02 to the default instance. When INST02 starts up again Windows will choose a new Process ID value. It may choose a higher number, like 2600, which would cause INST02 to become sqlservr#2 in perfmon, but it also could choose a low number, like 768, which would cause it to become sqlservr, bumping INST01 to sqlservr#1 and the default instance to sqlservr#2.
I hope this helps you use PowerShell and helps you understand perfmon a bit better.
Allen