Documentation forSQL Sentry

QuickTrace™

QuickTrace™

A QuickTrace is a comprehensive snapshot of activity created by combining process-level data and trace events collected during a brief sample period. Various metrics such as CPU, I/O, recompiles, cache misses, cursor operations, etc., are automatically aggregated, and are grouped and sorted providing a clear picture the processes, hosts, applications, or users responsible for activity during the sample. For a full list of the metrics gathered, see the QuickTrace Collected metrics table below.

Top SQL trace uses filters to collect only the heaviest events by default to maintain low overhead. A QuickTrace isn't filtered and collects all events and it's both time and row limited, to avoid impacting the performance of the target SQL Server. 

A QuickTrace is typically run manually from the Dashboard in response to observing high utilization in one or more metrics. For example, if there's a spike in Transactions/sec on the SQL Server Activity, run a QuickTrace by completing the following steps:

  1. Highlight the spike, and then right-click to open the context menu. Select Jump To > QuickTraces to open the QuickTraces tab. Performance Analysis Dashboard Jump To QuickTraces context menu 
  2. Select Run QuickTrace to open the Run a QuickTrace dialog box, configure your desired metrics, and then select Ok. QuickTraces tab Run QuickTrace
  3. Sort by Events to determine the cause. QuickTrace

Column Set

There are four Column Sets that can be selected and modified to provide a pre-determined view of the data with preset columns and sorting. If a QuickTrace is launched by right clicking the Network, CPU, SQL Server Activity, or Disk IO charts, the appropriate column set is used by default. Change the active column set by right-clicking the master or detail column header row, and then selecting the Column Chooser menu item.

QuickTraces tab Column Chooser context menu option

Note:  When you run a QuickTrace from the QuickTraces tab the SQL Activity Column Set is used.

The QuickTraces tab lists all QuickTraces occurring during the active date range. Execute QuickTraces manually or automatically using the Run QuickTraceaction in response to a condition such as Runtime Threshold Max or Performance Counter Threshold Max.

QuickTraces tab

Running a QuickTrace

Select Run QuickTrace from the QuickTraces tab or from the right-click menu on the other applicable tabs, to open the options menu. Enter the length of time the QuickTrace runs in the first dialog box. Choose to Collect statement events or not by selecting the checkbox in the second dialog box. Limit the number of Trace Data rows in the third dialog box. Select Ok to run the QuickTrace.

Note:  You can enable a QuickTrace to run when certain conditions are met by configuring the Run QuickTrace action. For more information about Actions in SQL Sentry, see the Actions article.

QuickTraces tab Run QuickTrace

Default Settings

  • The QuickTrace runs for 15 seconds. 
  • Collect statement events is unselected.
  • Limit Trace DataTo 10000 rows.
    Run QuickTrace default settings
Note:  Generally, these settings are fine but may need to be adjusted in certain situations.

Restrictions

To avoid impacting server performance on very busy systems, SQL Sentry restricts QuickTrace functionality under some circumstances.

QuickTrace Restrictions

A QuickTrace isn't allowed if a 100Mb adapter is present, and the last sample indicates there are >300 users or >3000 transactions per second for the targeted server.

This state is also checked before automated QuickTraces are run. The QuickTrace is disallowed if the state is detected. 

A QuickTrace isn't recommended in the case where 1000 Mb or above adapters are present, and the last sample indicates there are >500 users or >5000 transactions per second for the targeted server.

If this state is detected during a manually initiated QuickTrace, a warning generates indicating that the QuickTrace isn't recommended. The state is also checked before automated QuickTraces are run. The QuickTrace is disallowed if the state is detected. 

Warning:  SQL Sentry Version 7 introduced the above restrictions for QuickTrace functionality. For prior versions, use the above restrictions as a guideline when running a QuickTrace. Failure to follow these guidelines may cause a temporary suppression of transaction throughput. The risk of impact on performance is much greater if the network speed between the SQL Sentry client and the target server is less than 1000Mbps.

Exporting a QuickTrace

Once a QuickTrace is complete, it opens automatically for viewing. QuickTrace automatically opened

Export the QuickTrace by selecting Export Data from the File menu ( File > Export Data).

SQL Sentry File > Export Data

The following are four formats a QuickTrace can be exported in:

  • Adobe Acrobat Files (*.pdf)
  • CSV Files (*.csv)
  • Microsoft Excel (.xls) 
  • Web Pages (.htm, .html)

Display 

The top pane provides a series of filters and controls for specifying which records to display for the specified interval. 

Display Filters

Filter Image
SQL Servers SQL Sentry QuickTraces tab SQL Servers Filter
Owner QuickTraces tab Owners Filter
Sources QuickTraces tab Sources Filter

The bottom pane lists the records matching the current filters. The default filter is to display all records.

Controls

Top Pane

Control Description
Set filter After changing any of the filters, select Refresh,or press F5 to apply the filter.
List box filters
Select on a combination of items in any of the lists to set the filters. The default filter displays all records.
Select more than one item in a list. Shift-Click or CTRL-click
SQL Servers (dropdown) Select server instance to run a QuickTrace.
Run QuickTrace Start a QuickTrace against the select SQL Server instance.

Bottom Pane

Control Description
Sort Column Select any column header to sort by that column.
Group By box Drag any column header here to group by that column.
Double click on a row View the selected QuickTrace.

 Context Items

Context item Description
Jump to Dashboard Opens the Dashboard with the selected QuickTrace event overlaid on each of the charts.
Open View the selected QuickTrace.

QuickTrace Viewer

The QuickTrace Viewer presents a set of nested controls to inspect the trace. These controls behave the same as on the QuickTraces tab.

Right-clicking on a trace record presents a context menu that allows you to copy the event row data to the clipboard or kill the process associated with that trace event.

QuickTrace Kill Process or Copy context menu options

Note:  The individual text can be copied from the Text Data column, once highlighted, using (Ctrl + C) or the context menu.

Additional controls presented include:

Control Description
Reload Runs a new QuickTrace of the same target.
Refresh Available when a QuickTrace is run in real-time, and causes the QuickTrace to execute with same parameters.
Options Opens the Options pane. Available options in the Viewer are:         
  • Auto-highlight detail rows when sorting by an event totals column
  • Freezes metadata columns
  • Hides rows with empty values in sort column
  • Maintains row position when changing sort
  • Shows the QuickTrace process
  • Shows statement events if available
  • Shows system processes
Close Closes the QuickTrace Viewer.

QuickTrace Collected Metrics

The following metrics are collected per application when running a QuickTrace:

Metric Column Set
SPID CPU, Network, SQL Activity, Disk
Application  CPU, Network, SQL Activity, Disk
Host CPU, Network, SQL Activity, Disk
Database CPU, Network, SQL Activity, Disk
Login CPU, Network, SQL Activity, Disk
Login Time CPU, Network, SQL Activity, Disk 
Logout Time CPU, Network, SQL Activity, Disk
Events CPU, Network, SQL Activity, Disk
Duration CPU, Network, SQL Activity, Disk
CPU (Description) CPU, Network, SQL Activity, Disk
CPU % CPU, Network, SQL Activity, Disk
Logical Reads
 CPU, Network, SQL Activity, Disk
Logical Reads % CPU, Network, SQL Activity, Disk
Physical Writes CPU, Network, SQL Activity, Disk
Physical Writes % CPU, Network, SQL Activity, Disk
Physical Reads CPU, Network, SQL Activity, Disk
Physical Reads % CPU, Network, SQL Activity, Disk
Recompiles CPU, Network, SQL Activity, Disk
Recompiles % CPU, Network, SQL Activity, Disk
Cache Misses
 CPU, Network, SQL Activity, Disk
Cache Misses %
 CPU, Network, SQL Activity, Disk
Cursors
 CPU, Network, SQL Activity, Disk
Cursors %
 CPU, Network, SQL Activity, Disk
Physical IO CPU, Network, SQL Activity, Disk
Physical IO % CPU, Network, SQL Activity, Disk
Batches CPU, Network, SQL Activity, Disk
Batches % CPU, Network, SQL Activity, Disk
Transactions CPU, Network, SQL Activity, Disk
Transactions % CPU, Network, SQL Activity, Disk
SPs CPU, Network, SQL Activity, Disk
SPs % CPU, Network, SQL Activity, Disk
RPCs CPU, Network, SQL Activity, Disk
RPCs % CPU, Network, SQL Activity, Disk
Prep SQL CPU, Network, SQL Activity, Disk
Prep SQL % CPU, Network, SQL Activity, Disk
Network Reads CPU, Network, SQL Activity, Disk
Network Reads % CPU, Network, SQL Activity, Disk
Network Writes CPU, Network, SQL Activity, Disk
Network Writes % CPU, Network, SQL Activity, Disk
Cursor Ops CPU, Network, SQL Activity, Disk
Cursor Ops % CPU, Network, SQL Activity, Disk
Logins CPU, Network, SQL Activity, Disk
Logins % CPU, Network, SQL Activity, Disk
Objects Created CPU, Network, SQL Activity, Disk
Objects Created % CPU, Network, SQL Activity, Disk
Instance Resets CPU, Network, SQL Activity, Disk
Instance Resets % CPU, Network, SQL Activity, Disk
Client Network Address CPU, Network, SQL Activity, Disk
Client Interface Name CPU, Network, SQL Activity, Disk
Network Transport CPU, Network, SQL Activity, Disk
Network Auth Scheme CPU, Network, SQL Activity, Disk
Network Packet Size CPU, Network, SQL Activity, Disk
% Complete CPU, Network, SQL Activity, Disk
Est Completion Time CPU, Network, SQL Activity, Disk