How to: Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
SQL Server Profiler can correlate Microsoft Windows System Monitor counters with SQL Server or SQL Server 2005 Analysis Services (SSAS) events. Windows System Monitor logs system activity for specified counters in performance logs.
Note
For information about sharing logs among different versions of Windows, see the procedure at the end of this topic.
To correlate a trace with performance log data
In SQL Server Profiler, open a saved trace file or trace table. You cannot correlate a running trace that is still collecting event data. For accurate correlation with System Monitor data, the trace must contain both StartTime and EndTime data columns.
On the SQL Server Profiler File menu, click Import Performance Data.
In the Open dialog box, select a file that contains a performance log. The performance log data must have been captured during the same time period in which the trace data is captured.
In the Performance Counters Limit dialog box, select the check boxes that correspond to the System Monitor objects and counters that you want to display alongside the trace. Click OK.
Select an event in the trace events window, or navigate through several adjacent rows in the trace events window by using the arrow keys. The vertical red bar in the System Monitor data window indicates the performance log data that is correlated with the selected trace event.
Click a point of interest in the System Monitor graph. The corresponding trace row that is nearest in time is selected. To zoom in on a time range, press and drag the mouse pointer in the System Monitor graph.
To create performance logs that can be shared among different versions of Windows
In Control Panel, open Administrative Tools, and then double click Performance.
In the Performance dialog box, expand Performance Logs and Alerts, right-click Counter Logs, and then click New Log Settings.
Type a name for the counter log, and then click OK.
On the General tab, click Add Counters.
In the Performance object list, select a performance object you want to monitor. The names of SQL Server performance objects for default instances of SQL Server start with SQL Server and named instances start with MSSQL$instanceName.
Add as many counters as necessary for your SQL Server instance and other important values, such as processor time and disk time.
When you have finished adding counters, click Close.
Set values for the Sample data every interval. Start with a modest sampling interval, such as 5 minutes, and then adjust the interval if necessary.
On the Log Files tab, choose TextFile (Comma delimited) from the Log file type list. Comma-delimited text log files can be shared among different versions of Windows and can be viewed later in reporting tools such as Microsoft Excel.
On the Schedule tab, specify a monitoring schedule.
Click OK to create the performance log.
See Also
Other Resources
Using SQL Server Profiler
Starting SQL Server Profiler