Index Fragmentation

  • Hi everyone,

    I am fighting with a SQL Server 2014 table [IR_RecordingMedia] (5.5GB data, 6.8GB Index, 11.6 million rows) that is running well, but my vendor for the software using the table says it needs to be reindexed due to results of the following query showing >98% avg_fragmentation_in_percent.  I have SSMS maintenance plans re-indexing this table and SSMS properties show <10% for this same IR_RecodingMedia table's indexes.  I have read a lot of articles, but most point to the issue being tables with low page count where reindex has no impact, but my table has 5,995 pages on the PK_IR_RecordingMedia clustered index as shown in query results.  Please provide any info on how I can lower the avg_fragmentation_in_percent value of this table's indexes or am I trying to acheive the impossible?  Thanks for any help!

    Select object_name(b.object_id) as Table_Name, D.name as index_name, A.index_type_desc,A.avg_fragmentation_in_percent, A.fragment_count, A.avg_fragment_size_in_pages, A.page_count
    From sys.dm_db_index_physical_stats
    (DB_ID(N' I3_IC'), NULL, NULL, NULL, 'LIMITED') A
    INNER JOIN sys.objects B ON A.object_id = B.object_id
    INNER JOIN sys.indexes C ON C.object_id = B.object_id AND C.index_id = A.index_id
    INNER JOIN sys.sysindexes D ON D.id = B.object_id AND D.indid = A.index_id
    ORDER BY A.avg_fragmentation_in_percent DESC


  • Are you rebuilding the index? A rebuild should result in reduced fragmentation.

    BTW, if you're not experiencing lots of scans, it's possible that you don't need to worry that much about fragmentation. If you're primarily getting point lookups on your data, fragmentation has minimal impact.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Grant, thanks for the reply!  Yes, I am using a rebuild indexes online in SQL Enterprise.   The IR_RecordingMedia table definitely has a lot of read/scans as many people in our operations query that table throughout the day using the vendor supplied call recording lookup application.

  • If you've done a rebuild, it shouldn't be fragmented any more. What command are you using to do that rebuild?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I've setup the reindex jobs in SSMS Maintenance plans and also tried to reindex with right click table indexes and Reindex all so far.  The individual indexes frag % drops when viewed in SSMS right click index-> properties, but no joy in the lowering of % reported in the query from my original post.  I've also made sure to update statistics before rechecking the query from my original post.  Thanks

  • jarmendadmin - Monday, December 4, 2017 10:49 AM

    Hi everyone,

    I am fighting with a SQL Server 2014 table [IR_RecordingMedia] (5.5GB data, 6.8GB Index, 11.6 million rows) that is running well, but my vendor for the software using the table says it needs to be reindexed due to results of the following query showing >98% avg_fragmentation_in_percent.  I have SSMS maintenance plans re-indexing this table and SSMS properties show <10% for this same IR_RecodingMedia table's indexes.  I have read a lot of articles, but most point to the issue being tables with low page count where reindex has no impact, but my table has 5,995 pages on the PK_IR_RecordingMedia clustered index as shown in query results.  Please provide any info on how I can lower the avg_fragmentation_in_percent value of this table's indexes or am I trying to acheive the impossible?  Thanks for any help!

    Select object_name(b.object_id) as Table_Name, D.name as index_name, A.index_type_desc,A.avg_fragmentation_in_percent, A.fragment_count, A.avg_fragment_size_in_pages, A.page_count
    From sys.dm_db_index_physical_stats
    (DB_ID(N' I3_IC'), NULL, NULL, NULL, 'LIMITED') A
    INNER JOIN sys.objects B ON A.object_id = B.object_id
    INNER JOIN sys.indexes C ON C.object_id = B.object_id AND C.index_id = A.index_id
    INNER JOIN sys.sysindexes D ON D.id = B.object_id AND D.indid = A.index_id
    ORDER BY A.avg_fragmentation_in_percent DESC


    According to the table name of "IR_RecordingMedia", this appears to be for an "I3" telephone system and it looks like it might be for version 4 (ICV4 is the change if I recall).

    Unless I've missed my guess, the PK for that table is on the "RecordID" column and its datatype is UNIQUEIDENTIFIER... which means it uses a GUID.  As much as I hate GUIDs for PKs, they actually do work very well for inserts being fast because they avoid the "hot spot" that tables with an "ever increasing" PK provide.  Logical fragmentation also doesn't matter so much on today's large memory systems and disks, especially since there are so many users and the Read/Write heads are bouncing all over the disk anyway.   

    FYI, there are also two types of fragmentation... logical, which is what you folks are looking at, and physical, which is the only important part on such a system because, the rows in this table are generally accessed only one at a time whether it's for a query (Select), and INSERT, or an UPDATE.  Further, be advised that with such a low rowcount on a GUID PK, it doesn't take many inserts to very quickly reach 99.9999% logical fragmentation and it's harmless in this case especially since you page fullness (the physical fragmentation I spoke of) is at 94%.  If you let this go for another 3 or 4 years (about the same amount of time since we upgraded to ICV4 and haven't ever rebuilt indexes on this box), the PK will "settle out" at about 69-70% page fullness which is exactly what you'd want to set your FILL FACTOR to if you made the mistake of rebuilding the index. 

    I also just happen to be conducting experiments using 3.6million individually inserted GUIDs using various FILL FACTORs and have proof that this matters little.  Yes, you can make it so it almost never does another unexpected page split (rebuild at 70% Fill Factor, defrag at 5% logical fragmentation or at the very first sign of additional pages being added) but the number of page splits per day are miniscule even in my environment where we handle about 4000 calls per day.  My experiments use 10,000 inserts per logical day and they're miniscule there, as well.

    The bottom line here is that having a logical fragmentation of 99.9999% on this index is NOT a problem and you shouldn't waste your time either rebuilding it or reorganizing it and you have the proof of that... as you said in your original post, "that is running well".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • p.s. Almost forgot... much more important than the "logical fragmentation" is "page density" (that % of page fullness thing) and rebuilding stats.  For GUID clustered indexes, don't worry about page density unless they go less than 68%.  The "Natural Fill Factor" for GUIDs over time pans out to about 69.5% and all pages will eventually be filled and split because of the random nature of the Type 4 GUIDs used nowadays.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ah... one more thing... I don't believe they use NEWSEQUENTIALID to populate the RecordID column.  I believe they use just a normal, random GUID to populate it.  In that case and considering the average row size (446 for you IIRC... close to 600 for me), a 94% page density is actually too high and will be subject to more page splits than what you really want.  You can do one of two things to fix that... 1) stop rebuilding or reorganizing the index and let the "Natural Fill Factor" shake out or 2) rebuild the index with a 70% FILL FACTOR and then stop maintaining it except for maintaining the Statistics on the table/indexes.  Since the NCI's will be in similar shape and the system doesn't do deletes (which would affect the "Natural Fill Factor" of every index, I'd just take this whole table (all the indexes) off your index maintenance and just rebuild the stats.

    Just so you know that I practice what I preach, I've only done index maintenance on our phone system once several years ago before I realized what a mistake that was.  I've also not done any index maintenance on any of my other production boxes for more than 2 years and performance actually got better in the first 3 months and has not degraded over the 2 years.  The only index I've rebuilt was on one "audit" table where they do regular deletes that leave almost every page wanting for fullness (they all got down to 52% and it was a quarter Terabyte table).  Because it does have an ever increasing Clustered PK, I rebuilt that at 100% FF and rebuilt the NCI's at 70% and saved about 120 GB.  But that's the only reason why I rebuilt it was to recover disk space.

    I also rebuilt that table by using the trick of rebuilding the CI on a temporarily created file group in the BULK LOGGED recovery model, then rebuilt all the NCIs, and then did a final rebuild back to the PRIMARY File Group and dropped the temporary file group (and, of course, then returned to the FULL recovery model).  It took some extra time but was well worth it because it kept the log file from exploding and it kept me from having an extra 125GB of empty space to go on top of the 125GB of empty space that reducing the size of the index caused.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Monday, December 4, 2017 4:23 PM

    Ah... one more thing... I don't believe they use NEWSEQUENTIALID to populate the RecordID column.  I believe they use just a normal, random GUID to populate it.  In that case and considering the average row size (446 for you IIRC... close to 600 for me), a 94% page density is actually too high and will be subject to more page splits than what you really want.  You can do one of two things to fix that... 1) stop rebuilding or reorganizing the index and let the "Natural Fill Factor" shake out or 2) rebuild the index with a 70% FILL FACTOR and then stop maintaining it except for maintaining the Statistics on the table/indexes.  Since the NCI's will be in similar shape and the system doesn't do deletes (which would affect the "Natural Fill Factor" of every index, I'd just take this whole table (all the indexes) off your index maintenance and just rebuild the stats.

    Just so you know that I practice what I preach, I've only done index maintenance on our phone system once several years ago before I realized what a mistake that was.  I've also not done any index maintenance on any of my other production boxes for more than 2 years and performance actually got better in the first 3 months and has not degraded over the 2 years.  The only index I've rebuilt was on one "audit" table where they do regular deletes that leave almost every page wanting for fullness (they all got down to 52% and it was a quarter Terabyte table).  Because it does have an ever increasing Clustered PK, I rebuilt that at 100% FF and rebuilt the NCI's at 70% and saved about 120 GB.  But that's the only reason why I rebuilt it was to recover disk space.

    I also rebuilt that table by using the trick of rebuilding the CI on a temporarily created file group in the BULK LOGGED recovery model, then rebuilt all the NCIs, and then did a final rebuild back to the PRIMARY File Group and dropped the temporary file group (and, of course, then returned to the FULL recovery model).  It took some extra time but was well worth it because it kept the log file from exploding and it kept me from having an extra 125GB of empty space to go on top of the 125GB of empty space that reducing the size of the index caused.

    Thank you Jeff!!!!!   You hit it all exactly on the head.  After your advice and a little more digging I see that the recommendation by the phone system support team to reindex the IR tables was definitely not good without a lot of caveats.  We definitely insert a lot of records into this and other I3 tables and rebuilding with 100% fill factor was hurting my overall performance.   I'll be taking your advice and rebuilding with a lower fill factor in increments to watch out for any size/growth issues and monitor the indexes without rebuilding them so frequently.  I'll then go the script route on index maintenance at a much longer interval and probably when I do archive/deletes of older data only.   I was running crazy routines to keep log file growth checked with log backups after hitting each of these larger tables so frequently.

    Thanks for all your detailed advice, definitely saved me a some late nights fighting with this...much appreciated!

  • jarmendadmin - Monday, December 4, 2017 6:11 PM

    Jeff Moden - Monday, December 4, 2017 4:23 PM

    Ah... one more thing... I don't believe they use NEWSEQUENTIALID to populate the RecordID column.  I believe they use just a normal, random GUID to populate it.  In that case and considering the average row size (446 for you IIRC... close to 600 for me), a 94% page density is actually too high and will be subject to more page splits than what you really want.  You can do one of two things to fix that... 1) stop rebuilding or reorganizing the index and let the "Natural Fill Factor" shake out or 2) rebuild the index with a 70% FILL FACTOR and then stop maintaining it except for maintaining the Statistics on the table/indexes.  Since the NCI's will be in similar shape and the system doesn't do deletes (which would affect the "Natural Fill Factor" of every index, I'd just take this whole table (all the indexes) off your index maintenance and just rebuild the stats.

    Just so you know that I practice what I preach, I've only done index maintenance on our phone system once several years ago before I realized what a mistake that was.  I've also not done any index maintenance on any of my other production boxes for more than 2 years and performance actually got better in the first 3 months and has not degraded over the 2 years.  The only index I've rebuilt was on one "audit" table where they do regular deletes that leave almost every page wanting for fullness (they all got down to 52% and it was a quarter Terabyte table).  Because it does have an ever increasing Clustered PK, I rebuilt that at 100% FF and rebuilt the NCI's at 70% and saved about 120 GB.  But that's the only reason why I rebuilt it was to recover disk space.

    I also rebuilt that table by using the trick of rebuilding the CI on a temporarily created file group in the BULK LOGGED recovery model, then rebuilt all the NCIs, and then did a final rebuild back to the PRIMARY File Group and dropped the temporary file group (and, of course, then returned to the FULL recovery model).  It took some extra time but was well worth it because it kept the log file from exploding and it kept me from having an extra 125GB of empty space to go on top of the 125GB of empty space that reducing the size of the index caused.

    Thank you Jeff!!!!!   You hit it all exactly on the head.  After your advice and a little more digging I see that the recommendation by the phone system support team to reindex the IR tables was definitely not good without a lot of caveats.  We definitely insert a lot of records into this and other I3 tables and rebuilding with 100% fill factor was hurting my overall performance.   I'll be taking your advice and rebuilding with a lower fill factor in increments to watch out for any size/growth issues and monitor the indexes without rebuilding them so frequently.  I'll then go the script route on index maintenance at a much longer interval and probably when I do archive/deletes of older data only.   I was running crazy routines to keep log file growth checked with log backups after hitting each of these larger tables so frequently.

    Thanks for all your detailed advice, definitely saved me a some late nights fighting with this...much appreciated!

    Apologies for the late reply.  Thank you very much for the detailed feedback!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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