I have a logs table with millions of records. It is a heap without indexes. Would a clustered index make insertions slower?
Thanks in advance,
It depends, as always. If you have a non clustered primary key index on the heap, I'd think inserts into the heap are slower than inserts into the table with clustered primary key index, since the heap inserts will have to insert into the non-clustered index as well.
Otherwise, I don't think there will be much difference. You MAY get into latch contention on the last page of the clustered index, if you have an ever increasing key. But if that really affects you depends on the insert-frequency. Unless you have thousands or at least hundreds of inserts per second, I don't think you'll be able to note the difference.
If it IS a log table as you say, then you actually need to be very careful as to what the Clustered Index keys are if you add a Clustered Index. If the Clustered Index isn't in the same order as the inserts, then you can get some pretty massive page splitting and a whole lot of unnecessary fragmentation.
If it is "just" a log table (which also should mean that it is NEVER updated... only inserted into), then a Clustered Index, even in the correct order, may actually slow things down a bit.during inserts because it not only has to maintain the data at the leaf level, it may also have to maintain a 4 or 5 level B-TREE.
As Magnus says, though, "It Depends". My recommendation would be to restore the table to a test database and test your thoughts on it.
Also, I don't know how big your log table is in bytes. If it's big, post back here so I can tell you about a little trick to keep from blowing up your MDF and LDF files because building a Clustered Index of over 128 Extents (just 8MB in size) requires that the Heap be preserved in-place until the Clustered Index is committed. Only after that is the Heap dropped and so the Heap is somewhat large, say 100GB, you could end up with 120GB of expansion in your MDF file and a 100GB log file you hadn't planned on.
21 People are following this question.