Dela via


Utvärdera och korrigera klustrad kolumnlagringsindexhälsa i en dedikerad SQL-pool

Gäller för: Azure Synapse Analytics

Den här artikeln beskriver en något annorlunda metod för att utvärdera CCI-hälsotillståndet (Clustered ColumnStore Index). Följ stegen i följande avsnitt eller kör stegen i notebook-filen via Azure Data Studio.

Obs!

Innan du försöker öppna den här notebook-filen kontrollerar du att Azure Data Studio är installerat på den lokala datorn. Om du vill installera det går du till Lär dig hur du installerar Azure Data Studio.

I allmänhet påverkar två viktiga faktorer kvaliteten på en CCI:

  • Komprimera radgrupper och metadata – Det faktiska antalet radgrupper ligger nära det ideala antalet rader i radgruppen.

  • Komprimerade radgrupper – Radgrupper använder kolumnlagringskomprimering.

Andra villkor, till exempel små tabeller, överpartitionerade tabeller eller underpartitionerade tabeller, är utan tvekan av dålig kvalitet eller hälsa. Dessa villkor klassificeras dock bättre som designförbättringsmöjligheter som kan utvärderas i steg 4.

Steg 1: Analysera en sammanfattning av din CCI-hälsa

Använd följande fråga för att hämta en enda rad med mått.

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

Från resultatet kan du få en översikt över CCI-hälsan för din dedikerade SQL-pool. Den här informationen är inte direkt användbar, men hjälper dig att förstå vikten av underhållsrutiner för att uppnå ett idealiskt tillstånd.

Kolumnnamn Beskrivning
tables_assessed_count Antal CCI-tabeller
partitions_assessed_count Antal partitioner
Observera: Icke-partitionerade tabeller räknas som 1.
actual_rowgroup_count Fysiskt antal radgrupper
ideal_rowgroup_count Beräknat antal radgrupper som skulle vara idealiska för antalet rader
uncompressed_rowgroup_count Antal radgrupper som innehåller okomprimerade data. (Kallas även: ÖPPNA rader)
actual_size_in_mb Fysisk storlek på CCI-data i MB
uncompressed_size_in_mb Fysisk storlek på okomprimerade data i MB
excess_pct Procent av radgrupper som kan optimeras ytterligare
excess_size_in_mb Uppskattad MB från icke-optimerade radgrupper

Steg 2: Analysera detaljerad CCI-information

Följande fråga innehåller en detaljerad rapport om vilka tabellpartitioner som är kandidater för återskapande. CCI-information finns i tre mått som hjälper dig att identifiera och prioritera tabeller/partitioner som skulle ha störst nytta av underhåll. Ange lämpliga tröskelvärden för dessa mått i WHERE -satsen och använd sedan de mått som är mest intressanta för dig i ORDER BY satsen. Den detaljerade informationen kan också vara användbar för att avgöra om din dedikerade SQL-pool påverkas av ett stort antal små, fragmenterade tabeller, vilket kan leda till fördröjningar i kompilering.

Obs!

Den kommenterade fnMs_GenerateIndexMaintenanceScript funktionen är en tabellvärdesfunktion (TVF) som kan generera vanliga skript för att underhålla index. Om du vill hämta underhållsskripten i resultatet avkommenterar du raderna 37 och 39. Innan du kör frågan använder du skriptet i avsnittet Generera indexunderhållsskript för att skapa funktionen. När du kör underhållsskriptet som du får från resultatet bör du använda en resursklass med lämplig storlek, till exempel större eller xlargerc.

Kolumnnamn Kvalitetsegenskaper Beskrivning
excess_pct Kompakthet Procentandel av radgrupper som kan komprimeras ytterligare
excess_size_in_mb Kompakthet Uppskattad MB från icke-optimerade radgrupper
OPEN_rowgroup_size_in_mb Komprimering Faktisk MB av okomprimerade data i indexet
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;

Steg 3: Vad du ska göra när underhåll inte gör CCI-hälsan bättre

Att utföra underhåll på en tabell/partition kan resultera i något av följande scenarier:

  • excess_pct eller excess_size_in_mb är större än den var före underhåll.
  • Underhållssatsen misslyckas med otillräckligt minne.

Vanliga orsaker

  • Otillräckliga resurser.
  • Otillräcklig servicenivå (DWU).
  • Tabellen är stor och inte partitionerad.
  • Öka resurserna för underhållsinstruktionerna genom att ändra den körbara användarens resursklass eller arbetsbelastningsgrupp.
  • Öka tillfälligt DWU-nivån för att utföra underhållet.
  • Implementera en partitioneringsstrategi för den problematiska tabellen och utför sedan underhåll på partitionerna.

Steg 4: Sök efter designförbättringsmöjligheter

Även om den inte är omfattande kan följande fråga hjälpa dig att identifiera potentiella möjligheter som ofta kan orsaka prestanda- eller underhållsproblem som rör CCI:er.

Affärsmöjlighetsrubrik Beskrivning Rekommendationer
Liten tabell Tabellen innehåller färre än 15 miljoner rader Överväg att ändra indexet från CCI till:
  • Heap för mellanlagringstabeller
  • Standardklusterindex (radlagring) för dimension eller andra små sökningar
Partitionering av affärsmöjlighet eller underpartitionerad tabell Beräknat idealiskt antal radgrupper är större än 180 M (eller ~188 miljoner rader) Implementera en partitioneringsstrategi eller ändra den befintliga partitioneringsstrategin för att minska antalet rader per partition till mindre än 188 M (cirka tre radgrupper per partition per distribution)
Överpartitionerad tabell Tabellen innehåller färre än 15 miljoner rader för den största partitionen Överväga:
  • Ändra indexet från CCI till standardklusterindex (radlagring)
  • Ändra partitionsintervallet till närmare 60 M rader per partition
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

Generera skript för indexunderhåll

Kör följande fråga för att skapa dbo.fnMs_GenerateIndexMaintenanceScript en funktion i din dedikerade SQL-pool. Den här funktionen genererar skript för att optimera din CCI på tre sätt. Du kan använda den här funktionen för att underhålla inte bara CCIs utan även klustrade index (radlagring).

Parametrar

Parameternamn Obligatoriskt Beskrivning
@object_id J object_id av tabellen till målet
@partition_number J partition_number från sys.partitions till mål. Om tabellen inte är partitionerad anger du 1.

Utdatatabell

Kolumnnamn Beskrivning
rebuild_script Genererad ALTER INDEX ALL ... REBUILD instruktion för den angivna tabellen/partitionen. Icke-partitionerade heaps returnerar NULL.
reorganize_script Genererad ALTER INDEX ALL ... REORGANIZE instruktion för den angivna tabellen/partitionen. Icke-partitionerade heaps returnerar NULL.
partition_switch_script Gäller endast för partitionerade tabeller. om NULL tabellen inte är partitionerad eller om ett ogiltigt partitionsnummer har angetts. Om CCI skapades med en ORDER -sats återges den.
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

Mer information

Om du vill få en mer djupgående förståelse och skaffa extra utvärderingsverktyg för CCI på den dedikerade SQL-poolen kan du läsa: