T-SQL Execution Plan showing undefined expression?

user_19560477 0
Viewed 80

I want to start building a tool that more or less shows you the data-lineage of a query using parsing of the execution plan - so that you get information of the form:

Column A of Table XY was computed by taking Column B of Table XZ and adding Column C of Table PL

You get the idea :)

Now, when I tried some queries and looking at the corresponding execution plans, I ran into the issue that there was a random expression present without any definition as to how it is computed.

It appeared in a nested Loop OuterReferences Section, I queried just one table and seemingly performed an index scan followed by a key lookup. When "joining" those 2, the index scan and the key lookup, the query plan XML just showed:

ColumnReference = Column="Expr1020"

I tried searching the XML-File for another occurrence of Expr1020, but there were none.

Now, my question is: can anybody explain why this happens or what exactly happens in the query plan?

I figured every expression used should have a definition that is in some way based on the columns used, but this one is never referenced again :/

sql-execution-plan expression tsql sql-server
1 Answers

This is probably for nested loops prefetch.

See this article for more details

The output also shows that the mystery node uses an expression labelled [Expr1004] with a type of binary(24). This expression is shown in regular showplan output as an outer reference (correlated parameter) of the nested loops join: ... No definition is provided for this expression, and no other explicit reference is made to it anywhere else in the plan

...

The prefetch operator can issue multiple asynchronous reads based on outer-input values. It uses a small memory buffer to keep track, and to save index leaf page information in particular.

A reference to this information was seen earlier as the outer reference binary (24) expression label Expr1004. My understanding is that the leaf page information is used on the inner side of the join in hints to the storage engine to prevent duplicated effort by the normal read-ahead mechanisms. It may also be used for other purposes.

The XML execution plan doesn't show all details from the actual compiled plan. The actual compiled plan can contain additional properties and even entire operator nodes that the XML plan does not display.

user_732260
Related