How to capture the performance monitor counters and objects using PERFMON?

In the previous blogs, we have reviewed the performance objects and counters to identify memory, processor, disk, and overall SQL Server performance pressures and bottlenecks. In this blog post, we are going to review the steps in running the performance monitor (PERFMON) in both Windows Server 2003 and 2008 to capture the performance monitor counters and objects.

To minimize performance monitor overhead, it is usually recommended that you limit the number of counter selectively and save the counter logs locally on a local disk other than the ones that are being monitored instead of transferring the performance data across the network.

Windows Server 2003:

  1. Open Performance Monitor by running PERFMON in the “Run…” textbox.
  2. Expand the “Performance Logs and Alerts” node.
  3. Right-click on “Counter Logs.”
  4. Select “New Log Settings…” from the popup menu.
  5. Assign a named for the new log settings. Eg.: “ServerName_Perfmon.
  6. Click the “Add Counters…” button to start adding counters for each “Performance object” you wish to add counters from. Add the desired counters. Please see my previous blog posts on identifying memory, processor, disk, and overall SQL Server performance pressures and bottlenecks for suggestions.
  7. Once you have added all the required counters, click on the “Log files” tab and click on the “Configure…” button to specify the location for the log file to be stored in. Make sure the format stays in “Binary file” because you can parse them into different formats later.
  8. Click on the “Schedule” tab to schedule it.

To make the PERFMON file as a template to make it reusable, you can save the list of counters as an .htm file so that you can use it on other SQL Server hosts. You can right-click on the new log created and select the “Save Setting As…” option to save the file as .htm into the designated location. To use this list of objects and counters for other SQL Server hosts, open the .htm file in an editor such as Notepad and replace all instances of \\SQLServerMachineName with ‘ ‘ (a blank string) without the quotes.

To use the template PERFMON counter log file in a new host server for Windows Server 2003, open the PERFMON, in the “Performance Logs and Alerts” node right-click “Counter Logs” and select the option “New Log Settings From…” and then browse to the .htm template PERFMON file.

After the scheduled PERFMON completes, you can convert the .blg files to .csv files for further analysis. To convert perfmon .blg files to .csv file, you can use the command prompt and navigate to the directory where the binary file is located and type:

relog logfile.blg -f CSV -o newfile.csv

Windows Server 2008 and Windows Server 2008 R2

  1. Open Performance Monitor by running PERFMON in the “Run…” textbox.
  2. Under “Reliability and Performance”, expand “Data Collector Sets”.
  3. Right-click on “User Defined”
  4. Select “New” from the popup menu then select “Data Collector Set”.
  5. Assign a name for the new log settings. Eg.: “ServerName_Perfmon.
  6. Under the “Create data logs” section, check the checkbox for “Performance counter”.
  7. Click the “Add…” button to start adding counters for each “Performance object” you wish to add counters from. Add the desired counters. Please see my previous blog posts on identifying memory, processor, disk, and overall SQL Server performance pressures and bottlenecks for suggestions.
  8. Once you have added all the required counters, click the “OK” button and then click the “Next” button.
  9. Specify the location to save the file.
  10. Click the “Finish” button.
  11. Right-click the new file under the “User Defined” folder and select “Properties”.
  12. Click on the “Schedule” tab.
  13. Click on the “Add” button to schedule a “Beginning date:” and “Start time:” and then click on the “Stop Condition” tab to specify the “Overall duration.”

To make the PERFMON file as a template to make it reusable, you can save the list of counters as an .xml file so that you can use it on other SQL Server hosts. Right-click on the new log folder under the “User Defined” folder and select “Save Template…” to save the file as .xml in to the designated location.

To use a template perfmon counter log file in a Windows Server 2008 or 2008 R2 host server, open PerfMon > Reliability and Performance > Data Collector Sets > right-click on “User Defined” > New > “Data Collector Set” > name the file > select the “Create from a template (Recommended)” radio button > click the “Browse” button to browse to the template file > select the template .xml file > click the “Next” button > specify the location to save the file > the “Save and close” radio button will be selected > hit the “Finish” button.

To modify the new “DataCollector01” set, right-click on it and select “Properties” to add or remove the counters as necessary. Click the “OK” button when done.

To schedule the new log file, right-click on the new file under the “User Defined” section, click “Properties”, then click the “Schedule” tab.

To convert perfmon .blg files to .csv files, you can use the command prompt and navigate to the directory the binary file is located and type:

relog logfile.blg -f CSV -o newfile.csv

REFERENCES:

How to create a log using System Monitor in Windows
http://support.microsoft.com/kb/248345

Using Performance Monitor
http://technet.microsoft.com/en-us/library/cc749115.aspx

Be Sociable, Share!
Responses are currently closed, but you can trackback from your own site.

3 Responses to “How to capture the performance monitor counters and objects using PERFMON?”

  1. Bennie says:

    Thanks for sharing such a good idea, article is nice, thats why
    i have read it entirely

  2. homeschool help says:

    It’s in point of fact a great and useful piece of information. I’m glad that you just shared this helpful information with us. Please keep us informed like this. Thanks for sharing.

  3. Gabriela says:

    Keep on working, great job!