Index frangmenting

  • Good day,

    What would cause fragmentation on the Unique Clustered index on a table (its one and only index) that is only ever read from, and never written to?

    Thanks,

    P

  • What is the unique clustered index on that table?  If the table is never written to - how did the data get in the table in the first place?  A table with no data won't have any fragmentation.

    If you are assuming that an identity column set as the clustered index somehow prevents a table from experiencing fragmentation - that is not correct.  If you are assuming an identity column prevents fragmentation when the data is loaded, that too is incorrect.

    A good example is a load process that goes parallel - that parallel process can and will load data out of order.  The only way to ensure that a clustered identity index does not fragment when the table is loaded is to load the table one row at a time.

    If the clustered unique index is not an identity column - the only way to ensure no fragmentation when loading data is to again, insert a single row at a time in the clustered index order.

    Once loaded, an offline rebuild of the table should remove all fragmentation - unless mixed extents are used for that index.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • My understanding - that shouldn't ever happen.

    If the data isn't changing, the data shouldn't be getting fragmented.  INSERT, UPDATE, and DELETE operations would cause fragmentation, SELECTs shouldn't.

    My guess - something is changing the data even if you are not aware of it.  I would probably put an INSERT, UPDATE, DELETE trigger on the table and have it update an audit log and you can watch for data changes.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.

    Could backups be doing it? I realize I don't need to back it up, just easier to backup everything without hard-coding exceptions.

     

     

     

  • Are the indexes created before or after populating the data?  I would encourage you to create them AFTER populating the data OR rebuild them after populating the data.

    Backups don't modify data, so they will not cause fragmentation.  If backups could modify data, you wouldn't be able to trust the backup and thus wouldn't have any way to restore without potential for data changing.  The exception to this is the backup history tables which obviously need to be changed after a backup otherwise it is not a very good history table.

     

    Indexes get fragmented when data changes.  So any process that changes your data can result in fragmentation.  If the data is getting fragmented, it means something is changing it.  Based on my understanding of indexes, SOMETHING is running an INSERT, UPDATE, or DELETE after you have created your indexes on the table.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • As mentioned, the index is created post-load.

    Thanks for taking the time to respond. Trigger, here I come!

    I'll let you know what I find.

    Thanks again

    P

  • Before doing a trigger, may not hurt to look at other options.  Extended events for example.

    Triggers can be painful if you forget to drop them when you are done.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Won't be around for long, methinks.

    Also, still on 2008, and XE was not friendly in the slightest back ...er... now 🙂 That said, can't wait to start playing with it in our brand spankin' new 2016 servers. And yes, I know what last Monday was...

  • schleep wrote:

    Ah, I should have been clearer. It is dropped and re-created monthly, populated with 1.8m rows, and then create the index. Within 2 days, the dm tables are showing fragmentation.

    Could backups be doing it? I realize I don't need to back it up, just easier to backup everything without hard-coding exceptions. 

    Are you checking fragmentation after the index creation?  Or is this just something you check a couple days later and find?

    I would not create a clustered index after the load - I would have the clustered index already created and perform a rebuild after the load.  I haven't found there to be a significant difference in the load times with or without the clustered index.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Couple of days later. Wish I had the query handy, but it's on my other workstation, the one with the fried HD from a power blip last Friday. Good thing we RAIDed our programmers' machines...

    Process is Drop, Create, Populate, Index.

    I found dropping and re-creating the table was faster than truncate/delete, even with recovery mode = simple, back in SQL 6.5

     

  • So, the trigger has been in place, but has not fired. It's simple: On I/U/D, send me an email with the user_name().

    I rebuilt the index on Friday morning, this morning, it's at 66.42310000353% (always the same number).

     

  • schleep wrote:

    So, the trigger has been in place, but has not fired. It's simple: On I/U/D, send me an email with the user_name().

    I rebuilt the index on Friday morning, this morning, it's at 66.42310000353% (always the same number).

    What is that number coming from, please?  Is it the average percent of fragmentation or the average percent of page fullness?

    And what is the value immediately after you add the index or is that what your previous post states?

    --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)
    Intro to Tally Tables and Functions

  • It's coming from Table - Indexes - Reorganize. The underlying source is sys.dm_db_index_physical_stats, I believe.

    After load - create index, as well as after rebuild or reorg,  avg_fragmentation_in_percent = 0

     

     

  • Crud.  My apologies.  I hit "report" on your last post by mistake and it doesn't provide an "are you sure question".

    Anyway, you said...

    It's coming from Table - Indexes - Reorganize. The underlying source is sys.dm_db_index_physical_stats, I believe.

    After load - create index, as well as after rebuild or reorg,  avg_fragmentation_in_percent = 0

    That's the number that goes up to 66% in 2 days?  If that's true, someone is doing updates, inserts, or deletes somewhere and your trigger isn't catching it.

    --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)
    Intro to Tally Tables and Functions

  • So... since the trigger isn't catching it, is my next step Extended Events?

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

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