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')
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')
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')
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 Twitter | Follow me on FaceBook
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.
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.”