Support rebuilding DW indexes with Online = ON so the table is accessible during the rebuild
ALTER INDEX ALL ON VulnerabilityScope REBUILD
But, when the rebuild takes place, the entire table becomes locked from any query--even selecting one row. Users complain when the table is inaccessible during the 30 minute rebuild.
This currently returns an error:
ALTER INDEX ALL ON VulnerabilityScope REBUILD WITH (ONLINE = ON)
Parse error at line: 1, column: 48: Incorrect syntax near 'ONLINE'.
Please support rebuilding DW indexes with Online = ON
Thank you for the feedback folks. For now, you can use Alter Index reorganize as the online operation when rebuilding your CCI . We will reach out when this is on our roadmap. Please describe your scenario for this capability below and continue to vote. Thanks for your patience.
Simon D'Morias commented
With very wide tables Synapse is painfully slow (unusable) if the CCI is not in perfect condition.
I would really like to be able to do (assuming RCSI enabled):
ALTER TABLE X REBUILD ALL PARTITION Y
This way I'm 100% online. MS tend to push the CTAS/Partition Swap route - but two issues. One this requires a schema lock - when using DQ from PowerBI this is none starter. Two it is painfully slow for reloading smaller chunks of data in large partitions
Marty Piecyk commented
By the way, even queries with NO LOCK get stuck until the rebuild is complete:
SELECT TOP 1 * FROM VulnerabilityScope WITH (NOLOCK)
And, as expected, this results in an error:
ALTER INDEX ALL ON VulnerabilityScope REBUILD WITH (NOLOCK)