Understanding how SQL queries impact app performance, with dotTrace 6.1

Application performance depends on a huge variety of factors: code design, web server performance, possible network lags, and so on. Effective execution of SQL queries is an additional concern. Naturally, you want your profiler to cover as many of these performance aspects as possible.

In dotTrace 6.1 we introduced the long-awaited ‘SQL queries profiling’ feature. Now you can get exact data on how long a particular query took to execute, as well as what method ran the query.

Important notes:

  • SQL queries can be profiled only in the Timeline profiling mode.

  • The feature is available not only when profiling standalone applications, but also when you attach dotTrace to an already running process.

  • The following data providers are supported: SQLClient, OLE DB, Entity Framework, and ODBC.

  • As dotTrace uses ETW for collecting SQL events, profiling overhead is minimal. SQL query time is measured very accurately.

Now, let’s take a look at how it works.

How This Works

The only change you’ll notice in the UI is the new SQL Client filter in Interval Filters. Your workflow in Timeline Viewer is still straightforward: after profiling your app, you can start by looking at the Interval Filters.

Interval Filters

In our example, the app works with SQL for 497 ms. Let’s investigate how.

SQL Client Subfilters

Once the SQL Client filter is on, two more sub-filters appear: SQL Client: Connection String and SQL Client: Command. The first one is helpful in case your app uses several SQL connections. The second one shows how SQL Client time is distributed among particular SQL queries. In other words, it shows how long a particular query took to execute.

As query contents may be long, investigating them in the small Filters window could be problematic. That’s why we added a separate SQL Queries window which formats queries for a more convenient view:

SQL Queries Window

After you find a problematic query, the next step is to determine its origin, i.e. the method that called it. The SQL Queries window (like all other windows in Timeline Viewer) can work as a filter. Double-click a particular query, and all other filters (including Call Stack) will show data only for the time interval when this query executed:

Top Methods

Top Methods will show us the method that is responsible for running the query. Once you select the method in Top Methods, the Source View will show its code:

Source View

Thanks for reading! We hope you’ll find this new feature useful. Do try it on your own by downloading the latest dotTrace 6.1.

 

This entry was posted in How-To's and tagged , . Bookmark the permalink.

7 Responses to Understanding how SQL queries impact app performance, with dotTrace 6.1

  1. Pingback: Dew Drop – April 17, 2015 (#1995) | Morning Dew

  2. It would be nice if dotTrace could automatically inject SET STATISTICS XML ON|OFF as appropriate and capture the execution plan where possible.

    https://technet.microsoft.com/en-us/library/ms176107(v=sql.105).aspx

    Getting the execution plan goes hand-in-hand with identifying why a particular query is slow.

    Obviously this could only be for SQL Server and there might be some limitations (I think it *could* affect the behaviour of SqlDataReader). But I’d like to be able to opt-in to this if I understand the limitations.

  3. Marko Markovic says:

    Will a NHibernate data provider be supported as well, any time soon?

  4. Corey Weathers says:

    Does SQL profiling work during remote profiling sessions? I am able to see SQL query data if I run the full dotTrace application on the web server. But when I use the remote profiler I don’t see any of that data.

Leave a Reply

Your email address will not be published. Required fields are marked *