Devart Blog

SQL Server Execution Plans

Posted by on April 12th, 2016

One of the best methods to analyze the query performance is to explore the query execution plan. A query plan or query execution plan is an ordered set of steps utilized to access data in a SQL relational database management system. In this article, we will discuss the basics of the Query Execution Plan feature available in SQL Server.

Estimated and Actual Execution Plans in SQL Server

Execution plan shows how SQL Server is going to execute a query, it may also show the query execution result. This information is extremely important for troubleshooting query performance issues.

Imagine the situation when you have a good car and you want to improve it by introducing modifications of the performance or appearance. In other words, you need a car tuning. You contact a special car tuning service, an engineer looks at your car, looks at what exists, and asks you questions about exactly what you want. They then gives you an estimate of the work – approximately how long they thinks it will take the crew, what supplies will be needed, and the cost of those supplies.

SQL Server provides you a similar option. When you create a query, before committing to it, you can request an estimated execution plan.

The estimated query execution plan is intended to show what SQL Server would most likely do if it were to execute the query.

SQL Server estimates a number of rows that may be returned from tables. It also decides what operators to use for data retrieving. It chooses between Index Scan that retrieves all the rows from the table and Index Seek that retrieves selective rows from the table. It takes the decision on what to use to join tables together: nested loops, merge joins, or hash joins. Eventually you get the detailed plan of actions SQL Server is going to implement while executing an SQL query.

You can get the estimated plan for a specific query, stored procedure, or function. The plan comes in three different formats: text, XML, and graphical. If you need the text or XML formats, use the following commands: SET SHOWPLAN_TEXT ON and SET SHOWPLAN_XML ON respectively.

You can get the actual query execution plan once the query is executed. This plan provides the exact information about how many reads SQL Server performed, how many rows were read and what joins were used.

There are some cases when SQL Server cannot create an estimated execution plan. For example, if you are executing a query that has parameters and the literal values are not passed in. In this case, SQL Server is unable to create the estimated plan. If the query references a temporary table that is not declared, the execution plan also cannot be created.

The estimated execution plan can contain elements that may be absent on the actual plan. For instance, the estimated plan shows a scalar-value function call, the actual plan doesn’t.

Summarizing this, execution plan is an accurate guide to what SQL Server is going to do.

Tuning Queries

We can use the execution plan to play with tuning queries in order to improve query performance. dbForge Studio for SQL Server provides the Query Profiler tool to debug, troubleshoot, monitor, and measure your application’s SQL statements and stored procedures.  For the demo purposes, I will arm with this tool and AdventureWorks2014.

Rewrite a Query to Improve Performance

Let’s examine a poor query by exploring the estimated query execution plan.

Display_Estimated_Query_Plan

 

According to the plan, SQL Server will first perform a clustered index scan to query the Production.Product table. This query was identified as a poorly performing query. We need to modify it.

We can get rid of UNION ALL and the UPPER function as we querying the case-insensetive database.

As you can see, the second plan involves a single clustered index scan on the Production Product table. Thus, we utilize less resources while performing only one scan.

tuned_query_comparing_plans

 

The additional benefit is that dbForge Studio for SQL Server allows you to compare several execution plans to clearly see the difference.

Summary

Execution plan is the essential tool while developing SQL code. It will help you to understand why a specific query is slow or why one query runs faster than other. Both the graphical plans and the XML plans will give you all the data you need, and its up to you to decide what to use and when.

Leave a Reply