This is follow up question to @martin smith excellent answer https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

After that i have watched MCM video of Paul randal which shows why index fragmentation matters more in large scans only for huge tables..If i understood correctly, external/logical fragmentation occurs when logical continuity of pages differ from physical order in which they should be..

Suppose i have big table and identity as clustered key and no updates to clustered key and all pages are in logical order,does Sql guarantee all these pages lay in disk in close proximity...during first inserts it may block some space for this table,but after that many inserts may happen to other tables as well,so data might not be in close proximity...Is my understanding correct ?

  • 1
    Yes. That's why you see many people, Mr. Randal included, recommending you defragment indexes on an as-needed basis.
    – Hannah Vernon
    Commented May 26, 2015 at 3:22

1 Answer 1


That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most.

But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge problem. There are typically other matters that are more important to tune than trying desperately to avoid fragmentation.

  • 3
    I'd like to add that the performance cost of external fragmentation mostly applies to scans against spinning media due to seek time latency. It's not much of a concern with SSDs.
    – Dan Guzman
    Commented May 26, 2015 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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