Collecting SQL Profiler trace data

SQL Response can continuously collect SQL Profiler trace data on selected SQL Servers you are monitoring.

Enabling the collection of trace data allows you to review the key SQL statements that were executing around the time of a raised alert; captured SQL Profiler data from just before and after the alert is displayed as part of the diagnostic data provided with each raised alert on the Server.

Setting up SQL Profiler trace collection on a SQL Server

In the Trace column of the Servers to show area in the Filter pane, SQL Servers that are collecting trace data are labelled Yes.

To turn on the collection of SQL Profiler trace data for a SQL Server:

1. Right-click on the SQL Server in the Servers to show area of the Filter pane, and click Server properties.
2. In the SQL Server Properties dialog box, select Enable collection of trace data.

Alerts that are raised on that SQL Server from this point on will include trace data in the Performance snapshot section of the alert details pane. See Viewing performance snapshot data.

If you turn off the collection of SQL Profiler trace data for a SQL Server, this will only affect alerts that are raised from that point on. Existing raised alerts will still include the trace data.

For a short period after you enable trace on a SQL Server, the server icon in the Servers to show area indicates that SQL Response is attempting to connect to that SQL Server. SQL Response needs to reinitialize the connection to the SQL Server when you turn on collection of SQL Profiler trace data.

What trace data is collected?

The following trace information is collected (SQL Server event number in brackets) :

Note: The Profiler trace events that are collected are hard-wired in SQL Response, and cannot be customised.

Effect of running trace

Enabling the collection of SQL Profiler trace data on a SQL Server will increase processor activity on that SQL Server (typically, by up to 5%). Enabling collection of trace data on numerous computers can therefore reduce performance of the Alert Repository.

For that reason, we recommend that you enable trace data only on problematic or important SQL Servers.

Where are trace data files stored?

When SQL Profiler trace data is collected on a SQL Server, it is stored in the log folder for that SQL Server. SQL Response then collects this trace data from each monitored SQL Server at intervals of one minute, and stores it in the data folder on the Alert Repository server. About 20 minutes worth of trace data is stored in the data folder on the Alert Repository at any time; this requires roughly 1 gigabyte of disk space. Older trace files are automatically deleted.

What happens if I turn off collection of trace data?

SQL Response provides some diagnostic information for each alert even when the collection of trace data is turned off. CPU utilization, memory and disk performance, and data from various other SQL-based counters are still collected.