Slow inserts across a linked server?

Linked Servers offer a great way to connect two SQL Servers together, allowing remote querying and DML operations. Frequently, this is used to copy data from production to reporting. However, the temptation is to run the copy operation on the production, or source side. If you do that, even with a single INSERT INTO statement, SQL Server will process each individual row as a discrete INSERT INTO statement via a cursor operation. This makes for very slow inserts across a linked server. Running the operation from the destination server means SQL Server can simply query the remote source table for all the rows, inserting them as a set into the destination table. The difference in speed can be eye-watering.

Slow inserts across a linked server can be eye-watering, but probably not this badly!

Slow inserts across a linked server can be eye-watering, but probably not this badly!

Take the following MCVE as an example. It drops-and-creates a src_db database on the source server, and a dest_db database on the destination database. It also drops-and-recreates the required linked server, so don’t run this on a production SQL Server. Once the source and destination databases have been created, it copies around 800,000 rows from the source to the destination.

Here, we’re creating the linked server to the destination SQL Server:

This code creates the destination database and table on the destination SQL Server, via the linked server:

Now, the INSERT operation, being ran at the source server:

I stopped the insert after it ran for more than 7 minutes. FYI, it also took several minutes to roll back the operation.

Next, switch over to the destination server, and run this code to create a linked server to the source SQL Server:

Then run the INSERT from there:

The above insert completed in just 11 seconds. 800,000 rows, in eleven seconds.

When you see a “remote insert” operator in a query plan, investigate if you can move the work to the destination server.

Remote Insert Operator makes for very slow inserts across a linked server

BAD! Remote Insert Operator!

See the other posts in our series on performance.