11. Trace manager

Trace Manager allows you monitor and analyse everything that happens in the database in real time. It tracks and logs such events as: connection to database and disconnection from it, database creation and deletion, execution of DML and DDL, stored procedures, etc.

_images/trace_manager.png

Fig. 11.1 Trace manager

To start audit, you must select a connection and specify a profile with settings. You can specify configuration file in the extended properties, then it will be used instead of the profile.

_images/trace_config.png

Fig. 11.2 Trace configuration

Depending on the server version, the audit parameters are slightly different, so select the server corresponding to the database from the drop-down list. For a description of the parameters, see Trace manager configuration file settings.

The Changeable parameters button allows you to define the parameters for quick access, they will be displayed on Connection tab :

_images/trace_parameters.png

Fig. 11.3 Changeable parameters

11.1. Grid view

Event table in Trace Manager is filled in either when tracing is enabled or when a saved log file is opened. When opening a log file, there is an option to select the encoding.

By default, the event table displays all possible columns containing information about the registered event. Some columns can be hidden. To do this, click the Visible columns button and modify them as required. The filter block allows you to add a condition for displaying data.

_images/trace_manager_table.png

Fig. 11.4 Grid view

When you right-click on any table, a context menu appears to export the table.

See Trace manager events table for a description of the event table columns.

11.2. Trace analysis

Trace analysis displays information about events that occurred during a defined time interval. Events are added either when tracing is enabled or when a log file is opened.

Trace analysis parameters:

  • TIME - Information about query execution time;

  • READ - Information about number of pages read from the disc;

  • FETCH - Information about number of pages read from page cache;

  • WRITE - Information about number of pages recorded on the disc;

  • MARK - Information about number of pages changed in the page cache;

  • RSORT - Information about RAM size used for sorting;

  • DSORT - Information about the size of temporary files used in the query;

  • Rround values - If the value is greater than 10000, it will be converted to a larger unit until it becomes less than 10000;

  • Show Plan - Execution plan for query.

Additional parameters:

  • Period - Time period to be analysed; after changing the period, press the Rebuild button;

  • Compare queries by N symbols - Consider queries as identical if they have the same first N characters;

  • Filter events - Allows you to select the types of events to be analysed.

_images/trace_manager_analysis.png

Fig. 11.5 Result table

When you hover over a cell, a tooltip will show the sum of the column values and the average value. To view the full text of the query and its plan, you need to select the cell. Double-clicking will open a window with the event record in text format.

See Trace analysis for description of columns of the resulting event table.

11.3. Session Manager

The Session Manager displays a list of all currently available trace sessions. The manager tab is visible only when the current audit session is running.

_images/trace_manager_session.png

Fig. 11.6 Session Manager

In the window on the left, you can select the name of the session and see information about it (ID, starting user, start time), and stop the tracing session.