Microsoft® SQL Server® 2012 Bible

915

Chapter 37: Performance Monitor and PAL

37

Performance Analysis for Logs Tool (PAL)

PAL is a CodePlex tool that helps you along your performance monitoring path by reading in perfor-

mance monitor counter logs and analyzing them using known thresholds. This is a great option for both

novice and experienced administrators. You can download and learn more about the Performance

Analysis for Logs Tool at: http://pal.codeplex.com/.

Accessing Performance Counters with PowerShell

PowerShell enables you to easily discover and collect all performance counters from the

Operating System. After they are converted to a datatable, they are easily stored in a CSV,

Excel Spreadsheet, or a table inside of SQL Server. First, you need to know how to discover

what counters are available. After you know which counters you want to monitor, you can

grab large blocks of them using a hashtable. With PowerShell the easiest way to discover

them is to run the following code:

Get-Counter -ListSet SQLSERVER* | ForEach-Object {$_.CounterSetName,

$_.Paths} | Format-Table -Auto

To pick a list of performance counters, you simply create a hashtable. To explain what a

hashtable is to SQL people is easy: You know how you can’t have a comma separated list

of values passed into a query as a variable — unless you resort to XML. A hashtable is pre-

cisely that thing that you don’t have! To create a hashtable in PowerShell, just start with

“@(” then place your CSV list of counters inside of here; then close it with “).”

Following is a sample list of SQL Server performance counters:

$CountersList = @('\SQLServer:Plan Cache(*)\Cache Pages',

'\SQLServer:Buffer Manager\Buffer cache hit ratio',

'\SQLServer:Buffer Manager\Page lookups/sec',

'\SQLServer:Buffer Manager\Free list stalls/sec',

'\SQLServer:Buffer Manager\Free pages',

'\SQLServer:Buffer Manager\Database pages',

'\SQLServer:Buffer Manager\Reserved pages',

'\SQLServer:Buffer Manager\Stolen pages',

'\SQLServer:Buffer Manager\Lazy writes/sec',

'\SQLServer:Buffer Manager\Readahead pages/sec',

'\SQLServer:Buffer Manager\Page reads/sec',

'\SQLServer:Buffer Manager\Page writes/sec',

'\SQLServer:Buffer Manager\Checkpoint pages/sec',

'\SQLServer:Buffer Manager\Page life expectancy',

'\SQLServer:Wait Statistics(*)\Lock waits',

'\SQLServer:Latches\Average Latch Wait Time (ms)',

'\SQLServer:Access Methods\Failed tree page cookie',

'\SQLServer:General Statistics\User Connections')

c37.indd 915c37.indd 915 7/31/2012 10:03:53 AM7/31/2012 10:03:53 AM

http://www.it-ebooks.info