dotTrace
 
2021.3
Shortcuts: Visual Studio
Get dotTrace
You are viewing the documentation for an earlier version of dotTrace.
  1. Analyze Timeline Profiling Results
  2. Filters
  3. Events
  4. SQL Queries

SQL Queries

Last modified: 16 November 2021

warning

Known limitations:

  • .NET Core does not support SQL ETW events: The SQL Queries filter does not show any data for .NET Core applications.

  • Windows Vista and Windows Server 2008 do not support SQL ETW events: The SQL Queries filter does not show any data on these operating systems.

The SQL Queries event indicates time intervals where your application communicates with an SQL server. This includes time required for opening and closing connections, running commands, performing queries, and other SQL activities. Note that all measurements are performed on the client side - dotTrace measures the time between a client sending a request and it receiving a response.

dotTrace collects SQL data from the following data providers:

  • SQLClient

  • OLE DB*

  • Entity Framework

  • ODBC*

note

* dotTrace collects limited data about management commands.

If you use OLE DB, the following commands will not be shown in the SQL Queries: Commands sub-filter:

  • [Open Connection]

  • [Close Connection]

For ODBC, these are:

  • [Begin Transaction]

  • [Commit Transaction]

  • [Rollback Transaction]

In addition, ODBC and OLE DB do not provide connection data, therefore, in both cases the SQL Queries: Connection String sub-filter will show [Unknown] instead of the connection string.

Select the SQL Queries event to analyze how your app accesses data. For example, a web application may run slow due to a bottleneck in SQL server communication. In such a case, you can use the SQL Queries filter to determine the slowest queries and methods that run these queries.

When the SQL Queries event is selected, the Filters window contains sub-filters that allow you to refine the resulting filter: SQL Queries: Command and SQL Queries: Connection String.

SQL Queries: Command

The SQL Queries: Command sub-filter shows distribution of SQL operations time between SQL commands: queries, opening and closing connection commands, and so on. Use this sub-filter to determine how much time does a particular command take.

Sql client command 1

Pos 1 Command name or query content.

Pos 2 Total time spent on a SQL command summed up for all selected threads.

Pos 3 The percentage of time spent on a SQL command relative to the total selected time.

To apply the SQL Queries: Command filter:

  • Select the desired SQL command in the filter.

After you select a command, other filters will show data only for the time intervals where the selected command was performed.

Sql client command 2

SQL Queries window

If a command content is too long to fit the Filters window, it is truncated by width. To view the entire content of such a command, open the SQL Queries window. The window shows the same data as the SQL Queries: Command filter but, in addition, it allows finding commands by content and copying the content to clipboard.

Sql queries

To open the SQL Queries window

  • Do one of the following:

    • Use the menu View | SQL Queries.

    • Click the Themed icon datails table open screen gray button in the SQL Queries: Command filter.

As well as the SQL Queries: Command filter, the SQL Queries window also can work as a filter by an SQL command.

To apply the filter by a particular command (query)

  • Do one of the following:

    • Select the desired SQL command.

    • Double-click the desired SQL command.

    • Select the desired SQL command and press Enter

    • In the context menu of the desired SQL command, choose Select And Clear Others.

To apply the filter by a number of commands (queries)

  • Do one of the following:

    • Select the desired SQL command and press Space.

    • In the context menu of the desired SQL command, choose Select As Filter.

    This will add the SQL command to selection.

To copy command content to the clipboard

  • In the context menu of the desired SQL query, choose Copy To Clipboard.

To find a particular SQL command (query) by content

  • In the search field located on top of the SQL Queries window, start typing command (query) content you are looking for. As you type, the list of SQL commands narrows down, suggesting the ones that match the entered string.

For convenience, large commands consisting of multiple rows are truncated.

To see the full content of a truncated command

  • Do one of the following:

    • Click the ... button in the end of the command.

    • In the context menu of the desired SQL command, choose Show Full Query.

SQL Queries: Connection String

The SQL Queries: Connection String sub-filter shows distribution of SQL Queries time between SQL commands with particular connection parameters. This filter could be useful, for example, if your application communicates with more than one SQL server.

Sql client connection string 1

Pos 1 Connection string.

Pos 2 Total time of all SQL commands with these connection parameters.

Pos 3 The percentage of time spent running SQL commands with these connection parameters relative to the total selected time.

To apply the SQL Queries: Connection String filter

  • Select the desired string in the filter.

After you select a connection string, other filters will show data only for the time intervals where threads run SQL commands with corresponding connection parameters.

Sql client connection string 2