How to profile SQL queries
You can use the Query Profiler tool to debug, troubleshoot, monitor, and measure your application’s SQL statements and stored procedures. If your application has a performance problem that you think might be caused by a particularly long-running query, you can analyze query durations.
In this topic, we will review how to profile a simple query. As an example we will use the AdventureWorks2022 test database from Microsoft.
Profiling an SQL statement
We are going to select all persons from the Person table where the first name is ‘Robin’.
1. On the Start page, click Query Profiler. A new SQL document window opens.
2. In the text editor, type the following script:
SELECT
*
FROM AdventureWorks2022.Person.Person
WHERE FirstName = 'Robin'
3. Click Execute. The Plan Diagram window opens.
Note
The select icon contains the exclamation mark icon that warns about a shortcoming. A tooltip appears automatically, when you pause the mouse pointer over the Select icon. The bottom part of the tooltip contains the warning message, that tells us that the index is missing.
To add the index:
1. In Database Explorer, double-click the Person table to expand it.
2. Right-click the Index folder, point to New Index, and then click Non-Clustered Index. The Indexes tab of the Table editor opens.
3. On the General tab of the Index Properties grid, navigate to Index columns.
4. In the Name column of Index key columns, select the FirstName column from the drop-down list box.
5. Click Update Database.
Now, we can get back to the Query Profiler and click Get New Results.
Each time you get profiling results for the executed query, they appear in the tree view as a new node with time and date of query execution. When editing your query, you want to know whether your changes reduce query execution time or not. Query Profiler can quickly compare the profiling results. To compare results, hold the CTRL key and select both plan diagrams.
Note
When you save the query file after getting the query profiling results, the latter will be automatically stored in the *.design file.
Download dbForge Studio for SQL Server and try it absolutely free for 30 days!
Want to Find out More?
Overview
All features
Request a demo
