We have a DB which is heavily queried in many bizarre ways (user-definable queries). After a lot of profiling, we found that the IDENTITY of one table was NOT the best clustered index, it was three other fields which are almost always used together, so we changed the Clustered index over.

The next morning, the system was effectively frozen. According to the profiler, this should have been the 'perfect' solution. Unfortunately what we'd neglected to fully take into account was that although more than 60% of the queries were SARGed on that triplet, 40% were on other columns, including the ID, and the "Key Lookup" cost which was being incurred was now amazingly prohibitive.

For OLTP systems which drive UIs, The main problem is that the App typically pulls out most of the columns for display. While having the Clustered Index be something a bit complex (or non-standard) fixes the "bulk" of the queries, it can severely impact other queries, especially if the UI allows searches such Search By multiple (optional) arguments.

We also noticed that once the CI was moved, we had to add a lot of new statistics to 'fill in' for the old CI.

For this reason, unless there's a good reason, we take the "hot spot" hit (low trans/sec, so not too bad). It may not be perfect, but if your system grows 'organically' (i.e. the developers add queries and screens without a Change Management Board), a non-IDENTITY CI may do more harm than good.