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