5

I'm learning how to read the actual execution plan in SQL Server. I have noticed that SQL Server tends to represent intermediate value used in the physical query plan as e.g. expr1006, expr1007 etc. (i.e. expr followed by a number).

Here is a screenshot. Notice near the bottom, the expressions expr1006, expr1008, expr1009 listed in the section output list.

enter image description here

It it possible to find out what they really represent?

TT.
  • 15,774
  • 6
  • 47
  • 88
Thor
  • 9,638
  • 15
  • 62
  • 137
  • 1
    In addition to Martin Smith's answer below, there is a third party tool called SQL Sentry Plan Explorer (free download) which you can use to analyse your execution plans, there is a tab in it where you can view all the expressions used in the plan. – MJH Apr 15 '18 at 14:02

1 Answers1

8

Right click the compute scalar and choose "properties".

Look in the "Defined Values" section.

You will see something like expr1006 = SomeExpression() if expr1006 is computed there. There may be multiple expressions defined.

enter image description here

Otherwise follow the tree down towards the leaves to find the first place that expr1006 appears and look at the properties of that operator.

For large plans viewing as XML and searching for expr1006 is the quickest way to see where the expression is defined. This is also sometimes required even for small plans as for some constructs the DefinedValue contains a ValueVector rather than a ColumnReference and these don't show up in the SSMS UI. You need to go digging into the plan XML to see that the expressions are output from internal function GetRangeWithMismatchedTypes for example.

The above is usually enough. In some builds of SQL Server it was also possible to actually see the values of these expressions by using the query_trace_column_values extended event. More information about that here.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845