How does page split result in index fragmentation?

Question

I am trying to understand the reason behind index fragmentation.

Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that page can fit only 5 rows.

Page1: 1, 2, 3, 4, 5

Page2: 6, 7, 8, 10, 11

Page3: 12, 13, 14

Question 1: Now when there is an insert of value 9, then 9 added either to page2 (page split) or to a new page?

Question 2: How does this result in fragmentation? Is it because of empty spaces in the page due to page splits?

Question 3: Does clustered index always store data physically ordered in sequential pages or is it ordered logically and does this have any contribution to fragmentation?

Answer 1

Now when there is an insert of value 9, then 9 added either to page2 (page split) or to a new page?

One or the other. It doesn't really matter. Usually the splitting value isn't right in the middle and it must go on the old page or must go on the new page.

Page2: 6, 7, 8, 
Page4: 9, 10, 11
Page3: 12, 13, 14

How does this result in fragmentation? Is it because of empty spaces in the page due to page splits?

There's lots of different things that people call 'fragmentation'. Some people include half-empty pages as fragmentation per-se. But the more common meaning is that Page4, the new page, was allocated on the current open extent, or a new extent, while the existing pages were contiguous in one or two existing extents.

Does clustered index always store data physically ordered in sequential pages or is it ordered logically and does this have any contribution to fragmentation?

Only logically. When the logical page chain jumps around between extents, that's a kind of fragmentation. But remember the "physical" order isn't really "physical"; it's just the location inside a file, which is mapped to a disk location by the file system, which is mapped to a truly physical disk location by the disk's firmware. This is why fragmentation on solid state storage is not normally a big deal.