ScottPletcher (8/29/2014)
Lynn Pettis (8/29/2014)
ScottPletcher (8/29/2014)
The page split / fragmentation concern is often overblown. Remember, one INSERT, but you may read the row 1000, 10K, 100K+(?) or more times. Particularly given the availability of partitions, reorganizations, online rebuilds, etc., some fragmentation can be dealt with far better than using the wrong clus key just to reduce frag on the single INSERT of each row while drastically harming the performance of the vast majority of future SELECTs.Until you find yourself in that position. I haven't but I have talked with someone who was in that exact environment and defragging a highly fragmented clustered index would basically shutdown the mirroring to the DR site which is over a slow WAN connection.
You have to work with what you have and make the systems work under less than optimum conditions.
How did it get "highly" fragmented? For a very large table, that would take a very large number of "bad" (mid-page) page splits, which would not occur with a reasonable clustering key, even if it wasn't universally ascending.
Never asked for the details. What made sense as a clustered index for data access would fragment the index rapidly affecting system performance. Defragging the index would essentially shutdown the mirroring to the DR site.
The choice of a clustering index is more than data access it is also dependent on the environment. In this case a very narrow ever increasing clustered index was the best choice as it kept the index from fragmenting.