Two major factors affect the quality of a CCI:

• Compact rowgroups and metadata – The actual rowgroup count is close to the ideal count for the number of rows in the rowgroup.
• Compressed rowgroups – Rowgroups are using columnstore compression.

Step 1: Analyze a summary of your CCI health

WITH cci_detail AS (
    SELECT t.object_id,
          rg.partition_number,
          COUNT(*) AS total_rowgroup_count,
          SUM(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroup_count,
          CEILING((SUM(rg.[total_rows]) - SUM(rg.deleted_rows))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
          SUM(rg.size_in_bytes/1024/1024.) AS size_in_mb,
          SUM(CASE WHEN rg.state = 1 THEN rg.size_in_bytes END /1024/1024.) AS open_size_in_mb
   FROM sys.pdw_nodes_column_store_row_groups rg
   JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id
       AND rg.pdw_node_id = nt.pdw_node_id
       AND rg.distribution_id = nt.distribution_id
   JOIN sys.pdw_table_mappings mp ON nt.name = mp.physical_name
   JOIN sys.tables t ON mp.object_id = t.object_id
   GROUP BY t.object_id,
            rg.partition_number
)
SELECT COUNT(DISTINCT object_id) AS tables_assessed_count,
       COUNT(*) AS partitions_assessed_count,
       SUM(total_rowgroup_count) AS actual_rowgroup_count,
       SUM(ideal_rowgroup_count) AS ideal_rowgroup_count,
       SUM(open_rowgroup_count) AS uncompressed_rowgroup_count,
       CAST(SUM(size_in_mb) AS DECIMAL(19, 4)) AS actual_size_in_mb,
       CAST(SUM(open_size_in_mb) AS DECIMAL(19, 4)) AS uncompressed_size_in_mb,
       CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 100. AS DECIMAL(9, 4)) AS excess_pct,
       CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 1. AS DECIMAL(9, 4)) * SUM(size_in_mb) AS excess_size_in_mb
FROM cci_detail

From the result, you can get an overview of the CCI health for your dedicated SQL pool. This information isn’t directly actionable but helps you understand the importance of maintenance routines for achieving an ideal state.

Step 2: Analyze detailed CCI information

The following query provides a detailed report of which table partitions are candidates for rebuilding. CCI details are provided in three metrics that help identify and prioritize tables/partitions that would benefit most from maintenance. Set the appropriate threshold values for these metrics in the WHERE clause, and then in the ORDER BY clause, use the metrics that are of most interest to you. 

WITH cci_info AS(
    SELECT t.object_id AS [object_id],
          MAX(schema_name(t.schema_id)) AS [schema_name],
          MAX(t.name) AS [table_name],
          rg.partition_number AS [partition_number],
          COUNT(DISTINCT rg.distribution_id) AS [distribution_count],
          SUM(rg.size_in_bytes/1024/1024) AS [size_in_mb],
          SUM(rg.[total_rows]) AS [row_count_total],
          COUNT(*) AS [total_rowgroup_count],
          CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 1 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [OPEN_rowgroup_size_in_mb],
          SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 2 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [CLOSED_size_in_mb],
          SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count],
          SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows],
          CAST(SUM(CASE WHEN rg.[State] = 3 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [COMPRESSED_size_in_mb],
          SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
   FROM sys.[pdw_nodes_column_store_row_groups] rg
   JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
       AND rg.[pdw_node_id] = nt.[pdw_node_id]
       AND rg.[distribution_id] = nt.[distribution_id]
   JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
   JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
   GROUP BY t.object_id,
            rg.partition_number
)
, calc_excess AS(
    SELECT *,
        CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 100. AS DECIMAL(9, 4)) AS [excess_pct],
        CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 1. AS DECIMAL(9, 4)) * size_in_mb AS [excess_size_in_mb]
   FROM cci_info
)
SELECT calc_excess.* 
    -- , script.*
FROM calc_excess
-- CROSS APPLY dbo.fnMs_GenerateIndexMaintenanceScript(object_id, partition_number) AS script
WHERE -- set your own threshold(s) for the following; 0 is the ideal, but usually not practical
  calc_excess.[excess_size_in_mb] > 300
  OR calc_excess.excess_pct > 0.1
  OR calc_excess.OPEN_rowgroup_size_in_mb > 100
ORDER BY calc_excess.[excess_size_in_mb] DESC;

Step 3: What to do when maintenance doesn’t make the CCI health better

Performing maintenance on a table/partition may result in one of the following scenarios:

• excess_pct or excess_size_in_mb is larger than it was before maintenance.
• The maintenance statement fails with insufficient memory.

Typical causes

• Insufficient resources.
• Insufficient service level (DWU).
• Table is large and not partitioned.

Recommended mitigations

• Increase resources for the maintenance statements by changing the executing user’s resource class or workload group
• Temporarily increase the DWU level to perform the maintenance.
• Implement a partitioning strategy for the problematic table and then perform maintenance on the partitions.

Step 4: Check for design improvement opportunities

Though not comprehensive, the following query can help you identify potential opportunities commonly found to cause performance or maintenance issues concerning CCIs.

WITH cci_info AS (
    SELECT t.object_id AS [object_id],
          MAX(SCHEMA_NAME(t.schema_id)) AS [schema_name],
          MAX(t.name) AS [table_name],
          rg.partition_number AS [partition_number],
          SUM(rg.[total_rows]) AS [row_count_total],
          CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count]
   FROM sys.[pdw_nodes_column_store_row_groups] rg
   JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
       AND rg.[pdw_node_id] = nt.[pdw_node_id]
       AND rg.[distribution_id] = nt.[distribution_id]
   JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
   JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
   GROUP BY t.object_id,
            rg.partition_number
)
SELECT object_id,
       MAX(SCHEMA_NAME),
       MAX(TABLE_NAME),
       COUNT(*) AS number_of_partitions,
       MAX(row_count_total) AS max_partition_row_count,
       MAX(ideal_rowgroup_count) partition_ideal_row_count,
       CASE
           -- non-partitioned tables
           WHEN COUNT(*) = 1 AND MAX(row_count_total) < 15000000 THEN 'Small table'
           WHEN COUNT(*) = 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Partitioning opportunity'
           -- partitioned tables
           WHEN COUNT(*) > 1 AND MAX(row_count_total) < 15000000 THEN 'Over-partitioned table'
           WHEN COUNT(*) > 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Under-partitioned table'
       END AS warning_category
FROM cci_info
GROUP BY object_id

Generate index maintenance scripts

Run the following query to create dbo.fnMs_GenerateIndexMaintenanceScript function on your dedicated SQL pool. This function generates scripts to optimize your CCI in three ways. You can use this function to maintain not only CCIs but also clustered (rowstore) indexes.

ParametersExpand table

Output tableExpand table

CREATE FUNCTION dbo.fnMs_GenerateIndexMaintenanceScript (@object_id INT, @partition_number INT = 1)
RETURNS TABLE
AS
RETURN(
    WITH base_info AS (
        SELECT
            t.object_id
            , SCHEMA_NAME(t.schema_id) AS [schema_name]
            , t.name AS table_name
            , i.index_type
            , i.index_cols
            , i.index_type_desc
            , tdp.distribution_policy_desc
            , c.name hash_distribution_column_name
        FROM sys.tables t
            JOIN (
                SELECT
                    i.object_id
                    , i.index_id
                    , MAX(i.type) AS index_type
                    , MAX(CASE WHEN i.type = 5 AND ic.column_store_order_ordinal != 0 THEN ' ORDER ' ELSE '' END)
                        + '(' + STRING_AGG(
                        CASE
                            WHEN i.type IN (1, 5) 
                                AND (ic.key_ordinal != 0 OR ic.column_store_order_ordinal != 0)
                                THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
                        END
                        , ',') WITHIN GROUP(ORDER BY ic.column_store_order_ordinal, ic.key_ordinal) + ')' AS index_cols
                    , MAX(i.type_desc)
                        + CASE
                            WHEN MAX(i.type) IN (1, 5) THEN ' INDEX'
                            ELSE ''
                        END COLLATE SQL_Latin1_General_CP1_CI_AS AS index_type_desc
                FROM sys.indexes i
                    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE i.index_id <= 1
                GROUP BY i.object_id, i.index_id
            ) AS i
                ON t.object_id = i.object_id
            JOIN sys.pdw_table_distribution_properties tdp ON t.object_id = tdp.object_id
            LEFT JOIN sys.pdw_column_distribution_properties cdp ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
            LEFT JOIN sys.columns c ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
        WHERE t.object_id = @object_id
    )
    , param_data_type AS (
        SELECT
            pp.function_id
            , typ.name AS data_type_name
            , CAST(CASE
                WHEN typ.collation_name IS NOT NULL THEN 1
                WHEN typ.name LIKE '%date%' THEN 1
                WHEN typ.name = 'uniqueidentifier' THEN 1
                ELSE 0
            END AS BIT) AS use_quotes_on_values_flag
        FROM sys.partition_parameters pp
            JOIN sys.types typ ON pp.user_type_id = typ.user_type_id
    )
    , boundary AS (
        SELECT
            t.object_id
            , c.name AS partition_column_name
            , pf.boundary_value_on_right
            , prv.boundary_id
            , prv.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 ELSE 0 END AS [partition_number]
            , CASE
                WHEN pdt.use_quotes_on_values_flag = 1 THEN '''' + CAST(
                    CASE pdt.data_type_name
                        WHEN 'date' THEN CONVERT(char(10), prv.value, 120)
                        WHEN 'smalldatetime' THEN CONVERT(VARCHAR, prv.value, 120)
                        WHEN 'datetime' THEN CONVERT(VARCHAR, prv.value, 121)
                        WHEN 'datetime2' THEN CONVERT(VARCHAR, prv.value, 121)
                        ELSE prv.value
                    END    
                    AS VARCHAR(32)) + ''''
                ELSE CAST(prv.value AS VARCHAR(32))
            END AS boundary_value
        FROM sys.tables t
            JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
            JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal = 1
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
            JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
            JOIN param_data_type pdt ON pf.function_id = pdt.function_id
            JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE t.object_id = @object_id
    )
    , partition_clause AS (
        SELECT
            object_id
            , COUNT(*) - 1 -- should always be the 2nd to last partition in stage table
                + CASE WHEN MAX([partition_number]) = @partition_number THEN 1 ELSE 0 END -- except when last partition
                AS [source_partition_number]
            , 'WHERE ' + MAX(partition_column_name)
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 
                    ' >= ' + MIN(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
                    ELSE 
                    ' <= ' + MAX(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
                END
                + ' AND ' + MAX(partition_column_name)
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 
                    ' < ' + MAX(boundary_value)
                    ELSE
                    ' > ' + MIN(boundary_value)
                END AS filter_clause
            , ', PARTITION (' + MAX(partition_column_name) + ' RANGE ' 
                + CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 'RIGHT' ELSE 'LEFT' END 
                + ' FOR VALUES(' + STRING_AGG(boundary_value, ',') + '))' AS [partition_clause]
        FROM boundary
        WHERE [partition_number] BETWEEN @partition_number - 1 AND @partition_number + 1
        GROUP BY object_id
    )
    SELECT
        CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REBUILD' 
            + CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END + ';' END AS [rebuild_script]
        , CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REORGANIZE' 
            + CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END
            + CASE WHEN index_type = 5 THEN ' WITH (COMPRESS_ALL_ROW_GROUPS = ON)' ELSE '' END + ';' END AS [reorganize_script]
        , 'CREATE TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] WITH(' + index_type_desc + ISNULL(index_cols, '')
            + ', DISTRIBUTION = ' + distribution_policy_desc + CASE WHEN distribution_policy_desc = 'HASH' THEN '(' + hash_distribution_column_name + ')' ELSE '' END
            + partition_clause.partition_clause + ') AS SELECT * FROM [' + [schema_name] + '].[' + [table_name] + '] ' + filter_clause + CASE WHEN index_type = 5 AND index_cols IS NOT NULL THEN ' OPTION(MAXDOP 1)' ELSE '' END +  ';'
            + ' ALTER TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] SWITCH PARTITION ' + CAST(source_partition_number AS VARCHAR(16))
            + ' TO [' + [schema_name] + '].[' + [table_name] + '] PARTITION ' + CAST(@partition_number AS VARCHAR(16))
            + ' WITH (TRUNCATE_TARGET = ON);'
            + ' DROP TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp];' AS [partition_switch_script]
    FROM base_info
        LEFT JOIN partition_clause
            ON base_info.object_id = partition_clause.object_id
);
GO