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:

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?


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:


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:


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.



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