Ottimizzazione delle prestazioni con viste materializzate con pool SQL dedicato in Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse è un data warehouse relazionale su scala aziendale su una base data lake, con un'architettura futura, un'intelligenza artificiale predefinita e nuove funzionalità. Se non si ha familiarità con il data warehousing, iniziare con Fabric Data Warehouse. I carichi di lavoro esistenti del pool SQL dedicated possono eseguire l'aggiornamento a Fabric per accedere a nuove funzionalità tra data science, analisi in tempo reale e creazione di report.

• Inizia una prova gratuita di Fabric.
• Assistente alla migrazione per Fabric Data Warehouse.

Nel pool SQL dedicato, le viste materializzate forniscono un metodo di manutenzione ridotto per query analitiche complesse per ottenere prestazioni rapide senza alcuna modifica delle query. Questo articolo illustra le linee guida generali sull'uso di viste materializzate.

Viste materializzate e viste standard

Il pool SQL supporta viste standard e materializzate. Entrambe sono tabelle virtuali create con espressioni SELECT e presentate alle query come tabelle logiche. Le viste rivelano la complessità del calcolo dei dati comune e aggiungono un livello di astrazione alle modifiche di calcolo, quindi non è necessario riscrivere le query.

Una vista standard calcola i dati ogni volta che viene usata la vista. Non sono presenti dati archiviati su disco. Gli utenti usano in genere viste standard come strumento che consentono di organizzare gli oggetti logici e le query in un database. Per usare una vista standard, una query deve farvi riferimento diretto.

Una vista materializzata esegue il pre-calcolo, archivia e gestisce i dati nel pool SQL dedicato esattamente come una tabella. La ricomputazione non è necessaria ogni volta che viene usata una vista materializzata. Ecco perché le query che usano tutti o un subset dei dati nelle viste materializzate possono ottenere prestazioni più veloci. Ancora meglio, le query possono usare una vista materializzata senza farvi riferimento diretto, quindi non è necessario modificare il codice dell'applicazione.

La maggior parte dei requisiti di visualizzazione standard è ancora applicabile a una vista materializzata. Per informazioni dettagliate sulla sintassi della vista materializzata e altri requisiti, vedere CREATE MATERIALIZED VIEW AS SELECT.

Confronto	Visualizza	Visualizzazione materializzata
Visualizzare la definizione	Archiviato in Azure Data Warehouse.	Archiviato in Azure Data Warehouse.
Visualizzare il contenuto	Generato ogni volta che viene usata la visualizzazione.	Pre-elaborati e archiviati nel data warehouse di Azure durante la creazione della visualizzazione. Aggiornato man mano che i dati vengono aggiunti alle tabelle sottostanti.
Aggiornamento dati	Sempre aggiornato	Sempre aggiornato
Velocità di recupero dei dati di visualizzazione da query complesse	Lente	Veloce
Spazio di archiviazione aggiuntivo	No	Sì
Sintassi	CREA VISTA	CREATE MATERIALIZED VIEW AS SELECT

Vantaggi delle viste materializzate

Una visualizzazione materializzata progettata correttamente offre i vantaggi seguenti:

• Riduzione del tempo di esecuzione per query complesse con JOIN e funzioni di aggregazione. Più complessa è la query, maggiore è il potenziale per il risparmio di tempo di esecuzione. Il vantaggio maggiore è ottenuto quando il costo di calcolo di una query è elevato e il set di dati risultante è ridotto.

• Query Optimizer nel pool SQL dedicato può usare automaticamente viste materializzate distribuite per migliorare i piani di esecuzione delle query. Questo processo è trasparente per gli utenti, offre prestazioni di query più veloci e non richiede che le query facciano riferimento diretto alle viste materializzate.

• Richiede una manutenzione ridotta delle visualizzazioni. Una vista materializzata archivia i dati in due posizioni, un indice columnstore clusterizzato per i dati iniziali al momento della creazione della vista, e un archivio differenziale per le modifiche incrementali dei dati. Tutte le modifiche ai dati delle tabelle di base vengono aggiunte automaticamente all'archivio delta in modo sincrono. Un processo in background (motore di tuple) sposta periodicamente i dati dall'archivio delta all'indice columnstore della vista. Questa progettazione consente di eseguire query sulle viste materializzate per restituire gli stessi dati delle query direttamente sulle tabelle di base.

• I dati in una vista materializzata possono essere distribuiti in modo diverso dalle tabelle di base.

• I dati nelle viste materializzate ottengono gli stessi vantaggi di disponibilità elevata e resilienza dei dati nelle tabelle normali.

Rispetto ad altri provider di data warehouse, le viste materializzate implementate nel pool SQL dedicato offrono anche i vantaggi aggiuntivi seguenti:

• Aggiornamento automatico e sincrono dei dati con modifiche ai dati nelle tabelle di base. Non è richiesta alcuna azione da parte dell'utente.
• Ampio supporto per le funzioni di aggregazione. Vedere CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• Supporto delle raccomandazioni delle viste materializzate specifiche per le query. Vedere EXPLAIN (Transact-SQL).

Scenari comuni

Le viste materializzate vengono in genere usate negli scenari seguenti:

Necessità di migliorare le prestazioni di query analitiche complesse su dati di grandi dimensioni

Le query analitiche complesse usano in genere più funzioni di aggregazione e join di tabella, che si traducono in operazioni più onerose in termini di calcolo nell'esecuzione della query, ad esempio shuffle e join. Per questo motivo, il completamento di tali query richiede più tempo, in particolare nelle tabelle di grandi dimensioni.

Gli utenti possono creare viste materializzate per i dati restituiti dai calcoli comuni delle query, quindi non è necessaria alcuna ricompilazione quando questi dati sono necessari per le query, consentendo un costo di calcolo inferiore e una risposta di query più rapida.

Ottenere prestazioni più veloci senza o con modifiche minime alle query

Le modifiche allo schema e alle query nei data warehouse vengono in genere mantenute al minimo per supportare le normali operazioni ETL e la creazione di report. Gli utenti possono usare viste materializzate per l'ottimizzazione delle prestazioni delle query se i costi sostenuti dalle viste possono essere compensati dal miglioramento delle prestazioni delle query.

Rispetto ad altre opzioni di ottimizzazione, come la gestione della scalabilità e delle statistiche, creare e mantenere una vista materializzata è una modifica di produzione molto meno impattante e il potenziale miglioramento delle prestazioni è anche superiore.

• La creazione o la gestione di viste materializzate non influisce sulle query in esecuzione sulle tabelle di base.
• Query Optimizer può usare automaticamente le viste materializzate distribuite senza un riferimento diretto alla vista in una query. Questa funzionalità riduce la necessità di modificare le query nell'ottimizzazione delle prestazioni.

È necessaria una strategia di distribuzione dei dati diversa per ottenere prestazioni di query più veloci

Il data warehouse di Azure è un sistema di elaborazione parallela distribuito e massivo (MPP).

Synapse SQL è un sistema di query distribuito che consente alle aziende di implementare scenari di data warehousing e virtualizzazione dei dati usando esperienze T-SQL standard note ai data engineer. Espande anche le funzionalità di SQL per gestire scenari di streaming e Machine Learning. I dati in una tabella del data warehouse vengono distribuiti in 60 nodi usando una delle tre strategie di distribuzione (hash, round_robin o replicate).

La distribuzione dei dati viene specificata al momento della creazione della tabella e rimane invariata fino a quando la tabella non viene eliminata. La vista materializzata, essendo una tabella virtuale su disco, supporta le distribuzioni di dati hash e round_robin. Gli utenti possono scegliere una distribuzione dei dati diversa dalle tabelle di base, ma ottimale per le prestazioni delle query che usano spesso le viste.

Indicazioni per la progettazione

Ecco le indicazioni generali sull'uso di viste materializzate per migliorare le prestazioni delle query:

Progetto per il tuo workload

Prima di iniziare a creare viste materializzate, è importante avere una conoscenza approfondita del carico di lavoro in termini di modelli di query, importanza, frequenza e dimensioni dei dati risultanti.

Gli utenti possono eseguire EXPLAIN WITH_RECOMMENDATIONS SQL_statement <> per le viste materializzate consigliate da Query Optimizer. Poiché queste raccomandazioni sono specifiche delle query, una vista materializzata che offre vantaggi a una singola query potrebbe non essere ottimale per altre query nello stesso carico di lavoro.

Valutare queste raccomandazioni tenendo conto delle esigenze del carico di lavoro. Le viste materializzate ideali sono quelle che traggono vantaggio dalle prestazioni del carico di lavoro.

Tenere presente il compromesso tra query più veloci e il costo

Per ogni vista materializzata, è previsto un costo di archiviazione dei dati e un costo per la gestione della vista. Man mano che i dati cambiano nelle tabelle di base, le dimensioni della vista materializzata aumentano e cambia anche la struttura fisica.

Per evitare una riduzione delle prestazioni delle query, ogni vista materializzata viene mantenuta separatamente dal motore del data warehouse. Ciò include lo spostamento di righe dal delta store ai segmenti dell'indice columnstore e il consolidamento delle modifiche ai dati.

Il carico di lavoro di manutenzione aumenta quando aumenta il numero di viste materializzate e le modifiche delle tabelle di base. Gli utenti devono verificare se i costi sostenuti da tutte le viste materializzate possono essere compensati dal miglioramento delle prestazioni della query.

È possibile eseguire questa query per l'elenco di viste materializzate in un database:

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

Opzioni per ridurre il numero di viste materializzate:

• Identificare i set di dati comuni usati di frequente dalle query complesse nel carico di lavoro. Creare viste materializzate per archiviare tali set di dati in modo che l'ottimizzatore possa usarle come blocchi predefiniti durante la creazione di piani di esecuzione.

• Eliminare le viste materializzate con un utilizzo ridotto o che non sono più necessarie. Una vista materializzata disabilitata non richiede manutenzione, ma comporta comunque un costo di archiviazione.

• Combinare viste materializzate create nelle stesse tabelle di base o simili anche se i dati non si sovrappongono. La combinazione di viste materializzate può comportare una visualizzazione più grande rispetto alla somma delle visualizzazioni separate, ma il costo di manutenzione della visualizzazione dovrebbe ridurre. Per esempio:

-- Query 1 would benefit from having a materialized view created with this SELECT statement
SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement
SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single materialized view of this form
SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

Non tutte le prestazioni di ottimizzazione richiedono modifiche alle query

L'utilità di ottimizzazione del data warehouse può usare automaticamente le viste materializzate distribuite per migliorare le prestazioni delle query. Questo supporto viene applicato in modo trasparente alle query che non fanno riferimento alle viste e alle query che usano aggregazioni non supportate nella creazione di viste materializzate. Non è necessaria alcuna modifica della query. È possibile controllare il piano di esecuzione stimato di una query per verificare se viene usata una vista materializzata.

• Per altre informazioni sul recupero del piano di esecuzione effettivo, vedere Monitorare il carico di lavoro del pool SQL dedicato di Azure Synapse Analytics usando DMV.
• È possibile recuperare un piano di esecuzione stimato tramite SQL Server Management Studio (SSMS) o SET SHOWPLAN_XML.

Monitorare le viste materializzate

Una vista materializzata viene archiviata nel data warehouse come se si trattasse una tabella con indice columnstore cluster (CCI). La lettura dei dati da una vista materializzata include l'analisi dell'indice e l'applicazione di modifiche dall'archivio delta. Quando il numero di righe nell'archivio delta è troppo elevato, la risoluzione di una query da una vista materializzata può richiedere più tempo rispetto a una query diretta sulle tabelle di base.

Per evitare una riduzione delle prestazioni delle query, è consigliabile eseguire DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD per monitorare il overhead_ratio della visualizzazione (total_rows/base_view_row). Se il valore di overhead_ratio è troppo elevato, provare a ricompilare la vista materializzata in modo che tutte le righe nell'archivio delta vengano spostate nell'indice columnstore.

Visualizzazione materializzata e memorizzazione nella cache dei set di risultati

Queste due funzionalità vengono introdotte nel pool SQL dedicato nello stesso momento per l'ottimizzazione delle prestazioni delle query. La memorizzazione nella cache dei set di risultati viene usata per ottenere tempi di concorrenza elevati e tempi di risposta rapidi da query ripetitive su dati statici.

Per usare il risultato memorizzato nella cache, la forma della query che richiede la cache deve corrispondere alla query che ha prodotto la cache. Inoltre, il risultato memorizzato nella cache deve essere applicato all'intera query.

Le viste materializzate consentono modifiche ai dati nelle tabelle di base. I dati nelle viste materializzate possono essere applicati a una parte di una query. Questo supporto consente di usare le stesse viste materializzate da query diverse che condividono alcuni calcoli per ottenere prestazioni più veloci.

Esempio

In questo esempio viene usata una query simile a TPCDS che trova i clienti che spendono più denaro tramite catalogo rispetto ai negozi. Identifica anche i clienti preferiti e il paese/area geografica di origine. La query prevede la selezione dei primi 100 record risultanti dall'esecuzione di un'istruzione UNION su tre istruzioni sub-SELECT che includono operazioni SUM() e GROUP BY.

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

Controllare il piano di esecuzione stimato della query. Sono presenti 18 operazioni di shuffle e 17 join, la cui esecuzione richiede più tempo.

A questo punto, verrà creata una vista materializzata per ognuna delle tre istruzioni SUB-SELECT.

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

Controllare di nuovo il piano di esecuzione della query originale. A questo punto, il numero di operazioni di join passa da 17 a 5 e non ci sono più shuffle. Selezionare l'icona Operazione filtro nel piano. L'elenco di output mostra che i dati vengono letti dalle viste materializzate anziché dalle tabelle di base.

Con le viste materializzate, la stessa query viene eseguita molto più velocemente senza alcuna modifica del codice.

Passaggi successivi

Per altri suggerimenti sullo sviluppo, vedere Synapse SQL pool development overview (Panoramica sullo sviluppo per il pool Synapse SQL).

• Monitorare il carico di lavoro del pool SQL dedicato di Azure Synapse Analytics usando DMV.
• Visualizzare il piano di esecuzione stimato