7

I have a relative large table (for me) with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign).

Tip
--------------
Id          int (clustered index)
UserId      int
TipIndex    smallint
Value       binary(8)
LastChanged datetime2(3)
  • every user has between 1 to 400 tips that will be inserted at random during this period
  • UserId + TipIndex is unique
  • I never query Tips directly on the Identity Key (Id)
  • 99% of the times I query on UserId
  • I need all columns
  • I often query on 1 UserId (per page view), sometimes on a batches of 10.000s for statistics
  • this is a high traffic site during this period and it should be able to handle 30.000 queries on UserId per minute
  • Id is at the moment my Clustered Index, because I read that it leads to the least fragmentation.

So I'm hosting on SQL Azure and Azure already recommended adding an Index with included columns. I was always hesitant on using UserId,TipIndex as a Clustered Index, as Tips will be added at random. Meaning that I'm afraid of huge fragmentation problems etc.

My questions:

  • Doesn't an Index with included columns have the exact same problem?
  • Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
  • Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
  • How to prevent fragmentation?

I know in the end the answer is always "depends" or that I should measure it. But as I'm a solo developer and not with a lot of resources I am hoping on someone with more experience that has a gut feeling for this, so my first attempts have a higher chance of going in the right direction.

6

It seems you are too much concerned about fragmentation, As long as you keep updating statistics regularly, fragmentation shouldn't bother you much for performance. You may read more details about this on a video shared by Mr. Brent Ozar and also another page here. Let me try answering your question one by one:

Doesn't an Index with included columns have the exact same problem?

Index with columns in the include or be it a key column, doesn't differ much. Key columns are going to be part of B-tree whereas include columns are not however when you perform any insert/update/delete operation, this will have same expense as these columns need to be updated/inserted/deleted.

Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?

Not very sure what you meant by shadow table, if you meant by base table here then yes, you would have same problem as far as fragmentation is concerned.

Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?

As per your statement - "99% of the times I query on UserId", this is a good candidate for primary key clustered column. Since you are not going to use Id column very frequently, I don't see any problem in using composite primary clustered key in the form of UserId and TipIndex. In terms of size of index, it is as good as Id(int --> 4 byte) plus an additional column of tinyint type(1 byte).

Kindly understand that, clustered key is nothing but the order of data stored logically and doesn't have any physical existence unlike non-clustered key.

How to prevent fragmentation?

I would say updating statistics should be considered with priority than that of fragmentation. You may use maintenance script used by many DBAs across the world from Ola Hallengren. You can schedule it weekly or bi-weekly basis your requirement.

Hope this helps.

  • 2
    The size of the clustered index key comes into play for how much data is at the non-leaf levels of the clustered index, plus the amount of data that needs to be included in non-clustered indexes and at the leaf level of non-unique non-clustered indexes. But an even bigger size benefit is not having a second copy of the data that you don’t need if you’re always querying it the same way. – Rob Farley Dec 29 '20 at 12:19
  • 1
    Hi @Learning_DBAdmin - apparently it goes pretty well with the learning part! Thanks a lot for your extended answer! Both are very good answers that really help me a lot exploring the right path to go! – Dirk Boer Dec 29 '20 at 15:03
  • Clustered Index does NOT have to be a primary key, it doesn't even need to be unique. In this case it is unique though. – Charlieface Dec 29 '20 at 17:34
  • @Charlieface - Yes, primary key needn't be clustered and any key can be defined as clustered as it comes along with uniquifier of 4 byte, which will be added as a secret column in all NC indexes. IUD will become more expensive due to this, it should be done only when you have very strong reason for that. Best practices should be followed in most of the cases unless you have very-very good reason for not doing so. Anyway, I am yet to understand your point here. – Learning_DBAdmin Dec 30 '20 at 5:33
  • In this instance, OP has Id as the PK. Using those two columns as PK instead is not necessary to solve his issue, he can just create a unique clustered index on them and leave the PK as NC – Charlieface Dec 30 '20 at 10:28
7

Your questions:

Doesn't an Index with included columns have the exact same problem?

Yes.

Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?

Yes

Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?

I would, yes.

How to prevent fragmentation?

There are a couple of different types of fragmentation to consider. One is when you only have part of your pages being used because you’ve had page splits. If you have a lot of inserts, this will happen. Don’t stress too much. The other is when you have pages where the subsequent page is in a different extent. Again, I wouldn’t worry too much. If your data is mostly in the buffer cache, it doesn’t really matter if it moves across extents.

So... don’t worry about it too much. But don’t bother having a complete copy of the data in a way that you won’t actually be querying it.

  • 2
    Upvoted Sir. I guess, we were posting answer at same time, good to see that my points are matching with an expert like yours :) – Learning_DBAdmin Dec 29 '20 at 12:12
  • 2
    Your answer is good too. I’m pretty sure the “shadow table” just means an index with all the columns included. – Rob Farley Dec 29 '20 at 12:23
  • 1
    Hi @RobFarley, thanks a lot for your extended answer! This really helps me a lot in my struggle to get this performant and have a deeper understanding of the underlying technology. I have upvoted both answers and their are both really helping me out - I have accepted the other one, because I thought the extra points would make a bit of more difference for him then for you. Thanks for your understanding 😅 – Dirk Boer Dec 29 '20 at 15:07

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.