Automation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 1

This entry is part 1 of 3 in the series Automation of PAL

I recently was re-exposed to the PAL (Performance Analysis of Logs) Tool available on CodePlex at a wonderful SQLskills class.  I had played with the tool in the past but at the time the server I had for setting things up and automtaing processes was Windows 2000 so I forgot about this tool.  Now I have Windows 2008 server so I have the ability to re-explore the use of this tool.    This will be a multi-part post to go through how I setup a automatic system for collecting performance counters for SQL Server.

Part 1: Create tables to drive automation and create Performance Monitor Data Collector / Counter Log on each server

Step 1: Create Server Table

Give that I have over 100 hundred SQL Servers ranging from 2000 to 2008 R2 and using Windows Server 2000 thought Windows Server 2008 R2 I wanted a process that could be ran on each system automatically by turning on a flag.  First thing we need a database (mine is called DBA) to house our table  that list out our SQL Servers and whether to collect the PAL logs.  nce you create your database create the table below.

Step 2: Create table to hold PAL Parameters needed for processing

Next, we need a table for hold parameters that are need by the PAL tool.  Below is a print screen of the options PAL uses to process the Performance Counter Log:

PAL Options

Here is a script to create the table for the PAL options:

Step 3: Export the threshold file for SQL Server from the PAL Tool

You also need to use PAL and export the Perfmon Template File on the Threshold File tab or you can download mine here (save this file as a SQLServer2005+.txt and SQLServer2005+.xml) into our PowerShell scripts folder.

PAL Threshold File

Step 5: Create a Share name on each server to save the files

You will need a share setup on each server that you want to collect information from (I use one called SQL_Trace because I also have a process that automatically runs server traces and processes them on a remote server). I

Step 6: Create Performance Monitor Data Collector or Counter Log and Start Collecting

The final script goes out to each server using PowerShell to populate the Options table.   The only thing it does not populate is the RAID5_Drives and RAID1_Drives.  This has to populated manually and should be a comma delimited list of disk i.e. C,D,E. To take advantage of this script you must use the SQLPSX (SQL Server PowerShell Extensions) available on CodePlex.  Every time you need to start collecting performance counters on a new system change the PAL bit in the server table then execute the below script.

Next post will be a script to stop the SQLPerf collector on each server each night and rename the file with the date if on an older operating system.  If you have any questions or comments please leave them below.

Series NavigationAutomation of PAL (Performance Analysis of Logs) Tool for SQL Server Using Powershell – Part 2 >>
Tracy Boggiano
Follow me

Tracy Boggiano

Database Administrator at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Platform. She has worked on SQL Server 6.5 and up including currently SQL 2017 CTP 2.0. She enjoys monitoring, performance tuning, and disaster recovery technologies.

She also tinkered with databases in middle school to keep her baseball card collection organized.

Her passion outside of SQL Server is volunteering with foster children as their advocate in court through
Tracy Boggiano
Follow me