UNION ALL normally seems very efficient, but in this case while the two individual queries take two or three seconds each, the UNION ALL takes over 4 minutes.

Looking at the plans (and if I am reading them right) - the optimizer seems to be taking the same approach for the individual queries and all the extra time seems to be in the Work Table DT (Derived Table) section of the 1st (left hand) query:

I was going to upload the plans but I don't see the attach file (paper clip?) button any more :)

v16.0.0.2076

UPDATE

Looking at this further - is this all about the time taken to return the first row? The single queries both have a very fast First Row Run Time at the SELECT level, where as the UNION is the very high. Does the UNION have to fetch all the rows before returning anything?

Further UPDATE

The issue is the time to return all rows. The first query, run separately, returns its first row almost immediately, as does the second. However it takes several minutes to return ALL the rows of the first query (it shouldn't, but that's a different problem). The UNION ALL merely reflects this.

If OPTION (optimization_goal='first-row') is added the end of the UNION statement then the first rows are returned almost immediately. So the only difference really is that the optimizer is doing things differently depending on whether the first query is part of a UNION or not - the actual time taken to return all the rows is much the same.

NB The database has the default setting for "optimization_goal" of "all-rows".