Ayman Elnory

SQL Server Query Execution Plans for beginners – Types and Options

January 4, 2018 by

When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. As a matter of fact, though, you have to look at execution plans as weather forecasts.

In this article, I will be discussing various aspects of executions plans, types and common options.

Query optimization

The fundamentals of query optimization are based on the fact that SQL Server has always been a cost-based optimizer.

Cost-based optimization involves generating multiple execution plans and selecting the lowest cost execution plans to fire and finally execute a query

The CPU, IO, and memory are some of the parameters SQL Server uses in recognizing the cost and finally generating the execution plan.

What you will write against SQL Server is often totally different from what SQL Server actually executes. Hence, that is in the concept of logical query operation when compared to the physical operation which SQL Server uses.

For example, when we are talking about a logical INNER JOIN which actually gets translated physically on the execution by SQL Server as a nested loop JOIN.

Let us execute the following query to view the concept of logical query operation. You can check the option of include actual execution plan or CTRL+M

The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans


Here you can see that the physical operation involves nested loops while the logical operation includes an inner join.

Our physical operator in this particular case is the nested loop which is the actual execution mode for SQL Server, whereas the logical operation here is the inner join which is defined inside our select statement.

Finding an optimal plan

The way SQL Server ultimately determines an optimal plan is by finding the balance between what plan it wants to select and the maximizing the effectiveness of that the particular plan before the actual execution happens.

So there is a give-and-take between generating the best plan and completing the task, in a finite amount of time, before SQL Server has to step out from indefinitely going about optimizing the query plan.

SQL Server does this cost optimization and it is quite efficient in actually bringing about the best plan as fast as possible.

Let us check whether SQL Server chooses the optimal execution plan for the previous query or not. What we are going to do is to go ahead and explicitly state a hash join and compare execution plans.


This shows that giving an explicit hint in this particular case seems to be costly.

With applying the 80-20 rule, it is advisable to leave SQL Server (cost-based optimizer) to choose which is the best and most optimal plan is.

Estimated execution plans

The estimated execution plan, for all practical purposes, uses statistics and many other parameters based on what is available on this server and generates the plan.

This is an estimated plan and not specifically the plan that has been generated by SQL Server.

This is synonymous to the first analogy that we had given about the weather forecast.

Next we’ll review various display options for estimated execution plans

SHOWPLAN_TEXT

This option is similar to the estimated graphical execution plan, as it gives you the text of the execution plan and gives you information about what the execution plan is. It does not give you any of the frills which are normally associated with the execution plan.


SHOWPLAN_ALL

This option is similar to the estimated graphical execution plan, but has some of the additional features attached to it including the type of physical operator and type of logical operator


This again is the estimated execution plan; remember you did not see a result set where the actual query has been executed.

SHOWPLAN_XML


You can see that the query plan comes in an XML format. You can look at execution plan as per estimation.

Actual execution plan

We said that the estimated execution plan is like the weather forecast, so the actual execution plan is where you can realize whether it actually rained or not.

It shows what SQL Server actually does to get the result of your query.

Here we’ll review the display options for actual execution plans

STATISTICS TIME

This option gives you the amount of time that has been spent as far as SQL Server is executing this particular query. It also provides you interesting insights into some of the information.

In this particular case, the query gets executed with 163 rows and you can also see how much time of CPU that this particular query has taken and what is the actual time with respect to the execution process.

So here it states that the execution time is close to about 196 milliseconds.

If you want to know granular to what exactly the query is executing and how much of time and resources it is taking, this is one of the most ancient ways but still effective to use on any day.

STATISTICS IO

This option shows the IO characteristics. You can see that this particular query in this particular table has gone ahead and done these mini scans and these are the logical reads.

If you have scans and other mechanism hardware you will also see something which is interesting. You might also see physical reads, read-ahead reads and large object data including the reads that are associated with it.

When you have a large number of reads of a complex query, you might use these methods to actually look at which table is sending out a large number of reads and hence it is getting near the physical IO subsystem.

A physical read in this particular case shows zero because it is not getting cached. And if this value is very high that means a lot of physical IO has been happening to bring the data from the IO subsystem or your hard disk to the memory or the buffer pool.

The set statistics time on and set statistics IO on are great set operations that you can actually use to see how SQL Server goes about executing.

I have seen DBAs come back to me and say, “I can use profiler also as a mechanism to actually go ahead and look at these values”. But when you are analyzing a simple query or optimizing a simple operation, it is always handy to have these simple tools which work even from SQL Server 2000 days and these are very handy tools that give you information and insights into the query that you are playing with.

STATISTICS PROFILE

This is like SHOWPLAN_TEXT for estimated Execution plans but it retrieves the text of the actual execution plan which means that the query has already been executed.


STATISTICS XML

Also likeSHOWPLAN_XML, this option shows the actual execution plan in XML format.


Summary

Execution plans are a very important indicator for optimizing query performance. Every DBA needs to know all the details about the execution plan. I tried to be simple in discussing the very basic details for beginners. I hope this article has been informative for you.

Next article in this series:

References

See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan


Ayman Elnory

Ayman Elnory

I am Microsoft® Certified Solutions Expert: Data Management Analytics Plus Microsoft® Certified Solutions Expert Data Platform (MCSE). For more information check it here.

Living in Egypt, have worked as Microsoft Senior SQL Server Database Administrator for more than 4 years.

As a DBA, I design, install, maintain and upgrade all databases (production and non-production environments), I have practical knowledge of T-SQL performance, HW performance issues, SQL Server replication, clustering solutions, and database designs for different kinds of systems. I worked on all SQL Server versions (2008, 2008R2, 2012, 2014 and 2016).

I love my job as the database is the most valuable thing in every place in the world now. That's why I won't stop learning. In my spare time, I like to read, speak, learn new things and write blogs and articles.

View all posts by Ayman Elnory
Ayman Elnory
Query analysis

About Ayman Elnory

I am Microsoft® Certified Solutions Expert: Data Management Analytics Plus Microsoft® Certified Solutions Expert Data Platform (MCSE). For more information check it here. Living in Egypt, have worked as Microsoft Senior SQL Server Database Administrator for more than 4 years. As a DBA, I design, install, maintain and upgrade all databases (production and non-production environments), I have practical knowledge of T-SQL performance, HW performance issues, SQL Server replication, clustering solutions, and database designs for different kinds of systems. I worked on all SQL Server versions (2008, 2008R2, 2012, 2014 and 2016). I love my job as the database is the most valuable thing in every place in the world now. That's why I won't stop learning. In my spare time, I like to read, speak, learn new things and write blogs and articles. View all posts by Ayman Elnory

3,301 Views