Use Perfmon and SQL Profiler Together to Troubleshoot SQL Server Performance Issues
By John Combs
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. I always choose the .CSV
output because it can then be easily used for other purposes as well.
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, 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.
Visit my SQL DBA [http://bestsqldba.com Sorry, the website bestsqldba.com cannot be found]
site for more information.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
