SQL Server Administrator Tool: PAL

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | May 14, 2015

SQL Server Administrator Tool: PAL


This tool is more commonly referred to as PAL - Performance Analysis of Logs. This can be treated as an Authentic Tool that can be utilized with any Windows based DEV/QA/PROD Systems.

As the first part of the blog Series, I would like to introduce to all of you a very important tool which is not only popular but also very helpful for analyzing the Windows Performance Monitor Files (.BLG or .CSV).

This tool is more commonly referred to as PAL - Performance Analysis of Logs. This can be treated as an Authentic Tool that can be utilized with any Windows based DEV/QA/PROD Systems.

PREREQUISITES:

  • You need to have Microsoft .NET Framework 3.5 (along with Chart Feature) to let this tool work. If it is not installed already; it will prompt you to do so.
  • You must have basic understanding of the Perfmon Counters (either SQL Server Related OR Window Server Related) to use this tool effectively.



Let’s start exploring the PAL Tool. It is available for x86 and x64 Bit Systems and pretty easy to use.

1. Once you get a Perfmon File (Normally in .blg format); follow the below steps.

SQL Server Administrator Tool: PAL

 

 


2. The below Screenshot is showing the Browse Option through which we can upload our BLG File. It can be in a CSV Format also if you have exported Data in the same.


SQL Server Administrator Tool: PAL

 

3. The Date/Time Range Option allows you to filter out the range that you want to analyze or incase the file is too big to upload at once. You may face issues during uploading a big file or filter using PAL may not give you proper data results.

SQL Server Administrator Tool: PAL

4. In some cases; analysis requires Data to be precise OR with RANGE in seconds OR a few minutes. A Single BLG File can be broken into multiple files using Perfmon.

SQL Server Administrator Tool: PAL

5. After selecting the Date/Time range, go to next screen as shown below. Usually the default settings are considered.

SQL Server Administrator Tool: PAL

6. The below screen shows how one can select values from a drop down accordingly. You have to choose the Hardware Configuration of your Host for which BLG file is uploaded. IT is NOT IGNORABLE as wrong values will hamper your Analysis Result as using these values; PAL Tool makes basic Threshold for Perfmon Counters where applicable.

SQL Server Administrator Tool: PAL

SQL Server Administrator Tool: PAL

 

7. Powershell can be used to check the Memory/UserVA values using command advised by PAL.

SQL Server Administrator Tool: PAL

SQL Server Administrator Tool: PAL

8. This is a very important screen; please follow the Underlined Description which clearly shows that BY DEFAULT; your BLG File will be divided into 30 time slices. This is where Step 4 mentioned above is Important.

SQL Server Administrator Tool: PAL

9. Leave the below as default and go to next step.

SQL Server Administrator Tool: PAL

10. Go with the default option for the screen below.

SQL Server Administrator Tool: PAL

11. This is the final screen you will see once the process initiates.

SQL Server Administrator Tool: PAL

12. This is how the screen looks once the process is complete and automatically an Internet Explorer or default browser will show with the results.

SQL Server Administrator Tool: PAL

Final Result
This is how your Perfmon Analysis Result will look like and it will create a separate folder as mentioned in Step 9; with all the screenshots of the result. One screenshot for each counter you selected or data is being fetched by perfmon.

You will get an Analysis Report in the HTM file format with an address as shown below followed by the snapshots-
C:\Users\vsharma1\Documents\PAL Reports\VSHARMA1-PC_System_Monitor_Log_PAL_ANALYSIS_2014-11-21_00-36-16.htm

For Clarification; please make note that below Screen shots are just examples. Depending upon the Number of Counters selected and Data Received; you may get more pictures and lengthy result set.

 

SQL Server Administrator Tool: PAL

SQL Server Administrator Tool: PAL

 

 

SQL Server Administrator Tool: PAL

 

 

Below is the Screen where it shows Result based on Self-Created Threshold for counters which is the result of the values you mentioned in Step 6 and 7.

Final Words
I hope that you enjoy learning about this new tool through this blog. To reiterate this is an authentic tool which is globally used by DBAs and Wintel Admin community. If you have any queries or doubts please feel free to reach out to me at vssharma@Tricoresolutions.com. I am sure analyzing the Perfmon Files wouldn't have been so easy and presentable when sharing results with clients.

 

Connect with the author

 

Other SQL Blogs fom TriCore:

SQL Server Performance Troubleshooting – Missing Indexes

Understanding Backups with SQL Server AlwaysOn High Availability Mirrors

SQL SERVER RESOURCE BOTTLENECK – CPU -The First in a Four Part Series.