Profiling SQL queries - ANTS Performance Profiler

You can use ANTS Performance Profiler 6.3 Professional to profile database queries sent by an application to a Microsoft SQL server instance.

You might want to profile SQL queries if performance timings have revealed that a line of code involving a database query is particularly slow.

Note that:

• profiling SQL queries is not possible with SQL Server Express, because SQL Server Express does not expose performance counters, and so ANTS Performance Profiler cannot obtain results.
• the SQL Server instance must be on the same computer as the one that you are profiling.
• you can only profile SQL queries on Windows Vista or later, or Microsoft Server 2008.

Setting up SQL profiling

Before profiling SQL, we recommend that you check for performance issues in your code (and any third-party code). Profile SQL when you have identified a slow line of code that involves a SQL query.

Set up the profiler in the same way that you would configure the settings for performance profiling.

Ensure that SQL and file I/O: is set to Record SQL and file I/O performance.

Viewing SQL results while profiling

1. Drag to select the portion of the timeline that you are interested in.
2. Click SQL Server.

   

3. Note that the timeline is not automatically updated while profiling SQL queries. To display queries performed since you switched to SQL Server view, click Update.

   

   The timeline and the Query panel update to show the latest data.

Viewing SQL results after profiling

If profiling is not currently in progress, click SQL Server.

Tips

Long-length queries

If the SQL query is a multi-line query, or is just very long, it may be truncated in the Query list. To display the full query, select it. The query is shown in the SQL Source Code panel, which can be scrolled.

Note that the Hit Count, Avg Time and Time columns are empty in the SQL Source Code panel. This is a known issue. See the Hit Count and Time columns in the Query list instead.

Linking back to your code

To find which of your code's methods ran a particular SQL query:

1. Select the time when the query ran on the timeline. Include some time just before the SQL query ran because the method which runs the query will be called before the SQL server's load increases.
2. Switch back to Performance view.

   

3. Under Tree View Display Options, select Top-down (methods with source).
4. In the tree view, the method which ran the SQL query should be found near the highlighted method.

   

   Use the line-level timings to look for code that could be optimized.