it depends.... what have you read so far on it? plenty of information on it on the net for you to get here and just ask a question asked and...

before running more test I would drop index [idx_BTI_include] which you created thinking it would help. even though it is being used, it is the one giving poor estimates on rows...

anything on E:\SSISPackages-Logs\Log-Cube\DimProject.txt?
and if not can you change the command line to be

dtexec /FILE E:\SSISPackages\Packages-Cube\DimProject.dtsx /reporting V >>E:\SSISPackages-Logs\Log-Cube\DimProject.txt

and execute it again - by default /reporting ew...

Good progress.

I would however like to see both explain plans for my code (changing the union all to union) and Jonathan's with indexes without included columns.
for the...

even rewriting does not solve everything.

CRM 2016 - DB on compat 130
On my main CRM db in one of our tables (30 Milliion rows +) the following...

Has the OP stated that the correct where clause is
WHERE ( 
BAN = @LOC_BAN 
OR BulkID = @LOC_BulkId 
OR Serial_Number= @LOC_IMEI 
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date]...

you didn't supply us with the indexes on the table - as it seems you generated the script through SSMS I would advise you to set your scripting options to...

That is normal - when on visual studio all models created on a server have that name format.

should you deploy the model to the server then it will...

Bit curious - there may be underlying problems not mentioned.

re: it is not instant file initialization enabled - very few cases where this should not be enabled -...