At this point, nearly 2 weeks after upgrading from SQL-2014 to SQL-2017, and subsequently 5 days after enabling trace flag 692, I believe I can say that the trace flag has had a very positive impact on the unused space issue.
As our daily partitioned tables begin to roll through our weekly retention policy, some of the "larger" partitions are traded out for some of the smaller ones now being created with the trace flag, and we are recovering unused space to the database as a whole instead of accumulating it into reserved space for these tables. We have recovered almost 400GB of space that way in 5 days.
So, trying to gather some information as to what contributed to this in our application, since we cannot be the first database ever to upgrade SQL 2017, right? 😉
The database that was growing rapidly was a "history" database. Our operational database was not hit nearly as hard with this issue. Just the archival database. So what was the difference? Our operational database is just that...an OLTP database run transactionally, one record at a time, updated, inserted, etc. The History database, however, has many tables that are batch-inserted to for archival purposes.
Seems that would be a key factor in the impact of this change in behavior.
In fact, the table most impacted seems to be a table that was batch-inserted to with a SINGLE RECORD size batch. Seems the smaller the batch size, the more the waste.
After the changes (SQL version and TF692), a few of the tables now have MUCH smaller unused space now. 1/20th of what they were under SQL-2014. A few tables are still 3x what they were before under SQL-2014. But none of them near the size that they were under 2017 without the trace flag. One example, the unused space for one table went from ~10000 KB to a max of ~86679000 KB and settled back to about 28000 KB. Another example table from ~50000 KB to a max of ~9345808 KB and has settled back to about 4500 KB of unused space now. Have not dug into the differences between those tables yet.
I'm not a skilled article writer, but would love to summarize this some way, with some of the actual data that I have pre/during/post. Any tips on how to organize this to an article, as suggested?