The main problem with the estimated execution plan shown is the Top operator above the Clustered Index Scan of Table1. The scan has a residual predicate:
The optimizer tries to estimate how many rows it will need to read from the scan before one passes that test. The logic ...
I believe what you're running into is similar to what I've recently experienced a few times as mentioned in my DBA.StackExchange question. I'm by no means an expert on what's happening but will try to summarize my understanding.
When you join two tables together, the SQL Engine utilizes statistics on your data (based on the fields in your predicates) to ...
Discovered three ways to fix this performance bottleneck, and query completes within 1 sec after:
simply replace [c].cID by [t1].CompanyID in the select clause, no need to create indexes
add nonclustered index on [t1].CompanyID on Table1 - as Erik suggested. And no need to replace columns in the select clause
Changing your join to ...
I think you're making this situation more complex than needed because one of your assumptions is incorrect:
I can't add the standalone node to the WSFC because the storage for
the FCI would not be available in a DR situation.
You can still add the standalone instance to the WSFC, but you simply do not configure it as a possible owner of any of the FCI ...