The 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: http://pal.codeplex.com/
- PowerShell v2.0 or greater
- Microsoft .NET Framework 3.5 Service Pack 1
- Microsoft Chart Controls for Microsoft .NET Framework 3.5
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
- Install the PAL Tool and the dependencies on the system that PAL will be executed from.
- Due to overhead on an already taxed system, the PAL Tool can be installed on a remote system.
- Once PAL and the dependencies are installed, execute PAL.
- Click on the “Threshold File” Tab
- In the “Threshold file title” dropdown box, select “Microsoft SQL Server 2005/2008”
- Click on the “Export to Perfmon template file…” button
- 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”
- 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”.
- On the machine where the performance is being analysed, go to “Start” | “Run” | and Type: Perfmon. Press “Enter”
- Performance Monitor will open. Expand “Data Collector Sets” | Right click on “User Defined” | Select “New” | “Data Collector Set”
- 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”.
- In the “Which template would you like to use?” dialog box, click on “Browse”.
- Browse to the .xml file that was exported from PAL in Step 5 and select “Open”
- Click “Next”
- Note the location of where the file will be saved and click “Finish”
- In Performance Monitor, the User Defined Collector Set will be listed and will have a status of “Stopped”.
- Right click on the newly created Data Collector Set and select “Properties”
- Click on the “Schedule” Tab and click the “Add” button
- Select a “Beginning Date” and “Start Time” and click “OK”
- The Schedules will appear in the Dialog Box.
- Click on the “Stop Condition” Tab and select when Perfmon should stop collecting performance metrics. Click “Apply” and “OK”
- 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”.
- 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”.
- Browse to the path and click “Open.”
- The “Counter Log Path:” will now show the path to the .blg file that was collected from Perfmon.
- Click on the “Questions” Tab and provide input for each of the questions for the machine that Perfmon was executed.
- Click on the “File Output” Tab and verify the path and below “HTML Output” ensure “HTML Report” is checked.
- Click on the “Execute” Tab and select “Execute: Execute what is currently in the queue.” Click “Finish”
- A Command window will open, which begins the execution of the analysis and the progress is displayed.
- The following command output will appear once it is complete.
- 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.