The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I don’t foresee now.

The table is not wide, with just a few columns. It has about 8 million rows and bringing our site to a halt during peak times. (+1000s concurrent users). The data is not easily cacheable, because it is quite volatile and essential that it’s up to date.

There are a lot of reads on one column of the natural key, but also quite active inserting and updating. Say 8 reads, vs 1 updates vs 1 inserts.

Id (PK)         int
UserId*         int
Key1*           varchar(25)
Key2*           varchar(25)
Key3*           int
LastChanged     datetime2(7)
Value           varchar(25)
Invalid         bit

* this combination is the natural primary key

I need to query the most of the time on:

  • All rows for one UserId (most queried)
  • All rows for a list of UserIds (a lot of rows)
  • All rows for a list of UserIds with Key1 = X
  • All rows for a list of UserIds with Key2 = X
  • All rows for a list of UserIds with Key1 = X and Key2 = X

I know the final answer is always “profile it”, but we are quite on a time constraint here so any guidance or experienced opinions in advance would really be appreciated.

Thanks in advance,

  • For me, it would be easier to work with this if you provided the table structure as a CREATE statement and some actual sample queries. What exactly is the index being recommended by Azure? Does it have any current indexes? – LowlyDBA Apr 28 at 19:47
  • Test the recommended index as is. – stacylaray Apr 29 at 7:29

Unfortunately selecting a clustered index isn't just a matter of ever increasing. And insert performance and fragmentation aren't your only concerns. Here are a few things to think about:

Your identity column is ever increasing, your natural key isn't. This will increase your insert time, but inserts are generally pretty quick and in a OLTP system they aren't so much less frequent as less visible. For example doubling the time of a 4ms insert to 8ms and no one will notice. Increase the time of a select statement from 30 seconds to a minute and people will see it.

It's a similar problem with fragmentation. It exists, it can cause problems, but it's a manageable problem at worst.

That said, are these your only indexes? Because the clustered index keys are included in any non-clustered index. So a wider set of keys will actually increase the size of your non-clustered indexes and slow them down. And your identity column is only 4 bytes wide. Your natural key is 58 (assuming it's a unique key otherwise add 4 bytes for the uniquifier).

Ordered range scans can be very helpful, and are one of the big bonuses for the right clustered index. That said, I don't see either of the indexes you mention above as candidates for range scans. Those would be more for

SELECT * FROM table1 WHERE name BETWEEN 'Bob' and 'Mary'

You see them most with date data types, although queries similar to the above example happen too.

Also if you end up using your natural key as an NCI (non clustered index) then you might make sure it's a covering index for your main query(s). In other words it contains (generally by using the INCLUDE statement) any extra columns needed by the query. This means that SQL doesn't have to go back and check the CI (clustered index) and avoids an expensive lookup.

Last but certainly not least, do you have to worry about GDPR? Do you have any data for people who are in the European Union? If you do then you need to worry about natural keys in your scripts. If someone's information ends up in a script then you will need to include that in your plans when you have to remove someone's data.

Here is a summary I wrote a while back. It's more generic but might be helpful to look at:

https://sqlstudies.com/2016/08/29/natural-vs-artificial-primary-keys/

And a few other related posts:

In the end though, you are right. You should test it out. That said, I would personally guess that keeping the identity as your CI is going to be your best bet with the following 2 non clustered indexes (based on the queries you mentioned above):

  • UserId, Key1, Key2
  • UserId, Key2

I didn't include Key3 because you didn't mention it and it adds unnecessary width to your index. Beyond that, given the size of the table, you can include all of the non-key columns. Assuming that you have sufficient space for the indexes (which will be as big as the table is currently) it might improve your queries pretty significantly. Of course any data you don't have to send to your application shouldn't be sent and will improve your performance (and reduce the size of your index if you don't have to add it to the include).

  • 1
    Range scans are not necessarily just for range predicates. SELECT * FROM Table WHERE name = 'bob', where name is not unique, is also a range scan. This is in contrary to a singleton lookup, which would be the case if name was unique. – SQLRaptor Apr 30 at 2:11
  • Hi @KennethFisher, thanks for your extended answer. It gives me a lot of insights. When I'm reading your answer I'm actually getting the feeling that I should put a clustered index (UserId,Key1,Key2,Key) and two or three indexes with all columns included. As the 'rest of the data' (besides the clustered index) is very narrow almost everything is already there, saving the lookup. Is that correct? The natural primary key never changes. – Dirk Boer Apr 30 at 8:42
  • 1
    Honestly in the end I'd probably stick with the ID as the CI and NCIs for everything else. Particularly if you are going to include all of the other columns. In the end there isn't a lot of difference between the CI and NCIs that include every column. But I seem to recall the maintenance going better if your CI is smaller. – Kenneth Fisher Apr 30 at 14:34
  • @SQLRaptor I've been doing some looking and tried a few things out and I can't get that type of query to come up as anything other than a seek. Can you point me to a reference you are using? Also, interestingly during the reading I've been doing about it I noticed that you can get range scans with covering indexes as well as CIs. – Kenneth Fisher Apr 30 at 14:56
  • @KennethFisher you are confusing the execution plan physical operator with the logical operator. The execution plan operator tells you how the index non-leaf level was traversed to get to the starting point. For both singleton lookups and range scans, you will see a seek operator. The only case you will see a scan physical operator is when the whole index is scanned from start to end. Even with your range predicate (Name BETWEEN 'bob' and 'Mary') you should see a seek operator, even if the operations happens to scan the entire index. – SQLRaptor Apr 30 at 16:03

"Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I don’t foresee now." Don't listen to internet myths, including this one about the ever increasing clustering key. The choice to make the Primary Key clustered, especially for surrogate keys, is in most cases a poor choice. Clustered indexes excel at ordered range scans, which is something that will never take place with a surrogate key. Every choice has pros and cons, and I wouldn't be too concerned about fragmentation, especially on RAID arrays which introduce deliberate fragmentation of their own. Just monitor it, and re-org/rebuild as needed, and give the index a fill factor to keep it within reasonable fragmentation levels between re-orgs.

In fact, such an active table may suffer from 'last page contention' because of the ever increasing clustering key. one of the solutions to this issue, is to create a random access insert pattern, to avoid physical page contention. http://download.microsoft.com/download/b/9/e/b9edf2cd-1dbf-4954-b81e-82522880a2dc/sqlserverlatchcontention.pdf The query patterns as you describe it, definitely support this choice. My advice - go ahead and create the clustered index on the natural key. The results may surprise you, and the risk is relatively low for such a small table.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.