Prestandajustering med materialiserade vyer med dedikerad SQL-pool i Azure Synapse Analytics

I en dedikerad SQL-pool tillhandahåller materialiserade vyer en metod för lågt underhåll för komplexa analysfrågor för att få snabba prestanda utan någon frågeändring. I den här artikeln beskrivs den allmänna vägledningen om hur du använder materialiserade vyer.

Materialiserade vyer jämfört med standardvyer

SQL-poolen stöder både standardvyer och materialiserade vyer. Båda är virtuella tabeller som skapas med SELECT-uttryck och presenteras för frågor som logiska tabeller. Vyer visar komplexiteten i vanlig databeräkning och lägger till ett abstraktionslager i beräkningsändringar så att du inte behöver skriva om frågor.

En standardvy beräknar sina data varje gång vyn används. Det finns inga data lagrade på disken. Användare använder vanligtvis standardvyer som ett verktyg som hjälper till att organisera logiska objekt och frågor i en databas. Om du vill använda en standardvy måste en fråga referera direkt till den.

En materialiserad vy förberäknar, lagrar och underhåller sina data i en dedikerad SQL-pool precis som en tabell. Omkomputation behövs inte varje gång en materialiserad vy används. Därför kan frågor som använder alla eller en delmängd av data i materialiserade vyer få snabbare prestanda. Ännu bättre är att frågor kan använda en materialiserad vy utan att direkt referera till den, så det finns inget behov av att ändra programkoden.

De flesta standardvykraven gäller fortfarande för en materialiserad vy. Mer information om syntaxen för materialiserad vy och andra krav finns i CREATE MATERIALIZED VIEW AS SELECT.

Jämförelse	Utsikt	Materialiserad synpunkt
Visa definition	Lagras i Azure-informationslagret.	Lagras i Azure-informationslagret.
Visa innehåll	Genereras varje gång vyn används.	Förbearbetade och lagrade i Azure-informationslagret när vyn skapades. Uppdateras när data läggs till i de underliggande tabellerna.
Datauppdatering	Alltid uppdaterad	Alltid uppdaterad
Hastighet för att hämta visningsdata från komplexa frågor	Långsam	Snabbt
Extra lagringsutrymme	Nej	Ja
Syntax	SKAPA VY	SKAPA MATERIALISERAD VY SOM VÄLJ

Fördelar med materialiserade vyer

En välutformad materialiserad vy ger följande fördelar:

• Kortare körningstid för komplexa frågor med JOIN och aggregatfunktioner. Ju mer komplex frågan är, desto större är potentialen för att spara körtid. Den största fördelen uppnås när en frågas beräkningskostnad är hög och den resulterande datauppsättningen är liten.

• Frågeoptimeraren i en dedikerad SQL-pool kan automatiskt använda distribuerade materialiserade vyer för att förbättra frågekörningsplanerna. Den här processen är transparent för användare som ger snabbare frågeprestanda och kräver inte frågor för att referera direkt till de materialiserade vyerna.

• Kräver lågt underhåll av vyerna. En materialiserad vy lagrar data på två platser, ett grupperat columnstore-index för de första data vid vyns skapandetid och ett deltalager för inkrementella dataändringar. Alla dataändringar från bastabellerna läggs automatiskt till i deltalagret på ett synkront sätt. En bakgrundsprocess (tuppelflyttare) flyttar regelbundet data från deltalagret till kolumnlagringsindex för vyn. Med den här designen kan sökningar mot materialiserade vyer returnera samma data som när du söker i bas-tabellerna direkt.

• Data i en materialiserad vy kan distribueras på ett annat sätt än bastabellerna.

• Data i materialiserade vyer får samma fördelar med hög tillgänglighet och återhämtning som data i vanliga tabeller.

Jämfört med andra datalagerleverantörer ger de materialiserade vyer som implementeras i en dedikerad SQL-pool också följande ytterligare fördelar:

• Automatisk och synkroniserad datauppdatering med dataändringar i bastabeller. Ingen användaråtgärd krävs.
• Brett stöd för mängdfunktioner. Se även SKAPA MATERIALISERAD VY SOM SELECT (Transact-SQL).
• Stöd för frågespecifik materialiserad vyrekommendering. Se EXPLAIN (Transact-SQL).

Vanliga scenarier

Materialiserade vyer används vanligtvis i följande scenarier:

Behöver förbättra prestandan för komplexa analysfrågor mot stora data i storlek

Komplexa analysfrågor använder vanligtvis fler aggregeringsfunktioner och tabellkopplingar, vilket orsakar mer beräkningsintensiva åtgärder, till exempel blandningar och kopplingar i frågekörning. Det är därför dessa frågor tar längre tid att slutföra, särskilt i stora tabeller.

Användare kan skapa materialiserade vyer för data som returneras från vanliga beräkningar av frågor, så det krävs ingen omkomputering när dessa data behövs av frågor, vilket ger lägre beräkningskostnad och snabbare frågesvar.

Behöver snabbare prestanda utan eller minsta frågeändringar

Schema- och frågeändringar i informationslager hålls vanligtvis till ett minimum för att stödja vanliga ETL-åtgärder och rapportering. Personer kan använda materialiserade vyer för optimering av frågeprestanda om kostnaden för vyerna kan kompenseras av frågeprestandaförbättringen.

Jämfört med andra justeringsalternativ, till exempel skalning och statistikhantering, är det en mycket mindre effektfull produktionsförändring för att skapa och underhålla en materialiserad vy och dess potentiella prestandavinst är också högre.

• Att skapa eller underhålla materialiserade vyer påverkar inte de frågor som körs mot bastabellerna.
• Frågeoptimeraren kan automatiskt använda de distribuerade materialiserade vyerna utan direkt vyreferens i en fråga. Den här funktionen minskar behovet av frågeändring i prestandajusteringen.

Behöver olika datadistributionsstrategi för snabbare frågeprestanda

Azure Data Warehouse är ett distribuerat och massivt parallellt bearbetningssystem (MPP).

Synapse SQL är ett distribuerat frågesystem som gör det möjligt för företag att implementera scenarier för datalagerhantering och datavirtualisering med hjälp av vanliga T-SQL-upplevelser som är bekanta för datatekniker. Den utökar också funktionerna i SQL för att hantera scenarier för direktuppspelning och maskininlärning. Data i en informationslagertabell distribueras över 60 noder med någon av tre distributionsstrategier (hash, round_robin eller replikerad).

Datadistributionen anges vid tidpunkten för tabellskapandet och förblir oförändrad tills tabellen tas bort. Materialiserad vy är en virtuell tabell som lagras på disken och stöder hash- och round_robin-datadistributioner. Användare kan välja en datadistribution som skiljer sig från bastabellerna men som är optimal för prestanda för frågor som ofta använder vyerna.

Riktlinjer för design

Här är den allmänna vägledningen om hur du använder materialiserade vyer för att förbättra frågeprestanda:

Design för din arbetsbelastning

Innan du börjar skapa materialiserade vyer är det viktigt att du har en djup förståelse för din arbetsbelastning när det gäller frågemönster, prioritet, frekvens och storleken på resulterande data.

Användare kan köra EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> för de materialiserade vyer som rekommenderas av frågeoptimeraren. Eftersom dessa rekommendationer är frågespecifika kanske en materialiserad vy som gynnar en enskild fråga inte är optimal för andra frågor i samma arbetsbelastning.

Utvärdera dessa rekommendationer med dina arbetsbelastningsbehov i åtanke. De ideala materialiserade vyerna är de som gynnar arbetsbelastningens prestanda.

Var medveten om kompromissen mellan snabbare frågor och kostnaden

För varje materialiserad vy finns det en datalagringskostnad och en kostnad för att underhålla vyn. När data ändras i bastabellerna ökar storleken på den materialiserade vyn och dess fysiska struktur ändras också.

För att undvika försämrad frågeprestanda underhålls varje materialiserad vy separat av informationslagermotorn, inklusive att flytta rader från deltalager till segmenten för kolumnlagringsindex och konsolidera dataändringar.

Underhållsarbetsbelastningen ökar med antalet materialiserade vyer och förändringar av bastabeller. Användarna bör kontrollera om kostnaden för alla materialiserade vyer kan uppvägas av förbättringen i frågeprestanda.

Du kan köra den här frågan för listan över materialiserad vy i en databas:

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;

Alternativ för att minska antalet materialiserade vyer:

• Identifiera vanliga datauppsättningar som ofta används av komplexa frågor i din arbetsbelastning. Skapa materialiserade vyer för att lagra dessa datauppsättningar så att optimeraren kan använda dem som byggblock vid skapandet av exekveringsplaner.

• Släpp de materialiserade vyer som har låg användning eller som inte längre behövs. En inaktiverad materialiserad vy underhålls inte, men medför ändå kostnader för lagring.

• Kombinera materialiserade vyer som skapats på samma eller liknande bastabeller även om deras data inte överlappar varandra. Att kombinera materialiserade vyer kan resultera i en större vy i storlek än summan av de separata vyerna, men kostnaden för visningsunderhåll bör minska. Till exempel:

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

Alla prestandaoptimeringar kräver inte ändringar i frågor

Datalageroptimeraren kan automatiskt använda distribuerade materialiserade vyer för att förbättra frågeprestanda. Det här stödet tillämpas transparent på frågor som inte refererar till vyerna och för frågor som använder aggregeringar som inte stöds när materialiserade vyer skapas. Ingen frågeändring krävs. Du kan kontrollera en frågas beräknade körningsplan för att bekräfta om en materialiserad vy används.

• Mer information om hur du hämtar den faktiska körningsplanen finns i Övervaka din dedikerade SQL-pool-arbetsbelastning i Azure Synapse Analytics med DMV:er.
• Du kan hämta en uppskattad körningsplan via SQL Server Management Studio (SSMS) eller SET SHOWPLAN_XML.

Övervaka materialiserade vyer

En materialiserad vy lagras i datalager precis som en tabell med klustrat kolumnlagringsindex (CCI). Att läsa data från en materialiserad vy omfattar genomsökning av indexet och tillämpning av ändringar från deltalagret. När antalet rader i deltalagret är för högt kan det ta längre tid att hantera en fråga från en materialiserad vy än att direktfråga bastabellerna.

För att undvika försämrad frågeprestanda är det bra att köra DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD för att övervaka vyernas overhead_ratio (total_rows / base_view_row). Ifall overhead_ratio är för hög bör du överväga att återskapa den materialiserade vyn så att alla rader i deltalagret flyttas till kolumnindexet.

Cachelagring av materialiserad vy och resultatuppsättning

Dessa två funktioner introduceras i en dedikerad SQL-pool ungefär samtidigt för frågeprestandajustering. Cachelagring av resultatuppsättningar används för att uppnå hög samtidighet och snabba svarstider från repetitiva frågor mot statiska data.

Om du vill använda det cachelagrade resultatet måste formatet för cachebegärandefrågan matcha med frågan som skapade cacheminnet. Dessutom måste det cachelagrade resultatet gälla för hela frågan.

Materialiserade vyer tillåter dataändringar i bastabellerna. Data i materialiserade vyer kan tillämpas på en del av en fråga. Det här stödet gör att samma materialiserade vyer kan användas av olika frågor som delar viss beräkning för snabbare prestanda.

Exempel

I det här exemplet används en TPCDS-liknande fråga som hittar kunder som spenderar mer pengar via katalog än i butiker. Den identifierar också de önskade kunderna och deras ursprungsland/ursprungsregion. Frågan handlar om att välja de 100 främsta posterna från en UNION av tre sub-SELECT-satser som involverar SUM() och 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');

Kontrollera frågans uppskattade exekveringsplan. Det finns 18 omblandningar och 17 sammanslagningar, vilket tar mer tid att utföra.

Nu ska vi skapa en materialiserad vy för var och en av de tre under-SELECT-uttrycken.

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

Kontrollera körningsplanen för den ursprungliga frågan igen. Nu ändras antalet sammanfogningar från 17 till 5 och det finns ingen omfördelning längre. Välj ikonen Filteråtgärd i planen. Utdata visar att data läses från de materialiserade vyerna istället för bastabeller.

Med materialiserade vyer körs samma fråga mycket snabbare utan någon kodändring.

Nästa steg

Fler utvecklingstips finns i Översikt över Synapse SQL-utveckling.

• Övervaka arbetsbelastningen för din dedikerade SQL-pool i Azure Synapse Analytics med DMVs.
• Visa uppskattad exekveringsplan