0

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.

Jules
  • 1
  • Are you using any known scripts like [SQL Server Maintenance Solution](https://ola.hallengren.com/) from Ola Hallengren? – Ronaldo Aug 30 '19 at 15:14
  • We are not. We have a custom script that finds the CL indexes and reorganizes them, then rebuilds statistics on those tables. Then we use the maintenance wizard for all the other tables and DBs. – Jules Sep 03 '19 at 15:44

1 Answers1

0

Using the Wizard

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.

Maintenance Plan Wizard

Just remember that using this method will skip all indexes that do not support online rebuild.

Using Scripts

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.

Ronaldo
  • 351
  • 2
  • 11
  • This is exactly what I needed. We do use a custom script for the column indexes that use the sys.indexes, just not for the rest of them. – Jules Sep 03 '19 at 22:16
  • Cool, @Jules. If it works, remember to mark as the right answer. – Ronaldo Sep 03 '19 at 23:03