Archiving process not running fast enough I am archiving data from one database to another database on a different SQL server.We are archiving multiple tables in our database.Recently our inserts into the source database has increased and archiving is not running fast enough. I am thinking of splitting the archiving of the tables into seperate jobs, but is there anything i can do to improve the performance of my queries . 

This is the estimated execution plan 

This is the actual execution plan

The QueryTimeStats from the actual plan are below

+-----------+---------+-------------+---------+
| Statement | CpuTime | ElapsedTime | Percent |
+-----------+---------+-------------+---------+
|         1 |       3 |           3 | 0.00%   |
|         2 |       3 |           4 | 0.00%   |
|         3 |       0 |           0 | 0.00%   |
|         4 |       1 |           1 | 0.00%   |
|         5 |       0 |           1 | 0.00%   |
|         6 |       1 |           1 | 0.00%   |
|         7 |       6 |           6 | 0.01%   |
|         8 |       0 |           1 | 0.00%   |
|         9 |     516 |         538 | 0.49%   |
|        10 |   76063 |       79110 | 71.84%  |
|        11 |     496 |       21621 | 19.63%  |
|        12 |      91 |         237 | 0.22%   |
|        13 |       4 |           4 | 0.00%   |
|        14 |       3 |           4 | 0.00%   |
|        15 |    2176 |        2446 | 2.22%   |
|        16 |    2581 |        5102 | 4.63%   |
|        17 |      92 |         293 | 0.27%   |
|        18 |      20 |          39 | 0.04%   |
|        19 |       2 |           2 | 0.00%   |
|        20 |       3 |         242 | 0.22%   |
|        21 |       0 |           0 | 0.00%   |
|        22 |       0 |           0 | 0.00%   |
|        23 |       2 |           2 | 0.00%   |
|        24 |       5 |           6 | 0.01%   |
|        25 |     139 |         139 | 0.13%   |
|        26 |       0 |           1 | 0.00%   |
|        27 |       4 |           3 | 0.00%   |
|        28 |       4 |           6 | 0.01%   |
|        29 |      77 |          77 | 0.07%   |
|        30 |       0 |           1 | 0.00%   |
|        31 |       9 |           8 | 0.01%   |
|        32 |       3 |           4 | 0.00%   |
|        33 |       0 |           0 | 0.00%   |
|        34 |       1 |           1 | 0.00%   |
|        35 |       4 |           4 | 0.00%   |
|        36 |       5 |           8 | 0.01%   |
|        37 |      81 |          82 | 0.07%   |
|        38 |       0 |           1 | 0.00%   |
|        39 |       3 |           3 | 0.00%   |
|        40 |       4 |           6 | 0.01%   |
|        41 |     105 |         105 | 0.10%   |
|        42 |       1 |           7 | 0.01%   |
+-----------+---------+-------------+---------+
Doing an insert from the source side through a linked server is problematic for performance.  Each row inserted into the target is inserted via a discrete INSERT statement inside a cursor operation.  i.e. if you are inserting 12,000 rows into the target, SQL Server will actually execute the insert 12,000 times, once for each row.

If you re-write the archive process so it runs from the destination server, it will be much faster.

As an example from your execution plan, rewrite this:

INSERT INTO @MessageEvent (ID, DateTime) 
SELECT TOP (1500) ID, TimeStamp 
FROM ConnectAPI.dbo.MessageEvent (NOLOCK) 
ORDER BY ID


and run it from the destination server, like this:

INSERT INTO ConnectAPI.dbo.MessageEvent (ID, DateTime)
SELECT TOP(1500) ID, TimeStamp
FROM [SourceServer].ConnectAPI.dbo.MessageEvent
ORDER BY ID


Notice I left the NOLOCK query hint out.  You may want to make sure you understand the implications for partial reads and/or duplicate reads under READ UNCOMMITTED isolation, which is the isolation level used with the NOLOCK hint.

I wrote a blog post at SQLServerScience.com with minimally complete, verifiable example code showing how a simple insert of 800,000 rows from a source server to a destination server takes over 7 minutes when executed from the source server, and only 11 seconds when executed from the destination.