Analyze query plan

I am currently working on a couple of SQL related questions as part of an intake for a job I am applying for. I was able to answer all questions but there is one I am having trouble with. It relates to SQL query plans, something I am not too familiar with.

The question simply reads:

The query plan in the picture below is not performing well. What can you do to improve it?

Any help or tips will be appreciated. I have been looking into Table Spools and Nested Loops but I have trouble applying best practices to this case.

The plan has 3 full tables scans. Typically you'd create an index(es) to allow an index search to be used to avoid a table scan. OR, better if applicable, cluster the other tables so that a table scan is not needed.

For specifically what index(es) to build, we'd have to see the actual query and the table definitions, including all existing indexes (if any). If you can post the query plan in xml / text-containing-xml format, that would also be extremely helpful. The plan will show what columns SQL is searching by and/or matching by.

Hello Scott,

Thanks for the answer. I realize that without an actual query or table definitions that it's a hard puzzle to solve. I am afraid all they gave me was the image posted above.

Nevertheless, I am sure I can use your answer and some google searches to come up with something acceptable :slight_smile:

It's impossible to tune a query just from that image. You'd need at least the actual plan details.