ALTER INDEX (with REBUILD), if run several times both decreases and increases the average fragmentation for an index

Question

ALTER INDEX (with REBUILD), if run several times both decreases and increases the average fragmentation for the non-clustered index.  I would expect the average fragmentation to be 0 after the first run of ALTER INDEX.
 
Below is the sequence of events and results: 
Running the following SQL:

SELECT a.index_id, b.name, a.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(N'tmac'), OBJECT_ID(N'dbo.WS_Schedule_cur'), NULL, NULL, NULL ) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;  

produced: 

index_id   name                    avg_fragmentation_in_percent
1          PK_WS_Schedule_cur      14.0845070422535
15         WS_Schedule_cur_idx1    94.488188976378

I then ran SQL:

ALTER INDEX ALL ON dbo.WS_Schedule_cur REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

I then ran the ALTER INDEX, and the 1st SQL which produced:

index_id   name                    avg_fragmentation_in_percent
1          PK_WS_Schedule_cur      0
15         WS_Schedule_cur_idx1    42.8571428571429

Going from 14% to 0% for the PK is what I expected for both indexes. 
I am satisfied with going from 94% to 42% for the non-clustered index,
but why did I not get 0%?

Subsequent runs of the ALTER INDEX, and 1st SQL produced the following
percentages for the non-clustered index
which sometimes decreases and sometimes increases.  Why?

42.8571428571429
42.8571428571429
28.5714285714286
28.5714285714286 
21.4285714285714
21.4285714285714
35.7142857142857
28.5714285714286

Here is the DDL for the table, PK, and non-clustered index:

CREATE TABLE [dbo].[WS_Schedule_cur]( [Schedule_Id] [int] NOT NULL, [Schedule_Name] [varchar](30) NOT NULL, [Schedule_Owner] [varchar](4) NOT NULL, [Source_CA] [varchar](4) NOT NULL, [Sink_CA] [varchar](4) NOT NULL, [PSE] [varchar](6) NULL, [Product_Code] [tinyint] NULL, [Source] [varchar](30) NULL, [Sink] [varchar](30) NULL, [Flow] [char](8) NULL, [Schedule_Type] [varchar](20) NOT NULL, [Schedule_SubType] [int] NULL, [Schedule_Status] [varchar](12) NOT NULL, [Schedule_Start] [datetime] NOT NULL, [Schedule_End] [datetime] NOT NULL, [Time_Of_Last_Update] [datetime] NOT NULL, CONSTRAINT [PK_WS_Schedule_cur] PRIMARY KEY CLUSTERED ( [Schedule_Id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]) ON [PRIMARY]CREATE NONCLUSTERED INDEX [WS_Schedule_cur_idx1] ON [dbo].[WS_Schedule_cur] ( [Schedule_Name] ASC)INCLUDE ( [Schedule_Id],[Schedule_Status]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]

 

Answer 1

How big is the table in number of rows?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Subsequent runs of the ALTER INDEX, and 1st SQL produced the following
percentages for the non-clustered index
which sometimes decreases and sometimes increases.  Why?

I am giving U Brent Ozar's example

Lets pretend that your database is a phone book organized by last name, first name.
As people move into your city, we have to add them to the phone book.  Ideally, each page has some empty space, and we govern that with the fill factor.  When SQL Server rebuilds indexes, it uses the fill factor to decide how much free space to leave on each page.  If there’s not enough free space, SQL Server has to do some rearranging – but it can’t exactly shove a brand new page in the middle of the phone book.  The book’s already bound.  We’ll have to tack more blank pages onto the end.
Now ur scenarios ur rebuilding Index with fill factor ,what if new record comes at the time of rebuild it cannnot be inserted anywhere between empty pages so a new page is allocated for it now this new page has just one record and no more so total fragmentation increases,also this new page is out of order

Now after sometime you again rebuild so some records must have been inserted inot that page as it was empty so Fragmentation decreases

FYR:http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 3

Check the page count for those indexes. It depends on the page count.

---

Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

Answer 4

Hello,

Just as RAjukiran post above,please check the page accout firstly. Generally, we should not be concerned with fragmentation levels of indexes with less than 1,000 pages.
The following thread is about same issue, please refer to:
http://dba.stackexchange.com/questions/5365/why-is-my-database-still-fragmented-after-i-rebuilt-and-reindexed-everything

Regards,
Fanny Liu

If you have any feedback on our support, please click  here.

---

Fanny Liu
TechNet Community Support