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?
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.