Using the Performance Analysis of Logs (PAL) Tool

SQL logoThe Performance Analysis of Logs (PAL) Tool is an extremely useful tool that provides a baseline of performance metrics of many Microsoft products. PAL can be useful as a starting point when troubleshooting possible causes of degraded performance.

Used with Windows Performance Monitor (Perfmon), thresholds are predefined for a specific Microsoft service such as SQL Server, Exchange, IIS, MOSS and Active Directory. The analysis of the thresholds are automated and the PAL Tool creates a HTML report with graphical charts when the thresholds are exceeded.

Windows Performance Monitor (Perfmon.exe) is a built in tool in Windows Operating Systems. Performance Monitor can be used to examine how running programs affect a computer’s performance, both in real time and by collecting log data for later analysis. In this blog, Perfmon will be used to create a Data Collector Set that will be imported into Performance Analysis of Logs (PAL) to analyze Microsoft SQL Server.

PAL can be downloaded at the following location:

PAL Pre-Requirements:

Below are Step-by-Step instructions for using Perfmon and PAL to perform a SQL Server Performance Analysis.

NOTE: Perfmon must be executed on the system that is having performance issues and being analyzed. The PAL Tool, however, can be executed from any system and is best practice as there is some overhead that can negatively impact the performance on a system that is already having performance issues.

How to Perform a SQL Server Performance Analysis

  1. Install the PAL Tool and the dependencies on the system that PAL will be executed from.
    1. Due to overhead on an already taxed system, the PAL Tool can be installed on a remote system.
  2. Once PAL and the dependencies are installed, execute PAL.PAL tool analysis wizard
  3. Click on the “Threshold File” TabThreshold File Tab
  4. In the “Threshold file title” dropdown box, select “Microsoft SQL Server 2005/2008”
  5. Click on the “Export to Perfmon template file…” button
    1. A “Save AS” dialog box will appear. Type in a name for the file (Example: SQL_PAL_Threshold_Template) and ensure the File Type is .xml. Click “Save”Export to Perfmon template file
    2. A dialog box will appear asking to specify the named instances. Type the Named Instances to be analysed. If more than one exists, separate with a semi-colon without spaces. Click “OK”.Named Instances to be analysed
  6. On the machine where the performance is being analysed, go to “Start” | “Run” | and Type: Perfmon. Press “Enter”
  7. Performance Monitor will open. Expand “Data Collector Sets” | Right click on “User Defined” | Select “New” | “Data Collector Set”Data Collector Set
  8. The “Create new Data Collector Set” dialog box will appear. in the “Name” field, type a Name for the data set and ensure “Create from a template” is selected. Click “Next”.Create new Data Collector Set
  9. In the “Which template would you like to use?” dialog box, click on “Browse”.Which template would you like to use
  10. Browse to the .xml file that was exported from PAL in Step 5 and select “Open”exported xml file
  11. Click “Next”
  12. Note the location of where the file will be saved and click “Finish”Location of the file
  13. In Performance Monitor, the User Defined Collector Set will be listed and will have a status of “Stopped”.Stopped status
  14. Right click on the newly created Data Collector Set and select “Properties”Select Properties
  15. Click on the “Schedule” Tab and click the “Add” button
  16. Select a “Beginning Date” and “Start Time” and click “OK”
  17. The Schedules will appear in the Dialog Box.Schedule appears in dialog box
  18. Click on the “Stop Condition” Tab and select when Perfmon should stop collecting performance metrics. Click “Apply” and “OK”Stop Condition tab
  19. Once the scheduled time begins, the Data Collector set will show the status of “Running”. The collection may be stopped at any time by Right clicking on the Data Collector Set and clicking “Stop”.
  20. Once the Data Collector Set is complete. Open up PAL and click on the “Counter Log” Tab and browse to the .blg file that was created from Perfmon by clicking on the “…” button next to “Counter Log Path:”. The path was referenced in Step 12 and can also be found in Perfmon, below “User Defined” by selecting the named Data Collector Set and viewing “Output”.Counter Log tab
  21. Browse to the path and click “Open.”Browse to the path
  22. The “Counter Log Path:” will now show the path to the .blg file that was collected from Perfmon.Counter Log Path
  23. Click on the “Questions” Tab and provide input for each of the questions for the machine that Perfmon was executed.
    1. NumberOfProcessors
    2. ThreeGBSwitch
    3. SixtyFourBit
    4. TotalMemory
    5. RAID5Drives
    6. RAID1DrivesQuestions tab
  24. Click on the “File Output” Tab and verify the path and below “HTML Output” ensure “HTML Report” is checked.File Output Tab
  25. Click on the “Execute” Tab and select “Execute: Execute what is currently in the queue.” Click “Finish”
  26. A Command window will open, which begins the execution of the analysis and the progress is displayed.Begin execution of analysis
  27. The following command output will appear once it is complete.Completed command ouput
  28. The HTML file will open and it will provide a detailed analysis of the system performance and SQL. The report is color coded and extremely descriptive.
2014-09-23T08:25:34+00:00 September 23rd, 2014|

One Comment

  1. Muhammad Shakeel August 12, 2015 at 6:16 pm - Reply

    Three Questions:
    1) Will it keep collection data if i schedule it daily basis.
    2) Every time will I have to perform all the steps or just step No 25 “execute” to look at the data.
    3) How to setup this on performance monitor server for multiple sql servers.


Leave A Comment