clustered columnstore indexes

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  November 6, 2018 at 11:12 am

  #372950

  Hi, we are adding in clustered columnstore indexes.
  My current rebuild script is working for clustered indexes

  What would I need to amend to handle clustered columnstore indexes ?

  SELECT
       DB_NAME() as [dbName],
       tbl.name as [tableName],
       SCHEMA_NAME (tbl.schema_id) as schemaName,
       idx.Name as [indexName],
       pst.database_id as [databaseID],
       pst.object_id as [objectID],
       pst.index_id as [indexID],
    pst.page_count as [page_count],
    pst.record_count as [record_count],
    pst.avg_record_size_in_bytes as [avg_record_size_in_bytes],
    pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage],
       CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN
       ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE, ONLINE=ON, SORT_IN_TEMPDB = ONLINE', STATISTICS_NORECOMPUTE = OFF);''
       WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN
       ''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;''    
       ELSE
       NULL
       END
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , LIMITED'+@indexStatisticsScanningMode+''') as pst
     INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id
     INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id
    WHERE pst.index_id != 0 
     AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'')
       AND pst.avg_fragmentation_in_percent >= @variable_percent