0

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:

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

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

Thanks!

  • 4
    SentryOne plan explorer does a good job of exposing that stuff. It's free, too. – Erik Darling Jan 22 '17 at 15:51
  • hi. sentryone seems like a great tool. but does it display data that does not exist in the plan? because if it does exist in the plan, then i believe i can find it myself. and if not, how does sentryone know? ill check it out in any case... – Yisroel M. Olewski Jan 24 '17 at 10:59
1

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.

| improve this answer | |
  • hi thanks. i already have the plan, that is not the issue. but in the plan i cannot find what makes up a specific union. (ie union1399). i searched the text in the xml plan but cannot find any such reference – Yisroel M. Olewski Jan 24 '17 at 10:56
  • your making this harder than it really needs to be. That said, i will post an example query that produces the same later today. – clifton_h Jan 24 '17 at 14:01
  • Leave the Pinal statement as is, please. As i said, he has a lot of good material that covers topics like above – clifton_h Jan 27 '17 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.