Statistiche nel data warehousing di Fabric

  • Articolo

Si applica a: Endpoint di analisi SQL e Warehouse in Microsoft Fabric

Warehouse in Microsoft Fabric usa un motore di query per creare un piano di esecuzione per una determinata query SQL. Quando si invia una query, Query Optimizer tenta di enumerare tutti i piani possibili e scegliere il candidato più efficiente. Per determinare quale piano richiederebbe il minor sovraccarico (I/O, CPU, memoria), il motore deve essere in grado di valutare la quantità di lavoro o righe che potrebbero essere elaborate in ogni operatore. Quindi, in base al costo di ogni piano, sceglie quello con la quantità minima di lavoro stimato. Le statistiche sono oggetti che contengono informazioni rilevanti sui dati, per consentire a Query Optimizer di stimare questi costi.

Come sfruttare le statistiche

Per ottenere prestazioni ottimali delle query, è importante disporre di statistiche accurate. Microsoft Fabric supporta attualmente i percorsi seguenti per fornire statistiche pertinenti e aggiornate:

Statistiche manuali per tutte le tabelle

L'opzione tradizionale di mantenere l'integrità delle statistiche è disponibile in Microsoft Fabric. Gli utenti possono creare, aggiornare ed eliminare le statistiche a colonna singola basate su istogramma rispettivamente con CREATE STATISTICS, UPDATE STATISTICS e DROP STATISTICS. Gli utenti possono anche visualizzare il contenuto delle statistiche a colonna singola basate su istogramma con DBCC SHOW_STATISTICS. Attualmente è supportata una versione limitata di queste istruzioni.

  • Se si creano manualmente statistiche, è consigliabile concentrarsi su quelle usate in modo predefinito nel carico di lavoro di query, in particolare in GROUP BYs, ORDER BYs, filtri e JOIN.
  • È consigliabile aggiornare regolarmente le statistiche a livello di colonna dopo le modifiche apportate ai dati che modificano significativamente il conteggio delle righe o la distribuzione dei dati.

Esempi di manutenzione manuale delle statistiche

Per creare statistiche sulla dbo.DimCustomer tabella, in base a tutte le righe di una colonna CustomerKey:

CREATE STATISTICS DimCustomer_CustomerKey_FullScan
ON dbo.DimCustomer (CustomerKey) WITH FULLSCAN;

Per aggiornare manualmente l'oggetto DimCustomer_CustomerKey_FullScanstatistiche, ad esempio dopo un aggiornamento di dati di grandi dimensioni:

UPDATE STATISTICS dbo.DimCustomer (DimCustomer_CustomerKey_FullScan) WITH FULLSCAN;

Per visualizzare informazioni sull'oggetto statistiche:

DBCC SHOW_STATISTICS ("dbo.DimCustomer", "DimCustomer_CustomerKey_FullScan");

Per visualizzare solo informazioni sull'istogramma dell'oggetto statistiche:

DBCC SHOW_STATISTICS ("dbo.DimCustomer", "DimCustomer_CustomerKey_FullScan") WITH HISTOGRAM;

Per eliminare manualmente l'oggetto DimCustomer_CustomerKey_FullScanstatistiche :

DROP STATISTICS dbo.DimCustomer.DimCustomer_CustomerKey_FullScan;

Gli oggetti T-SQL seguenti possono essere usati anche per controllare le statistiche create manualmente e create automaticamente in Microsoft Fabric:

Statistiche automatiche nella query

Ogni volta che si esegue una query e Query Optimizer richiede statistiche per l'esplorazione del piano, Microsoft Fabric creerà automaticamente tali statistiche se non esistono già. Dopo aver creato le statistiche, Query Optimizer può usarle per stimare i costi del piano della query di attivazione. Inoltre, se il motore di query determina che le statistiche esistenti rilevanti per la query non riflettono più in modo accurato i dati, tali statistiche verranno aggiornate automaticamente. Poiché queste operazioni automatiche vengono eseguite in modo sincrono, è possibile prevedere che la durata della query includa questa volta se le statistiche necessarie non esistono ancora o sono state apportate modifiche significative ai dati dopo l'ultimo aggiornamento delle statistiche.

Per verificare le statistiche automatiche in fase di query

Esistono diversi casi in cui è possibile prevedere un certo tipo di statistiche automatiche. Le statistiche più comuni sono le statistiche basate su istogrammi, richieste da Query Optimizer per le colonne a cui si fa riferimento in GROUP BYs, JOINs, clausole DISTINCT, filtri (clausole WHERE) e ORDER BYs. Ad esempio, se si vuole visualizzare la creazione automatica di queste statistiche, una query attiverà la creazione se le statistiche per COLUMN_NAME non esistono ancora. Ad esempio:

SELECT <COLUMN_NAME>
FROM <YOUR_TABLE_NAME>
GROUP BY <COLUMN_NAME>;

In questo caso, è necessario prevedere che le statistiche per COLUMN_NAME siano state create. Se la colonna è anche una colonna varchar, vengono create anche le statistiche relative alla lunghezza media delle colonne. Se si desidera convalidare le statistiche sono state create automaticamente, è possibile eseguire la query seguente:

select
    object_name(s.object_id) AS [object_name],
    c.name AS [column_name],
    s.name AS [stats_name],
    s.stats_id,
    STATS_DATE(s.object_id, s.stats_id) AS [stats_update_date], 
    s.auto_created,
    s.user_created,
    s.stats_generation_method_desc 
FROM sys.stats AS s 
INNER JOIN sys.objects AS o 
ON o.object_id = s.object_id 
INNER JOIN sys.stats_columns AS sc 
ON s.object_id = sc.object_id 
AND s.stats_id = sc.stats_id 
INNER JOIN sys.columns AS c 
ON sc.object_id = c.object_id 
AND c.column_id = sc.column_id
WHERE o.type = 'U' -- Only check for stats on user-tables
    AND s.auto_created = 1
    AND o.name = '<YOUR_TABLE_NAME>'
ORDER BY object_name, column_name;

Questa query cerca solo statistiche basate su colonne. Per visualizzare tutte le statistiche esistenti per questa tabella, rimuovere i NOMI JOIN in sys.stats_columns e sys.columns.

È ora possibile trovare l'oggetto statistics_name della statistica dell'istogramma generato automaticamente (dovrebbe essere simile _WA_Sys_00000007_3B75D760a ) ed eseguire il T-SQL seguente:

DBCC SHOW_STATISTICS ('<YOUR_TABLE_NAME>', '<statistics_name>');

Ad esempio:

DBCC SHOW_STATISTICS ('sales.FactInvoice', '_WA_Sys_00000007_3B75D760');

Il Updated valore nel set di risultati di DBCC SHOW_STATISTICS deve essere una data (in formato UTC) simile a quando è stata eseguita la query GROUP BY originale.

Queste statistiche generate automaticamente possono quindi essere sfruttate nelle query successive dal motore di query per migliorare i costi del piano e l'efficienza di esecuzione. Se si verificano modifiche sufficienti nella tabella, il motore di query aggiornerà anche tali statistiche per migliorare l'ottimizzazione delle query. Lo stesso esercizio di esempio precedente può essere applicato dopo la modifica significativa della tabella. In Infrastruttura il motore di query SQL usa la stessa soglia di ricompilazione di SQL Server 2016 (13.x) per aggiornare le statistiche.

Tipi di statistiche generate automaticamente

In Microsoft Fabric sono disponibili più tipi di statistiche generate automaticamente dal motore per migliorare i piani di query. Attualmente, sono disponibili in sys.stats , anche se non tutti sono interattivi:

  • Statistiche istogrammi
    • Creazione per colonna in cui sono necessarie statistiche istogrammi in fase di query
    • Questi oggetti contengono informazioni sull'istogramma e sulla densità relative alla distribuzione di una determinata colonna. Analogamente alle statistiche create automaticamente in fase di query nei pool dedicati di Azure Synapse Analytics.
    • Il nome inizia con _WA_Sys_.
    • Il contenuto può essere visualizzato con DBCC SHOW_STATISTICS
  • Statistiche relative alla lunghezza media delle colonne
    • Creato per colonne di caratteri variabili (varchar) maggiori di 100 che richiedono una lunghezza media della colonna in fase di query.
    • Questi oggetti contengono un valore che rappresenta la dimensione media della riga della colonna varchar al momento della creazione delle statistiche.
    • Il nome inizia con ACE-AverageColumnLength_.
    • Il contenuto non può essere visualizzato e non può essere visualizzato dall'utente.
  • Statistiche di cardinalità basate su tabella
    • Creato per tabella che richiede la stima della cardinalità in fase di query.
    • Questi oggetti contengono una stima del conteggio delle righe di una tabella.
    • Denominato ACE-Cardinality.
    • Il contenuto non può essere visualizzato e non può essere visualizzato dall'utente.

Limiti

  • È possibile creare e modificare manualmente solo statistiche istogrammi a colonna singola.
  • La creazione di statistiche su più colonne non è supportata.
  • Altri oggetti statistiche possono essere visualizzati in sys.stats, a parte le statistiche create manualmente e create automaticamente. Questi oggetti non vengono usati per l'ottimizzazione delle query.

Contenuto correlato