What is fragmentation, the impact of fragmentation on Io, and what is physical order and what is logic

Today, someone asked me about the physical order and logical order, just like this topic.

Page fragmentation: the next page of the leaf page in the iam page is different from the next page in the page header.

Logical Order: The order in which the leaf pages of the index are formed through the previous page.

Physical Order: The order allocated in IAM

DROP TABLE dbo.t_indexgoCREATE TABLE t_index (id INT PRIMARY KEY ,VALUE CHAR(1000))GOINSERT INTO t_index VALUES(1,'1')INSERT INTO t_index VALUES(2,'2')INSERT INTO t_index VALUES(3,'3')INSERT INTO t_index VALUES(4,'4')INSERT INTO t_index VALUES(6,'6')INSERT INTO t_index VALUES(7,'7')INSERT INTO t_index VALUES(8,'8')INSERT INTO t_index VALUES(5,'5')DBCC IND (db_test,t_index,1)

Pagefid pagepid iamfid iampid objectid indexid partitionnumber partitionid iam_chain_type pagetype indexlevel nextpagefid nextpagepid prevpagefid prevpagepid
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 80 null 1298103665 1 1 72057594040549376 in-row data 10 null 0 0 0 0
1 78 1 80 1298103665 1 1 72057594040549376 in-row data 1 0 1 90 0 0
1 89 1 80 1298103665 1 1 72057594040549376 in-row data 2 1 0 0 0 0
1 90 1 80 1298103665 1 72057594040549376 in-row data 1 0 0 1 78

Then their allocation in IAM is:

Iam: single page allocations @ 0x00000000118ea08e

Slot 0 = (1: 78) slot 1 = (1: 89) slot 2 = (1: 90)
Slot 3 = (0: 0) slot 4 = (0: 0) slot 5 = (0: 0)
Slot 6 = (0: 0) slot 7 = (0: 0)

Iam: extent alloc status slot 1 @ 0x00000000118ea0c2

(1:0)-(1: 896) = not allocated

If you can see that there are no fragments in your eyes, you can refer to the definition of the fragments as if there is an 89 index page in the middle of two subpages.

So what is the impact of fragmentation on Io performance? In fact, fragmentation does not have a great impact on the performance of precise search (for example, using a unique clustered index for search), but it has an impact on Area scanning:

1. if your shards are across different zones, the size of the allocation unit you set will be involved. If your allocation unit is as large as the partition, you may need to seek again and other problems, resulting in Io time change.

2. If your fragments are not across zones, the only problem is that your page density is not high enough, leading to performance problems.