A Correlated View For Performance Tuning
Imagine that you are experiencing sporadic high CPU use by your SQL Server and you need to find the query or queries that are causing the problem. You might start up the Windows Performance Monitor (Perfmon) to monitor CPU. Perfmon will show you when these spikes occur and how much CPU they use but it will not show you the code that might be causing them. If you would like a free graphical tool that will show you the spikes as well as the code causing them, relax, you already have one.
Perfmon output can be correlated with trace data to give you the view you need to fix the problem. The view above shows you what the correlated output might look like. The beauty of this view is that you can click anywhere in the lower graph and, in the upper tabular trace data, you will jump to the code that is executing at that moment. There are also buttons that will jump directly to highest and lowest values for any specific perfmon counter.
Of course this involves creating a Perfmon counter log and a Profiler trace file to run during the same period of time. Your Profiler trace file must include the startdate column and both the Profiler trace and Perfmon counter log must contain the metrics in which you are interested.
We assume you are experienced in creating, running and saving traces but if you are not familiar with creating Performance Monitor counter logs here is a link that can step you through the process. .
To load the perfmon file into Profiler simply open Profiler, load the trace file you created. From there select Import Performance Data from the File menu. You will be prompted to navigate to the perfmon file you created.
At this point you can select all or some of the Perfmon metrics you want to see in the graph. I would advise looking at only a few at a time because the small graph window can get cluttered and hard to read. However, if you want to change the selection you have to go back to Import Performance Data.
It is a constant surprise to me when I go out to a client site to see how many otherwise-knowledgeable SQL techies don’t know of this inter-operability between these two widely used monitoring applications.