Welcome to Bucaro TecHelp!

Bucaro TecHelp
HTTPS Encryption not required because no account numbers or
personal information is ever requested or accepted by this site

About Bucaro TecHelp About BTH User Agreement User Agreement Privacy Policy Privacy Site Map Site Map Contact Bucaro TecHelp Contact RSS News Feeds News Feeds

Use Perfmon and SQL Profiler Together to Troubleshoot SQL Server Performance Issues

SQL Profiler

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.

RSS Feed RSS Feed

Follow Stephen Bucaro Follow @Stephen Bucaro

Fire HD
[Site User Agreement] [Privacy Policy] [Site map] [Search This Site] [Contact Form]
Copyright©2001-2024 Bucaro TecHelp 13771 N Fountain Hills Blvd Suite 114-248 Fountain Hills, AZ 85268