If you're not sure where to start and don't know what resource type is your bottleneck such as memory or CPU, one place may be to do some analysis on...

try running this from within a SQL Agent job:
exec xp_cmdshell 'echo %temp%'

One thing I recall, although I don't use the feature very often, is that in Standard edition you would have to use the NOEXPAND hint when querying an indexed view,...

I notice you have a GROUP BY clause at the end, how many rows are there typically in table GEMS_CIX_EQ_C for the combination of the 4 columns in the GROUP...

Have you looked at the actual execution plan of the slow query to see that the new index is being used?  Building an index also adds new statistics to the...

Could it possibly be a timeout issue?  I think I've seen similar behavior in SSIS when there is a timeout in the source of a dataflow, it doesn't necessarily halt...

Instead of trying to execute the RESTORE remotely like this, could a SQL Agent job be setup on the remote server that does this RESTORE?  If so then all you...

The biggest problems offhand are:
-  dealing with the 2 different columns to group OrderLineItem by depending on the ReturnStatus, either OrderItemID or OrigItemID.  There's a Hash Match (Aggregate) operation consuming...

If the 2 tables have some value in common such as ID here, then you can do a join in the UPDATE statement, from your example though it isn't clear...

Offhand I'd think that these would be different areas that need to relate to each other, so they would work better if all in the same database.  I'd definitely consider...