Worked example: Profiling SQL queries - ANTS Performance Profiler

This walkthrough demonstrates how you can profile the SQL queries generated by your application.

Note: SQL Profiling is only available with Windows Vista or later. You must profile a locally-hosted SQL server. It is not possible to use SQL profiling with Microsoft SQL Server Express editions.

Before you start

To follow this walkthrough, you will need to:

install the Customers database table on your SQL server
A copy of the Customers database table is provided in %ProgramFiles%\Red Gate\ANTS Performance Profiler 7\Tutorials\CS\QueryBee\Customers.zip

Ensure that no indexes or primary keys are set on the table.
start QueryBee.exe
A copy of QueryBee.exe is provided in %ProgramFiles%\Red Gate\ANTS Performance Profiler 7\Tutorials\CS\QueryBee\QueryBee.exe

Procedure

To profile QueryBee while it sends SQL queries to the server:

Start ANTS Performance Profiler.
Select .NET executable
Enter the path to QueryBee.exe
Ensure that Record SQL and file I/O performance is enabled.
Click Start Profiling to start QueryBee. Select the test database and click Connect.
Run the query SELECT * FROM Customers to ensure that all records are read into memory, and that further queries will not include file I/O overheads.
Run the query SELECT * FROM Customers WHERE Country LIKE 'Canada'
The following query is executed, using the equals operator instead:
SELECT * FROM Customers WHERE Country='Canada'
To test updated information, run the following query:
UPDATE Customers SET Email='a@example.com' WHERE ID1=100
To show that a primary key will make this query quicker, a primary key is applied to the ID1 field in SQL Server Management Studio:
Run the following query in QueryBee:
UPDATE Customers SET Email='b@example.com' WHERE ID1=100
In the ANTS Performance Profiler window, click Stop Profiling
On the Performance Analysis menu, set ANTS Performance Profiler to SQL Server profiling mode:

ANTS Performance Profiler displays the time taken for each query and the number of rows affected or returned. The LIKE operator is slower than the = operator for text comparison, and the update query was much quicker with a primary key than without a key.

AP_SQL_results

The examples in this worked example have been kept deliberately simple.

A more complex use case for SQL profiling that you may consider is when complex code generates an SQL query at runtime. In such cases, it might be difficult to check exactly what query has been run, but profiling an application with ANTS Performance Profiler allows you to see immediately how your application has interacted with the SQL server.

Was this article helpful?

Search support