Actual Execution Plan hides some queries!!!

July 30, 2017 Execution Plans 1236 Views


Some queries do not appear in Actual Execution Plans; and are totally hidden. Sometimes, this weird behaviour makes query optimization difficult.

What is hidden?

The Actual Execution Plan is supposed to reveal all the details about the execution of the query, however, it is not always the reality. Scalar user-defined functions are totally invisible in the Actual Execution Plan. To make it even worse, scalar user-defined functions are usually a performance bottleneck, and this bottleneck is totally hidden in the Actual Execution Plan.

Let's see an example:

Consider the query below that uses a scalar user-defined function MinUnitPriceByCategory in the SELECT clause, we will run this query in the Northwind database.

SELECT *, dbo.MinUnitPriceByCategory ( c.CategoryID )
FROM Categories as c

The query references the function MinUnitPriceByCategory which searches the Products table and picks up the minimum UnitPrice of the products of the given CategoryId. Obviously, for every given category, in other words every row in the SELECT query, the Products table is searched for the minimum UnitPrice. The Products table seems to be accessed many times, even more than the actual Categories table. However, the Actual Execution Plan does not indicate this at all. The Products table is completely hidden. Here is the Actual Execution Plan:
The Execution Plan has nothing that indicates the Products table is accessed. There is only one icon that is about the Categories table, and that's all.

Estimated Execution Plan is more fair about Scalar user-defined functions

However, the Estimated Execution Plan is more realistic in this specific case and reveals some details about the scalar user-defined functions used in the query. Below is the Estimated Execution Plan of the same query:

The Estimated Execution Plan shows the plan for the whole query on the top, followed by the plan of the scalar function used in the query.

What to do then?

In summary, merely Actual Execution plan is not enough. There are times that Estimated Execution Plan reveals more information, which also happens to be very important information. Therefore, both Actual and Estimated execution plans should be inspected in parallel.

How do you rate this topic?

Further reading:
What does arrow thickness mean in Execution Plans?
How to read Execution Plans
What is an Execution Plan ?
Blocking vs Non Blocking operators
What are the components of an Execution Plan?