SQL Server Fragmentation for the table having less than 24 data pages

Hi Friends,

Here I am going to explain a problem which is asked by one of my friend.

Problem: Let’s run the below query:

USE master
go
create database PRINCE
go
USE PRINCE
go
create table xtprince
(
    id int identity(1,1),
    name char(8000)
)
go
insert into xtprince values('SQLGEEKS')
go 8
create clustered index IX_CLUS_xtprince on xtprince(id)

Here I have created a table xtprince, where one data page will contain one record only. Here this table contain 8 records i.e. 8 data pages. After inserting the records I have created a clustered index IX_CLUS_xtprince on the table.

Now when I run the below DMV to check the avg_fragmentation_in_percent then it is showing value 37.5 i.e. 37.5% SQL Server fragmentation is there. Why?

USE PRINCE
go
select avg_fragmentation_in_percent,avg_record_size_in_bytes,index_level,
       page_count,database_id,avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(DB_ID('PRINCE'),OBJECT_ID('xtprince'),1,NULL,'DETAILED')

1_SQL_Server_Fragmentation_for_the_table_having_less_than_24_data_pages

Explanation:  Actually this is due to the internal architecture of SQL Server. When we create any table and insert data then first 8 pages allocates from mixed extends. After that SQL allocates pages from dedicated extent as shown below:

USE PRINCE
GO
dbcc extentinfo('prince','xtprince')

2_SQL_Server_Fragmentation_for_the_table_having_less_than_24_data_pages

Here mixed allocation will convert to dedicated extent allocation only if we have more than 24 data pages and we rebuild the index as shown below:

USE PRINCE
go
insert into xtprince values('SQLGEEKS')
go 17
ALTER INDEX [IX_CLUS_xtprince] ON [dbo].[xtprince] REBUILD
go
select avg_fragmentation_in_percent,avg_record_size_in_bytes,index_level,
       page_count,database_id,avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(DB_ID('PRINCE'),OBJECT_ID('xtprince'),1,NULL,'DETAILED')
go
dbcc extentinfo('prince','xtprince')

3_SQL_Server_Fragmentation_for_the_table_having_less_than_24_data_pages

From above output it is clear that now the avg_fragmentation_in_percentage is 0. So when you implement the Index rebuilding to remove fragmentation then also consider that the total number of data pages for that object should be greater than 24.

 

Regards

Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

2 Comments on “SQL Server Fragmentation for the table having less than 24 data pages”

  1. Hallo Prince,

    with deepest respect to your work for your blog but..

    1. if you take infos from other sql experts (in this case it is Kalen Delany) you should consider it by using a reference to the dedicated article
    http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/23/328.aspx

    2. If you try to reproduce the example you should get deeper into detail.

    Extract from Kalen’s great article:
    “I found out that the algorithm has changed. In SQL Server 2005, single page allocation is only disabled for the leaf level of the clustered index (the actual table data) if the table has least 3 extents (24 pages). Allocations to the upper levels use a different allocator, and if there are less than 24 pages for the upper index levels, they will also use single page allocations. So in SQL Server 2005, I need at least 24 data pages to remove the single page allocation for the data.”

    So if you add new records into your example you will have new mixed extens for the b-tree again. This behaviour will change if you have at least 3 extends in the B-Tree.

    Your example (including the screenshot after REBUILD) may give a wrong interpretation for readers.

  2. Hi Rick,

    Good to see you here! As you mention “This behaviour will change if you have at least 3 extends in the B-Tree.” i already mention in my blog “Here mixed allocation will convert to dedicated extent allocation only if we have more than 24 data pages and we rebuild the index.”

Leave a Reply

Your email address will not be published. Required fields are marked *