get table and field names from union in execution plan I'm trying to find performance issues on query which is based on other queries etc.

I found that I have a union query which is slow, but I have no idea which union query/column it is. 

All I get in the execution plan are nodes like:

 &lt;SeekPredicates&gt;
                                              &lt;SeekPredicateNew&gt;
                                                &lt;SeekKeys&gt;
                                                  &lt;Prefix ScanType="EQ"&gt;
                                                    &lt;RangeColumns&gt;
                                                      &lt;ColumnReference Database="[mydb]" Schema="[dbo]" Table="[Sales]" Column="SaleID" /&gt;
                                                    &lt;/RangeColumns&gt;
                                                    &lt;RangeExpressions&gt;
                                                      &lt;ScalarOperator ScalarString="[Union1399]"&gt;
                                                        &lt;Identifier&gt;
                                                          &lt;ColumnReference Column="Union1399" /&gt;
                                                        &lt;/Identifier&gt;
                                                      &lt;/ScalarOperator&gt;
                                                    &lt;/RangeExpressions&gt;
                                                  &lt;/Prefix&gt;
                                                &lt;/SeekKeys&gt;
                                              &lt;/SeekPredicateNew&gt;
                                            &lt;/SeekPredicates&gt;


How can I know which fields/tables make up this "union1399"? So can index it or remove it or otherwise optimize the query.

Thanks!
The answer lies in the system table/views.


  Always refer to the official documentation from MSDN first, and
  search Dave Pinal second, as you will likely find these types of
  questions answered. ~ Helpful Hint #3 ;)



The system table you want is through sys.dm_exec_query_plan


This function stores the execution plans of the caches plans

And you can acquire the needed sql_plan varbinary value from one of three places:

sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests


An example is like the following:

SELECT qt.text, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE 'SELECT %'


I left the predicate in this example, but you can find different methods to achieve the same goal.

Anyways, the XML column query_plan will have all of the tables, columns, and even the values of any variables available in that plan. If you click on it, you get shown the visual query plan, but copy and pasting the field will have the actual XML plan used in the query.

Cheers.