Is fragmentation causing a problem that needs to be solved?

May be check this out.

"6hrs-12hrs to reorganize the index" - Was that to reorganize the entire index or just for one partition? Do you use any columnstore indexes or they're all rowstore? Also, agreed with alroc's question / point.

Yes - fragmentation is causing us problems. If we don't keep on to of it the table will get severely fragmented and performance will drop off a cliff. There are no column store index's - what I have provided is the full details of the table (excluding the partition setup). I believe that the reorganise is on just the latest or fragmented partition. I have read extensively on the general believe that fragmented tables don't matter - this is not my experience where if fragmentation gets out of control it can cause significant issues.

Is field_id sequential? I see is foreign keyed to datFields, and also the leading key on your clustered index.

You ask if the fragmentation comes from point 3. I can't see that it does, assuming the information you leave us is correct. If those triggers only modifies the Value column and that column isn't part of an index, then the row location should be stable (no data movement). I echo Brendan's question: How does the filed_id look like? Is it ever increasing? If not, then you are possible inserting "all over the place".

Yes - the field id is a unique key for datFields table (not shown). These are ever increasing in that table. In total there would be maybe 10,000 unique fieldID values that are used.

1) Fragmentation isn't your problem. In 2022 it's no one's problem. 2) If the table is partitioned, each partition is its own b-tree, which means the table will not be "sequential on disk", which means again, not a fragmentation problem. 3) Your problem is most likely stats, but since you're too focused on fragmentation being a problem there is no actual data on what "performance issues" are or the signs of it. More data is needed like query plans from "performance issue" times, waits output, etc. 4) Fragmentation isn't your problem.

Really - so fragmentation never causes issues anywhere?

Very black and white view that does not correlate to my real world experience. I have had many examples of tables becoming severely fragmented (80-90%) and queries to the table increase by factors of X10. In these cases no amount of updating statistics makes any difference and in the end dropping and re-creating the index was the fix. Anyway - I'm guessing there is no further advice on the issues I am facing? Could it be related to order of the index (place date before field?) or would this be a candidate to modify the fill-factor?

" In these cases no amount of updating statistics makes any difference and in the end dropping and re-creating the index was the fix. " So you have some extremely weak correlated item with NO data and thus it's the root cause? You do realize that dropping an index does much more than "fix fragmentation", right? If you believe this strongly that it can only be fragmentation, then show us the data. Right now you have a religious belief with only faith and no data. Put the data up and prove your case.

I believe @SeanGallardy-MostlyRetired is correct, I've never encountered a case where fragmentation was the root source of a performance problem. For reference, I've worked with tables in the 10s of billions of rows too, which were about a terabyte in size for each table, and it wasn't worth doing index maintenance on them for the work vs reward. Conversely, I have worked with a 3rd party vendor application's database, with a small amount of data, where fragmentation was repeatedly a performance bottleneck because the OLTP tables didn't have clustered indexes - that was the root issue.

Now the reason I asked if you used any columnstore indexes is because those are the only ones that sometimes still warrant index maintenance, but usually different than a rowstore index, and to optimize a characteristic of columnstore indexes that leverage heap storage for some of the data. Also, as Sean mentioned, re-creating an index does more than clean up fragmentation, such as automatically recalculating statistics. I think the lack of answers here is the fact there's no apparent issue to be solved unless you can provide more evidence, but not doing re-orgs would be the fastest option.

Thanks all for your responses - I will keep looking and will run some further tests on our dev enviro. Turning of index defrag on production would be a little too risky for me at the moment considering the time to recover. Question - if we have a 90% fragmented table how does this impact performance. I would assume that it would mean many pages approaching 50% full? Would this not impact performance increasing cache size and read times on the data?

Also with such large fragmentation implies many page splits - does this not impact performance as well on a system that is constantly inserting data? Would a different fill factor help this?

And finally - although all seem to be indicating do not worry about it - I still don't understand why the fragmentation is occurring as I am mostly inserting what I thought would be the end of the table (today's date mostly). Does the order of the fields in the index impact this? i.e If I modified the index to be (Date, FieldID) would this potentially mean significantly less page splits?

So - I have been following this red-gate.com/simple-talk/blogs/… I ran this for 10min on production system and after an estimated 6423 inserts I get around 995 page splits on the datData table. 1 in 6 inserts triggering a page split feels high?