How To Set PAL Data Collector Set Template On The SQL Server – Support Hub

May 22, 2024 10:54
Updated

Attachments

saSylJhuqk.mp4
2 MB Download

Performance Analysis of Logs (PAL) Tool is a powerful tool that reads in a predefined Performance Counters log, and then analyzes it using known thresholds to produce a very readable report. This document will demonstrate how to set up PAL's Data Collector Set template on the SQL Server active node.

What Is PAL?

PAL is a 3rd party tool developed by Clint Huffman, Senior Microsoft Premier Field Engineer at Microsoft, aimed to simplify Performance Counters' collection and analysis. It's a powerful tool that reads in a predefined Performance Counters log, and then analyzes it using known thresholds to produce a very readable report useful for finding system level bottlenecks: Server hardware activity (network, disks, CPU, etc.) + SQL Server activity + OS activity.

This document will demonstrate how to set up PAL's Data Collector Set template on the SQL Server active node. The template itself is an XML file that can be provided by TEC.

Overview

1. Step by step setup:

1.1. Get the correct Data Collector Set template from TEC.

1.2. Set up the Data Collector Set template on the SQL Server's active node's Performance Monitor.

1.3. Adjust the Data Collector Set's and the log's properties.

1.4. Start PAL collector set.

2. Step by step collection:

2.1. Stop the PAL Data Collector Set.

2.2. Collect the Data Collector Set .blg file.

2.3. Zip the collected .blg file.

2.4. Re-start the PAL Data Collector Set.

Instructions

Step by step setup

Get the correct Data Collector Set template from TEC

Contact Dalet TEC and provide them with a General Diagnostics report in order to receive the correct PAL Data Collector Set template.

Set up the Data Collector Set template on the SQL Server's active node's Performance Monitor

On the active SQL Server node, open the Windows Run dialogue (from the Start Menu or Win+R) and enter "perfmon.exe". This will open the built-in Performance Monitor tool.

Expand 'Data Collector Sets' -> Right-click on 'User Defined' -> Click on 'New' -> 'Data Collector Set'.

Name your data collector set and choose 'Create from a template (Recommended)'.

Don't choose a preset data collector set, but rather click on 'Browse' and browse to the PAL *.xml you've created.

Click on the PAL collector set and then click 'Next'.

Select the designated storage folder. It doesn't have to be on the default location, but it's usually good enough.

Select the user to run the collector set as (usually '<Default>' is good enough), then choose 'Open properties for this data collector set' and click 'Finish'.

Adjust the Data Collector Set's and the log's properties

In the Properties of the collector set we only need to set the stop condition, so click on the 'Stop Condition' tab -> Check the 'Maximum Size' and set a desired size (in MB). 3000MB is usually enough to cover 4-6 days.

Click on the newly create PAL collector set -> Right-click on the collector set data file -> Click on 'Properties'.

Set the log file name. We recommend using the customer's name here. Then, in the 'Log mode' section, check 'Overwrite' and 'Circular (requires a non-zero maximum file size)'.

Start PAL collector set

Right-click on the PAL collector set -> Click on 'Start'.

Step by step collection

Stop the PAL Data Collector Set

On the active SQL Server node, open the Windows Run dialogue (from the Start Menu or ⊞Win + R) and enter "perfmon.exe". This will open the built-in Performance Monitor tool.