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.
- 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.