Wednesday, November 21, 2012

0 Establish the baseline for SQL Server Performance tuning using Perfmon and PAL

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

image

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.

image

Edit and export the Perfmon template

image

The defend window has better readability for counter description. More easy to read in the native perfmon window.

image

After you export the template, you can also open it in the text editor to review or edit it.

image

Import the Perfmon template

Select User Defined under Data Collector Sets for New –> Data Collector Set

image

image

Click Browser and select the template

image

image

Start the Perfmon

image

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

image

Make sure the environment are mating on the target SQL Server host.

image

This to determine the interval for the log. Keep the default “AUTO”, unless you have good reason.

image

After you execute, it will call the PowerShell script to process.

image

After the process complete, it would open the report in the default browser

image

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates