Why Fragmentation Exists after INDEX REORGANIZE or REBUILD

Introduction

When we are working with Index fragmentation, a lot of query comes in mind. One of the questions that one of my friends asks me I would like to explain it and try to answer it. I think that lot of professional have the same type of query in mind.

Questions

As we all know that to see the Index defrag percent (from MS SQL 2005) we use

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL,NULL) 

But after defragment the index by using REORGANIZE or REBUILD we find some of the index is till fragmented > 66%. It is especially for Master tables. Where there are low numbers of records exists.

Why it occurs

In SQL Server, the page size is 8 KB. Extents are the basic unit. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

Extents are two types

·         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.

References: http://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx

When a new table is created MS SQL server does not know about how many rows will be entered in the table. When we add data MS SQL Server allocate a sing page of 8KB from mixed extents. Once this table has at-least 3 extents then the MS SQL Server will start issuing space from uniform extents and 1 extent at a time instead of a single page.

This dangerous that for a small number of records lots of space will be wasted from uniform extents.

When we calculate the fragmentation, these single page allocations drive the fragmentation level up.

What to do

That is the reason we shouldn't look at the fragmentation of the small tables.

MSFT documentation says:

Fragmentation will have an impact on the performance only if the number of pages in the table/index is around 1000 pages.

Hope you like it.

Posted by: MR. JOYDEEP DAS