Cannot decrease logical fragmentation on an index

  • I have just updated a maintainance script that runs once a week and either reorganises or rebuilds indexes depending on the logical fragmentation percentage.

    My script compares the logical fragmentation beforehand and afterwards so that I can see if its made a difference.

    When I run this script on the dev box however I see that in nearly all cases the logical fragmentation does not decrease.

    I picked one table from the list in particular that has two indexes based on it.

    The clustered index has a logical fragmentation of 66.6 %, the non-clustered is 0%.

    The fill factor is set to 100% (at the moment it will be changed later)

    However whenever I run any of the following statements







    (OR the same code with the non-clustered index)

    It makes no difference whatsoever to the fragmentation level and the details always stay the same

    (from DBCC SHOWCONTIG / sys.dm_db_index_physical_stats shows the same figures)




    No errors are reported when I run the commands and I have the correct privelleges to do so.

    When I do a select from sys.indexes I can see that the fillfactor has changed from 100 to 70 (or vice versa) so the commands are running.

    So the question is what is keeping the fragmentation at 66.6% and is there anything that can be done to reduce it. Is there some other

    issue such as hard disk fragmentation that is causing the problem and preventing the desired solution from running?

    Thanks in advance for any help.

  • How many rows are in the tables? I've seen this when the number of rows is very low. Other than that, rebuilding the index usually changes the fragmentation percentage.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • There are 2347 rows in this table.

    Looking at some of the other tables that no change in the frag % it does seems that the majority are all

    in the 50 to 4000 rows in table range.

    The largest table reported had 14732 rows in it and the frag % was in a range where the script was trying to do a re-organise to de-frag it with no change at all. However when I manually did a rebuild on the index instead it did reduce the fragmentation. However trying to do the same (opposite method to what the script was trying to do) with the other tables reported doesn't seem to work.

    Currently I am trying to do a reorganise when the frag % is > 20 and < 50

    and a rebuild when its > 50.

  • It doesn't surprise me to see it in the tables with 50 rows. I ran into the same issue with tables that had less than ~200 rows.

    The others... I'm not sure. It should work. I double checked the behavior on a couple of tables just now. Any other information on the system? Have you run update statistics on it recently?

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I am running sp_updatestats at the end of the proc.

    I was thinking that as its on the dev box there might be an issue with the actual hard drive being fragmented but I don't know enough about the issue.

    here is the proc code maybe you can see something I am doing wrong:

    ALTER PROCEDURE usp_sql_jobboard_index_stats_maintainance




    DECLARE @ReOrganiseLvl int,

    @RebuildLvl int,

    @DEBUG bit

    SET @DEBUG = 1

    --For small fragmentation we try a re-organise for larger fragmentation we do a rebuild

    SET @ReOrganiseLvl = 10 --the frag percent level which a re-organise is carried out

    SET @RebuildLvl= 25 --the frag pecent level which a rebuild is carried out - Must be higher than the level for re-organise

    IF @RebuildLvl <= @ReOrganiseLvl


    PRINT 'You must set the rebuild percent level to be higher than the reorganise level.'



    DECLARE @tableName varchar(255),

    @Object_id int,

    @Index_id int,

    @IndexName varchar(255),

    @Frag decimal,

    @AfterDeFrag decimal,

    @Method varchar(10),

    @ErrorMsg varchar(500),

    @SQL varchar(1000),

    @ERROR bit

    DECLARE @INDEXES TABLE(tableName varchar(255), Object_id int, Index_id int, IndexName varchar(255), frag decimal, AfterDeFrag decimal, Method varchar(10), ErrorMsg varchar(500))


    (tableName, Object_id, Index_id, IndexName, Frag)

    SELECTobject_name(d.object_id), d.object_id, d.index_id, as IndexName, avg_fragmentation_in_percent

    FROMsys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) as d

    JOINsys.indexes as i ON i.object_id = d.object_id and d.index_id = i.index_id

    WHERE d.Index_Type_Desc <> 'HEAP' AND

    i.Name is not null AND

    object_name(d.object_id) LIKE 'tbl_%' AND

    avg_fragmentation_in_percent >= @ReOrganiseLvl

    ORDER BY object_name(d.object_id)


    SELECTtableName, Object_id, Index_id, IndexName, Frag


    WHEREAfterDeFrag IS NULL

    ORDER BY tableName






    INTO @tableName, @Object_id, @Index_id, @IndexName, @Frag


    SELECT @ERROR = 0, @ErrorMsg = ''

    IF @FRAG < @RebuildLvl

    BEGIN --do a defrag/re-organise

    SET @Method = 'REORGANIZE'

    SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON dbo.' + @TableName + ' REORGANIZE'


    ELSE --do a rebuild/reindex


    SET @Method = 'REBUILD'

    SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON dbo.' + @TableName + ' REBUILD'


    IF @DEBUG = 1


    PRINT @Method + ' Index: ' + @IndexName + ' on table: ' + @TableName



    --now run the reindex / reorganise in a try catch


    EXEC (@SQL)



    SELECT @ERROR = 1, @ErrorMsg = CAST(ERROR_NUMBER() as varchar) + ' - ' + ERROR_MESSAGE()

    IF @DEBUG = 1

    PRINT 'ERRORED ' + @ErrorMsg


    --if it didn't error lets see if it made the slightest bit of difference

    IF @ERROR = 0


    SELECT@AfterDeFrag = avg_fragmentation_in_percent

    FROMsys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL)

    WHEREobject_id = @Object_id AND

    index_id = @Index_id AND

    alloc_unit_type_desc = 'IN_ROW_DATA' --ignore out of row blob data



    SELECT @AfterDeFrag = @Frag --as it errored no change in this

    --update table


    SETMethod = @Method,

    AfterDeFrag = @AfterDeFrag,

    ErrorMsg = @ErrorMsg

    WHEREObject_ID = @Object_ID AND

    Index_ID = @Index_id






    ORDER BY TableName

    --now update statistics

    EXEC sp_updatestats



  • How many pages do the tables have?

    Re org doesn't touch the non-leaf levels of the index, whereas rebuild does. That could be the cause of some of your differences.

    Oh, and btw, you don't need to update the stats of any index that's been rebuilt. Stats are updated during the rebuild. Re org doesn't update stats.

    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 page count ranges from 2 to 77.

    It seems that those indexes that have pages > 20 (roughly) are having re-organises carried out against them whilst the indexes with smaller no of pages and higher logical frag are having rebuilds.

  • I have just asked someone to take a look at the phyisical hard drive that the dev server is on and they

    say it reports 97% fragmentation which isn't good. We are going to defrag the box over the weekend and then on Monday I will re-run the script to see if it makes any difference.

  • The tech lab rats say they have defraged the hard disk and its down to 0%.

    However I ran the script again and it made no difference. I picked one table in particular that had 83% fragmentation (just over 1000 rows of data) and manually ran ALTER INDEX statements for REBUILD and REORGANIZE which made no difference whatsoever.

    However I then ran the older DBCC DBREINDEX command and it actually did decrease down to 63% which I don't understand why a) The DBCC worked when the ALTER index didnt. Its SQL 2005 with correct compatibility mode on. b) Also why the DBCC worked but only slightly.

    I changed the script to use DEFRAG and DBREINDEX instead of ALTERS and on a small percentage of the tables it made a difference but on the majority they stayed the same like the previous code.

    However this shows that on those few tables the DBCC commands did make a difference in changing the fragmentation in some cases from 50%+ down to 0 whereas the ALTER INDEX reorg/rebuild had no effect. Why would that be?

    Also with the one table/index in particular I also tried dropping the index totally and rebuilding from scratch but it still reports high fragmentation straight away after re-creation.

    I have tried changing the fill factor, padding etc

    fill factor = 100% (page fullness 95%, frag=87%)

    fill factor = 70% (page fullness 69%, frag=63%)

    fill factor = 50% (page fullness 47%, frag=43%)

    I have tried changing it from a clustered to a non-clustered also as the key is on a numeric that has a few gaps in the sequence I tried putting it on a newly created sequential numeric with no gaps but that didn't make a difference either.

    Does anyone have any suggestions. Thanks

  • Make sure you have enough contiguous extents to build the new index in and that you are attempting one index at a time rather than using the ALL option. If there is not enough space in the database, it will not be able to recreate the index entirely contiguous.

    I assume we are talking extent/external fragmentation here since the page fullness seems to agree with the fill factors.

    You might check out this technet article. It is somewhat dated but I think most of it still applies.

    Amongst other things, it says:

    “Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. "

    One suggestion I heard was to create a file group with lots of empty space in it and re-create the index in that file group to give it plenty of contiguous extents to work with. If that works, you can try moving it back to the original filegroup afterwords.

    One question you need to ask is whether it really even matters: External fragmentation only affects range scans such as when you seek into a clustered index and then scan in all the records from the first value to the final value. And even then it has to do with IO Read-ahead buffering, so if the table were 100% in memory external fragmentation would essentially be a non-issue; the problem, of course is that that's rarely the case as you try to fit a 100GB database into 2GB of RAM.

    Let me know if any of this is helpful!

  • Oh. I should also mention that external fragmentation should be as high as 100% if your clustered table/index has less than 9 pages; SQL Server allocates the first 8 pages on mixed extents, which by definition will mean that you often have non-contiguous pages. I believe you can even have every page on a seperate non-contiguous extent which would result in 100% fragmentation as a normal situation. So, until you have enough rows to fill 8 pages, you can expect very high external fragmentation.

    And if the table is not a clustered index (has no clustered index/is a heap), external fragmentation has no meaning since there is no logical order to the data and there is no B-Tree. So fragmentation is essentially always 100% for tables with no clustered indexes (although the non-clustered indexes on non-clustered tables(heaps) can have their own fragmentation issues).

    So, to summarize: you are wasting your time if you try to defragment the logical page order or extent order of tables that are less than 9 pages in size. And also forget about trying to defragment tables that have no clustered index (although you can worry about any non-clustered indexes regardless of whether a clustered index exists on the table).

  • Very interesting information. I didn't know most of that and it explains a lot. Basically I am worrying

    about nothing in the majority of these cases then.

    Thanks for your input!

  • Not only is it futile to try and defrag such small tables, but the benefit you would get, even if you were able to do so, would be negligible.

    I would suggest that in your scripts you restrict the tables/indexes slated for defragmenting to having over, say 10 pages of data - and ignore the rest.

    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Hi,

    I even face the same issue while not able to remove fragmantation after usning alter index rebuild option.

    Moreover index pages are more than 10 in my case.

    Can anyone give some more information about this?????


    Amit Khanna

  • Microsoft has as whitepaper on this.

    "Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

    My recommendation would be to have a filter on a 1000 pages and report back if you have indexes of that size where you're not able to remove the fragmentation.

    I have a stored procedure that could help you with this.

    You could also use the script in Books Online.

    Ola Hallengren

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

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