SQL Server 2014 slow remote insert

I have several linked servers and I want insert a value into each of those linked servers. On first try executing, I’ve waited too long for the INSERT using CURSOR. It’s done for about 17 hours. But I’m curious for those INSERT queries, and I checked a single line of my INSERT query using Display Estimated Execution Plan, it showed a Cost of 46% on Remote Insert and Constant Scan for 54%.

Below of my code snippets I worked before

DECLARE @Linked_Servers varchar(100)

DECLARE CSR_STAGGING CURSOR FOR 
    SELECT [Linked_Servers] 
    FROM MyTable_Contain_Lists_of_Linked_Server

OPEN CSR_STAGGING

FETCH NEXT FROM CSR_STAGGING INTO @Linked_Servers

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC('
            INSERT INTO ['+@Linked_Servers+'].[DB].[Schema].[Table] VALUES (''bla'',''bla'',''bla'')
        ')
END TRY
    BEGIN CATCH
        DECLARE @ERRORMSG as varchar(8000)
        SET @ERRORMSG = ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM CSR_STAGGING INTO @Linked_Servers
END

CLOSE CSR_STAGGING
DEALLOCATE CSR_STAGGING

Also below, figure of how I check my estimation execution plan of my query

I check only INSERT query, not all queries.

How can I get best practice and best performance using Remote Insert?

Answer

You can try this, but I think the difference should be negligibly better. I do recall that when reading on the differences of approaches with doing inserts across linked servers, most of the standard approaches where basically on par with each other, though its been a while since I looked that up, so do not quote me.

It will also require you to do some light rewriting due to the obvious differences in approach (and assuming that you would be able to do so anyway). The dynamic sql required to do this might be tricky though as I am not entirely sure if you can call openquery within dynamic sql (I should know this but ive never needed to either).

However, if you can use this approach, the main benefit is that the where clause gets the destination schema without having to select any data (because 1 will never equal 0).

INSERT OPENQUERY (
    [your-server-name],
    'SELECT             
        somecolumn
        , another column        
    FROM destinationTable
    WHERE 1=0'   
    -- this will help reduce the scan as it will 
    -- get schema details without having to select data
    )
SELECT
    somecolumn
    , another column        
FROM sourceTable

Another approach you could take is to build a insert proc on the destination server/DB. Then you just call the proc by sending the params over. While yes this is a little bit more work, and introduces more objects to maintain, it add simplicity into your process and potentially reduces I/O when sending things across the linked servers, not to mention might save on CPU cost of your constant scans as well. I think its probably a more clean cut approach instead of trying to optimize linked server behavior.