For an instructor lead, in-depth look at learning SQL click below.
Whether you’re a DBA, a SQL developer or a data analyst, knowing how to analyze and optimize SQL Server query execution plans is one of the best skills to have in your toolkit. Understanding how the SQL Server optimizes your queries and how it makes a plan to retrieve the data you need is key to writing efficient, fast, and high-performing SQL queries.
What is a Query Execution Plan?
A Query Execution Plan explains how the SQL Server’s optimizer would access the data to fulfill the user’s request and meet the conditions specified in the SQL query. Often, there are many ways for SQL Server to get a certain piece of data – Understanding the Query Execution Plan (QEP) can help us determine the most efficient method.
Anatomy of the Execution Plan
|
1 2 3 4 5 6 |
BEGIN TRANSACTION; SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 710; COMMIT TRANSACTION; |
The simplest form of the execution plan includes the following details: operation, object, cost. The operation is the type of action that the optimizer performs, the object is where the optimizer is performing the operation, and the cost is about how much system resource will be consumed by the action – the optimizer’s goal is always to minimize the cost.
Reading Query Execution Plans
Understanding Query Execution Plans can be a little tricky at first because plans are read from right to left, and from top to bottom. Each icon in the plan corresponds to a particular operation, like a table scan or a join. The arrows between icons signify the flow of data from one operation to the next.
Example:
|
1 2 3 4 5 6 |
SELECT OrderDate, OrderQty, ProductID FROM Sales.SalesOrderDetail WHERE ProductID = 710 ORDER BY OrderQty DESC; |
In the above query, SQL Server would first do an ‘index scan’ operation on the SalesOrderDetail table, filtering out all SalesOrderDetail rows where the ProductID does not match 710. It would then perform a ‘sort’ operation to order the results by OrderQty in descending order before finally selecting the OrderDate, OrderQty, and ProductID columns to return as the result set.
By examining the QEPs, you can find out if there are any inefficiencies causing your SQL Server to use unnecessary system resources.
Conclusion
Query execution plans are an excellent tool for both SQL Developers and DBAs. A well-tuned database application will not only run faster but require less resource from your server, leaving more for other applications and processes. With practice, you’ll become proficient in reading and optimizing these Execution Plans.