Columnstore Indexes- Identification and space usage

How to find tables with columnstore indexes

As we know that SQL 2012 has added feature of Nonclustered Columnstore Index. SQL 2014 onwards it will support Clustered Columnstore Indexes also.

As databases are growing we also need to track which tables has clustered and nonclustered columnstore indexes.



Sys.indexes has added two more type values to find tables which has columnstore indexes

Type 5 for CLUSTERED COLUMNSTORE

If the table has clustered columnstore index it will not support any other indexes.

Type 6 for NONCLUSTERED COLUMNSTORE

If it has nonclustered columnstore index it can support additional indexes.




CREATE TABLE temp1
(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX  idx1 ON temp1

Creating other index will fail as columnstore index doesn't support any other index. 

CREATE INDEX IDX2 ON temp1 (id)


 



CREATE TABLE temp2
(id INT)

CREATE NONCLUSTERED COLUMNSTORE INDEX  idx1 ON temp2(id)

CREATE INDEX idx  ON temp2(id)

SELECT OBJECT_NAME(OBJECT_ID),* FROM SYS.INDEXES WHERE TYPE IN (5,6)



How to get Index Size 

Also we can run below query to get space used by indexes.



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


SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
       SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME = 'temp1'
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name






No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts