Performance issue while reorganizing an index on a very large table

Since index reorganization is an online activity less impact our user processes are getting blocked during maintenance.

This sentence is not really grammatical. Can you elaborate?

Index maintenance on a table with that size will always be a challenge.

You can do an online rebuild, which keeps the table available, particularly if you use WAIT_AT_LOW_PRIORITY. But it still takes resources. This can be mitigated by doing a resumable rebuild, to let the rebuild run only hours with less traffic. If you have such hours.

It you partition the table, you can rebuild one partition at the time. But if the queries does not lend themselves well for partitioning, that will not be good for performance.

@Erland Sommarskog ,

Thanks for your response and comment
Please read the sentence as

"even though index reorganization is an online activity with minimal impact, our user processes are still getting blocked, and the application experiences degradation during the index maintenance."

Index rebuild online:
Will it block user processes the same way I've noticed in index reorganization? (internal processes (e.g., ID: 27) blocking user processes). Also If user processes are getting blocked and the system becomes unresponsive, cancelling the rebuild would be time-consuming due to the rollback process?

Online index rebuild and REORGANIZE are "online" in very different ways.

Let's start with old fashioned offline index rebuild. This way of building an index cannot accept any changes to the table while it's running, and therefore it takes a schema-modification (Sch-M) lock on the table during the entire process. A Sch-M lock is incompatible with all other locks, so the table is inaccessible for everyone else.

REORGANIZE essentially performs a bubble-sort of the pages and shuffles them around a few at a time. The pages being shuffled are locked exclusively for the duration of the shuffling transaction, but once it is committed, the locks are released. These transactions are normally fairly short. However, consider this scenario: A slow-running query takes shared locks on some pages and holds them for a longer duration. The REORGANIZE process gets blocked by this reader. Now, other processes wants to read rows from these pages, but these processes get stuck behind the REORGANIZE in the queue. Had the reorg process not been there, they would not have been blocked by the slow reader.

Online rebuild is entirely different. The process of building the index is completely detached from the current table, and no locks are taken in that process. The rebuild process picks up changes to rows in some fashion that I don't recall right now. However, there are two points when it needs to take a Sch-M lock on the table: When it starts and then it finalises the operation by shifting in the new index structure.

These operations are swift, so the Sch-M locks are normally short-lived. However, the same scenario as I described above can happen here: There is a long-running query - and that can even be a NOLOCK query - blocks the index rebuild from performing the operation, and then everyone else get held up in the queue behind the rebuild process. But there are good news: There is the option WAIT_AT_LOW_PRIORITY that prevents this from happening. The index rebuild will then always be at the end of the queue. This option is not available with REORGANIZE.

cancelling the rebuild would be time-consuming due to the rollback process?

I have not tried this with a really big table in full recovery, so I abstain from answering. But there is the RESUMABLE option. With this option you can stop and restart the rebuild later. And with RESUMABLE the index build operation is committed every now and then, which should also shorten the time it takes to do a rollback.

While all this may seem appealing, I should add that there is also an overhead. It usually takes longer time to do an online index rebuild than an online rebuild, and if you stick in RESUMABLE, it takes even longer. But if you can keep the system available, that is a big win.