SQL Server index fragmentation is high after rebuilt

  • I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on these indexes is 493934 pages and size in MB is 3854.

  • Heaps?

    Autoshrink or manual shrink?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It's a clustered index. Not a heap table.

  • 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 strategy sql internal 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.

    you can also use commercial tool for defragment your SQL sever database like Redgate SQL Monitor, Lepide SQL Storage Manager etc

  • itsmemegamind (12/3/2012)


    you can also use commercial tool for defragment your SQL sever database like Redgate SQL Monitor, Lepide SQL Storage Manager etc

    SQL Monitor does not defragment the DB and all that Storage Manager will do is schedule the built-in index rebuild or reorg operations.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The strange thing is that one of the tables in particular, the one which contains over 400k pages and is 3.8GB in size doesn't grow all that much. There was no data written to it in the past week, the fill factor is 0 and yet the fragmentation is over 95% after recent rebuilt. It was rebuilt on 11/30 and no data appended or updated since that time.

  • Luk (12/3/2012)


    the fill factor is 0 and yet the fragmentation is over 95% after recent rebuilt.

    the index rebuild works on the basis of the fill factor and in this case i dont think fill factor will help here. can you set the fillfactor 80 or 75 so that you can ge the benefit of index rebuild and disk mangement (with 0 or 100 fillfactor ..more space is required and IO also get affected)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (12/5/2012)


    the index rebuild works on the basis of the fill factor and in this case i dont think fill factor will help here.

    What are you trying to say here?

    can you set the fillfactor 80 or 75 so that you can ge the benefit of index rebuild and disk mangement

    Index rebuilds don't require any specific fill factor. Simply setting fill factor to a lower value with no analysis and no investigation would not be a good thing to do.

    (with 0 or 100 fillfactor ..more space is required and IO also get affected)

    Actually no, with 0 fill factor less space is required.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bhuvnesh (12/5/2012)


    with 0 or 100 fillfactor ..more space is required and IO also get affected)

    You cannot specify a fillfactor of 0 in the rebuild command, any attempt to do so results in the following message

    SQL Server database engine


    Msg 129, Level 15, State 1, Line 1

    Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100.

    Now, the fillfactor setting in sp_configure has a default value of 0, this equates to a value of 100.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • My guess is that when the db was setup nobody played with fill factor settings and it shows up as 0 on the fragmentation report that I now run once a week. I'll try and restore this db to another box and play with it there. I have limited resources and can't be running stuff in prod during the day.

  • You still haven't answered my question about shrinks.

    Fill factor is not going to affect how the index rebuild works. It'll affect how the index fragments after that, but not the rebuild operation itself.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • We don't shrink this db at all.

  • OK, so we're running the automated script that determines which indexes need to be rebuild and tries rebuilding them. For some reason the script logs to the table that the rebuild has taken place but it doesn't do anything. Something to look into I guess. Another question I have is SQL fragementation report. I don't think I should be expecting this situation but it happens. I have a table with a PK clustered index. The primary key has only one field which is a bigint. Now, when I run a fragmentation report that particular PK appears twice because it's allocated to both IN_ROW_DATA and LOB_DATA. Why would it be allocated to LOB_DATA?

  • Luk (12/11/2012)


    Now, when I run a fragmentation report that particular PK appears twice because it's allocated to both IN_ROW_DATA and LOB_DATA. Why would it be allocated to LOB_DATA?

    The table has a LOB column in it somewhere. Varchar(max), nvarchar(max), varbinary(max), XML or one of the old SQL 2000 LOB types.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yes, that's correct, but the index itselft is composed only of that bigint.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply