Professional SQL Server® 2005 Performance Tuning

3.11. Combining Performance Monitor Logs and SQL Profiler Trace

Troubleshooting performance related problems in SQL Server 2000 often required gathering Performance Monitor logs, SQL Profiler traces, and activity details from system tables such as sysprocesses. There was no capability provided to combine the log and trace data to provide any kind of unified view. Interpreting this data often required system administrators to undertake the arduous and pain-staking process of manually stepping through SQL Profiler traces and then switching to the same point in time of the Performance Monitor logs to gain an understanding of resource status while a specific query or stored procedure was executed. While laborious and time consuming, this method did allow system administrators to correlate SQL Server activity (Profiler) with resource utilization (Performance Monitor).

Fortunately, analysis of performance data has been significantly improved in SQL Server 2005 with a feature allowing importing of Performance Monitor log files into a SQL Profiler trace. The process is quite straight forward, and necessitates both Perfmon and Profiler traces be captured to file. Of course you'll need to configure each Profiler and Perfmon separately, although these should run simultaneously. Once the log and trace files have been created, you should open the trace file in SQL Profiler and from the file menu select Import Performance Data and select the Perfmon log file. Further details, including a screen ...