DataWriter

June 1, 2011

Finding an Expression Definition in a Graphic Execution Plan

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 10:18 pm
Tags: ,

Today on Twitter #SqlHelp, someone asked how to find the definition of an expression in a graphic execution plan. I thought I’d pass along my answer in case it helps someone else.

Consider a very simple query, which includes a scalar operator, such as a SUM():

  1. SELECT ProductID
  2.       , TotalQty = sum(sd.OrderQty)
  3.   FROM AdventureWorks2008R2.Sales.SalesOrderDetail  sd
  4. GROUP BY ProductID

It produces a simple graphical execution plan like this:

Expression_01

Examining the Hash Match operator shows an output named “EXPR1002”. It’s a calculated expression, but what’s its definition?

Expression_02

To find a calculated expression’s definition, select the operator and hit F4 to bring up the Properties window.  The definition is in the Defined Properties entry.  Here, it’s the SUM() of the OrderQuantity.

image

In a more complex query the output of one operator may be used by another operator, resulting in expressions operating on expressions, so you may need to check more than one step to find the fields involved.

Advertisements

1 Comment »

  1. Interesting.

    I don’t suppose that this would provide any information for FETCH API_CURSOR statements? I’m on vacation so I can’t check myself at the moment.

    Comment by Fred — June 3, 2011 @ 7:58 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: