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

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

Published Friday, March 02, 2012 5:34 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

 

ALZDBA said:

Another great post, Allen.

Another example of powershell's versatility and the way it can serve use all.

March 3, 2012 8:37 AM
 

Allen White said:

In yesterday's post I showed you how I set up performance data gathering in an environment where I can't

March 3, 2012 2:14 PM
 

Kokoy said:

Thanks for the post, I'm getting  the following error, do we need special permission to run this?

Access is denied.

You're running with a restricted token, try running elevated.

March 19, 2012 5:57 PM
 

AllenMWhite said:

Kokoy, you do need to have access to the system state to be able to capture these counters. Work with your network administrator to set up a process for gathering the counters.  Good luck.

March 19, 2012 8:05 PM
 

ALZDBA said:

Just a quick note:

Chances are Logman throws an error. This will not be captured in a Try/Catch because Logman puts that in its standard output.

Quick workaround can be:

"Logman start SQLPerf" | Out-File -FilePath $LogmanCmdFile -Force -Encoding default  

$test = ( Start-Process -FilePath $LogmanCmdFile -Wait -RedirectStandardOutput $LogmanResultFile -RedirectStandardError $LogmanErrorFile -Passthru )

$LogmanResult = Get-Content $LogmanResultFile

if ( $LogmanResult -like '*error:*') {

$EvtLog = Get-EventLog -LogName "application" -ComputerName . | where { $_.Source -eq "SysmonLog" } | select -First 1

$EvtLog.message | ft -AutoSize  

}

$LogmanResult = Get-Content $LogmanErrorFile

if ( $LogmanResult ) {

write-host "FATAL: $LogmanResult"

}

August 30, 2012 9:50 AM
 

AllenMWhite said:

Thanks, Johan!

August 30, 2012 10:19 AM
 

Vlada said:

Hi Allen,

What administrator needs to do if we want the logman works without "try running elevated" ?

Thanks.

September 18, 2014 11:48 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