sp_BlitzIndex: add warning for paused-and-soon-to-die resumable index operations

[ReeceGoding]

Is your feature request related to a problem? Please describe.
Doctor sp_BlitzIndex has successfully diagnosed many of my patients. My newest 2022 case was found to have feature-phobia and self-loathing. My prescription was to spend an hour every Sunday trying to REBUILD the patient's confidence. I thought that I was doing well, but we're making no progress. My patient is different from the others. If it walks away from some work for more than a day, then it loses it all.

I would like to take my dear Doctor sp_BlitzIndex on a training course to help guarantee such patients receive proper treatment.

Dead jokes aside, I had no idea that paused resumable index operations automatically die as of SQL Server 2022 until I started reading through the errors that resumable operations can throw. I cannot adequately express how baffled I am by this. If you have the kind of maintenance window that requires pausing index operations, it's probably weekly. But the default is to kill your paused operations daily! And I've never seen this documented anywhere in the docs for resumable indexes. I've never even seen a blogger mention it. Seriously, did anyone know?

I also don't think that there's anything in SQL Server to tell you that your paused operation has died like this. When I saw mine vanish on a dev box one day, the first thing that I did was ask if anyone had killed it! sp_BlitzIndex needs to scream about this kind of thing. I wonder if it should even be in sp_Blitz.

Describe the solution you'd like
Add a new check to the mode 4 output of sp_BlitzIndex to tell you what value you have PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES set to. Give it different text for 0, for the default value, and for non-zero non-default values. Furthermore, add a new warming to anywhere that we hold information about paused rebuilds. This warning is to tell you when your paused index rebuild is going to be aborted by Microsoft.

Describe alternatives you've considered
Add a new check to the mode 4 output of sp_BlitzIndex to tell you what value you have PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES set to. Give it different text for 0, for the default value, and for non-zero non-default values. Furthermore, modify the text of the paused resumable operation warnings anywhere that we hold information about paused rebuilds. This warning is to tell you when your paused index rebuild is going to be aborted by Microsoft.

Are you ready to build the code for the feature?
Yes, but it's going straight to the bottom of my open-source to-do list. It would require me spinning up a 2022 container and is best left until #3609 has a fix merged.

[BrentOzar]

HAHAHA, that's awesome - I wasn't aware of that change either! I have a lot of 2022 labs, so I can knock this out this week. I need to do a release of sp_BlitzIndex anyway, and this will be fun to code & test. I'll keep you posted. Nice find!

[ReeceGoding]

As an added surprise, this obscure documentation for a new-to-SQL-Server-2022 feature also pretends that PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES doesn't exist. It claims

Failure or pausing a resumable table constraint operation doesn't terminate its execution. Rather, it leaves the operation in an indefinite paused state.

Emphasis mine. The pausing isn't indefinite: it's controlled by PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES.

[BrentOzar]

I added code in sp_BlitzIndex so that if PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES has been set, we now show the expiration date in the details on paused operations, like this:

PAUSED on dbo.Votes.PostId_UserId_BountyAmount_VoteTypeId since 2025-04-06 05:51:30. 47.38% complete after 1 minute(s). This blocks DDL, fails transactions needing table-level X locks, and can pile up ghosts. Will be automatically removed by the database server at 2025-04-06 06:21:30.700.

sp_Blitz had an existing warning for any non-default database scoped configuration options, but ... we hadn't updated it for 2022! I added the new 2022 options.