SQL Query Optimization with Query Profiler

Query Profiler generates profiling results that can help you analyze and maximize query performance in SQL Server databases. The tool allows you to collect detailed statistics about executed queries, reveal and force slow queries and troubleshoot performance issues.

dbForge Studio for SQL Server delivers query profiler — a visual tool for tuning query performance that includes the functionality totally equivalent to MS SSMS. Also, dbForge Query Profiler offers additional features to make developers and DBA's work easier and more efficient.

View the EXPLAIN plan and profiling results, and monitor session statistics
Save time on detecting bottlenecks in your queries
View time delays at different stages of executing a query
Compare differences in query profiling results visually
Get a clear and automated way to profile your database queries
Fetch SQL query data faster
Trace SQL queries and diagnose performance issues with slow queries

Execution plan diagram

In order to visualize and tune query execution plan by pinpointing the slow-executing nodes, use the execution plan diagram.

It is a convenient graphical tool that helps you see the efficency of the queries at your fingertips. Each query in the batch being analyzed is displayed with the cost of each query as a percentage of the total batch cost.

To see the information about a particular node, simply point to it.

Visualize queries with SQL Query Profiler Plan Diagram

Wait statistics

With the Wait Stats tab of Query Profiler, you can easily detect potential bottlenecks in your query. The tab is located right next to the Query profiling results tab.

The tab displays a list of events with timestamps and waits associated with them. To be more precise, the tab displays the name of the event, the exact time when it took place along with how long it took to be completed, type of the wait, signal duration, transaction ID, and more.

View waiting statistics in Query Profiler

Plan tree

Plan tree displays EXPLAIN results returned by the executed query. You will see that the statements and operations are hierarchically arranged in a tree view. Along with the operations, you will see the corresponding database objects, estimated cost, rows, and executors of a given statement.

Use the Plan Tree tab to get information on how SQL Server executes a SELECT statement. It indicates positions where adding an index to a table, optimizing joins, etc., could increase the performance of the query.

Visualize queries with SQL Query Profiler Plan Tree

Top operations list

The top operations list allows you to quickly view the most time-consuming operations. Having all the most resource- and time-consuming operations in front of you will help understand which parts of the SQL code need your attention and optimize them accordingly.

The tab is quite similar to the plan tree. However, the entries are not arranged hierarchically. By default, Query Profiler sorts the entries by the highest estimated cost. You can sort the list by CPU, I/O, and other costs to get a visual picture for your convenience.

View the most time-consuming operations in top operations list

Table I/O

Query Profiler also includes the Table I/O tab that represents the input-output statistics for all tables involved in the execution plan.

To generate a table, simply click on the corresponding tab in Query Profiler and all the required information will be displayed in a convenient format.

Use the Table I/O tab to see the detailed information regarding the scan count, logical and physical reads, and other interactions with the tables in a database.

Compare query differences in the profiling results grid

Compare profiling results

The key point in SQL queries optimization is seeing the differences in profiling results after you change your query.

By selecting the profiling results for multiple query executions you will get the differences highlighted in the grid. That allows you to compare general results of a query execution as well as session statistics for each query execution.

The query profiling results that came out better will be highlighted in green. On the contrary, any declines in performance will be marked red.

View query differences on the Session Statistics tab

Conclusion

Query Profiler is a query optimization tool that helps you profile and improve SQL query performance, as well as track the differences in profiling results when executing the query several times. With the tool, you can detect slow running queries, examine the workload, and analyze bottlenecks to resolve performance issues in SQL databases. Profiling can also be used to determine the unexpected behavior of queries.