The pages of small indexes are stored on mixed extents.

Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index

In general, fragmentation on small indexes is often not controllable.

Hi Sankar,

In our case I am only getting 9% Fragmentation only. Looking at the size of table and details we are populating, I am not sure we can reduce it even further sometime.

Please advise, I feel that there is some disconnect from my side.

I am using SQL Server 2008 R2.

Usually, Fragmentation will be caused by 3 reasons.

1. Because of Page Splits. (If the newly inserted records fall in between existing set of records).
2. because of allocation strataegy sql internall follow while allocating pages for table/index. SQL Server allocates pages for a new table or index from mixed extents. When the table or index grows to eight pages, all future allocations use uniform extents. So, if the no of pages crosses 8, then only it can allocate Uniform extent, otherwise it will allocate Mixed extent..
3. Because of assigning Next Page to another leaf level in index. Since Index Non-Leaf Levels also increase along with Leaf Level data, suppose if Page 1000 is assigned to Level 0, if in Level 1, a new page needs to be created, then Storage Engine will assign 1001 to Level1. This will cause the Fragmentation in Level0. Assigning a new page from another extent for Level 1 makes the disk move forward and back, which delays the write operations.

Out of all these reasons, re-building index will remove the fragmentation caused by page splits only....But here it is not caused by pagesplits. its because of the 2nd and 3 rd reasons i specified above. So, rebuilding index will not be useful.....

In Detail,

A Fragment Page is nothing but a page, where it is out-of-order with its next physical page. Suppose If an page’s with ID 1000 is allocated to a table, if the next physical page allocated to the table should be 1001, If the next physical page allocated to the table is other than 1000, then that page will be considered as a Fragment.

In one way, A Fragment can also be indicated as a part. In our example, A table CI has 10 fragments and 1000 pages in Leaf Level. So, we can say, 1000 pages are occupied across 10 parts (Fragments). Each of this part will have its pages in sequential order.

We can implement our own version also to find out No of Fragments..... You can see the execution of below script will gives the list of fragments and their ending pages and next fragment starting pages.

create table tblNumbers
(
    Id int identity(1,1) primary key,
    Num int
)

;with N as
(
    select 0 as Num union all select 0 union all select 0 union all select 0 union all select 0 union all
    select 0 union all select 0 union all select 0 union all select 0 union all select 0
),
Numbers as
(
    select ROW_NUMBER() over (Order by (select 1)) as rn from N N1,N N2,N N3,N N4,N N5, N N6
)
insert into tblNumbers
select rn from Numbers 

SELECT page_count, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tblnumbers'),NULL, NULL, 'detailed') 
where index_id = 1 and index_level = 0

CREATE TABLE sp_tablepages
(
ID int identity(1,1) primary key,
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int
)

TRUNCATE TABLE sp_tablepages;
INSERT INTO sp_tablepages
EXEC ('DBCC IND (test, tblNumbers, 1)');  

delete from sp_tablepages where IndexLevel <> 0 or IndexLevel is null


;with cte as
(
    select PagePID,ROW_NUMBER() over (Order by ID) as ID from sp_tablepages
)
select *
from cte S
inner join cte  SN on SN.ID  = S.ID + 1 and SN.PagePID <> S.PagePID + 1

SELECT page_count, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tblnumbers'),
     NULL, NULL, 'detailed') where index_id = 1 and index_level = 0

Above script returns the fragments in the table and returns each fragment end page and next fragment start page. If you observe that result and sptablepages table rows values, u can find that, for first few fragments, next pages are not allocated to same table, But they belongs to first 8 pages. Where as Remaining fragments next pages are in sptablepages, but they assigned to different index level.

So, finally conclusion is as this fragmentation is not because of page splits, rebuilding the index will not be useful........ .

This is the article i wrote based on this..

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/what-exactly-is-fragmentcount

Hi Nupur, There seems to be an issue (unconfirmed yet) with the way fragmentation is calculated in tempdb on SQL Server 2005 and its reporting slightly higher number than in SQL Server 2008 R2. But that is unrelated to the actual question. Thanks for participating.

Hi Sankar,

I added one more column to your DMV.

SELECT 'After Index Fragmentation' [When] , DB_NAME ( database_id )[Database Name] , 
        OBJECT_NAME ( object_id ) [Object Name] ,
        index_id ,index_level ,
        avg_fragmentation_in_percent ,
        fragment_count ,
        record_count,
        page_count FROM sys.dm_db_index_physical_stats
        ( DB_ID ( N'tempdb' ), OBJECT_ID ( N'dbo.Customer'), NULL, NULL ,'DETAILED');
GO

It uses total of 28 pages.

First 8 are single allocation as they will find some mixed extended to populate for first 8 pages. After that uniformed extend will be assigned to pages.

Which leads to talk about remaining 28-8 = 20 pages. Each extend will have 8 pages. That leads we will need 3 extends to contain 20 pages. 2 of this extends will be occupied completely but the last extend will have only 4 pages. That means it is half empty.

Looking at this - total leaf level pages are 27 (as one of them will be root level pages). 4/27 = 0.1481481481481481 (resulting 14.81% fragmentations).

If you check the following image which matches the same.

Additional information can be also retrieved from the http://sqlblog.com/blogs/kalendelaney/archive/2006/10/23/328.aspx

DBCC EXTENTINFO(TempDb, Customer, -1)
GO

will give you similar results as well.

Thanks for wonderful question.

For smaller tables, as the data is written in mixed extents, fragmentation will be higher. Also, for smaller table, SQL will rather write quickly to any space available as I/O will not be so high when retrieving, that it does not try very hard to find a page within the same extent that can take the record.

From my point of view the main issue is the total number of pages reserved for the table and not the mixed extents allocation method all by itself:

As long as the total number of pages allocated or reserved by this allocation unit after the index rebuild is less than 34 (at least for my system 9.00.4053.00, Build 2600: Service Pack 3), SQL Server will keep the first eight pages in mixed extents allocation method.

As soon as there are 34 pages allocated or reserved it will move 7 of the mixed extents allocation pages into the full 8-pages extent, keeping one 8k page for the Index Allocation Map (IAM).

To describe the process of filling the table (disclaimer: based on the numbers I got on my system):

The first 82 rows will be inserted into a single page extent. The 83rd value requires a second page, which will required another page for the IAM. So we have three mixed page extents. After inserting row 568, we have 8 single pages in mixed extents allocation (including one page for IAM).

With row 569 we open the first 8-pages extent. After the insert of row 2775 we have 8 pages in mixed extents allocation and three full 8-pages extents and 33 pages in total.

If we rebuild the index at this point, we'll still end up with the first 8 pages in mixed extents allocation. The first seven mixed pages holding data will still be fragmented, leading to fragmentation (16.6% in this case).

But as soon as we insert the 2776th row, those seven pages were moved into the 8-pages extent. Now we don't have any data left in the mixed extents allocation and therefore no more fragmentation.

The same behavior can be demonstrated by keeping the number of inserted rows at 2000 and change the column type for the Customer table to CHAR() instead of VARCHAR(). This will also lead to a number of allocated pages higher than 33 causing the index rebuild to have no more fragmentation.

"To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents."

(Reference BOL. So the main cause of fragmentation is that initial page allocation is using mixed extent (single page allocations), and we can use DBCC EXTENTINFO to confirm this using the following script:

DBCC EXTENTINFO(tempdb, Customer, 1)

And the result was:

file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1           151         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           174         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           175         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           200         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           201         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           202         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           203         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           204         1           1           21575115    1           1                576460752590995456   In-row data          0x6000000000000000
1           208         8           8           21575115    1           1                576460752590995456   In-row data          0x4040404040404040
1           216         7           8           21575115    1           1                576460752590995456   In-row data          0x4040404040404000

It is clear from the result that the first 8 page allocations was mixed (single page allocation), and after that it is switched to uniform. But as I already mentioned BOL states "If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents". So let us put things under test by dropping PRIMARY KEY constraint (hence the clustered index), and create it again using the following script:

USE tempdb
GO

IF EXISTS(SELECT *
            FROM sys.indexes
           WHERE object_id = OBJECT_ID(N'dbo.Customer')
             AND name = N'PK_Customer')
   ALTER TABLE dbo.Customer
      DROP CONSTRAINT PK_Customer;
GO

ALTER TABLE dbo.Customer
   ADD CONSTRAINT PK_Customer
      PRIMARY KEY CLUSTERED(CustomerID);
GO

And if we check the index fragmentation using dmdbindexphysicalstats we notice no change in the avgfragmentationin_percent, and DBCC EXTENTINFO returns the same result (still single page allocations for the first 8 pages). So we left with no choice but to disable single page allocations entirely to confirm that it is the main cause of fragmentation, and to achieve this we can use trace flag 1118 using the following:

DBCC TRACEON(1118, -1)

IF EXISTS(SELECT *
            FROM sys.indexes
           WHERE object_id = OBJECT_ID(N'dbo.Customer')
             AND name = N'PK_Customer')
   ALTER TABLE dbo.Customer
      DROP CONSTRAINT PK_Customer;
GO

ALTER TABLE dbo.Customer
   ADD CONSTRAINT PK_Customer
      PRIMARY KEY CLUSTERED(CustomerID);
GO

And if we check the index fragmentation using dmdbindexphysicalstats we got different results. On my machine I got this:

When                      Database Name Object Name index_id    index_level avg_fragmentation_in_percent fragment_count       record_count
------------------------- ------------- ----------- ----------- ----------- ---------------------------- -------------------- --------------------
After Index Fragmentation tempdb        Customer    1           0           4.54545454545455             2                    2000
After Index Fragmentation tempdb        Customer    1           1           0                            1                    22

And DBCC EXTENTINFO confirms that all page allocations were uniform. On my machine I got this:

file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1           320         1           8           85575343    1           1                864691128906547200   In-row data          0x4000000000000000
1           328         8           8           85575343    1           1                864691128906547200   In-row data          0x4040404040404040
1           336         8           8           85575343    1           1                864691128906547200   In-row data          0x4040404040404040
1           344         5           8           85575343    1           1                864691128906547200   In-row data          0x4040404040000000
1           360         1           8           85575343    1           1                864691128906547200   In-row data          0x4000000000000000

To sum up the reason behind fragmentation was single page allocations.

basically for small table's performance impact on fragmentation is undetectable cause 8 page allocation would be from mixed extents which is not at all guaranteed to be in sequence, it could be anywhere in database files and take my words, rebuilding index can't change this behavior.

in small table, those mixed pages weight a lot during fragmentation calculations and that is why rebuilding index may not reduce fragmentation and believe me that fragmentation would not affect your query's performance.

BTW, I have seen many cases in small tables that after rebuilding index, fragmentation increases rather than decreases.

Robert, a SQL Server developer is surprised to see high index fragmentation on a table that has a clustered index. The table has only few thousand records and even after repeated attempts to rebuild the clustered index, the index fragmentation seems to be quite high. Robert is perplexed about this and can you explain the behaviour?

Fragmentation is the existence of fragments of data on various data pages, where the pages are non-contiguous, either physically or by comparison to the logical order of the data.

SQL Server allocates one page at a time for the first 8 pages of data, after which a single extent of 8 pages is allocated each time more space is required (known as a uniform extent as all 8 pages are reserved for a single object). The single page allocations form part of mixed extents, in which up to 8 different objects can reserve/use a page.

Adding a clustered index, in theory, tells SQL Server that more than 8 pages are required therefore single page allocations are not needed. In practice however, this only applies where over 24 pages (3 extents) are required for the data. And as the leaf pages and upper index levels use different allocators, then we can have a scenario where the leaf pages use more than 3 extents and have uniform extents allocated, but the upper index levels are using single page allocations.

So for relatively small tables using a clustered index, the fragmentation may appear quite high as the upper index levels, if not the leaf pages also, will form part of mixed extents. Reorganising the index will not change the physical pages used, and rebuilding will simply reproduce the single page allocations leaving the fragmentation at a similar level to beforehand. The data will be rebuilt so that the logical ordering of the data matches the physical ordering, however the pages within mixed extents will not exist contiguously.