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.
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
. The Products
table seems to be accessed many times, even more than the actual
table. However, the Actual Execution Plan does not indicate this at all. The Products
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.