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.
Allen