Table Info for Clustered Column Store Indexes

I’ve used a variation of this query for years and it worked pretty well, until clustered column store indexes came along.

The general problem is that the column store indexes store the storage in LOB records, although data records are also included.  This resulted in the tables with column store clustered indexes having twice the row count.

I am now working with Open Live Writer, and the plugins seem to be unavailable, yet…

 

 

WITH nci AS (    SELECT    i.[object_id]
                        , COUNT(i.[object_id]) AS [NumIndexes]
                        , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) AS GBytes
                FROM        sys.indexes i
                        INNER JOIN sys.partitions p
                            ON    p.[object_id] = i.[object_id]
                            AND    p.index_id = i.index_id
                        INNER JOIN sys.allocation_units au
                            ON    au.container_id = p.partition_id
                WHERE    i.index_id = 2
                AND        i.[object_id] > 100
                GROUP BY i.[object_id] )

SELECT    db_name() AS DBName
        , i.object_id
        , OBJECT_NAME(i.object_id) AS [TableName]
        , i.name AS [ClusteredIndexName]
        , i.index_id
        , i.type_desc AS index_type
        , SUM(p.[rows]) AS row_cnt
        , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) AS CI_GBytes
        , nci.[NumIndexes]
        , nci.GBytes AS [NCI_IndexGBytes]
        , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) + nci.GBytes AS [TotalTableSize]
        , p.data_compression_desc
FROM        sys.indexes i
        INNER JOIN sys.partitions p
            ON    p.[object_id] = i.[object_id]
            AND    p.index_id = i.index_id
        INNER JOIN sys.allocation_units au
            ON    au.container_id = p.partition_id
            AND    au.[type] = 1        — IN_ROW_DATA, required for column store indexes, which also has LOB data.
        LEFT OUTER JOIN nci nci
            ON    nci.[object_id] = i.[object_id]
WHERE    i.index_id <= 1
AND        i.[type] in (0, 1)        — HEAP or CLUSTERED
AND        i.[object_id] > 100
GROUP BY i.[object_id]
        ,    i.name
        , i.index_id
        , i.type_desc
        , nci.NumIndexes
        , nci.GBytes
        , p.data_compression_desc

UNION

SELECT    db_name() AS DBName
        , i.object_id
        , OBJECT_NAME(i.object_id) AS [TableName]
        , i.name AS [ClusteredIndexName]
        , i.index_id
        , i.type_desc AS index_type
        , SUM(p.[rows]) AS row_cnt
        , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) AS CI_GBytes
        , nci.[NumIndexes]
        , nci.GBytes AS [NCI_IndexGBytes]
        , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) + nci.GBytes AS [TotalTableSize]
        , p.data_compression_desc
FROM        sys.indexes i
        INNER JOIN sys.partitions p
            ON    p.[object_id] = i.[object_id]
            AND    p.index_id = i.index_id
        INNER JOIN sys.allocation_units au
            ON    au.container_id = p.partition_id
            AND    au.[type] = 2        — IN_ROW_DATA, required for column store indexes, which also has LOB data.
        LEFT OUTER JOIN nci nci
            ON    nci.[object_id] = i.[object_id]
WHERE    i.index_id <= 1
AND        i.[type] in (5)        — Column Store
AND        i.[object_id] > 100
GROUP BY i.[object_id]
        ,    i.name
        , i.index_id
        , i.type_desc
        , nci.NumIndexes
        , nci.GBytes
        , p.data_compression_desc
ORDER BY CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1073741824.0 AS NUMERIC(12, 4)) + nci.GBytes DESC

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>