0

I'm a recently recruited Data Engineer, I have made a bunch of ETL pipelines planned to run daily, the destination tables are truncated and charged again.

When I arrived I found other ETLs that also run in a daily manner, but when I checked the fragmentation percentage on the indexes the rates were too high, so I'm thinking of creating an optimization task for all the indices.

Most of the clustered indices (primary keys) are doing just fine, but the non-clustered indices are suffering from high fragmentation, How and when should I rebuild/reorganize indices? After or before loading the new data?

Improve this question
13
  • 1
    Are you facing performance issue ?
    – Shanky
    May 10 at 14:36
  • 1
    precisely SQL Server & MySQL Divide your question to 2 separate ones - one question == one DBMS.
    – Akina
    May 10 at 14:45
  • 3
    In SQL Server rebuilding an index is the same as dropping and recreating it. And do you have any evidence that rebuilding the indexes will improve performance? Fragmentation is not a problem in and of itself.
    – David Browne - Microsoft
    May 10 at 16:15
  • 3
    "80 90% frag level... sooner or later that will result in performance issues" -- That's not actually true. Here at Stack Overflow, we do not do any regular index maintenance for fragmentation. We only do regular Statistics updates.
    – AMtwo
    May 10 at 17:02
  • 2
    Good way to make yourself known to the entire company is to start changing things that no one is complaining about.
    – SMor
    May 10 at 17:25

1 Answer 1

Reset to default
2

I would do this,

  1. Truncate table before re-load.
  2. Script out existing index definitions and drop the existing.
  3. Run the re-load which should be done much faster than before as there were no pre-existing index's on the table.
  4. Then, re-create the indexes using definitions extracted from step (2).

Pros:

  • Fast load times
  • Controls the fragmentation that you are concerned about.

Cons:

  • Every time you have to re-create your indexes.

Note: If the time of completion is a factor then you probably would need to try both approaches(Re-create vs Re-build) and see what works best for you.

Improve this answer
1
  • While I agree with everyone else's perspective of not worrying about index fragmentation (it's almost always useless), this is a good answer in general on best approaches for optimal management of ETL / staging tables.
    – J.D.
    May 10 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.