A great technique for troubleshooting SQL Server performance issues is to make use of both Perfmon and SQL Profiler together. Each of these tools on their own can give you insight into what is happening on your database server, but when combined they can clearly narrow down your performance bottleneck to specific events or statements that you can then focus on to resolve the performance problem.
First thing you need to do is to set up your Perfmon and save its results to a file. You do this by right clicking on trace logs, and then adding the counters that you want to monitor, then click on the log files tab and choose your output type.
Next you will want to set up your SQL Profiler to trace the events that you want to monitor. The built in templates are fine for this purpose, just choose the one that best matches the type of problem that you are trying to troubleshoot for. Choose the option to save your trace results to a file and specify where you want the file to be saved.
Now that you have both your Perfmon and Profiler set up and ready to go just start them both and monitor for the event you are troubleshooting. Once it has occurred then stop Perfmon and Profiler. Open Profiler back up and load the output file from the path you specified when you set up the Profiler trace. Now you want to add the data you gathered from Perfmon. To do this you click on File, and then Import performance data. Browse to where you saved the Perfmon log and double click on it. A dialogue box will open asking you to choose which counters you want to display. Pick your counters and then click OK.
You will now see the Perfmon graph above the SQL Profiler trace window in Profiler. Now all you have to do is click on the Perfmon graph where your event has taken place and you will see the Profiler window automatically scroll to that same point in time. For example, if you were troubleshooting a spike in CPU activity you would click on the Perfmon graph where you see the CPU spike begin and then the Profiler window will scroll down to the corresponding time and you will now be able to see exactly what statements were running in SQL Server at that time. Now you can focus on those statements to help correct the performance bottleneck.
Using Perfmon and SQL Profiler together can be a powerful combination to help you resolve performance issues on your SQL Server. Use this technique on a regular basis to keep your database server operating at peak efficiency.
Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.
