In a database environment, certain questions related to performance pop up repeatedly: why is my query running slowly? And why is SQL Server not using the index? etc. As a DBA, first action to these will be to look at the execution plan. This blog is about understanding SQL Server Execution Plan.
What is an Execution Plan?
An Execution Plan tells us what’s going on in the background when SQL Server executes a query. It shows how the query optimizer joins the data from the various tables defined in the query, which indexes it uses, if any, how it performs any aggregations or sorting and much more. It also estimates the cost of all of these operations in terms of the relative load placed on the system.
What Happens When a Query is submitted?
When we submit a query to SQL Server, a number of processes on the server work on that query. The purpose of all these processes is to manage the system such that it will read and write the data.
These processes come to action every time a query is submitted. While there are many different actions occurring simultaneously within the SQL Server, we’re here focusing on the processes around queries. The process of meeting the requirements of queries, breaks down into two stages:
- Processes that occur in the relational engine.
- Processes that occur in the storage engine.
Estimated and Actual Execution Plans
There are two types of execution plans.
One is Estimated Execution Plan that represents the output from the optimizer. The operators or steps within the plan are logical steps, because they illustrate the optimizer’s view of the plan and don’t represent what physically occurs when the query runs. Since estimated plans never access data, they are very useful for large and complex queries that have high execution time.
Other is Actual Execution Plan that represents the output from the actual query execution. It shows data representing what actually happened when the query is executed. Actual execution plans are mostly preferred because they show important execution statistics such as the number of rows accessed by a specified operator.
- Generating Estimated Execution Plan
We’ll start by viewing the graphical estimated execution plan that the query optimizer generated. To get the estimated execution plan, select the query and click Display Estimated Execution Plan icon as shown below. We can also achieve this by pressing ctrl + L.
- Generating Actual Execution Plan
To generate actual execution plan, select the query, click Include Actual Execution Plan icon as shown below and then execute the query. We can also enable Include Actual Execution Plan by hitting ctrl + M. Output will include the result of the query with execution plan.
Understanding graphical execution plans
The icons in fig 1 and fig 2.1 represent two different operators. On the left side of the execution plan is the Select operator and on the right side of the plan is Table Scan operator. Table Scan is one of the operators to look for when we try to troubleshoot performance issues. So, it is clear from the figures above that we read the plans from the left to the right. In the example above, the logical order is the definition of the SELECT criteria followed by the Scan operator.
The arrow represents the data passed between the operators, as represented by the icons. The thickness of the arrow reflects the amount of data passed and a thicker arrow means more rows. This is another visual clue as to where performance issues may lie. We can hover with the mouse pointer over the arrow and it will show the number of rows as illustrated in fig 3.
Each of the icons and arrows has a pop-up window called a ToolTip which we can access by hovering our mouse pointer over the icon. Using the query shown in above figures, we pull up the estimated execution plan. Hover over the Select operator and we can see the ToolTip window as shown in fig 4 below.
What does the ToolTip window indicate?
- Cached plan size – How much memory the plan generated by this query will take up in the plan cache.
- Degree of Parallelism – Whether this plan used multiple processors. This plan uses a single processor as shown by the value of 1.
- Estimated Operator Cost – The cost of operation presented as percentage of total batch cost.
- Estimated Subtree Cost – It represents the estimated cost that the optimizer thinks this operator will take.
- Estimated Number of Rows – It is the number of rows produced by the operator.
ToolTip information for next operator in the execution plan, the Table Scan is shown in fig 5.
The logical operators are the result of the optimizer’s calculations for what should happen when the query executes. The physical operators represent what actually occurred.
Estimated I/O Cost and Estimated CPU Cost is the estimated cost of all I/O activity for the operation and the estimated cost of all CPU activity for the operation, respectively.
Execution Plan basically tells us how SQL Server may execute a query or how it executed a query in the past, to help in identifying poorly performing query.
- Working with SQL Server Database on Microsoft Azure (Part 1)
One of the trickiest implementation for any organization is the database. They require dedicated server and someone to manage it. Cloud services such as Microsoft Azure give organizations opportunity to…
- SQL Server – Reporting Services Migration
A while back, I got a task in my project, which was to migrate SQL Server Reporting Services from SQL 2008 to SQL 2012. Now, I had two ways of…
- Column Store Index in SQL Server 2012
This post is about the new feature, i.e., Column Store Index which is available since SQL 2012 version. Microsoft has released column store index to improve the performance by 10x.…
- Oracle Goldengate
Oracle GoldenGate is an Oracle proprietary software for real-time data integration and replication that supports different databases- Oracle, DB2, SQL Server, Ingres, MySQL etc. Even the source and target database…
- HAWQ/HDB and Hadoop with Hive and HBase
Hive: Apache Hive is a data warehouse infrastructure built on top of Hadoop for providing data summarization, query, and analysis. HBase: Apache HBase™ is the Hadoop database, a distributed, scalable, big…
- Teradata Intelligent Memory (TIM)
Overview of Teradata Teradata is a RDBMS (Relational Database Management System). This system is solely based on off-the-shelf (readymade) symmetric multiprocessing (SMP) technology combined with communication networking, connecting SMP systems…