Finding the right pattern for creating indexes,redesign query structure
Without knowing anything about the data, if we assume your JOIN's are on related data, a UNION isn't what you want. The related data will be presented disjointed on separate result rows.
There are no hard-and-fast rules on index design; the right design is the one that increases performance on your queries without consuming excessive resources. As a rule of thumb, however, you want an index that contains your JOIN, WHERE, and ORDER BY columns. INCLUDE the columns you are SELECTing. But before you create the index, be sure that another index can't do the trick with an added column or two.


Start there, and see how performant the query is. Use SET STATISTICS TIME ON and SET STATISTICS IO ON and run with "Include Actual Execution Plan" enabled while you tinker. I actually like to do this before I create any indexes so I have a baseline to compare my "improved" query with.

Also, be sure that 'optional' tables are LEFT JOIN.