SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simplified maintenance of columnstore indexes


Simplified maintenance of columnstore indexes

Author
Message
alexander.parakhnevich 27977
alexander.parakhnevich 27977
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 70

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




Evgeny Garaev
Evgeny Garaev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4242 Visits: 1530
There is a way how to contribute a script - http://www.sqlservercentral.com/Contributions/Home Wink
alexander.parakhnevich 27977
alexander.parakhnevich 27977
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 70
Evgeny - Wednesday, December 27, 2017 6:17 PM
There is a way how to contribute a script - http://www.sqlservercentral.com/Contributions/Home Wink


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search