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

  • 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]

     

    Friday, June 14, 2013 7:08 PM

Answers


  • 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

    • Proposed as answer by Fanny Liu Monday, June 17, 2013 3:10 AM
    • Marked as answer by Fanny Liu Friday, June 21, 2013 3:20 AM
    Saturday, June 15, 2013 5:59 AM
  • 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

    • Marked as answer by Fanny Liu Friday, June 21, 2013 3:20 AM
    Monday, June 17, 2013 5:51 AM

All replies

  • How big is the table in number of rows?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 14, 2013 9:54 PM

  • 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

    • Proposed as answer by Fanny Liu Monday, June 17, 2013 3:10 AM
    • Marked as answer by Fanny Liu Friday, June 21, 2013 3:20 AM
    Saturday, June 15, 2013 5:59 AM
  • 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.

    • Proposed as answer by Fanny Liu Monday, June 17, 2013 3:10 AM
    Saturday, June 15, 2013 6:35 AM
  • 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

    • Marked as answer by Fanny Liu Friday, June 21, 2013 3:20 AM
    Monday, June 17, 2013 5:51 AM