THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Using PowerShell to Help Performance Monitoring

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

Published Wednesday, January 28, 2009 8:52 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Norm Enger said:

Great script. I modified it a bit as follows.

1. Save the following into a file named "script.ps1" (line breaks are okay)

$i = -1; $t="";

Get-Process -name "sqlservr" |

Sort-Object -Property ID |

ForEach-Object -process {

Get-WMIObject Win32_Service -filter ("ProcessID =" + $_.Id);

$s="                is noted in PerfMon as process name ""sqlservr";

$i++;

if($i -gt 0){

$t=$s + "#" + [string]$i + """";

echo $t

}

else {

$s = $s + """";

echo $s

}

} |

Format-Table -autosize -Property ProcessID, Name

2. Save the script to a "c:\scripts" folder.

3. Copy the shortcut for PowerShell from the start menu to your Desktop and modify the Target for the shortcut by adding the following at the end (after "powershell.exe"):

-noexit -command "& 'c:\scripts\script.ps1' "

A complete command on my system looks like this:

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -noexit -command "& 'c:\scripts\script.ps1' "

4. Test the script by executing the shortcut.

February 6, 2009 4:09 PM
 

David said:

Does this technique work for any process besides SQL server? Like multiple Notepad windows open, etc.

I tested this out and it doesn't print anything when I run your sample code replacing SQL server process name with notepad or other process names (that are running). It returns nothing instead of a table.

I'm using Powershell v1.0 on Win 2003 with default config.

March 12, 2009 10:02 PM
 

David said:

But the following however does work:

Get-Process -name notepad | Sort-Object -Property ID | Format-List

So not sure what the problem is (on my system).

March 12, 2009 10:05 PM
 

Brad Corbin said:

Allen-

You should know that perfmon does NOT always display the instances in ascending order by ProcessID. I've got a server that it shows out of order.

Not exactly sure why its happening in this case. It could be because of mixed SQL versions, or 32-bit SQL 2000 on a 64-bit server, or some other craziness.

However, there is an easy workaround:

In your perfmon counter log, simply include the "Process:ID Process" counter for each of the SQL processes, and it should record the ID for each of them directly.

Putting together a blog post on this now.

Brad

January 27, 2010 6:47 PM
 

Bob Snyder said:

Thank you Norm for the script,allowing me to join instance names with perfmon stats - so helpful!

January 10, 2013 10:27 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement