Nonclustered Index Rebuild Online on Heap Table

Question

Recently read that rebuilding a heap online in SQL Server Enterprise is single threaded and does not go parallel.

My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?

Background

I am trying to find the best place to actually test it, but right now I have limited hardware available to me. I am new to the company and I am not advocating a heap, I am trying to steer them towards clustered indexes, but they have random GUIDs everywhere and lots of the developers think clustered indexes will hurt performance. I am trying to educate them on the advantages of using clustered indexes rather than heaps and one of those components is index maintenance.

We had one stored procedure that was doing over 27 million logical reads. I went in and looked at fragmentation on one of the key table scans, rebuilt the heap and logical reads went down to 560,000. Just looking at forwarded records and fragmentation I would not have even rebuilt that table through a scripted job. Trying to make them understand if we are gonna keep heaps we need to perform regular maintenance on these things.

The cost of this maintenance is higher for rebuilding heaps than having clustered indexes. That is really where my question about single threaded is coming from. Sorry to be long winded but thought would help explain question.

Answer 1

My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?

It can be multi threaded, the restriction is ONLY to alter table rebuild command when done with ONLINE option. I would like to quote from Enabling Compression on Heap MSDN Blog

(1) If you use ONLINE option then HEAP rebuild is single threaded. Note, this restriction is not there when you are rebuilding an index which supports multi-threaded ONLINE build. However, in both cases, OFFLINE build does support multi-threading.

A non-clustered index rebuild can be multi threaded. Please note for index rebuild to use multiple threads the edition should be Enterprise edition or above( if there is such a edition). This is still true from SQL Server 2005 to SQL Server 2019.

---

For example, rebuilding a heap with three nonclustered indexes online:

ALTER TABLE dbo.Fish REBUILD WITH (ONLINE = ON);

The heap rebuild is serial:

...while the associated nonclustered index rebuilds are parallel: