WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, June 2, 2011

Gathering physical and SQL counters with Powershell

Lately, I have been doing lots of load testing bursts with a trace and subsequent perfmon collections.  Since I like to keep things easy, I wrote a Powershell script to aid in the collection of data.  I push the files to a share so the application teams can grab them and look for themselves.  This is nice because it allows lesser experienced folks to use it and hand over results with a test.  The counters are some I picked based on a recent load test and are not a definite nor all encompassing list of counters.

$serverToMonitor = "computerName"
$sqlServerToMonitor = "SQLInstanceforcomputerName"
$file = "C:\PerfMonResults\PerfmonResults.csv"
$samplesToTake = 150
$sampleInterval = 5

$counters= ("\[instancekey]:Access Methods\Worktables Created/sec") `
,("\[instancekey]:Access Methods\Workfiles Created/sec")`
,("\[instancekey]:Buffer Manager\Page life expectancy") `
,("\Processor(_Total)\% Processor Time") `
,("\[instancekey]:SQL Statistics\Batch Requests/sec") `
,("\PhysicalDisk(*)\Avg. Disk sec/Read") `
,("\PhysicalDisk(*)\Avg. Disk sec/Write")

## Takes a counter array and replaces a token
[instancekey] with SQL Specific Name
Function Get-SQLCounters{
    param([string] $SQLServerToMonitor, $counters)
    $counterArray = @() # holds the instance specific counters array to pass into get-counter
    # Generate a counter path friendly name (SQLServer (default instance) or MSSQL$InstanceName)
    [int]$instPos = $SQLServerToMonitor.IndexOf("\");
    if($instPos -gt 0){
        $instPos += 1;
        $instancekey = "MSSQL$" + $SQLServerToMonitor.Substring($instPos,($SQLServerToMonitor.Length - $instPos))
    } else { # Default Instance
        $instancekey = "SQLServer"
    ## Rebuilds Counter array with SQL Specific counters
    foreach($cnter in $counters) {
        $counterArray += $cnter.Replace("[instancekey]",$instancekey)
     return $counterArray;

## Build SQLServer specific counter array
$counterArray = Get-SQLCounters -SQLServerToMonitor $sqlServerToMonitor -counters $counters

Get-Counter -ComputerName $serverToMonitor -Counter $counterArray -MaxSamples -$samplesToTake -SampleInterval $sampleInterval |
    %{$_.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp; } |
    Export-Csv -Path $file -NoTypeInformation

#If Windows 7 - you can push to perfmon file and open in perfmon after collection is complete (read up on export-counter)
Invoke-Item $file

No comments:

Post a Comment