PAL

In this article I use PAL (Performance Analysis of Logs), by Clint Huffman.
This is a great tool to get valuable feed back on the performance and possible issues with your SQL Instances.
You may want to read the article on PerfMon with PowerShell first, as it will show the way I collect performance data to use with PAL.

Performance Monitor

So I talked about using PerfMon with PowerShell here

Performance Monitor on Technet.

Performance Analysis of Logs (PAL)

One very special usage of performance counters is Performance Analysis of Logs (PAL), written by Microsoft Premier Field Engineer Clint Huffman. It's a tool that reads your performance log files, compares them with a treshold file of your choice and delivers an extensive report in HTML or XML.
There are treshold files for different technologies, like Exchange2007, SharePoint, HyperV, IIS,.... Yes, some are ancient and are not getting any updates. But for us, the SQL Server treshold files matter, and there is one for SQLServer2008R2, SQLServer2012, SQLServer2014 and a generic SQLServer. Those are put together by Microsoft's David Pless.
It's really wonderful and deserves a lot more attention than it has gotten so far. So if you're a DBA, download PAL.
The most recent version is 2.7.7 and dates from 2016-05-05.
The project is downloadable from Codeplex, but we all know that will end soon. I've found no replacement location for this project yet.

The Counters

You may find the advice to start a PerfMon Data Collector Set via the wizard and choose a general template for System Diagnostics. Because we don't want to bloat our logs with unnecessary data, I like to control myself which counters to include. To do so, we need to know what counters PAL needs for our analysis. Let's assume we're working with a SQL Server 2014 instance.
Trying to find the counters in the SQLServer2014.xml treshold file? PowerShell to the rescue!
The installation wizard wants to install PALcollector in C:\Program Files, but I redirected that to my C:\scripts folder. I also have a \PerfMon directory in there to collect output and keep my scripts that deal with PerfMon. You can put your files wherever you like, just alter the paths in the code below.

PS> [xml]$list = Get-ChildItem C:\scripts\PALCollector\SQLServer2014.xml
PS> $list.pal.analysis | Select @{l="counter";e={"`"" + $_.primarydatasource + "`""}} |
      Where { $_.counter -notlike "*\PAL Generated(*" } |
      Select -ExpandProperty counter -unique | Set-Content C:\scripts\PerfMon\PAL-SQL2014-Counters.txt

Tadaa! And there we have our counters:

What happened?
We read the treshold file and cast it as XML into the $list variable.
Then we navigate through the XML nodes and select the counters in the primarydatasource nodes.
We filter out the PAL Generated counters because PerfMon doesn't know those, they are calculated by the tool as a ratio between two existing counters.
The Select -ExpandProperty part turns our counter names into strings and with -unique we remove duplicates ( in this example there were only two ), as PerfMon will return errors when counters are added twice.
Finally, we write the counters to a simple text file.

The Data Collector Set

I copied the counter list file to the SQL Server I want to analyze, and put it in the C:\scripts directory over there. I also created a directory to keep the logs. Execute logman on that computer:

PS> Enter-PSSession SQLServer1
[SQLServer1]PS> logman create counter PALSQL2014 -f csv -max 200 -si 00:01:00 -v MMddHHmm -o "C:\scripts\output\SQLServer1-PAL.csv" -cf "C:\scripts\PAL-SQL2014-Counters.txt" -b 25/11/2016 00:00:00 -e 25/11/2016 23:58:00 -r

I've explained this in my PerfMon article
You have to be local admin on this server.

Move the files

As we don't want to compromise the performance and integrity of our SQL Server, we'll do the analysis on our workstation. I have a PowerShell job that uses Robocopy to periodically move the PerfMon log files to a file share.

Launch PAL

Now we're there!
While you can start PAL from Windows Start menu or by double clicking the .exe, I prefer PowerShell. So do you, right?
Now I have some handicap that you may or may not have: the locale setting on my workstation is not en-US. That's a prerequisite for PAL. Well, it will work but not correct, I think the problem is with decimal numbers where the . and , are misinterpreted.
Jeffrey Snover himself, PowerShell's dad, provided us with a neat little function to overcome this: Using-Culture. So this is what I do:

Function Using-Culture (
[System.Globalization.CultureInfo]$culture = (throw “USAGE: Using-Culture -Culture culture -Script {scriptblock}”),
[ScriptBlock]$script= (throw “USAGE: Using-Culture -Culture culture -Script {scriptblock}”))
{
    $OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
    trap 
    {
        [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
    }
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
    Invoke-Command $script
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}

Set-Location C:\scripts\PALCollector

$PALParams = @{
Log =  "\\Myshare\PerfMon\SQLServer1\SQLServer1-PAL_04110000.csv"
ThresholdFile = "C:\scripts\PALcollector\SQLServer2014.xml"
Interval = "AUTO"
IsOutputHtmlv = $True
HtmlOutputFileName = "[LogFileName]_PAL_ANALYSIS_[DateTimeStamp].htm"
IsOutputXml = $False
XmlOutputFileName = "[LogFileName]_PAL_ANALYSIS_[DateTimeStamp].xml"
AllCounterStats = $false
NumberOfThreads = 8
IsLowPriority = $True
BPEHealth = "1000"
OLTPvsOLAP = $True
OS = "64-bit Windows Server 2012"
PhysicalMemory = "32"
PLEHealth = "1000"
UserVa = "2048"
UsingInMem = $False
}

Using-Culture en-US { C:\scripts\PALCollector\PAL.ps1 @PALParams -ExecutionPolicy ByPass -NoProfile }

What does this beautiful piece of code accomplish?
First, there's a copy of Jeffrey's function. It will set our culture to en-US, execute the scriptblock we provide, and set the culture back to what it was.
Next, Set-Location will move our PWD (present working directory) to the PALcollector directory. This is not absolutely necessary, but just the way I chose to do it this time (probably because I had a problem with file locations before, I don't remember).
Then we execute the defined function Using-Culture with PAL.ps1 as work horse in the scriptblock. We have a list of parameters for the script, which I assign first to the $PALParams variable. This makes it easier to call the script with @PALParams as parameter collection.
Did you notice the $ when assigning, and @ when calling? This is parameter splatting and deserves an article on its own.
You should check all parameters in your environment because the values you submit for OS, PhysicalMemory and the like will influence the evaluation of the counters.
Maybe one day I'll wrap this all in a new function where you can supply all needed parameters or, even better, some will be autocollected. Don't wait for it.

Look at that!

If you left the IsOutputHtmlv = $True, you now have a .html report opened in your default web browser. It's also saved in your documents folder ( or somewhere else if you chenged the defaults in PAL.ps1).
It can be a very long list of tables and graphs, with warnings or errors on counters that exceeded the treshold values.
Activate your SQL Server brain module and evaluate what you see.

To be continued

If you prefer to use PAL with the GUI, check these posts by
Jim Youmans, at Sql Server Central, 2017-04-12
Mike Walsh, at StraightPath Solutions, 2016-07-18
Fpweb.net crew, at Fpweb.net, 2014-09-23
Jonathan Kehayias, at Simple Talk, 2012-11-30

Previous Post Next Post