THE SQL Server Blog Spot on the Web

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

Allen White

Performance Data Gathering

As a DBA, one of the things I struggled with was gathering all my important system information in one place, so I could see easily where my problems might be. Gathering perfmon data, combining it with trace data, adding in disk space information - it was hard to do this automatically.

This is probably why I like PowerShell so much. I can do this from a single script and don't have to try to merge data later.

The perfmon data gathering was always a problem for me, trying to export the log files into something useful. I've found that .NET has a namespace specifically for that - System.Diagnostics.PerformanceCounter. By creating (instantiating) objects of the PerformanceCounter type, I can collect the same perfmon counter data that perfmon, and I can control where that data goes. Here's the PowerShell code to capture some key counters:

$ppt = New-Object System.Diagnostics.PerformanceCounter
$ppt.CategoryName = 'Processor'
$ppt.CounterName = '% Processor Time'
$ppt.InstanceName = '_Total'
$pptv = $ppt.NextValue()
$mab = New-Object System.Diagnostics.PerformanceCounter
$mab.CategoryName = 'Memory'
$mab.CounterName = 'Available MBytes'
$pql = New-Object System.Diagnostics.PerformanceCounter
$pql.CategoryName = 'System'
$pql.CounterName = 'Processor Queue Length'
$bch = New-Object System.Diagnostics.PerformanceCounter
$bch.CategoryName = 'SQLServer:Buffer Manager'
$bch.CounterName = 'Buffer cache hit ratio'
$brs = New-Object System.Diagnostics.PerformanceCounter
$brs.CategoryName = 'SQLServer:SQL Statistics'
$brs.CounterName = 'Batch Requests/sec'

So, in these few lines we're setting up the collection for Percent Processor Time, Available Megabytes of Memory, Processor Queue Length, Buffer Cache Hit Ratio and Batch Requests/sec. Not a bad start to building our performance baseline. Notice that I called the NextValue method for the Percent Processor Time counter. I did this because this counter needs to be "seeded" - the first returned value is always zero.

Next we need to have a place to put the values. I like the fact that PowerShell uses objects, and learned that youc an create your own objects, with whatever properties you'd like. COOL!

$perf = new-object object

Now I have an object called $perf with no properties. I need to add the properties corresponding to the counters I'm collecting, so I'll use the Add-Member cmdlet to do this. (Note that piping the $perf object into the add-member cmdlet causes the object to become the value passed via the -InputObject parameter to add-member. The pipeline doesn't really go right-to-left here.)

In my case I'm setting up each additional properties as NoteProperties, and will assign values to them. I could have also used the ServerProperty parameter and used a scriptblock, but every time the object is referenced it would run the script block, and I wanted the results to be static in time.

$perf | add-member NoteProperty Server 'MyServer'
$perf | add-member NoteProperty Date get-date
$perf | add-member NoteProperty PctProc $ppt.NextValue()
$perf | add-member NoteProperty Memory $mab.NextValue()
$perf | add-member NoteProperty ProcQueLn $pql.NextValue()
$perf | add-member NoteProperty BufCchHit $bch.NextValue()
$perf | add-member NoteProperty BatRecSec $brs.NextValue()

I do need a place to put the object once it's populated, so I'll create an empty array like this:

$sysperf = @()

Then, at the end of each iteration I add the object to the array.

$sysperf += $perf

Now I can just loop through this last bit of code every 15 seconds or so to get a feel for how the system is performing, like this:

Start-Sleep -s 15

Finally, when I've got the information I want, I can use the export-csv cmdlet to load the data into a CSV file.

$sysperf | export-csv -noTypeInformation sysperf.csv

I could also use the Invoke-SQLCMD cmdlet to load it directly to SQL Server if I chose, but this is easier at a client site.

At the PASS Community Summit in Seattle next month I'll be demonstrating the full script using this code. Hope to see you there.


Published Friday, October 09, 2009 9:28 AM by AllenMWhite



ALZDBA said:

Once again, a very nice and applicable sample Allen !

Thumbs up for Seattle ;)

October 12, 2009 8:27 AM

michael moen said:


drop me a line how we can stay in touch. I really like working with you!.

November 11, 2009 11:38 AM

Leif Abbott said:

Allen, I attended your session at SQLPass on Gathering SQL Server Performance Data with PowerShell.  I was wondering if there was any way I could get the scripts that you used in your various demo's.  I really enjoyed your presentation and was hoping I could use the scripts and adapt them to work in my organization. If this is something that is feasible, please email me:  abbottl (at)

November 12, 2009 1:51 PM

Adrian said:

I was wondering on whether SQL Server's Data Collector (Management Data Warehouse) is not a nicer alternative for this.

August 9, 2010 5:42 AM

AllenMWhite said:

Leif - the script should be downloadable from the PASS site.

Adrian, the problem with MDW (from my perspective) is that it only works for SQL Server 2008 and beyond, and many of us are still supporting servers running SQL 2005 and SQL 2000.  This method works for all of them.

August 9, 2010 8:24 AM

Glen said:


So the scripts are only available to those who attended SQLPass?

August 26, 2010 2:15 PM

Jim Puder said:


I attended your 24Hrs of SQL PASS this week (9/15/2010), and was wondering if you might be posting the script you used to gather SQL performance.  You were going a little fast and it was a bit hard for me to follow the whole discussion.

Jim Puder

September 17, 2010 12:54 PM

Aaron Bertrand said:

In a current project, I need to collect performance counters from a set of virtual machines that are

January 31, 2011 7:54 AM

Jim Dorame said:

Allen is there way way to gather the perfmon data generated from a 32 bit instance of SQL Server 2005 from a 64 bit client?

May 7, 2013 5:18 PM
New Comments to this post are disabled

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


Privacy Statement