Appendix C - Using SQL Trace
SQL Trace is a graphical utility that is used to monitor database activity. The monitoring can be done in real-time or can be done on particular users, applications, or hosts. This utility can only be used with SQL Server 6.5 servers.
Starting SQL Trace
The name of the SQL Trace executable file is SQLTRACE.EXE and it should be located in the SQL Server BINN directory. By default, the SQL Server 6.5 installation will place a shortcut to SQL Trace in the SQL Server program group. This (or another) shortcut can be used to start the utility or it can be started from the command line. Figure C.1 shows SQL Trace immediately after it is started.Fig. C.1 - When started, SQL Trace will prompt the user to log in to a database.
After successfully connecting to a database, the user is prompted to select which defined filters should be started (see Fig. C.2). If no filters are defined, this dialog box does not appear.
Fig. C.2 - Filters can begin monitoring a database as soon as the user successfully logs in to that database.
Filters can now be added, deleted, edited, started, stopped, and paused. In addition, several other utilities can be started from within SQL Trace:
- ISQL/w
- SQL Enterprise Manager
- Windows NT Performance Monitor
When starting an ISQL/w session from within SQL Trace, ISQL/w will attempt to login with the login information used to connect SQL Trace to a SQL Server database.
Using SQL Trace
The SQL Trace interface is divided into two sections:
- Active Filter Pane
- Filter Status Pane
Activities that meet a filter's criteria are displayed in the active filter pane (or window). The name of the filter being displayed is shown in the title bar. Later, in the Setting Up a Filter section, filters will be explained in more detail.
At the bottom of the SQL Trace window is the filter status pane. This pane is made up of several items that display information about filters defined for the current server. Figure C.3 shows a SQL Trace window with an active filter pane and filter status pane visible.
Fig. C.3 - The filter status pane can be toggled between visible and invisible.
The filter status bar tracks the following information about defined filters for the current server:
- Status—Graphically represents the state (started, stopped, or paused) of a filter.
- Filter Name—Displays the name that was assigned to the filter when it was defined.
- Screen—If you check this option, an active filter pane is displayed for that filter.
- Script—Checking this option will cause the activity monitored by the filter to be saved as an SQL script.
- Log—Checking this option will cause the activity monitored by the filter to be saved in a log file.
Setting Up a Filter
To create a new filter, follow these steps:
- Either select New Filter from the File menu or click the New Filter button (refer to Fig. C.3). The New Filter dialog box appears (see Fig. C.4).
Fig. C.4 - Filters can be added or deleted form the New Filter dialog box.
- Enter a Filter Name as well as any specific Login Name, Application, or Host Name to monitor. To monitor all connections, leave the default <All>.
- Choose the Capture Options page at the bottom of the dialog box. The SQL can be captured as screen output (View on Screen), SQL script (Save to Script File), log file (Save as Log File), or any combination of the three. Each connection can be monitored separately using the Per Connection option. In addition to the commands issued, performance data can be collected by checking the Include Performance Information oiption.
- Select the Events page and choose the events for which to capture data. As shown in Figure C.5, the Events page also enables you to filter SQL and RPC statements. The SQL and RPC filters are used as string values for which to search. Multiple values can be entered, separated by a semicolon, and the percent sign is used as a wildcard.
Fig. C.5 - SQL and RPC statements that contain a specified string value can be filtered.
- Click the Add button to add the filter to the defined filters for the current server.
SQL Trace is a simple but powerful tool. It can be used for many purposes. Debugging applications can be simplified by using SQL Trace to monitor SQL statements issued from an application. Performance issues can be addressed by recording a particular user's activity. And security can be enhanced by checking for dangerous activity on a server.
