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.
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:
- Highlight the spike, and then right-click to open the context menu. Select Jump To > QuickTraces to open the QuickTraces tab.
- Select Run QuickTrace to open the Run a QuickTrace dialog box, configure your desired metrics, and then select Ok.
- Sort by Events to determine the cause.
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.
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 QuickTrace action in response to a condition such as Runtime Threshold Max or Performance Counter Threshold Max.
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.
- The QuickTrace runs for 15 seconds.
- Collect statement events is unselected.
- Limit Trace Data To 10000 rows.
Note: Generally, these settings are fine but may need to be adjusted in certain situations.
To avoid impacting server performance on very busy systems, SQL Sentry restricts QuickTrace functionality under some circumstances.
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.
Export the QuickTrace by selecting Export Data from the File menu ( 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)
The top pane provides a series of filters and controls for specifying which records to display for the specified interval.
The bottom pane lists the records matching the current filters. The default filter is to display all records.
|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.|
|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.|
|Jump to Dashboard||Opens the Dashboard with the selected QuickTrace event overlaid on each of the charts.|
|Open||View the selected QuickTrace.|
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.
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:
|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: |
|Close||Closes the QuickTrace Viewer.|