A look at avg_fragmentation_in_percent
The value of this column helps to determine the External Fragmentation of both indexes and tables. When the fragmentation at leaf level of an index occurs, it is known as Logical Fragmentation. When the heap of the table is fragmented, it is known as Extent Fragmentation.
Logical Fragmentation indicates the mismatch of logical order of the index pages against the physical order. For example if one of the pages in the index which is 100, indicates that the next page is 95, the 95th page is logically out-of-order. But if the next page of the 100th page is 105, then the 105th page is considered in order. The pages that have been used for the index can be seen with the DBCC IND command. Let us test this with table we created. We shall start from the beginning.
Run queries 1 and 2. Check the column avg_fragmentation_in_percent in the result of query 2. This is what I got from the query:
|
Index_level |
avg_fragmentation_in_percent |
|
0 |
43.75 |
|
1 |
0 |
Normally, a value above 10 for avg_fragmentation_in_percent in the leaf level of an index is considered logically fragmented. Run the below query and see how pages are logically ordered.
— query 5
DBCC IND (‘TestDatabase’, ‘TestTable’, 2)
|
PagePID |
Index_level |
NextPagePID |
|
…. |
…. |
…. |
|
185 |
0 |
194 |
|
194 |
0 |
119 |
|
119 |
0 |
127 |
|
…. |
…. |
…. |
The above table shows part of the result of DBCC IND. Note that you need to pass the database name as the first parameter and table name as the second parameter. The third parameter accepts values like -2 (for all IAMs), -1 (for all IAMs, data pages, and index pages), 0 (for all IAMs and data pages) or index id. Look at the 194th page. The next page of 194th is 119; hence 119th page is logically out-of-order. Run query 4 again to rebuild the index. Check the results of query 2 and 5. Here is the result of mine.
|
Index_level |
avg_fragmentation_in_percent |
|
0 |
6.25 |
|
1 |
0 |
|
PagePID |
Index_level |
NextPagePID |
|
…. |
…. |
…. |
|
197 |
0 |
198 |
|
198 |
0 |
199 |
|
199 |
0 |
200 |
|
…. |
…. |
…. |
See the results of DBCC IND. Pages in the index are reorganized and the logical order matches the physical order. Column avg_fragmentation_in_percent shows the value as 6.25 which is a good indication that the index is not logically fragmented.
Is Internal Fragmentation always unhealthy?
No. There are situations where it comes in handy by avoiding External Fragmentation (or Logical Fragmentation). For example, if an index is Internally Fragmented, pages have room for new intermediate records; resulting in no page splits for new records that increases the number of pages out-of-order. Let us take a simple example. Run queries 1 and 4 again. Now run query 2. The result of my query 2 is like below:
|
Index_level |
avg_fragmentation_in_percent |
Page_count |
avg_page_space_used_in_percent |
|
0 |
6.25 |
16 |
92.4280331109464 |
|
1 |
0 |
1 |
92.1793921423277 |
Now run query 3 (Update query). Check the fragmentation again by running query 2:
|
Index_level |
avg_fragmentation_in_percent |
Page_count |
avg_page_space_used_in_percent |
|
0 |
6.25 |
16 |
47.7074128984433 |
|
1 |
0 |
1 |
92.1793921423277 |
Now the index is Internally Fragmented. Note that the values of index keys. They are something like; AAA…, CCC…, EEE. Assume that we need to insert a record such as BBB… which requires being set between AAA… and CCC… in the index. If the page that contains the last value of AAA (let’s say the page number is 100 and the next one is 101) has enough space, SQL Server does not need to split the page and move half of records to new page. If it has no space, splitting is required and half of the records in 100th page will be moved to new pages (let’s say it is 120). This changes the logical order of pages. Now the logical order of the index is, 100->120->101 which indicates that the 101st page is out-of-order. If this happens in large scale, you will see heavy logical fragmentation. But this can be avoided if pages have enough of space.
Run below query to insert some records. It inserts records like BBB, DDD.
— query 6
DECLARE @a INT
SET @a = 66
WHILE (@a < 86)
BEGIN
INSERT INTO TestTable ([Value])
SELECT REPLICATE(CHAR(@a), 445) + CONVERT(VARCHAR(10), @a)
SET @a = @a + 2
END
GO
Check the fragmentation again by running query 2. You will see that still the avg_fragmentation_in_percent is 6.25. The index is therefore not Logically Fragmented because pages had enough spaces for new records. Let’s do the same, but before inserting new records, let’s rebuild the index by removing Internal Fragmentation.
Run queries 1 and 4 again. Now the values of avg_fragmentation_in_percent and avg_page_space_used_in_percent are 6.25 and 92.4280331109464 respectively. Update the table by running query 3. Rebuild the index again by running query 4. Check the fragmentation again. It may show that the index is logically fragmented. In my result, it says the logical fragmentation is 87.5. Run query 5 to see page numbers. Check leaf level pages (Index level 0). In my case, 119th page is out-of-order. Rebuild the index again by running query 4. Check the result with query 2 and 5. Now the logical fragmentation is 12.5 but leaf levels pages are not out-of-order.
Let us insert a record by running query 6. Note that the current internal fragmentation level is 95.4395231035335 which indicates no internal fragmentation, hence no space for intermediate records, it might require page split for new records that causes logical fragmentation. Run the query 6 and check with both queries 2 and 5. My result show that the index is logically fragmented. The value of the fragmentation is 93.3333333333333. If you check the result of query 5 (DBCC IND), you will see many out-of-order pages because of page splits. This proves that internal fragmentation helps to avoid logical fragmentation (but not always).