How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to analyze the Perfmon trace and detect IO bottlenecks)
So you have finally collected the Perfmon trace. Good job! Now, how can you analyze and evaluate this data?
It is easier than it seems:
1. Open Perfmon and go to the Performance Monitor tab.
2. Right click on the graph and choose to Remove All Counters
3. Right click on the graph and choose Properties.
4. Go to the source tab, choose Log files and click on the Add button.
5. Locate the Perfmon trace file and double click on it.
6. Now click on the Apply button.
7. Optionally you may use the Time Range button to examine only a portion of the trace (e.g. see the data from only 14:00 to 14:30).
8. Go to the Data tab and click on the Add button.
9. Add these Objects/Counters:
- Under the Memory object, add the counter: Available MB
- Under the Processor object, add the counter: %Processor Time (Instance: Total)
- Under the Physical Disk object, add the counters: Avg. Disk Sec/Read and Avg. Disk Sec/Write (All Instances)
- Under the Paging File object, add the counter: %Usage (Instance: Total)
- Under the Network Interface object, add the counter: Bytes Total/sec (All Instances)
So what are normal values for a SQL Server box:
- Available MB must not drop below 100 MB, otherwise you will see severe performance issues due to Windows memory pressure.
- The average of %Processor Time must be less than 70%, otherwise you have a bottleneck at the CPU.
- These two counters need to be constantly under 20 msec to have good disk performance, otherwise you possibly have a bottleneck at the disk subsystem.
- %Usage must be less than 30%, otherwise you will see performance issues due to paging. Install more memory to mitigate this issue.
- Bytes Total/sec needs to be 50% or lower than the NIC bandwidth. Any higher and you will see Network performance issues.
Next week I will explain how you can read and evaluate the SQL Server Perfmon counters. Stay tuned!