2

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%   |
+-----------+---------+-------------+---------+
  • 1
    Does seem glacially slow to remote insert 12,012 rows. Apart from that for Query 11 you could try adding an OPTION (RECOMPILE) so the cardinality of the table variable is taken into account and see if that improves things on that one – Martin Smith Apr 24 at 13:21
  • So what exactly is the issue with this? Is it the fact that your archival process is taking 2 minutes or is it that other transactions are being blocked for up to 79 seconds? If it's the blocking and this doesn't have to all be one transaction then I'd suggest putting this into batches, possibly combining the delete and insert into a single statement to make it easier. DELETE TOP (50) combined with OUTPUT DELETED INTO Archive. – Steve Hood Apr 24 at 16:28
  • Hi Steve, it is just the time it takes to complete, I need to try and it get it to run faster – Martin Swart Apr 26 at 9:36
5

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.

1

I went through your script and the execution plan and could see that you are following deletion for achieving archiving. I would like to share my experience with you for archival, we perform archival of our database almost every year or in a year and half. Earlier we used to follow similar approach as of yours and this was taking too long for us and also had to get approval for longer hours of down time, used to suffer from bloated logs, had to back them up after every heavy delete operations - overall a humongous task and it used to be hard to get approval.

Later on, we changed our approach and started following as per below:

We developed few procedures based on type of tables and say table name is Employee and we are to perform archival up to 31st December 2017 - We will do insert of all the records available in Employee table after 31st December 2017 to Employee_New and once this insert operation is completed successfully. Rename Employee table to Employee_Arch_20171231 and Employee_New table to Employee(containing only those records which are after archival period). Create all the indexes on new Employee table similar to old Employee(Employee_Arch_20171231) and drop indexes from old Employee table. Insert operation is much faster than delete operation and requires much lesser resource and log space than that of delete operation. During this operation, you would need down-time which will be much shorter than earlier down time.

Once archive is done and all data is validated successfully with business, Employee_Arch_20171231 table's data will be merged with main archive table(for compliance purpose). This way, your archived table doesn't have any reference to any objects and you are free to perform operation that you like to do based on your business requirement.

One thing to note in the above archival process is to be very much well aware and careful of below scenarios:

  1. PK-FK constraint between tables.

  2. Triggers on tables(in case there is some action based on insert/delete/update etc).

  3. Filtered index

I hope above helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.