When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. A matter of fact, though, we have to look at execution plans as weather forecasts.
In this article, we will be discussing various aspects of execution plans, types and common options.
In this article, we will be discussing various aspects of execution 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.
The CPU, IO and memory are some of the parameters SQL Server uses in recognizing the cost and finally generating the execution plan.
What we will write against SQL Server is often totally different from what SQL Server actually executes. Hence, that is in the concept of logical query operations when compared to the physical operation with 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. We can check the option of include actual execution plan or CTRL + M
Here you can see that the physical operation involves nested loops while the logical operations 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 specially 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 us the text of the execution plan and gives us information about what the execution plan is. It does not give any of the frills which are normally associated with the execution plan.
SHOWPLAN_ALL
This option is similar to the estimated graphic execution plan, but has some of the additional features attached to it including the type of physical operator and type of logic operator.
This again is the estimated execution plan; remember we did not see a result set where the actual query has been executed.
SHOWPLAN_XML
We can see that the query plan comes in a XML format. We 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,
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 us interesting insights into some of the information.
In this particular case, the query gets executed with 163 rows and we 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 we 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.
So here it states that the execution time is close to about 196 milliseconds.
If we 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. We 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 we have scans and other mechanism hardware we will also see something which is interesting. We might also see physical reads, read-ahead reads and large object data including the reads that are associated with it.
When we have a large number of reads of a complex query, we 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 means lot of physical IO has been happening to bring the data from IO subsystem or our hard disk to the memory or the buffer pool.
The set statistics time on and set statistics IO on are great set operations that we can actually use to see how SQL Server goes about executing.
We can also use profiler as a mechanism to actually go ahead and look at these values. But when we are analyzing a simple query or optimizing a simple operation, it is always handy to have these simple tools which work even from SQL 2000 days and these are very handy tools that give you information and insights into the query that we are playing with.
A physical read in this particular case shows zero because it is not getting cached. And if this value is very high means lot of physical IO has been happening to bring the data from IO subsystem or our hard disk to the memory or the buffer pool.
The set statistics time on and set statistics IO on are great set operations that we can actually use to see how SQL Server goes about executing.
We can also use profiler as a mechanism to actually go ahead and look at these values. But when we are analyzing a simple query or optimizing a simple operation, it is always handy to have these simple tools which work even from SQL 2000 days and these are very handy tools that give you information and insights into the query that we 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 like SHOWPLAN_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.
