sp_BlitzIndex: add warning for paused resumable index operations

[ReeceGoding]

Is your feature request related to a problem? Please describe.
Unlike sp_Blitz, there is nothing in sp_BlitzIndex that reads sys.index_resumable_operations. This can cause problems, because a hidden resumable operation could change the results of any analysis that I'm conducting with sp_BlitzIndex.

Describe the solution you'd like
Something almost identical to the sp_Blitz output should be added to the mode 4 output of sp_BlitzIndex. A notice should also be added to the output that comes from passing in a table by name. I am unsure if said notice should be its own new table (hidden if empty) or somehow bolted on to the table that currently tells us about missing indexes.

Describe alternatives you've considered
I should look at what we currently do for online index operations. I'm not sure what DMV they're in, but there must be a good reason for whatever we're currently doing with them (even if what we're doing is nothing at all).

Are you ready to build the code for the feature?
Sure. I should have a free weekend soon.

[BrentOzar]

Makes sense - I'll assign the issue to you.

While you're doing testing, try doing index creations & rebuilds inside a transaction. I've seen issues where DMVs don't honor nolock requests, like this: #2130

My worry is that we don't know if sys.index_resumable_operations honors isolation level requests, and if we ship new checks on that DMV, we might hit new edge case issues where we cause blocking pileups.

[ReeceGoding]

@BrentOzar Any suggestions on where this should fall in in the list of sp_BlitzIndex checks by priority?

[ReeceGoding]

@BrentOzar Any suggestions on where this should fall in in the list of sp_BlitzIndex checks by priority?

I've opted to set the priority ridiculously high. On top of invalidating whatever index analysis you're trying to do, these can block DDL. It feels pretentious to say that my new check is super important, so I welcome being told otherwise.

I've added it to the mode 0 and mode 4 output in this branch. It was more work than I expected but seems to work. I'll not submit the PR until I've done the testing Brent requested, which is probably at least two weekends away. Feel free to play around with it as is. As mentioned in the original post, I'd also like to add a notice to the output that comes from passing in a table by name. I haven't looked at that yet.

[BrentOzar]

I agree with you on the high priority, actually. It's dangerous to leave those lying around, so it makes sense. Good work!

[ReeceGoding]

Easily fixed, but too funny to not share: my current code is incorrect because sys.indexes doesn't include indexes that don't exist yet.

[BrentOzar]

@ReeceGoding I laughed out loud.

[ReeceGoding]

@BrentOzar I'm nearly ready to submit the PR. However, I think the locking/blocking tests that you're asking for may be impossible. I'll try more, but my first attempt had SQL Server throw error 574.

It fails before it even checks if the table is real.

BEGIN TRAN

ALTER INDEX [NO]
ON [StackOverflow2010].[dbo].[I am not real]
REBUILD
WITH (ONLINE= ON, RESUMABLE = ON) ;

[ReeceGoding]

This comment has been merged into #3620