Please help with these merge joins
SQL – 2016 — stats up to date and indexes rebuilt prior to running
The Clustered Index Scan on *[Big_Table_Staging].[PK_Big_Table_Staging]* has this warning and has the highest estimated cost (check the Top Operations tab). The scan reads 18+ million records to return 1.1 million records. Finding the correct index will reduce the IO cost for these nodes. Rob Farley has a blog post about these warning that is helpful.
There are 2 Key Lookups on the REprocess_BTS_Items table that could be removed, though it does not look like it will have much effect on the overall performance of the query.
The Product related tables are returning more than 13 million records and are the largest costs of the query. These tables are referenced via the view, [v_ProductSimple], which appears to be returning every product in the system. Could a filer be applied here to reduce the amount of data that is being processed by the query?
With as many Joins and subqueries being used, you may consider breaking this down into smaller chunks and using temp tables to hold data. This may allow you to access a smaller subset of the data and process much quicker.