The problem is that our regular maintenance plan that rebuilds indexes on all databases that are over 30% fragmented regularly rebuilds our new columnstore indexes which maxes our CPU and runs the risk of killing the server.
We reorg our columnstore indexes just prior to the regular maintenance.
How can we stop our regular maintenance plan from rebuilding our columnstore indexes?
We are on SQL Server 2016 Enterprise Edition.
According to the Reorganize and rebuild indexes documentation:
Up to SQL Server 2017 (14.x), rebuilding a clustered columnstore index is an offline operation. The Database Engine has to acquire an exclusive lock on the table or partition while the rebuild occurs. The data is offline and unavailable during the rebuild even when using NOLOCK, Read-committed Snapshot Isolation (RCSI), or Snapshot Isolation. Starting with SQL Server 2019, a clustered columnstore index can be rebuilt using the ONLINE=ON option.
Knowing this, as long as you're using SQL Server 2016 you could check the wizard option For index types that do not support online index rebuilds Do not rebuild indexes.
Just remember that using this method will skip all indexes that do not support online rebuild.
If possible to change the way you do the rebuilds, you could start using scripts instead of the wizard and query sys.indexes to help you choose the right indexes to be rebuilt. It is a System View that can list all of your indexes. One of the columns of this view is type and the values 5 and 6 are related to Clustered columnstore and Nonclustered columnstore respectively. Joining this view on the queries you use would make it possible to filter the columnstore indexes out and avoid rebuilding them with the others.
