Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
In this article, we are going to see how we can get the query execution plan when using the SQL Server relational database system.
The execution plan allows you to understand the operations done by the database engine when executing a specific SQL query.
SQL execution plan
The steps involved in executing a given SQL query can be visualized in the following diagram.
The first step is to parse the SQL query into an AST (Abstract Syntax Tree). During parsing, the database validates the SQL query syntax and the referenced database resources (e.g., tables, columns). The Parser can preoptimize the query by eliminating parts which are not redundant.
The resulting Query Tree is sent to the Optimizer, which produces a step-by-step list of operations that are needed in order to fetch the desired result set.
The resulting execution plan is sent to the Executor, which runs it and sents the fetched result set back to the database client.
The estimated SQL execution plan
The estimated execution plan is generated by the Optimizer without running the SQL query.
In order to get the estimated execution plan, you need to enable the SHOWPLAN_ALL setting prior to executing the query.
SET SHOWPLAN_ALL ON
Now, when running the following SQL query:
FROM post p
WHERE EXISTS (
FROM post_comment pc
pc.post_id = p.id AND
pc.review = 'Bingo'
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
SQL Server will generate the following estimated execution plan:
For brevity, not all columns have been included in the estimated execution plan above. Nevertheless, you can get a picture of what the plan contains and how it can help you figure out what the database engine does behind the scenes when executing a given SQL query.
After running the query we are interested in getting the estimated execution plan, you need to disable the SHOWPLAN_ALL as, otherwise, the current database session will only generate estimated execution plan instead of executing the provided SQL queries.
SET SHOWPLAN_ALL OFF
SQL Server Management Studio estimated plan
In the SQL Server Management Studio application, you can easily get the estimated execution plan for any SQL query by hitting the CTRL+L key shortcut.
The actual SQL execution plan
The actual SQL execution plan is generated by the Optimizer when running the SQL query. If the database table statistics are accurate, the actual plan should not differ significantly from the estimated one.
SET STATISTICS IO, TIME, PROFILE ON
To get the actual execution plan on SQL Server, you need to enable the STATISTICS IO, TIME, PROFILE settings, as illustrated by the following SQL command:
SET STATISTICS IO, TIME, PROFILE ON
Now, when running the previous query, SQL Server is going to generate the following execution plan:
After running the query we are interested in getting the actual execution plan, you need to disable the STATISTICS IO, TIME, PROFILE ON settings like this:
SET STATISTICS IO, TIME, PROFILE OFF
SQL Server Management Studio actual plan
In the SQL Server Management Studio application, you can easily get the estimated execution plan for any SQL query by hitting the CTRL+M key shortcut.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
If you need to optimize the execution of a given SQL query, you have to inspect the execution plan in order to determine what makes the query run slowly. Without visualizing the query execution plan, you cannot tell what the database does under the hood when running a certain SQL statement.
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).