My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system incrementing from -9223372036848909236, 1 and the other is incrementing from 1134873337, 1

I pull the data into my data warehouse from each of my sources and the data is partitioned on the UTCDate which is also a source value.

Filter criteria will always include the UTCDate. May or maynot have ID as criteria

Question: will i have less fragmentation on my clustered index UTCDate, ID or ID, UTCDate

  • 1
    Is this a fact table? Dimension? How is it used? I'm also a bit confused on the way you are using your ID - is this a way to handle two systems entering into the same warehouse and hoping to avoid collisions?
    – Mike Walsh
    Dec 16 '12 at 4:06
  • Also how does the data get into this table? What type of model do you have? How do your other tables look? How many rows do you get per day? How big is it now?
    – Mike Walsh
    Dec 16 '12 at 4:08

The Short Answer: I misread your question a little bit. The below discussion still applies and explains my answer, but I would suggest UTCDate, ID for your index. Especially because of your partitions and the fact that UTCDate is included in every query. The question of fragmentation is a good one, and I imagine you'll see some in either of your two choices, but the performance benefit would be best with this way, I feel.

The Previous Discussion:

This is one you'll likely get a few answers on and I've asked a couple questions in the comments. That said a few quick thoughts to get started (ignoring the multiple system question and the way the IDs are being used):

1.) Presuming you are talking about SQL Server partitioning, a partition aligned clustered index is generally best. So in my mind that is a good vote for UTCDate.

2.) You indicate that filters will always (is that really always? or figuratively always?) have UTCDate. In my mind that is another good vote for UTCDate.

3.) Because you'll be having IDs coming in at both a high range and a low range, you will experience some level of index fragmentation on ID. Assuming UTCDate is always increasing and you aren't entering data from dates all over the place, you may actually experience less fragmentation going with UTCDate.

You also didn't indicate if this is a fact table or dimension. Assuming it is a fact table, that is another good argument for date in my book. If you are always querying filtering on a date or date range, then the clustered index being on that range scan value should help your queries. The fact that it would be partition aligned would also likely help.

If this is a dimension and you will sometimes join on ID to pull data out of it, then there could be a stronger argument for going with ID, though I'd argue that the unaligned nature of that index to your partition could cause you more grief in the future performance wise.

I would not bother adding ID to the index. Unless you include it enough as a filtering predicate and could actually see an improvement in performance of most queries. Yes, the date column alone wouldn't be unique, but if this table is a BIGINT, the 4 byte uniqueifier SQL Server adds to the clustered index to make it unique would actually cost less in terms of storage space than that ID column.

These are just my thoughts and they are quick thoughts. This is one of those things you should really test, try a couple ways and see how it works out. Though partitioning sometimes forces your hand a bit. So I would probably end up with the date column only as my first choice and date plus ID as my second choice. Others will likely have alternative ideas on that one, too. And more information edited back into your question will help.

  • Would you recommend IDs from one source to increase, and the IDs from another source to decrease?
    – A-K
    Dec 17 '12 at 2:19
  • That wouldn't be a first choice for a whole bunch of reasons, I asked about that in a comment on the question, though.. What happens if a third source is added? What happens if one increases its activity over the other? Why not just assign a system ID and let them use the same ID scheme and have the ID increase for everybody and go in order for everybody. But I'd need more, that's just my first thought.. What about you Alex?
    – Mike Walsh
    Dec 17 '12 at 2:29
  • Mike, in my systems clients request and are assigned ranges of numbers as needed. They determine how much they need themselves. This works for any number of sources, possibly with different levels of activity. I did not, however, investigate in depth how that affects fragmentation.
    – A-K
    Dec 17 '12 at 3:37
  • Well we can handle fragmentation, right? That is an interesting pattern and if it works, it works :)
    – Mike Walsh
    Dec 17 '12 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy