Sql-server – Splitting a large table to improve performance

Advice:

1. Normalize data stored.

2. Consider carefully and figure out your write model. For orders, can you overwrite? do you need to mark superceded and append?

The above are generic data entry pieces of advice, but regarding automated application to application interfaces, you have the issue of erroneous data entry. To deal with this it is probably better to accept orders etc. but have the invoicing done by humans, who can cross check with the real world. Also this keeps people in control of your books. So I would add to that:

1. Separate automated entry from homan review and approval.

If CreatedOn is not the clustered index (and especially if it is not indexed at all), and you are inserting new data sequentially (e.g. new rows have a newer CreatedOn), you should consider making that the clustered index. Then you shouldn't have any contention when you are deleting old data and inserting new data, since it will be on a completely different set of pages. Unless for some reason your inserts or the delete are escalating locks. If that is the case then instead of one big transaction:

DELETE [massive amount of rows]
  WHERE CreatedOn < [somedate];

You should consider breaking that up into chunks, as you suggested; but, just doing that in a loop and not adding any other changes won't have that much of an effect, since that may still be operating as a single transaction (and your problem might be exasperated by transaction log writes, and perhaps poor autogrowth settings for the transaction log). My method is typically as follows (with no "outer" transaction at play) - commit each set in its own transaction, and checkpoint or backup the log in between transactions. This lets user queries get in between your transactions and also reduces the impact on the log. Note that I picked 1000 arbitrarily; that might not be the right number for your scenario, but I'm pretty sure 10 is not.

DECLARE @somedate DATE = DATEADD(DAY, -1, GETUTCDATE());

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple recovery: CHECKPOINT;
  -- otherwise: BACKUP LOG ...;

  BEGIN TRANSACTION;

  DELETE TOP (1000) dbo.MyTable
    WHERE CreatedOn < @SomeDate;
END

But again, this will help your process most if CreatedOn is clustered, or at least indexed (having no context I don't know if your overall workload would be better off with that as clustered, but I do know that this query will work much better if it is).