How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to collect a detailed Perfmon trace)

Most people believe that in order to troubleshoot performance issues in a SQL Server instance you need to be an expert. That is not entirely true as most performance problems are very easy to identify.

This week I will discuss how you can collect a performance trace using the most basic tool:

 

Perfmon

 • Click on Start | Run and type Perfmon.exe

• Go to “Data Collector sets”, expand it and then right click on “User Defined”.

• Choose to create a new “Data Collector Set”.

• Give a name to the set, e.g. “SQLPerf”. Also choose to “Create Manually (Advanced)”. Click on Next.

• Then choose to “create data logs” and check only the “Performance counter” option. Click on Next.

 

I like to add all the counters and all instances from the below Perfmon objects, so that I can capture all the details and filter them as needed later on:

 

 Operating System Objects:

• Memory

• Network Interface

• Objects

• Paging File

• Physical Disk

• Process

• Processor

• Redirector

• Server

• Server Work Queues

• System

• Thread

 

SQL Server Objects:

• SQLServer: Access Methods

• SQLServer: Buffer manager

• SQLServer: Locks

• SQLServer: SQL Statistics

 

I usually set the interval of the Perfmon Log to 10 seconds. I would set it even lower to have more frequent samples, but the servers I examine are already overloaded so I try not to put extra pressure on them.

Now if you right click on the new collector set you created and choose Start, the trace will start to collect samples from the above objects every 10 seconds. Usually I keep the trace running at least 20 minutes to get a good picture from the server performance. After you stop the trace, it should be saved inside a subfolder of this folder: c:\Perflogs. It will have a file extension of .blg

To load the saved trace in Perfmon, you can simply right click on the .blg file.

Or you can open Perfmon first and then load the trace:

• Go to the “Source” tab

• Choose the “Log files” options and click on the Add button

• Browse the folders and locate the .blg file. Click on the file and press OK

• Right click on the Perfmon’s window and choose to Add Counters

As you can see, right clicking on the .blg file was a much simpler process :)

  

Next week I will explain some simple steps to analyze the Perfmon trace and identify common performance issues and IO bottlenecks. Analyzing the Perfmon trace will be as simple as collecting it.

See you next week!