Valutare e correggere l'integrità dell'indice columnstore in cluster in un pool SQL dedicato
Si applica a: Azure Synapse Analytics
Questo articolo presenta un approccio leggermente diverso alla valutazione dell'integrità dell'indice columnstore cluster ( CCI). Seguire la procedura descritta nelle sezioni seguenti o eseguire i passaggi nel notebook tramite Azure Data Studio.
Note
Prima di tentare di aprire questo notebook, assicurarsi che Azure Data Studio sia installato nel computer locale. Per installarlo, vedere Informazioni su come installare Azure Data Studio.
In generale, due fattori principali influiscono sulla qualità di un CCI:
Compattare rowgroup e metadati : il conteggio effettivo dei rowgroup è vicino al conteggio ideale per il numero di righe nel rowgroup.
Rowgroup compressi : i rowgroup usano la compressione columnstore.
Altre condizioni, ad esempio tabelle di piccole dimensioni, tabelle con partizionamento eccessivo o tabelle con partizionamento inferiore, sono probabilmente di scarsa qualità o integrità. Tuttavia, queste condizioni sono classificate meglio come opportunità di miglioramento della progettazione che possono essere valutate nel passaggio 4.
Passaggio 1: Analizzare un riepilogo dell'integrità CCI
Usare la query seguente per ottenere una singola riga di metriche.
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
Dal risultato è possibile ottenere una panoramica dell'integrità CCI per il pool SQL dedicato. Queste informazioni non sono direttamente utilizzabili, ma consentono di comprendere l'importanza delle routine di manutenzione per ottenere uno stato ideale.
| Nome colonna | Descrizione |
|---|---|
tables_assessed_count |
Conteggio delle tabelle CCI |
partitions_assessed_count |
Conteggio delle partizioni Nota: le tabelle non partizionate verranno conteggiate come 1. |
actual_rowgroup_count |
Numero fisico di rowgroup |
ideal_rowgroup_count |
Numero calcolato di rowgroup ideali per il numero di righe |
uncompressed_rowgroup_count |
Numero di rowgroup che contengono dati non compressi. (Noto anche come: righe OPEN) |
actual_size_in_mb |
Dimensioni fisiche dei dati CCI in MB |
uncompressed_size_in_mb |
Dimensioni fisiche dei dati non compressi in MB |
excess_pct |
Percentuale di rowgroup che potrebbero essere ulteriormente ottimizzati |
excess_size_in_mb |
MB stimato da rowgroup non ottimizzati |
Passaggio 2: Analizzare informazioni dettagliate su CCI
La query seguente fornisce un report dettagliato di quali partizioni di tabella sono candidate per la ricompilazione. I dettagli di CCI vengono forniti in tre metriche che consentono di identificare e definire le priorità delle tabelle/partizioni che potrebbero trarre vantaggio dalla manutenzione. Impostare i valori di soglia appropriati per queste metriche nella clausola WHERE e quindi nella clausola ORDER BY, usare le metriche che sono di interesse per l'utente. Le informazioni dettagliate possono essere utili anche per determinare se il pool SQL dedicato è interessato da un numero elevato di tabelle piccole e frammentate, che possono causare ritardi nella compilazione.
Note
La funzione commentata fnMs_GenerateIndexMaintenanceScript è una funzione con valori di tabella (TVF) che può generare script comuni per la gestione degli indici. Per ottenere gli script di manutenzione nel risultato, rimuovere il commento dalle righe 37 e 39. Prima di eseguire la query, usare lo script nella sezione Generare script di manutenzione dell'indice per creare la funzione. Quando si esegue lo script di manutenzione ottenuto dal risultato, assicurarsi di usare una classe di risorse di dimensioni appropriate, ad esempio largerc o xlargerc.
| Nome colonna | Caratteristica della qualità | Descrizione |
|---|---|---|
excess_pct |
Compattezza | Percentuale di rowgroup che potrebbero essere ulteriormente compattati |
excess_size_in_mb |
Compattezza | MB stimato da rowgroup non ottimizzati |
OPEN_rowgroup_size_in_mb |
Compressione | MB effettivi di dati non compressi nell'indice |
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;
Passaggio 3: Cosa fare quando la manutenzione non migliora l'integrità del CCI
L'esecuzione della manutenzione in una tabella/partizione può comportare uno degli scenari seguenti:
excess_pctoexcess_size_in_mbè maggiore di quanto fosse prima della manutenzione.- L'istruzione di manutenzione ha esito negativo con memoria insufficiente.
Cause tipiche
- Risorse insufficienti.
- Livello di servizio insufficiente (DWU).
- La tabella è grande e non partizionata.
Mitigazioni consigliate
- Aumentare le risorse per le istruzioni di manutenzione modificando la classe di risorse o il gruppo di carico di lavoro dell'utente in esecuzione.
- Aumentare temporaneamente il livello DWU per eseguire la manutenzione.
- Implementare una strategia di partizionamento per la tabella problematica e quindi eseguire la manutenzione sulle partizioni.
Passaggio 4: Verificare le opportunità di miglioramento della progettazione
Anche se non completa, la query seguente consente di identificare le potenziali opportunità che causano più comunemente problemi di prestazioni o manutenzione relativi a CCI.
| Titolo opportunità | Descrizione | Consigli |
|---|---|---|
| Tabella di piccole dimensioni | La tabella contiene meno di 15M di righe | Valutare la possibilità di modificare l'indice da CCI a:
|
| Opportunità di partizionamento o tabella sottoposta a partizionamento | Il numero di rowgroup ideali calcolato è maggiore di 180M (o ~188M righe) | Implementare una strategia di partizionamento o modificare la strategia di partizionamento esistente per ridurre il numero di righe per partizione a meno di 188M (circa tre gruppi di righe per partizione per distribuzione) |
| Tabella partizionata eccessivamente | La tabella contiene meno di 15M di righe per la partizione più grande | Valutare:
|
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
Generare script di manutenzione degli indici
Eseguire la query seguente per creare dbo.fnMs_GenerateIndexMaintenanceScript una funzione nel pool SQL dedicato. Questa funzione genera script per ottimizzare l'CCI in tre modi. È possibile usare questa funzione per gestire non solo gli indici CCI, ma anche gli indici cluster (rowstore).
Parametri
| Nome parametro | Obbligatorio | Descrizione |
|---|---|---|
@object_id |
Y | object_id della tabella di destinazione |
@partition_number |
Y | partition_number da sys.partitions a destinazione. Se la tabella non è partizionata, specificare 1. |
Tabella di output
| Nome colonna | Descrizione |
|---|---|
rebuild_script |
Istruzione generata ALTER INDEX ALL ... REBUILD per la tabella/partizione specificata. Gli heap non partizionati restituiranno NULL. |
reorganize_script |
Istruzione generata ALTER INDEX ALL ... REORGANIZE per la tabella/partizione specificata. Gli heap non partizionati restituiranno NULL. |
partition_switch_script |
Si applica solo alle tabelle partizionate; sarà NULL se la tabella non è partizionata o se viene specificato un numero di partizione non valido. Se l'CCI è stato creato con una ORDER clausola , verrà eseguito il rendering. |
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
Ulteriori informazioni
Per acquisire una conoscenza più approfondita e acquisire strumenti di valutazione aggiuntivi per CCI nel pool SQL dedicato, vedere: