2

I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of queries filter by a foreign key and a date range.

Currently, there is a clustered index on the id column, and a non-clustered index on the two columns of importance (TrackerId and DateRecorded). If I were to swap the indexes, our queries will run a lot faster. Would this negatively affect insert times?

  • 1
    Whic DBMS are you talking about? – a_horse_with_no_name Oct 16 '17 at 6:42
  • Azure SQL, I assume the same answer will apply to other DBMSs though – Andrew Williamson Oct 16 '17 at 9:09
1

In your actual design you have unique, static,narrow, ever-increasing clustered index key that causes no page splits when rows are inserted.

If you create your clustered index on TrackerId and DateRecorded (you want to use these columns in this order, right? because your seek predicate has an equality on TrackerId and range on DateRecorded) you'll get non-increasing clustered key that will cause page splits on INSERT.

It will be wider and maybe not unique.

But final decision is up to you. We don't know if this table has more reads or writes, and page splits can be mitigated by choosing appropriate Fill Factor and regular index rebuild.

Here your can read more on it: Mitigating Index Fragmentation by Paul Randal

  • It's worth mentioning that readings may be received out of order, and there are many inserts, as opposed to only a few reads (which return lots of rows in each read). Thanks for the link, looks like I've got some reading to do... – Andrew Williamson Oct 16 '17 at 8:38
  • Well, I tested it out, and it turned out to be a very bad idea. The system's average data IO went up by a factor of 10, because we have about one insert per second. – Andrew Williamson Oct 16 '17 at 20:58
  • Would the effect be similar if I were to create a non-clustered index, and include almost every column in the index? Are page splits as much of a concern for non-clustered indexes as for clustered? – Andrew Williamson Oct 16 '17 at 21:21
  • If you create non clustered index on (TrackerId, DateRecorded) you'll get the same page splits because your TrackerId are always random. When you include all the columns as included columns you will maintain 2 copies of your table instead of only one, with different sort order. – sepupic Oct 17 '17 at 6:51
  • Are your inserts are single or batched? If you insert many rows in one statement, it also causes SORT in insert plan. Did you try low fill factor, for example 50? – sepupic Oct 17 '17 at 6:53

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.