December 22, 2017 at 2:35 pm
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
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
December 27, 2017 at 6:17 pm
There is a way how to contribute a script - http://www.sqlservercentral.com/Contributions/Home 😉
December 28, 2017 at 1:46 am
Evgeny - Wednesday, December 27, 2017 6:17 PMThere 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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply