It is important to establish the baseline while doing the performance tuning. Having consistent method to collect the performance metrics make it easy to see the affect of the changes we made during the performance tuning exercise.
Perfmon.exe is the utility that we can set up and collect the performance metrics on the Windows.
Here is the Perfmon screenshot capture on my Win2008R2 SP1
PAL: PAL is stands for Performance Analysis of Logs. It is the open source application from codeplex and can help us to analysis the perform log. We can also use it to defined the perform template.
Here is the screenshot of the PAL.
Edit and export the Perfmon template
The defend window has better readability for counter description. More easy to read in the native perfmon window.
After you export the template, you can also open it in the text editor to review or edit it.
Import the Perfmon template
Select User Defined under Data Collector Sets for New –> Data Collector Set
Click Browser and select the template
Start the Perfmon
In the meantime, I am using HAMMERORA to generate some load on the SQL Server. You can refer here for how to set it up.
Analysis the Perfmon log
The Perfmon log default is output to C:\PerfLogs\Admin\[Collector Name].
PAL is CPU intense process. DO NOT run the PAL on your production SQL Server host.
Select the log path
Make sure the environment are mating on the target SQL Server host.
This to determine the interval for the log. Keep the default “AUTO”, unless you have good reason.
After you execute, it will call the PowerShell script to process.
After the process complete, it would open the report in the default browser
We can review the report and do some analysis on it.
Known issue with PAL
- Not yet fully support SQL Server 2012. However, there is the workaround for it.
- Require Office Web components. As far as I know, it is not install by default on Win8/Win Server 2012. MSFT also has deprecate that technology.
Would perfmon adding more performance cost to the system?
In general, Perfmon would take 1-2% of COU load, but it depends on:
- Sample Interval
- Disk Performance
- Number of counters