Some queries do not appear in Estimated Execution Plans.

July 30, 2017 Execution Plans 473 Views


Estimated Exection Plan is not 100% clear. There are some queries that are hidden in that.

What is hidden?

In this post, I've been talking about scalar user-defined functions that are hidden in Actual Execution Plan. There also some queries that do not appear in Estimated Execution Plans.
Triggers, unlike scalar user defined functions, are totally invisible in the Estimated Execution Plan.

Let's see an example:

Imagine you have a Customers table and a CustomersLog table. The Customers table has some triggers to log the changes into the CustomersLog table. Below is the trigger body of the insert operation.

ON Customers
     INSERT INTO CustomersLog
        CustomerId ,
        Action ,
        CustomerId ,
        'Add' AS [Action] ,
        GETDATE () AS [DateTime]
     FROM Inserted
With that trigger in mind, let's execute the following query:
        CustomerId ,
        CompanyName ,
        'Some Company Name',
        'Some Name'

The following is the Estimated Execution Plan of the query above:

Apparently, the Estimated Execution Plan has no implication of the CustomersLog table. To put it in simple words, the Estimated Execution Plan totally hides the trigger.

However, things are differet in Actual Execution Plan:

As highlighted in the image in red, the Actual Execution Plan reveals the reference to the CustomersLog table. It is not just the reference, the Actual Execution Plan also reveals a lot of details regarding the IO cost, CPU cost, number of executions, number of rows affected, ...

How do you rate this topic?

Further reading:
What is an Execution Plan ?
Blocking vs Non Blocking operators
How to read Execution Plans
What does arrow thickness mean in Execution Plans?
Some SQL functions are constants