Best option to index an application log table for quick insertion and retrieval in date order?

Question

Suppose I have an application trace log table with three columns: (id uniqueidentifier, message nvarchar(max), and started datetime2).

The primary key (clustered) is on [id]. I need to be able to insert records as quickly as possible, but I'm not sure if that's the best primary key. It makes sense because I'm using EntityFramework and that's the default, but the uniquidentifier type has pretty random values for each insertion, so it would insert rows all over the place when clusted on id. I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that. At the same time, I need a way of retrieving the rows quickly in order by [started].

My question is, would it be better to: 1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date, or should I 2. Leave the primary key on [id] and add a unique non-clustered index on (started, id).

My reasoning for including both (started,id) in the 2nd option is so it can be a unique index that would include the clustering key (which is always implicitly included anyway). I don't want to include any other columns in the index because it would duplicate a lot of [message] data unnecessary, when all I'm really interested in is an index that speeds retrieval of rows in order. Or, is there a better option than the one's I mentioned?

Answer 1

I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.

At insert rates of under 10,000/sec hot page latch contention is not a big issue, and the efficiency and locality of end-of-index inserts is preferable.

1. Change the primary key to be composite on (started, id) so it's the only index and the data pages are in order by date

Yes. Prefer the solution where you don't need two indexes. And look here for a solution to using a datetime2 as the only unique index on the table by filling the sub-second precision with values generated by a sequence.

Answer 2

I would: Change the date to a clustered (non primary) index.
Change the id to a non clustered primary key.

Changing the clustered index to the date (assuming those happen mostly in order which I assume it would for a log) will speed up the insertion process. Right now the id keys are essentially random which means that you may have a new row that gets inserted at the beginning of the values and that means that every value must be moved since it's clustered. This slows down inserts. Since you usually query log tables by date and not by id keys changing to a clustered index on the date will also speed up your retrievals so that's a win-win.

Because your date is not guaranteed to be unique you still need the id of course. You can leave it as the Primary Key if you prefer since it's unique but just modify it to be unclustered (they are created clustered by default if no other clustered index exists but that's not required). By leaving it as the primary key (unclustered) you still have a primary point of reference if you did need a foreign key.