Simplified maintenance of columnstore indexes

  • Hello all,

    I've just started to use columnstore Indexes in a SQL Server 2016 for a datawarehouse and realized, that it would be nice to have a SP, doing a maintenance of my ccix and ncix, without to know how they called and in wich tables they are 🙂 After examining of this info: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-defragmentation following scenarios came into my focus -

    1) I would like to be able to start a maintenance of a columnstore Index for a table, after data was loaded in it from a SSIS package.

    2) I would like to start a maintenance from aMS Job Agent for all ccix in some schema in my database, after all ETLs have been completed.

    3) I would like to proceed with a maintanance for all ccix within a database All of this I would like to do in a simple way, just giving names of db, schema or table to a store procedure.

    Also I would like to be able make a choice if I do it with a rebuild or with a reorg.

    I have solved it for me and I'm glad to share it with you. I belive everyone who is developing for a data warehouse with columnstore Indexes and tries to avoid OPEN row_groups from DeltaStore will need it:-)

    Usage examples:

    1) Command runs  a reorg in each table (for only partitions with OPEN/CLOSED RGs) in MyDB.

         exec [dbo].[usp_Maint_ColumnStore] @database='MyDB'

    2) Command runs a rebuild in each table (for all partitions)  in MyDB with current compression settings.

         exec [dbo].[usp_Maint_ColumnStore] @database='MyDB', @Mode='reb', @Partitions='all'

    3)Command runs a reorg in each table (for only partitions with OPEN/CLOSED RGs) in MySchema in MyDB

        exec [dbo].[usp_Maint_ColumnStore] @mode='reorg', @database='MyDB', @schema='MySchema'

    4) Command runs a rebuild for MyTable (for all partitions) in MySchema in MyDB with compression 'COLUMNSTORE_ARCHIVE'

       exec [dbo].[usp_Maint_CumnStore] @mode='reb', @database='MyDB', @schema='MySchema', @table='MyTable', @compression='arc', @Partitions='all'

    5) Command runs a rebuild for MyTable (for partitions, including row_groups with fragmentation over 15%) in MySchema in MyDB with compression 'COLUMNSTORE'

       exec [dbo].[usp_Maint_CumnStore] @mode='reb', @database='MyDB', @schema='MySchema', @table='MyTable', @compression='col', @fragmentation=15

    (...and lastone for somebody, who will take it a basic for own modifications)

    6) Command just print a query on a screen, for some Debugging cases...without execution

       exec [dbo].[usp_Maint_CumnStore] @mode='reb', @database='MyDB', @schema='MySchema', @table='MyTable', @Debugging=1

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =======================================================================================================================
    -- Author:  Alexander Parakhnevich
    -- Create date: 2017.12.27
    -- Description: The procedure runs a maintenance for ColumnStore Indexes for a given database(including all schemas), a schema(including all tables) or a table.
    --     The parametr @Mode defines, which maintanance methode REORGANIZE(online) or REBUILD(offline) will be used. Default = 'REORGANIZE'
    --     The parametr @Compression defines, which compression option will be used with @Mode=REBUILD. Default ='COLUMNSTORE'.
    --     The parametr @Partitions defines, if [ALL] partitions or only ones with OPEN row_groups and deleted rows [EXPLICIT] will be proceeded. Default = 'EXPLICIT'
    -- =======================================================================================================================
    Create PROCEDURE [dbo].[usp_MAINT_ColumnStore]
       @Database  varchar(100) = '',    -- (Required) Name of a database
       @Schema   varchar(100) = '',    -- (Optional) Name of a schema
       @Table   varchar(100) = '',    -- (Optional) Name of a table

       @Mode   varchar(30)  = 'REORGANIZE',  -- Values: 'REBUILD', 'REORGANIZE'
       @Compression varchar(30)  = NULL,    -- Values: NULL(current compression setting stays), 'COLUMNSTORE', 'COLUMNSTORE_ARCHIVE'
       @Partitions  varchar(30)  = 'EXPLICIT',  -- Values: 'EXPLICIT', 'ALL'
       @Fragmentation  int    = 0,    -- Values: % of a fragmentation level in a row_group, which should be exepted. Default value = 0%

       @Debugging  bit    = 0     -- Values: 0=(query will be run), 1=(query will be printed on your screen). Default=0.
    AS
    BEGIN
     SET NOCOUNT ON;

    declare @VAR_Mode varchar(30)=iif(replace(replace(@Mode,' ',''),' ','') like '%reb%','REBUILD','REORGANIZE');
    declare @VAR_DATA_COMPRESSION varchar(30)=iif(@Compression is NULL,NULL,iif( (replace(replace(@Compression,' ',''),' ','') like '%arc%') ,'COLUMNSTORE_ARCHIVE','COLUMNSTORE'));
    declare @VAR_Partitions varchar(30)=iif(replace(replace(@Partitions,' ',''),' ','') like '%all%','ALL','EXPLICIT');
    declare @VAR_Fragmentation varchar(3)=cast(iif(@Fragmentation between 0 and 100, @Fragmentation,0) as varchar(3));    -- Default value = 0%
    declare @VAR_database varchar(100) = replace(replace(replace(replace(@Database,'[',''),']',''),' ',''),' ','');
    declare @VAR_schema varchar(100) = replace(replace(replace(replace(@Schema,'[',''),']',''),' ',''),' ','');
    declare @VAR_table varchar(100) = replace(replace(replace(replace(@Table,'[',''),']',''),' ',''),' ','');

    declare @linebreak char(2) = CHAR(13)+CHAR(10);
    declare @sql varchar(8000) =NULL;

    if @VAR_Mode='REORGANIZE'
     BEGIN
     SET @sql='
    USE ['+@VAR_database+']
    SELECT
    ''ALTER INDEX [''+ind.name+''] ON [''+sh.Name+''].[''+t.name+''] REORGANIZE '''+iif(@VAR_Partitions='EXPLICIT','+ iif(stor.FgPart=1,''PARTITION = ''+ cast(stor.partition_number as varchar(5)),'''')+','+''''+')+''' WITH (COMPRESS_ALL_ROW_GROUPS = ON);'' as Command
    FROM sys.indexes ind
    INNER JOIN sys.tables t ON ind.object_id = t.object_id '+iif(@VAR_table!='','and t.name= '''+@VAR_table+'''','')+' '+'
    INNER JOIN sys.schemas sh on sh.schema_id = t.schema_id '+iif(@VAR_schema!='','and sh.Name = '''+@VAR_schema+'''','')+' '+'
    '+iif(@VAR_Partitions='EXPLICIT','INNER JOIN (select distinct object_id, partition_number, (select iif(count(*)>1,1,0) as A from sys.partitions where object_id = TabA.object_id and data_compression in (3,4)) as FgPart
     from sys.dm_db_column_store_row_group_physical_stats TabA where state in (1,2)) stor on stor.object_id=ind.object_id','')+'
    WHERE ind.type in (5,6)
    UNION
    SELECT
    ''ALTER INDEX [''+ind.name+''] ON [''+sh.Name+''].[''+t.name+''] REORGANIZE '''+iif(@VAR_Partitions='EXPLICIT','+ iif(stor.FgPart=1,''PARTITION = ''+ cast(stor.partition_number as varchar(5)),'''')+','+''''+')+''';'' as Command
    FROM sys.indexes ind
    INNER JOIN sys.tables t ON ind.object_id = t.object_id '+iif(@VAR_table!='','and t.name= '''+@VAR_table+'''','')+' '+'
    INNER JOIN sys.schemas sh on sh.schema_id = t.schema_id '+iif(@VAR_schema!='','and sh.Name = '''+@VAR_schema+'''','')+' '+'
    '+iif(@VAR_Partitions='EXPLICIT','INNER JOIN (select distinct object_id, partition_number, (select iif(count(*)>1,1,0) as A from sys.partitions where object_id = TabA.object_id and data_compression in (3,4)) as FgPart
    from sys.dm_db_column_store_row_group_physical_stats TabA where state in (1,2)) stor on stor.object_id=ind.object_id','')+'
    WHERE ind.type in (5,6)'
     END
    ELSE
     if @VAR_Mode='REBUILD'
     BEGIN
     set @sql='
    USE '+@VAR_database+'
    SELECT
    ''ALTER INDEX [''+ind.name+''] ON [''+sh.Name+''].[''+t.name+''] REBUILD PARTITION = '''+iif(@VAR_Partitions='EXPLICIT','+ iif(stor.FgPart=1,cast(stor.partition_number as varchar(5)),''ALL'')','+''ALL''')+iif(@VAR_DATA_COMPRESSION is not NULL,'+'' WITH (DATA_COMPRESSION = '+@VAR_DATA_COMPRESSION+');''','+'';''')+' as Command
    FROM sys.indexes ind
    INNER JOIN sys.tables t ON ind.object_id = t.object_id '+iif(@VAR_table!='','and t.name= '''+@VAR_table+'''','')+' '+'
    INNER JOIN sys.schemas sh on sh.schema_id = t.schema_id '+iif(@VAR_schema!='','and sh.Name = '''+@VAR_schema+'''','')+' '+'
    '+iif(@VAR_Partitions='EXPLICIT','INNER JOIN (select distinct object_id, partition_number, (select iif(count(*)>1,1,0) as A from sys.partitions where object_id = TabA.object_id and data_compression in (3,4)) as FgPart
     from sys.dm_db_column_store_row_group_physical_stats TabA
     where (iif(TabA.deleted_rows>0,TabA.deleted_rows*100/TabA.total_rows,0)>='+@VAR_Fragmentation+')
     OR (TabA.state in (1,2))
     OR ((TabA.state=3 AND TabA.trim_reason not in (1,4,5,6) AND (TabA.total_rows*100/1048576)<=100-'+@VAR_Fragmentation+'))) stor on stor.object_id=ind.object_id','')+'
    WHERE ind.type in (5,6)'
     END;

    IF OBJECT_ID('tempdb..#Temp_command') IS NOT NULL drop table #temp_command;
    create table #temp_command (RowId int identity(1,1) not NULL,Command varchar(500) NULL);

    begin try
     insert into #temp_command exec(@SQL);
     
     if @Debugging=1 begin print (@SQL) end;

     if not exists(select 1 from #temp_command)
      Begin
       print 'No commands to be proceeded: the name of a schema or a table might be wrong OR the table includes no partitions has to be proceeded.'+@linebreak+@SQL;  
      end;
     declare @itr int =1;
     declare @Finish int = (select max(RowId) from #temp_command);
     declare @SQL1 varchar(8000) =NULL;

     while @itr<=@Finish
      BEgin
       set @SQL1='USE ['+@VAR_database+']; '+(select Command from #temp_command where RowId=@itr)
       begin try
        if @Debugging=1
         begin
          print (@SQL1);
         end
        else
         begin
          exec (@SQL1);
          print @VAR_Mode+' successed! - '+@SQL1;
         end;   
       end try
       begin catch
        print @VAR_Mode+' FAILED! - '+@SQL1;
       end catch;
       set @itr=@itr+1;
      End;
    end try
    begin catch
     print 'Wrong command: '+@linebreak+@SQL;
    end catch;
    END
    GO

  • There is a way how to contribute a script - http://www.sqlservercentral.com/Contributions/Home 😉

  • Evgeny - Wednesday, December 27, 2017 6:17 PM

    There is a way how to contribute a script - http://www.sqlservercentral.com/Contributions/Home 😉

    Spasibo Evgeny 🙂
    I have already found it and published it there es well, but I don't know if it is possible and simple to edit something (in case of some updates in the query)  , without to stuck in an approuval round.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply