System tables for columnstore indexes - sys.column_store_row_groups


System tables for columnstore indexes.

Columnstore index is a new feature and will be used heavily once 2014 is live.
We also have to be familiar with lots of stuffs related to it.



System table for columnstore indexes

sys.column_store_row_groups

This table provides information for clustered columnstore index on a per-segment basis

State Description

1.Open:-Group is updatable  , in rowstore format not in columnstore format
2.Closed :-Once open group fulls its state changed to closed, Data can not be inserted in it.
3.Compressed:- Closed group is compressed by tuple mover and state will be displayed as compressed

Deleted rows :-

Rows are not physically deleted until tables/index are rebuild as its a soft delete
Till then rows which are deleted will be displayed in deleted_row column .
When large number of rows are deleted in a group tables becomes inefficient.
So to reduce size and i/o and improve performance we have to rebuild table/index.
If every rows from a group is deleted than its deallocated by tuple mover and its state will be retired

Updating rows

If rows are updating from compressed groups they are deleted in compressed group and inserted in the current open group.


Lets see an example over here






CREATE TABLE t
(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX idx ON t



DECLARE @i INT
SET @i = 1
WHILE @i <= 1000000
BEGIN
       INSERT INTO t
       VALUES (@i)
       SET @i = @i  + 1
END




Now we will insert more rows again



DECLARE @i INT
SET @i = 1000000
WHILE @i <= 2000000
BEGIN
       INSERT INTO t
       VALUES (@i)
       SET @i = @i  + 1
END






Now we can see more groups are created to accept new rows.








Now we will delete rows



DELETE FROM t WHERE id < 1000



We can see this rows are showed in deleted column
This is soft delete and rows present in table

To remove its completely we have to rebuild table or index.




ALTER TABLE t REBUILD
or
ALTER INDEX idx ON t REBUILD
 
 


Now we can see all deleted rows are removed completely.



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts