Leistungstuning mit materialisierten Ansichten

Materialisierte Ansichten für dedizierte SQL-Pools in Azure Synapse bieten eine geringe Wartungsmethode für komplexe analytische Abfragen, um eine schnelle Leistung ohne Abfrageänderungen zu erzielen. In diesem Artikel werden die allgemeinen Anleitungen zur Verwendung materialisierter Ansichten erläutert.

Materialisierte Ansichten im Vergleich zu Standardansichten

Dedizierter SQL-Pool in Azure Synapse unterstützt standard- und materialisierte Ansichten. Beide sind virtuelle Tabellen, die mit SELECT-Ausdrücken erstellt und Abfragen als logische Tabellen angezeigt werden. Ansichten kapseln die Komplexität der allgemeinen Datenberechnung und fügen eine Abstraktionsebene zu Berechnungsänderungen hinzu, sodass keine Abfragen neu geschrieben werden müssen.

Eine Standardansicht berechnet ihre Daten jedes Mal, wenn die Ansicht verwendet wird. Auf dem Datenträger sind keine Daten gespeichert. Benutzer verwenden in der Regel Standardansichten als Tool, mit dem die logischen Objekte und Abfragen in einem dedizierten SQL-Pool organisiert werden können. Um eine Standardansicht zu verwenden, muss eine Abfrage direkt darauf verweisen.

Eine materialisierte Ansicht berechnet, speichert und verwaltet ihre Daten in einem dedizierten SQL-Pool genau wie eine Tabelle. Jedes Mal, wenn eine materialisierte Ansicht verwendet wird, ist keine Neukompilierung erforderlich. Aus diesem Grund können Abfragen, die alle oder eine Teilmenge der Daten in materialisierten Ansichten verwenden, eine schnellere Leistung erzielen. Noch besser können Abfragen eine materialisierte Ansicht verwenden, ohne direkt darauf verweisen zu müssen, sodass kein Anwendungscode geändert werden muss.

Die meisten Anforderungen an eine Standardansicht gelten weiterhin für eine materialisierte Ansicht. Ausführliche Informationen zur materialisierten Ansichtssyntax und anderen Anforderungen finden Sie unter CREATE MATERIALIZED VIEW AS SELECT

Vorteile der Verwendung materialisierter Ansichten

Eine ordnungsgemäß gestaltete materialisierte Ansicht bietet die folgenden Vorteile:

• Verringern Sie die Ausführungszeit für komplexe Abfragen mit JOINs und Aggregatfunktionen. Je komplexer die Abfrage ist, desto höher ist das Potenzial für das Sparen von Ausführungszeiten. Der größte Vorteil wird gewonnen, wenn die Berechnungskosten einer Abfrage hoch sind und der resultierende Datensatz klein ist.
• Der Optimierer im dedizierten SQL-Pool kann automatisch bereitgestellte materialisierte Ansichten verwenden, um Abfrageausführungspläne zu verbessern. Dieser Prozess ist für die Benutzer transparent und sorgt für eine schnellere Abfrageleistung, wobei die Abfragen keinen direkten Verweis auf die materialisierten Ansichten erfordern.
• Erfordert niedrigen Wartungsaufwand für Ansichten. Alle inkrementellen Datenänderungen aus den Basistabellen werden automatisch synchron zu den materialisierten Ansichten hinzugefügt, was bedeutet, dass sowohl die Basistabellen als auch die materialisierten Ansichten in derselben Transaktion aktualisiert werden. Dieses Design ermöglicht es, bei Abfragen von materialisierten Ansichten die gleichen Daten wie bei der direkten Abfrage der Basistabellen zu erhalten.
• Die Daten in einer materialisierten Ansicht können anders als die Basistabellen verteilt werden.
• Daten in materialisierten Ansichten erhalten die gleichen Vorteile für hohe Verfügbarkeit und Resilienz wie Daten in regulären Tabellen.

Die in dediziertem SQL-Pool implementierten materialisierten Ansichten bieten außerdem die folgenden Vorteile:

Im Vergleich zu anderen Data Warehouse-Anbietern bieten die materialisierten Ansichten, die in dediziertem SQL-Pool implementiert werden, auch die folgenden Vorteile:

• Breite Unterstützung von Aggregatfunktionen. Siehe CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• Die Unterstützung für abfragespezifische materialisierte Ansichtsempfehlung. Siehe EXPLAIN (Transact-SQL).
• Automatische und synchrone Datenaktualisierung mit Datenänderungen in Basistabellen. Es ist keine Benutzeraktion erforderlich.

Häufige Szenarien

Materialisierte Ansichten werden in der Regel in den folgenden Szenarien verwendet:

Muss die Leistung komplexer analytischer Abfragen gegen große Datenmengen verbessern

Komplexe analytische Abfragen verwenden in der Regel mehr Aggregatfunktionen und Tabellenverknnungen, was zu rechenintensiven Vorgängen wie Shuffles und Verknüpfungen in der Abfrageausführung führt. Aus diesem Grund dauern komplexe analytische Abfragen länger, insbesondere bei großen Tabellen.

Benutzer können materialisierte Ansichten für die daten erstellen, die aus allgemeinen Berechnungen von Abfragen zurückgegeben werden. Daher ist keine Neuberechnung erforderlich, wenn diese Daten von Abfragen benötigt werden, sodass niedrigere Berechnungskosten und schnellere Abfrageantworten möglich sind.

Benötigen Sie eine schnellere Leistung ohne oder minimale Abfrageänderungen.

Schema- und Abfrageänderungen in dedizierten SQL-Pools werden in der Regel mindestens beibehalten, um reguläre ETL-Vorgänge und Berichte zu unterstützen. Personen können materialisierte Ansichten für die Optimierung der Abfrageleistung verwenden, wenn die kosten, die durch die Ansichten entstehen, durch den Gewinn der Abfrageleistung ausgeglichen werden können.

Im Vergleich zu anderen Optimierungsoptionen wie Skalierung und Statistikverwaltung ist es eine weniger wirkungsvolle Produktionsänderung, um eine materialisierte Ansicht zu erstellen und aufrechtzuerhalten, und ihre potenzielle Leistungssteigerung ist ebenfalls höher.

• Das Erstellen oder Verwalten von materialisierten Ansichten wirkt sich nicht auf die Abfragen aus, die für die Basistabellen ausgeführt werden.
• Der Abfrageoptimierer kann die bereitgestellten materialisierten Ansichten automatisch ohne direkten Ansichtsverweis in einer Abfrage verwenden. Diese Funktion reduziert die Notwendigkeit von Abfrageänderungen bei der Leistungsoptimierung.

Benötigen Sie unterschiedliche Datenverteilungsstrategie für eine schnellere Abfrageleistung

Dedizierter SQL-Pool ist ein verteiltes Abfrageverarbeitungssystem. Daten in einer SQL-Tabelle werden mithilfe einer von drei Verteilungsstrategien (Hash, round_robin oder repliziert) auf bis zu 60 Knoten verteilt.

Die Datenverteilung wird zur Erstellungszeit der Tabelle angegeben und bleibt unverändert, bis die Tabelle gelöscht wird. Materialisierte Ansicht, die eine virtuelle Tabelle auf dem Datenträger ist, unterstützt Hash- und round_robin Datenverteilungen. Benutzer können eine Datenverteilung auswählen, die sich von den Basistabellen unterscheidet, aber optimal für die Leistung von Abfragen, die die Ansichten verwenden.

Entwurfsleitfäden

Hier ist die allgemeine Anleitung zur Verwendung materialisierter Ansichten zur Verbesserung der Abfrageleistung:

Entwerfen für Ihre Arbeitsauslastung

Bevor Sie mit der Erstellung materialisierter Ansichten beginnen, ist es wichtig, ein tiefes Verständnis Ihrer Arbeitsauslastung in Bezug auf Abfragemuster, Wichtigkeit, Häufigkeit und die Größe der resultierenden Daten zu haben.

Benutzer können EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> für die vom Abfrageoptimierer empfohlenen materialisierten Ansichten ausführen. Da diese Empfehlungen abfragespezifisch sind, könnte eine materialisierte Ansicht, die einer einzelnen Abfrage Vorteile bietet, für andere Abfragen in derselben Workload möglicherweise nicht optimal sein.

Bewerten Sie diese Empfehlungen im Hinblick auf Ihre Workloadanforderungen. Die idealen materialisierten Ansichten sind diejenigen, die die Leistung der Arbeitslast verbessern.

Beachten Sie den Kompromiss zwischen schnelleren Abfragen und den damit verbundenen Kosten

Für jede materialisierte Ansicht fallen Datenspeicherungskosten und Kosten für die Wartung an. Da sich die Daten in Basistabellen ändern, nimmt die Größe der materialisierten Ansicht zu, und die physische Struktur ändert sich ebenfalls. Um Eine Leistungsbeeinträchtigung der Abfrage zu vermeiden, wird jede materialisierte Ansicht separat vom SQL-Modul verwaltet.

Der Wartungsaufwand wird höher, wenn sich die Anzahl der materialisierten Ansichten und der Basistabellen ändert. Benutzer sollten überprüfen, ob die Kosten, die aus allen materialisierten Ansichten entstehen, durch den Leistungsgewinn der Abfrage ausgeglichen werden können.

Sie können diese Abfrage ausführen, um eine Liste der materialisierten Ansichten in einem dedizierten SQL-Pool zu generieren:

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;

Optionen zum Verringern der Anzahl der materialisierten Ansichten:

• Identifizieren Sie gängige Datensätze, die häufig von den komplexen Abfragen in Ihrer Workload verwendet werden. Erstellen Sie materialisierte Ansichten, um diese Datasets zu speichern, damit der Optimierer sie beim Erstellen von Ausführungsplänen als Bausteine verwenden kann.

• Entfernen Sie die materialisierten Ansichten, die eine geringe Auslastung aufweisen oder nicht mehr benötigt werden. Eine deaktivierte materialisierte Ansicht wird nicht aktualisiert, aber sie verursacht weiterhin Speicherkosten.

• Kombinieren Sie materialisierte Ansichten, die auf denselben oder ähnlichen Basistabellen erstellt wurden, auch wenn sich ihre Daten nicht überlappen. Das Kombinieren von materialisierten Ansichten könnte zu einer größeren Ansicht als die Summe der separaten Ansichten führen, die Wartungskosten für die Ansicht sollten jedoch reduziert werden. Beispiel:

-- 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

Nicht alle Leistungsoptimierungen erfordern Abfrageänderungen

Der SQL-Abfrageoptimierer kann bereitgestellte materialisierte Ansichten automatisch verwenden, um die Abfrageleistung zu verbessern. Diese Unterstützung wird transparent auf Abfragen angewendet, die weder auf die Ansichten verweisen noch Aggregate nutzen, die bei der Erstellung von materialisierten Ansichten nicht unterstützt werden. Es ist keine Abfrageänderung erforderlich. Sie können den geschätzten Ausführungsplan einer Abfrage überprüfen, um zu bestätigen, ob eine materialisierte Ansicht verwendet wird.

Überwachen materialisierter Ansichten

Eine materialisierte Ansicht wird wie eine Tabelle mit einem gruppierten Columnstore-Index (CCI) im dedizierten SQL-Pool gespeichert. Das Lesen von Daten aus einer materialisierten Ansicht umfasst das Scannen der CCI-Indexsegmente und das Anwenden inkrementeller Änderungen aus Basistabellen. Wenn die Anzahl der inkrementellen Änderungen zu hoch ist, kann das Auflösen einer Abfrage aus einer materialisierten Ansicht länger dauern als das direkte Abfragen der Basistabellen.

Um eine leistungsbeeinträchtigung der Abfrage zu vermeiden, empfiehlt es sich, DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD auszuführen, um das overhead-Verhältnis der Ansicht zu überwachen (total_rows / max(1, base_view_row)). Benutzer sollten die materialisierte Ansicht neu erstellen, wenn ihre overhead_ratio zu hoch ist.

Materialisierte Ansicht und Zwischenspeicherung von Resultsets

Diese beiden Features im dedizierten SQL-Pool werden für die Optimierung der Abfrageleistung verwendet. Das Zwischenspeichern von Ergebnismengen wird verwendet, um eine hohe Parallelität und schnelle Reaktionszeiten bei sich wiederholenden Abfragen auf statische Daten zu erhalten.

Um das zwischengespeicherte Ergebnis zu verwenden, muss die Form der Abfrage, die den Cache anfordert, mit der Abfrage übereinstimmen, die den Cache erstellt hat. Darüber hinaus muss das zwischengespeicherte Ergebnis auf die gesamte Abfrage angewendet werden.

Materialisierte Ansichten ermöglichen Datenänderungen in den Basistabellen. Daten in materialisierten Ansichten können auf einen Teil einer Abfrage angewendet werden. Diese Unterstützung ermöglicht es, dieselben materialisierten Ansichten von verschiedenen Abfragen zu verwenden, die einige Berechnungen teilen, um die Leistung zu erhöhen.

Beispiel

In diesem Beispiel wird eine TPCDS-ähnliche Abfrage verwendet, die Kunden findet, die mehr Geld über den Katalog ausgeben als in Geschäften, identifizieren sie die bevorzugten Kunden und ihr Land/ihre Herkunftsregion. Die Abfrage umfasst die Auswahl der ersten 100 Datensätze aus der UNION von drei Teilauswahl-Anweisungen mit SUM() und 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');

Überprüfen Sie den geschätzten Ausführungsplan der Abfrage. Es gibt 18 Shuffles und 17 Joins-Vorgänge, die mehr Zeit in Anspruch nehmen. Als Nächstes erstellen wir eine materialisierte Ansicht für jede der drei Sub-SELECT-Anweisungen.

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

Überprüfen Sie den Ausführungsplan der ursprünglichen Abfrage erneut. Jetzt ändert sich die Anzahl der Verknüpfungen von 17 auf 5, und es gibt keine Umverteilung. Wählen Sie im Plan das Symbol "Filtervorgang" aus. In der Ausgabeliste werden die Daten aus den materialisierten Ansichten anstelle der Basistabellen gelesen.

Bei materialisierten Ansichten wird dieselbe Abfrage schneller ohne Codeänderung ausgeführt.

Nächste Schritte

Weitere Entwicklungstipps finden Sie in der Übersicht über die dedizierte SQL-Poolentwicklung.