By Kalman Toth, M.Phil., M.Phil., MCDBA

January 7, 2006

In the past, you could run PerfMon counters and SQL Server trace simultaneously when working on performance issues. To correlate, for example, a huge disk write event with a batch stored procedure was a manual process. Basically you had to look in two windows side by side matching the time for the two kinds of event logs. SQL 2005 introduces automation for this process, a godsend for DBA-s.

SQL Server 2005 Profiler can correlate Microsoft Windows System Monitor (Performance Monitor in Windows NT 4.0) counters with SQL Server 2005 or SQL Server 2005 Analysis Services (SSAS) events that are saved to a trace file with both StartTime and EndTime data columns. Windows System Monitor logs system activity for specified counters in performance logs, which are saved to a file as well. The results are two files each with a timeline.

Using SQL Server 2005 Profiler, you can open a Microsoft Windows performance log file, choose the counters you want to correlate with a trace, and display the selected performance counters alongside the trace in the SQL Server 2005 Profiler graphical user interface displaying queries, stored procedures recorded in the trace file. When you select an event in the trace window, a vertical red bar in the System Monitor data window pane of SQL Server Profiler indicates the performance log data that correlates with the selected trace event. You can just grab the vertical red bar and move it over to PerfMon spike and the highlight line repositions in the trace window to the query or stored proc which took place at the same time, therefore very likely causing the spike.

On the Performance Tuning CD a unit is dedicated to the exciting topic of Correlating a Trace with Windows Performance Log Data. I turned on both trace and PerfMon recording and proceeded to create heavy-duty read, write and cpu activities. The correlation mechanism has perfectly displayed the spike-query interplay.

Since monster queries are here to stay, the trace-PerfMon correlation feature adds a very powerful tool to the arsenal of an SQL 2005 DBA.