Profiler and Perfmon Co-relation in SQL Server 2005

One of the neat features introduced in SQL Server Profiler in version 2005 is the ability to co-relate profiler results with perfmon results. This is good because it gives us the ability to correctly see what was going on in the system. There may be times when you see spikes in Performance Monitor on your SQL Server machine such as a high CPU usage, large amounts of memory consumption, or overall slower performance etc..  Before SQL Server 2005, you would have to capture a trace, use sysprocesses, syslockinfo and other system related data from SQL Server and capture your Performance Monitor logs. After doing all this, you would need to manually reconcile what happened between them to figure out why performance was suffering in the system.  Manual re-conciliation of the wait events and queues really became very cumbersome.

With SQL Server 2005, you still need to capture a trace and your Performance Monitor logs. However, Profiler has the capabilities to let you attach Performance Monitor logs and then scroll through your T-SQL statements while Profiler automatically shows graphically what happened in your Performance Monitor logs. The process also works if you click in the Performance Monitor user interface in Profiler, which jumps you to the statement that correlates to that timestamp. This helps immensely in troubleshooting issues in the system.

To demonstrate this, let’s start a perfmon and a profiler trace and let’s use this simple script to generate some small amount of load. The laptop on which this is being run is a very minial configuration so am sure this would be enough to peg the CPU. Here’s the script:

create table t (col1 datetime)
go

declare @i int
set @i = 1
while (@i < 100000)
begin
insert into t values (getdate())
set @i = @i + 1
end

Before we kick it off, let’s start the traces: Start/Run, type in perfmon, and hit Enter. From there, you’ll want to create a new counter log under the Performance Logs and Alerts by right-clicking the Counter Logs node and selecting New Log Settings. Then, you can click the Add Counters button and add % processor time. For the sake of this demo, we can start this on a manual basis (the schedule tab). Once you’re done, hit OK. Now, let’s start up the profiler trace as well using SQL Server Profiler. Just chose the default template for this demo and start both the perfmon and profiler trace.

Now, run that script and once it is done, stop the traces and save the profiler trace. You cannot open up the co-relation with perfmon till you save a profiler trace and then load it back up. Once the profiler trace is saved, in Profiler, from the File menu, select Import Performance Data. Select the location where you stored your Performance Monitor log. Then, select from the File menu Open and then Trace. Select the location where you stored your Profiler trace. Now, you can use Performance Monitor correlation (from File/Import Performance Data) between the two to figure out what effect on the processor a certain SQL statement had. See figure below:

This shows that the CPU was really pegged at 100% when the processing was going on. I also had Reporting Services running at the same time so you see some calls from Report Server as well. This is a great way of troubleshooting performance issues and co-relation between these 2 great tools is a very nice feature in SQL 2005. In future blogs, we will post some of the performance counters and profiler templates that we use for our performance tuning and benchmarking.