Understanding Logical Fragmentation – Series 2

Post understanding of what is logical fragmentation, now I would use DBCC IND to calculate Logical Fragmentation and compare it with sys.dm_db_index_physical_stats dmv.

The formula to calculate Logical fragmentation is

Fragmentation % = (Fragment Counts – 1) / Page Counts * 100

There are 2 types of extents Mixed Extent and Uniform extent. Mixed Extents means each page of extent may belong to one or more number of objects. To rephrase it an extent may contain pages from objects amounting to a maximum of 8 objects or minimum of 2 objects.

In our case let us consider a table of 8 pages.

Case 1: Each page belongs to a different extents and each page is not physically next to pervious page.

In our case below each page in an extent belongs to a different object (by color code). So in this extent each page belongs to Objects like "A”, “B”, “C” etc.

Assume our object A contains 8 Pages and each of these 8 Pages are in different extents and are not consecutive.

NOTE: Last of Current Extent and First page of NEXT extents are considered as different fragments but as constituents of same fragment.

Number of Pages: 8, Number of Fragments: 8.

Fragmentation % = ((8 – 1) / 8 )* 100 = 87.5

Case 2: 4 Pages belong to Extent A and 4 Pages belong to different Extent and in each extent they are contiguous pages.

Number of Page: 8, Number of Fragments: 2 (Extent A and Extent B and assuming each pages in Extent are contiguous)

Fragmentation % = ((2-1) / 8 ) * 100 = 12.5%

In similar way we can calculate fragmentation based on Number of Fragments and Number of Pages.

The reason why we may have to reduce 1 from Fragment counts is if there is any object with only 1 Page them it would be only in 1 Fragment. So according to our formula

Fragmentation % = ( (1 – 1 ) / 1 ) * 100 = 0

Else even for 1 Page we would get fragmentation as 100% :). To avoid this one needs to deduct 1 from Fragment Counts.

Coming to an example in SQL Server, I have used below script to create and populate table:

use tempdb
go
create table TestFragmentation(Id int, ch char(900))
go
create index Ind_TestFragmentation on TestFragmentation(ch)
go
declare @i int
set @i = 1
while (@i < 11)
begin
    insert into TestFragmentation values(1,Replicate(@i,400))
    set @i = @i + 1
end
go
dbcc traceon(3604,-1)
go
declare @objid int
declare @dbid int
select @objid = object_id('TestFragmentation'), @dbid = db_id()
dbcc ind(@dbid,@objid,2)

From above example, exclude Page 114 as it is an IAM page and this index is of 2 levels

Root Page (Page Type = 2 and Index Level = 1) and Leaf level Page (Page Type = 2 and Index level  = 0)

Now according to formula:

Fragmentation % at Non Leaf Level (Root Page , Page ID = 174) =  ((1 – 1) / 1) * 100 = 0%

Fragmentation % at Leaf Level ( 3 Pages Page ID = 110, 78, 41) = ((3 – 1) / 3 ) * 100 = 66.67 %

To check our results run below query:

select * from sys.dm_db_index_physical_stats(2,2121058592,2,1,Default)

From above index_level = 1 is non leaf level and in this case root level and there is only 1 page and see avg_fragmentation_in_percent it is 0 where as for the first row where index_level = 0 it is 66.67 which equates to our formula of fragmentation.

To test it further we have added some more rows using below script and this has been executed 4 times

declare @i int
set @i = 1
while (@i < 11)
begin
    insert into TestFragmentation values(1,Replicate(@i,400))
    set @i = @i + 1
end

go 4

 

Now run this command:

declare @objid int
declare @dbid int
select @objid = object_id('TestFragmentation'), @dbid = db_id()
dbcc ind(@dbid,@objid,2)

 

Out of this select only pages with Index Level 0 and page Type = 2. If we look at columns PageID, Next page and Prev Page we see that all of them are in different fragments. 10 Pages in 10 different fragments.

Fragmentation % = ((10 – 1) / 10) * 100 = 90.

And running query:

select * from sys.dm_db_index_physical_stats(2,2121058592,2,1,Default)

See this it is same as our calculation..

Let us see affect of reindexing on this table with below command:

dbcc dbreindex('TestFragmentation',ind_TestFragmentation)

go

Post reindexing,

There are 7 pages in leaf level with 3 fragments thus

Fragmentation % = ((3 – 1) / 7 ) * 100 = 28.57.

Hope with these 2 posts would help in understanding Fragmentation to some extent if you have questions please feel free to send mail to gurucb@hotmail.com