Metodtips för dedikerade SQL-pooler i Azure Synapse Analytics

  • Artikel

Den här artikeln innehåller en samling metodtips som hjälper dig att uppnå optimala prestanda för dedikerade SQL-pooler i Azure Synapse Analytics. Om du arbetar med en serverlös SQL-pool kan du läsa Metodtips för serverlösa SQL-pooler för specifik vägledning. Nedan hittar du grundläggande vägledning och viktiga områden att fokusera på när du skapar din lösning. Varje avsnitt introducerar dig till ett koncept och pekar sedan på mer detaljerade artiklar som beskriver begreppet mer ingående.

Inläsning av dedikerade SQL-pooler

Vägledning för inläsning av dedikerade SQL-pooler finns i Vägledning för inläsning av data.

Minska kostnaderna genom att pausa och skala

Mer information om hur du minskar kostnaderna genom pausning och skalning finns i Hantera beräkning.

Underhålla statistik

Dedikerad SQL-pool kan konfigureras för att automatiskt identifiera och skapa statistik för kolumner. De frågeplaner som skapats av optimeraren är bara lika bra som tillgänglig statistik.

Vi rekommenderar att du aktiverar AUTO_CREATE_STATISTICS för dina databaser och håller statistiken uppdaterad dagligen eller efter varje belastning för att säkerställa att statistik för kolumner som används i dina frågor alltid är uppdaterad.

För att förkorta underhållstiden för statistik, var selektiv om vilka kolumner som har statistik eller behöver den vanligaste uppdateringen. Du kanske till exempel vill uppdatera datumkolumner där nya värden kan läggas till dagligen. Fokusera på att ha statistik för kolumner som ingår i kopplingar, kolumner som används i WHERE-satsen och kolumner som finns i GROUP BY.

Ytterligare information om statistik finns i artiklarna Hantera tabellstatistik, SKAPA STATISTIK och UPPDATERA STATISTIK .

Justera frågeprestanda

Gruppera INSERT-satser i batchar

En engångsinläsning till en liten tabell med en INSERT-instruktion som INSERT INTO MyLookup VALUES (1, 'Type 1')kan vara den bästa metoden beroende på dina behov. Men om du behöver läsa in tusentals eller miljontals rader under dagen är det troligt att singleton INSERTS inte är optimala.

Ett sätt att lösa det här problemet är att utveckla en process som skriver till en fil och sedan en annan process för att regelbundet läsa in den här filen. Mer information finns i INSERT-artikeln .

Använda PolyBase för att snabbt läsa in och exportera data

Dedikerad SQL-pool stöder inläsning och export av data via flera verktyg, inklusive Azure Data Factory, PolyBase och BCP. För små datamängder där prestanda inte är viktigt räcker alla verktygen för dina behov.

Kommentar

PolyBase är det bästa valet när du läser in eller exporterar stora mängder data, eller om du behöver snabbare prestanda.

PolyBase-inläsningar kan utföras med hjälp av CTAS eller INSERT INTO. CTAS minimerar transaktionsloggning och är det snabbaste sättet att läsa in dina data. Azure Data Factory har också stöd för PolyBase-belastningar och kan uppnå prestanda som liknar CTAS. PolyBase stöder olika filformat, inklusive Gzip-filer.

Om du vill maximera dataflödet när du använder Gzip-textfiler kan du dela upp filer i 60 eller fler filer för att maximera parallelliteten för din belastning. För snabbare totalt genomflöde bör du överväga att använda samtidig inläsning av data. Ytterligare information som är relevant för det här avsnittet finns i följande artiklar:

Läsa in och sedan fråga externa tabeller

PolyBase är inte optimalt för frågor. PolyBase-tabeller för dedikerade SQL-pooler stöder för närvarande endast Azure Blob Files och Azure Data Lake Storage. De här filerna har inga beräkningsresurser som stöder dem. Därför kan dedikerade SQL-pooler inte avlasta det här arbetet och måste läsa hela filen genom att läsa in den så att tempdb den kan läsa data.

Om du har flera frågor för att fråga efter dessa data är det bättre att läsa in dessa data en gång och få frågor att använda den lokala tabellen. Ytterligare PolyBase-vägledning ingår i artikeln Guide for using PolyBase (Guide för användning av PolyBase ).

Hash-distribuera stora tabeller

Tabeller distribueras som standard med resursallokering (Round Robin). Den här standardinställningen gör det enkelt för användarna att börja skapa tabeller utan att behöva bestämma hur deras tabeller ska distribueras. Resursallokeringstabeller kan fungera tillräckligt för vissa arbetsbelastningar. Men i de flesta fall ger en distributionskolumn bättre prestanda.

Det vanligaste exemplet på en tabell som distribueras av en kolumn som överträffar en resursallokeringstabell är när två stora faktatabeller är anslutna.

Om du till exempel har en ordertabell distribuerad av order_id, och en transaktionstabell också distribueras av order_id, blir den här frågan en direktfråga när du ansluter din ordertabell till transaktionstabellen på order_id. Dataförflyttningsåtgärder elimineras sedan. Färre steg innebär en snabbare fråga. Mindre dataflyttning gör också att frågor körs snabbare.

Dricks

När du läser in en distribuerad tabell ska dina inkommande data inte sorteras på distributionsnyckeln. Om du gör det blir belastningen långsammare.

Artikellänkarna nedan ger dig ytterligare information om hur du förbättrar prestandan genom att välja en distributionskolumn. Du hittar också information om hur du definierar en distribuerad tabell i WITH-satsen i create table-instruktionen:

Överpartitionera inte

Även om partitionering av data kan vara effektivt för att underhålla dina data via partitionsväxling eller optimering av genomsökningar med partitionseliminering, kan för många partitioner göra dina frågor långsammare. Ofta fungerar en partitioneringsstrategi med hög kornighet som kan fungera bra på SQL Server kanske inte fungerar bra på en dedikerad SQL-pool.

Om du har för många partitioner kan du minska effektiviteten för grupperade kolumnlagringsindex om varje partition har färre än 1 miljon rader. Dedikerade SQL-pooler partitionerar automatiskt dina data i 60 databaser. Så om du skapar en tabell med 100 partitioner blir resultatet 6 000 partitioner. Varje arbetsbelastning skiljer sig åt, så det bästa rådet är att experimentera med partitionering för att se vad som fungerar bäst för din arbetsbelastning.

Ett alternativ att överväga är att använda en kornighet som är lägre än vad du har implementerat med hjälp av SQL Server. Överväg till exempel att använda vecko- eller månadspartitioner i stället för dagliga partitioner.

Mer information om partitionering finns i artikeln Tabellpartitionering .

Minimera transaktionsstorlekar

INSERT-, UPDATE- och DELETE-instruktioner körs i en transaktion. När de misslyckas måste de återställas. Minimera transaktionsstorlekarna när det är möjligt för att minska risken för en lång återställning. Du kan minimera transaktionsstorlekarna genom att dela in INSERT-, UPDATE- och DELETE-instruktioner i delar. Om du till exempel har en INSERT som du förväntar dig att ta 1 timme kan du dela upp INSERT i fyra delar. Varje körning förkortas sedan till 15 minuter.

Dricks

Dra nytta av minimal loggning, t.ex. med CTAS, TRUNCATE, DROP TABLE eller INSERT, för att tömma tabeller och minska risken för återställning.

Ett annat sätt att eliminera återställningar är att använda åtgärder med endast metadata, t.ex. med partitionsväxling, för datahantering. I stället för att till exempel köra en DELETE-instruktion för att ta bort alla rader i en tabell där order_date var i oktober 2001, kan du partitionera dina data varje månad. Sedan kan du växla ut partitionen med data för en tom partition från en annan tabell (se ALTER TABLE-exempel).

För tabeller som inte är partitionerade kan du använda en CTAS för att skriva de data som du vill behålla i en tabell i stället för att använda DELETE. Om en CTAS tar samma tid är det mycket säkrare att köra eftersom den har minimal transaktionsloggning och kan avbrytas snabbt om det behövs.

Mer information om innehåll som rör det här avsnittet finns i artiklarna nedan:

Minska frågeresultatstorlekar

Om du minskar frågeresultatstorleken kan du undvika problem på klientsidan som orsakas av stora frågeresultat. Du kan redigera frågan för att minska antalet rader som returneras. Med vissa verktyg för frågegenerering kan du lägga till "översta N"-syntaxen i varje fråga. Du kan också CETAS frågeresultatet till en tillfällig tabell och sedan använda PolyBase-export för bearbetning på nednivå.

Använda minsta möjliga kolumnstorlek

När du definierar din DDL använder du den minsta datatypen som stöder dina data, vilket förbättrar frågeprestandan. Den här rekommendationen är särskilt viktig för CHAR- och VARCHAR-kolumner. Om det längsta värdet i en kolumn är 25 tecken definierar du kolumnen som VARCHAR(25). Undvik att definiera alla teckenkolumner med en stor standardlängd. Definiera dessutom kolumner som VARCHAR när det är allt som behövs i stället för att använda NVARCHAR.

Se tabellöversikten, tabelldatatyperna och CREATE TABLE-artiklarna för en mer detaljerad genomgång av viktiga begrepp som är relevanta för informationen ovan.

Använda tillfälliga heap-tabeller för tillfälliga data

När du tillfälligt landar data på dedikerade SQL-pooler kommer heap-tabeller i allmänhet att göra den övergripande processen snabbare. Om du läser in data endast för att mellanlagra dem innan du kör fler transformeringar går det snabbare att läsa in tabellen till en heap-tabell än att läsa in data till en klustrad kolumnlagringstabell.

Inläsning av data till en temporär tabell kommer också att läsas in mycket snabbare än när en tabell läses in till permanent lagring. Temporära tabeller börjar med ett "#" och är endast tillgängliga för den session som skapade den. De kan därför bara fungera i begränsade scenarier. Heap-tabeller definieras i WITH-satsen i en CREATE TABLE-instruktion. Om du använder en temporär tabell måste du också komma ihåg att skapa statistik för den temporära tabellen.

Mer information finns i artiklarna Temporära tabeller, SKAPA TABELL och SKAPA TABELL SOM SELECT .

Optimera grupperade columnstore-tabeller

Grupperade kolumnlagringsindex är ett av de mest effektiva sätten att lagra dina data i en dedikerad SQL-pool. Som standard skapas tabeller i en dedikerad SQL-pool som Clustered ColumnStore. Det är viktigt att segmentkvaliteten är bra för att uppnå bästa prestanda med frågor mot columnstore-tabeller. När rader skrivs till columnstore-tabeller när minnet är hårt belastat, kan columnstore-segmentens kvalitet påverkas.

Segmentkvaliteten kan mätas med antalet rader i en komprimerad radgrupp. Mer information om hur du identifierar och förbättrar segmentkvaliteten för grupperade kolumnlagringstabeller finns i artikeln Orsaker till dålig kolumnlagringsindexkvalitet i artikeln Tabellindex.

Eftersom kolumnlagringssegment av hög kvalitet är viktiga är det en bra idé att använda användar-ID:t som finns i den medelstora eller stora resursklassen för inläsning av data. Om du använder lägre informationslagerenheter vill du tilldela en större resursklass till inläsningsanvändaren.

Kolumnlagringstabeller skickar vanligtvis inte data till ett komprimerat kolumnlagringssegment förrän det finns mer än 1 miljon rader per tabell. Varje dedikerad SQL-pooltabell distribueras till 60 olika distributioner. Därför kommer kolumnlagringstabeller inte att gynna en fråga om inte tabellen har fler än 60 miljoner rader.

Dricks

För tabeller med mindre än 60 miljoner rader kanske det inte är den optimala lösningen att ha ett columnstore-index.

Om du partitionera dina data måste varje partition ha 1 miljon rader för att kunna dra nytta av ett grupperat kolumnlagringsindex. För en tabell med 100 partitioner måste den ha minst 6 miljarder rader för att kunna dra nytta av ett grupperat kolumnlager (60 distributioner 100 partitioner 1 miljon rader).

Om tabellen inte har 6 miljarder rader har du två huvudsakliga alternativ. Minska antingen antalet partitioner eller överväg att använda en heaptabell i stället. Det kan också vara värt att experimentera för att se om bättre prestanda kan uppnås genom att använda en heap-tabell med sekundära index i stället för en kolumnlagringstabell.

När du kör frågor mot en columnstore-tabell körs frågorna snabbare om du bara väljer de kolumner som du behöver. Mer information om tabell- och kolumnlagringsindex finns i artiklarna nedan:

Använda en större resursklass för att förbättra frågeprestanda

SQL-pooler använder resursgrupper som ett sätt att allokera minne till frågor. Till en början tilldelas alla användare till den lilla resursklassen, vilket ger 100 MB minne per distribution. Det finns alltid 60 distributioner. Varje distribution får minst 100 MB. Den totala systemomfattande minnesallokeringen är 6 000 MB, eller knappt 6 GB.

Vissa frågor, t.ex. stora kopplingar eller inläsningar till grupperade columnstore-tabeller, kan dra nytta av större minnesallokeringar. Vissa frågor, till exempel rena genomsökningar, ser ingen fördel. Användning av större resursklasser påverkar samtidigheten. Därför bör du ha dessa fakta i åtanke innan du flyttar alla användare till en stor resursklass.

Mer information om resursklasser finns i artikeln Resursklasser för arbetsbelastningshantering .

Använd mindre resursklass för att öka samtidigheten

Om du märker en lång fördröjning i användarfrågor kan användarna köras i större resursklasser. Det här scenariot främjar förbrukningen av samtidighetsfack, vilket kan leda till att andra frågor köar. För att avgöra om användarnas frågor placeras i kö kör du SELECT * FROM sys.dm_pdw_waits för att se om några rader returneras.

Resursklasserna för arbetsbelastningshantering och sys.dm_pdw_waits artiklar ger dig mer information.

Använda DMV:er för att övervaka och optimera frågor

Dedikerade SQL-pooler har flera DMV:er som kan användas för att övervaka frågekörning. I övervakningsartikeln nedan får du stegvisa instruktioner om hur du visar information om en fråga som körs. För att snabbt hitta frågor i dessa DMV:er kan det vara bra att använda alternativet LABEL med dina frågor. Mer detaljerad information finns i artiklarna i listan nedan:

Nästa steg

Se även felsökningsartikeln för vanliga problem och lösningar.

Om du behöver information som inte anges i den här artikeln kan du söka på microsofts Q&A-frågesida efter Azure Synapse där du kan ställa frågor till andra användare och till Produktgruppen för Azure Synapse Analytics.

Vi övervakar aktivt detta forum för att kontrollera att dina frågor besvaras antingen av en annan användare eller av någon av oss. Om du föredrar att ställa dina frågor om Stack Overflow har vi även ett Azure Synapse Analytics Stack Overflow-forum.