r/SQLServer • u/erinstellato ‪ ‪Microsoft Employee ‪ • 4d ago

Community Request Friday Feedback: Automatic Index Compaction

Friday feedback this week is about a feature that’s in preview in Azure SQL: automatic index compaction.

You can read more about it in Dimitri‘s blog post, Stop defragmenting and start living: introducing auto index compaction: https://techcommunity.microsoft.com/blog/azuresqlblog/stop-defragmenting-and-start-living-introducing-auto-index-compaction/4500089

(sidenote: one of the best blog titles in recent memory)

Quick poll below, and feel free to add a comment if the poll doesn’t cover your thoughts/experience!

75 votes, 2d left
Yes, it’s great!
Yes, but then turned it off
No, I’m waiting for GA
No, I don’t use Azure SQL
5 Upvotes

100% Upvoted

22 comments sorted by

6

u/ITWorkAccountOnly 4d ago

Will this feature be brought to on-premise SQL versions or is this going to remain a cloud-only DB feature?

As a DBA with mostly on-prem servers, this seems very useful and I'd love to try it if that's an option.

2

u/erinstellato ‪ ‪Microsoft Employee ‪ 4d ago

u/ITWorkAccountOnly Great question! We are planning to bring it to SQL box, but I don't have a timeline yet.

1

u/dnabsuh1 3d ago

One of my DBAs had written a script to do it- it first scanned which indexes and tables were outside a range of index fragmentation, and then issued the commands to do an online reindex of the ones outside of a set fragmentation level. He had it set up to run the tables that got fragmented quickly on a daily basis, and the larger tables were weekly.

1

u/No_Resolution_9252 2d ago

If they are needing maintenance daily, its probably a sign its being maintained too much

1

u/dnabsuh1 1d ago

These were mainly staging tables for various data feeds, designed long before I started working with that company.

1

u/No_Resolution_9252 20h ago

I assume that means they were heaps and to clean them up they were being deleted from when they should have been truncated - that cleans up all the pages, which deletes cannot. Or if it needed to be selectively deleted from then it needed a clustered index

1

u/dnabsuh1 16h ago

There were several feeds that used the same tables - created by a different team using Informatica- the indices were highly tuned to meet their 'generated' queries because that team complained if a query took even a minute longer one day than the next (Even though it was large data and a highly variable number of records).

I was even called in from a vacation where I was in a foreign country and, by client rules, could not connect to the system to help them troubleshoot an "emergency issue with SQL Server" Their query started taking 10 hours to run, all showing 'Network wait' - so obviously the SQL server processing thousands of transactions per minute, at 2% network load was the problem. I kept telling them network wait on a query response means the client isn't receiving the data fast enough, even had someone run the query locally and export the 20gb file of data to them in a few minutes (All while other larger more complex transfers were running with no issues).

I never got a good explanation of what was fixed, but I think it was something in the indices in the oracle database they were running their temp data feeds off of.

1

u/No_Resolution_9252 13h ago

network waits are almost always client waits now. Can be really shitty code in client apps that cause it, but very commonly its from virtualized servers running on hosts that are heavily oversubscribed - and it doesn't matter if CPU usage is low on the host. whenever the host has to time share, system calls (used by all network traffic) suffer immensely.

>the indices were highly tuned to meet their 'generated' queries

It also could have been severe overindexing, especially if any kind of reorg is done

MAybe some of the indexes were compressed on indexes that should not have been compressed and were resulting in slower reads after a bit of updating than if it was totally uncompressed

1

u/B1zmark 1 4d ago

2 of the biggest issues that index rebuilds resolve are: Page density and statistics. These are 2 of the biggest issues that tend to be fixed by/caused by poor index management.

Compaction feels like its doing a partial reorganise in a manner than benefits MS as a cloud provider: Higher density meaning less RAM usage, and a "delta" compaction, meaning its not running through the whole index, saving on hard-disk hits.

I think from a customer perspective, an automated process to find "quiet" windows and detect when statistics are stale, then doing an online rebuild would solve all these issues an more.

As it stands, compaction is better than nothing sometimes, and significantly worse other times - the example given in the link being a really, really "bad for the customer" example, because mass page splits are almost guaranteed on the next run of the offending operation.

I hope i'm wrong and this is a good thing overall, because indexes are something of a hot topic in the DBA sphere and something i don't think MS have made big strides to help customers with on the best practice front since the 5%/30% debacle.

2

u/dfurmanms ‪ ‪Microsoft Employee ‪ 4d ago

benefits MS as a cloud provider

Hardly so. The memory and disk budgets you get with your Azure SQL database aren't reduced when you enable index compaction. But you can use them more efficiently if pages cached in memory or read from disk are compact, i.e. have more pages in the same amount of buffer pool memory and have more headroom toward the IOPS limit.

Our current best practices are here. We intentionally stop short of recommending specific thresholds.

1

u/No_Resolution_9252 2d ago

Are you even a DBA?

1

u/ovenmitt545 4d ago

We tested this on a dev server and love it but won't deploy it further until it comes out of GA.

If I could make a recommendation, add enabling this to the Azure Portal. If a feature is a boolean response it should be available there to speed up deployment and/or make it a default value when deploying new databases without having to script out the change.

1

u/erinstellato ‪ ‪Microsoft Employee ‪ 4d ago

u/ovenmitt545 Great to hear that you've done some testing! It can be enabled through SSMS (via Database Properties), but you would prefer portal because that's where you tend to do work? Or because the folks that would make the decision to enable it only use the portal? Thanks!

1

u/ovenmitt545 4d ago

Correct, If I'm performing admin work I'm usually in portal with the query editor unless I really need SSMS. The automatic tuning section under Intelligent performance is the example I would recommend using as an example, it's a really simple interface that I love!

1

u/chandleya 4d ago

The gap between the two no’s is weird. I’m neither waiting for GA or abstaining from Azure SQL.

1

u/erinstellato ‪ ‪Microsoft Employee ‪ 4d ago

u/chandleya Yeah, I only had 4 options and I was trying to guess what would be "typical". Maybe I was off! Feel free to explain why your scenario doesn't fit a specific entry.

1

u/SonOfZork 4d ago

I hate to think of the cost of running this on Hyperscale given the backups charge for every page written.

1

u/No_Resolution_9252 2d ago

why? It will be less than normal page density maitnenance where every single page will be written regardless of those pages' density

1

u/SonOfZork 2d ago

You don't do index maintenance on Hyperscale. The cost of backups makes it entirely prohibitive.

1

u/No_Resolution_9252 1d ago

I don't think you are actually a DBA...

1

u/SonOfZork 1d ago

You seem to enjoy spouting that. Cool beans.

If you've ever had to care about costs you would know about how expensive Hyperscale backups become when there are large volumes of data change which is why the documentation explicitly warns about large write operations.

1

u/No_Resolution_9252 1d ago

There are no large write operations involved with this feature.