Setup Perfmon with PowerShell and Logman

In a session yesterday I was reminded of the power of a tool on every server, Logman, to manage performance data gathering. It's a pretty amazing tool, and I'm starting to get to know how much more it can do than I've even considered, but here's a simple example of what I've done so far.

First things first, I'm going to leverage the SMO Server object to get the default backup directory, so I can put the perfmon output into a directory where I know it's going to be backed up. The Server.Settings.BackupDirectory property has the full path for the default backup location, so let's grab that.

$inst = 'SQLTBWS\INST01' # I get this from a command-line argument, but hard coded it for completeness
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
$bkup = $s.Settings.BackupDirectory

Next, I'm going to check to see if a subdirectory called SQLPerf under that backup directory exists, and if not, create it.

# Create the evaluation results directory if it doesn't exist
if (!(Test-Path -path "$bkup\SQLPerf\"))
	{
	New-Item "$bkup\SQLPerf\" -type directory | out-null
	}

Because I get the instance name from a script argument, I don't know if it's a default or named instance. This matters when defining perfmon counters to capture, so we'll "Split" on the backslash character, and if there isn't a second object after the split, the instance is a default instance. In that case the $sqlcntr variable is set to "SQLServer", but for a named instance we need to set $sqlcntr to the instance service name, which is "MSSQL$xxxxx", where xxxxx is the instance name after the backslash. Additionally store the machine name in the $boxnm variable so I can create the output files using that name.

# Get the instance name to determine if it's a default instance
$srv = $inst.Split("\")
if ($srv.Count -eq 1) {
	$sqlcntr = 'SQLServer'
	$boxnm = $inst
	}
	else {
	$sqlcntr = 'MSSQL$' + $srv[1]
	$boxnm = $srv[0]
	}

Next, I'll create a 'here-string' containing the configuration properties to send to Logman. Those properties consist of a list of the counters I want to capture. The only thing that's "special" here is the construct "$($sqlcntr)", which allows PowerShell to replace that construct with the counter service name, and allows us to specify valid counters for perfmon. Once we've created the string in the $cfg variable, we can pipe that to the out-file cmdlet, directing it to the machine name config file in the SQLPerf directory under the default backup directory.

$cfg = @"
\Processor(_Total)\% Processor Time
\Memory\Available MBytes
\Paging File(_Total)\% Usage
\PhysicalDisk(_Total)\Avg. Disk sec/Read
\PhysicalDisk(_Total)\Avg. Disk sec/Write
\System\Processor Queue Length
\$($sqlcntr):Access Methods\Forwarded Records/sec
\$($sqlcntr):Access Methods\Page Splits/sec
\$($sqlcntr):Buffer Manager\Buffer cache hit ratio
\$($sqlcntr):Buffer Manager\Page life expectancy
\$($sqlcntr):Databases(_Total)\Log Growths
\$($sqlcntr):General Statistics\Processes blocked
\$($sqlcntr):SQL Statistics\Batch Requests/sec
\$($sqlcntr):SQL Statistics\SQL Compilations/sec
\$($sqlcntr):SQL Statistics\SQL Re-Compilations/sec
"@
$cfg | out-file "$bkup\SQLPerf\$boxnm.config" -encoding ASCII

Now we've got the config file that logman needs to configure perfmon to our specifications. I call the collection name 'SQLPerf', and you can see the command line options in the TechNet article here. Once the collection is created we run logman again and issue the start command on the collection.

logman create counter SQLPerf -f bincirc -max 100 -si 60 --v -o "$bkup\SQLPerf\$boxnm" -cf "$bkup\SQLPerf\$boxnm.config"
logman start SQLPerf

Now, we don't want this to run forever, so once a day (usually at about 5am) I stop perfmon, move the counter data to a dated subdirectory, then start up perfmon again.

# Stop Perfmon Counter log
logman stop SQLPerf

# Get the current date/time and create the dated subdirectory
$dt = get-date -format yyyyMMddHHmmss
New-Item "$bkup\SQLPerf\$boxnm$dt" -type Directory | out-null

# Move trace files into a dated subdirectory
Move-Item ("$bkup\SQLPerf\$boxnm*.blg") ("$bkup\SQLPerf\$boxnm$dt")

# Start Perfmon Counter log
logman start SQLPerf

These steps allow me to create a consistent method of gathering performance data where the gathering process can't directly update my analysis database.

Allen