October 27, 2015 at 2:49 am
I have a large table with clustered columnstore index
how I could deternime when it is necessery to rebuid it ?
October 27, 2015 at 4:59 am
If I would use SSIS to do the compare, I would need a merge join to join the two scans from the tables which would mean the data needs to be sorted.
Could you not use a cached Lookup on the IDs, as opposed to a Merge Join
and then use an SSIS script (gives you access to C# hashbytes function which isn't limited to varchar(8000)) to compare for UPDATES.
There is a good article on it here.
October 27, 2015 at 5:06 am
Ignore pervious post accidently posted to the wrong thread.
October 27, 2015 at 6:34 am
its all there on BOL, a simple google search would have been much easier than posting on this forum
https://msdn.microsoft.com/en-us/library/dn589807%28v=sql.120%29.aspx#rebuild
October 27, 2015 at 7:33 am
Niko Neugebauer has written a great series of blog posts about Clustered ColumnStore internals.
Clustered Columnstore Indexes – part 36 (“Maintenance Solutions for Columnstore”)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2015 at 10:28 am
Thank you
October 27, 2015 at 11:36 pm
October 28, 2015 at 1:53 am
From this query
SELECT object_name(p.object_id) as TableName,
p.partition_number as Partition
,cast( Avg( (rg.deleted_rows * 1. / rg.total_rows) * 100 ) as Decimal(5,2)) as 'Total Fragmentation (Percentage)',
sum (case rg.deleted_rows when rg.total_rows then 1 else 0 end ) as 'Deleted Segments Count',
cast( (sum (case rg.deleted_rows when rg.total_rows then 1 else 0 end ) * 1. / count(*)) * 100 as Decimal(5,2)) as 'DeletedSegments (Percentage)'
FROM sys.partitions AS p
INNER JOIN sys.column_store_row_groups rg
ON p.object_id = rg.object_id
where rg.state = 3 -- Compressed (Ignoring: 0 - Hidden, 1 - Open, 2 - Closed, 4 - Tombstone)
group by p.object_id, p.partition_number
order by object_name(p.object_id);
I found that total fragmentation is 0.35%
but my queries became slow
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply