Updating a view in sql server
If I execute this SELECT query via the Linked Server it executes in less than a second. This sends the query to the remote server and executes the query there.
All these considerations lead to a plan that does not feature static partition elimination. If the predicate evaluates to false, the subtree under the Filter is not executed.In the plan fragment below, the startup Filters ensure that only the green operators execute; the red ones never start at all: On the writing side, The normal (not 'startup') Filter just to the right of each Clustered Index Update operator ensures that only changes for the current table are passed on.In the example plan, only one Clustered Index Update (and its associated nonclustered index maintenance operators) receives any rows: indexes were also partition-aligned.I will also show you one way of handling this situation. I also created a Linked Server named SERV1 on SERV2 that connects to SERV1.
Now we will execute some queries on SERV2 using the linked server.
SQL Server Linked Servers are a great to query remote databases, but when running update queries they don't always behave like they should.