I'm trying to figure it out how to optimize/improve the following thing, on a VM hosted in MS Azure and a database, also hosted in MS Azure. Besides the instance hosted in MS Azure, there is another instance (local) that has a dedicated database for an integration made between two software applications and there is a linked server between the two instances.
Lately, I noticed that the remote scan is taking up to 100% causing poor performance on some stored procedures that updates the database hosted in Azure.
Here's how the execution plan looks like:
Part of the stored procedure executed against the database from local instance that do some updates on the linked server, looks like this:
UPDATE docl SET docl.udfvalue1=WMSC.QtyReceptioned, docl.udfvalue2=WMSC.QtySent, docl.Stringfield1=WMSC.WAREHOUSE FROM MyLinkedServer.DataBase.dbo.DocLines docl INNER JOIN #WMS_Confirmed WMSC on docl.gid=WMSC.GID
UPDATE es SET es.ADFLAG3 = 1 FROM MyLinkedServer.Database.dbo.DocHeader es WHERE es.OrderDate>'2018-01-01' -- OrderDate is one of the indexes on `DocHeader` table AND EXISTS (SELECT 1 FROM #WMS_Confirmed WMSC WHERE es.gid = WMSC.fDocGID );
What I found online, like enabling
Dynamic Parameters and
NestedQueries on linked server provider or
OPTION (RECOMPILE), didn't helped at all. Is there something else that I am missing? Thanks