Metodtips för serverlösa SQL-pooler i Azure Synapse Analytics

  • Artikel

I den här artikeln hittar du en samling metodtips för att använda en serverlös SQL-pool. Serverlös SQL-pool är en resurs i Azure Synapse Analytics. Om du arbetar med en dedikerad SQL-pool kan du läsa Metodtips för dedikerade SQL-pooler för specifik vägledning.

Med en serverlös SQL-pool kan du köra frågor mot filer i dina Azure Storage-konton. Den har inte lokala lagrings- eller inmatningsfunktioner. Alla filer som frågemålet är externa till en serverlös SQL-pool. Allt som rör läsning av filer från lagring kan påverka frågeprestanda.

Några allmänna riktlinjer är:

  • Kontrollera att dina klientprogram är indelade i en serverlös SQL-pool.
    • Om du använder klientprogram utanför Azure kontrollerar du att du använder en serverlös SQL-pool i en region som är nära klientdatorn. Exempel på klientprogram är Power BI Desktop, SQL Server Management Studio och Azure Data Studio.
  • Kontrollera att lagrings- och serverlös SQL-pool finns i samma region. Lagringsexempel är Azure Data Lake Storage och Azure Cosmos DB.
  • Försök att optimera lagringslayouten genom att använda partitionering och hålla filerna i intervallet mellan 100 MB och 10 GB.
  • Om du returnerar ett stort antal resultat kontrollerar du att du använder SQL Server Management Studio eller Azure Data Studio och inte Azure Synapse Studio. Azure Synapse Studio är ett webbverktyg som inte är utformat för stora resultatuppsättningar.
  • Om du filtrerar resultat efter strängkolumn kan du försöka använda en BIN2_UTF8 sortering. Mer information om hur du ändrar sortering finns i Sorteringstyper som stöds för Synapse SQL.
  • Överväg att cachelagra resultaten på klientsidan med hjälp av Power BI-importläget eller Azure Analysis Services och uppdatera dem regelbundet. Serverlösa SQL-pooler kan inte ge en interaktiv upplevelse i Power BI Direct Query-läge om du använder komplexa frågor eller bearbetar en stor mängd data.
  • Maximal samtidighet är inte begränsad och beror på frågekomplexiteten och mängden data som genomsöks. En serverlös SQL-pool kan samtidigt hantera 1 000 aktiva sessioner som kör enkla frågor. Talen tas bort om frågorna är mer komplexa eller genomsöker en större mängd data, så i så fall bör du överväga att minska samtidigheten och köra frågor under en längre tidsperiod om det är möjligt.

Klientprogram och nätverksanslutningar

Kontrollera att klientprogrammet är anslutet till närmaste möjliga Azure Synapse-arbetsyta med den optimala anslutningen.

  • Samlokalisera ett klientprogram med Azure Synapse-arbetsytan. Om du använder program som Power BI eller Azure Analysis Service kontrollerar du att de finns i samma region där du placerade din Azure Synapse-arbetsyta. Om det behövs skapar du de separata arbetsytor som är kopplade till dina klientprogram. Att placera ett klientprogram och Azure Synapse-arbetsytan i olika regioner kan orsaka större svarstid och långsammare strömning av resultat.
  • Om du läser data från ditt lokala program kontrollerar du att Azure Synapse-arbetsytan finns i den region som ligger nära din plats.
  • Kontrollera att du inte har problem med nätverksbandbredd när du läser en stor mängd data.
  • Använd inte Azure Synapse Studio för att returnera en stor mängd data. Azure Synapse Studio är ett webbverktyg som använder HTTPS-protokollet för att överföra data. Använd Azure Data Studio eller SQL Server Management Studio för att läsa en stor mängd data.

Layout för lagring och innehåll

Här följer metodtips för lagring och innehållslayout i en serverlös SQL-pool.

Samplacera din lagrings- och serverlösa SQL-pool

För att minimera svarstiden kan du samplacera ditt Azure Storage-konto eller Azure Cosmos DB-analyslagring och din serverlösa SQL-poolslutpunkt. Lagringskonton och slutpunkter som etablerats när arbetsytan skapas finns i samma region.

Om du har åtkomst till andra lagringskonton med en serverlös SQL-pool för optimala prestanda kontrollerar du att de finns i samma region. Om de inte finns i samma region blir svarstiden längre för datans nätverksöverföring mellan fjärrregionen och slutpunktens region.

Azure Storage-begränsning

Flera program och tjänster kan komma åt ditt lagringskonto. Lagringsbegränsning sker när det kombinerade IOPS- eller dataflöde som genereras av program, tjänster och serverlösa SQL-poolarbetsbelastningar överskrider gränserna för lagringskontot. Därför får du en betydande negativ effekt på frågeprestanda.

När begränsning identifieras har en serverlös SQL-pool inbyggd hantering för att lösa det. Serverlös SQL-pool gör begäranden till lagring i långsammare takt tills begränsningen har lösts.

Dricks

För optimal frågekörning ska du inte stressa lagringskontot med andra arbetsbelastningar under frågekörningen.

Förbereda filer för frågor

Om möjligt kan du förbereda filer för bättre prestanda:

  • Konvertera stora CSV- och JSON-filer till Parquet. Parquet är ett kolumnformat. Eftersom den är komprimerad är filstorlekarna mindre än CSV- eller JSON-filer som innehåller samma data. Serverlös SQL-pool hoppar över de kolumner och rader som inte behövs i en fråga om du läser Parquet-filer. Serverlös SQL-pool behöver mindre tid och färre lagringsbegäranden för att läsa den.
  • Om en fråga riktar sig mot en enda stor fil kan du dela upp den i flera mindre filer.
  • Försök att behålla csv-filstorleken mellan 100 MB och 10 GB.
  • Det är bättre att ha lika stora filer för en enda OPENROWSET-sökväg eller en extern tabellPLATS.
  • Partitionera dina data genom att lagra partitioner till olika mappar eller filnamn. Se Använda filnamns- och filsökvägsfunktioner för att rikta in sig på specifika partitioner.

Samla din Azure Cosmos DB-analyslagring och serverlös SQL-pool

Kontrollera att din Azure Cosmos DB-analyslagring placeras i samma region som en Azure Synapse-arbetsyta. Frågor mellan regioner kan orsaka enorma svarstider. Använd regionegenskapen i anslutningssträng för att uttryckligen ange den region där analysarkivet finns (se Fråga Azure Cosmos DB med hjälp av en serverlös SQL-pool):account=<database account name>;database=<database name>;region=<region name>'

CSV-optimeringar

Här följer metodtips för att använda CSV-filer i en serverlös SQL-pool.

Använd PARSER_VERSION 2.0 för att köra frågor mot CSV-filer

Du kan använda en prestandaoptimerad parser när du kör frågor mot CSV-filer. Mer information finns i PARSER_VERSION.

Skapa statistik manuellt för CSV-filer

Serverlös SQL-pool förlitar sig på statistik för att generera optimala frågekörningsplaner. Statistik skapas automatiskt för kolumner med sampling och i de flesta fall är samplingsprocenten mindre än 100 %. Det här flödet är detsamma för varje filformat. Tänk på att när du läser CSV med parser version 1.0 stöds inte sampling och automatisk skapande av statistik sker inte med samplingsprocent mindre än 100 %. För små tabeller med uppskattad låg kardinalitet (antal rader) utlöses automatisk statistikgenerering med samplingsprocent på 100 %. Det innebär att fullscan utlöses och automatisk statistik skapas även för CSV med parser version 1.0. Om statistik inte skapas automatiskt skapar du statistik manuellt för kolumner som du använder i frågor, särskilt de som används i DISTINCT, JOIN, WHERE, ORDER BY och GROUP BY. Mer information finns i statistik i en serverlös SQL-pool .

Datatyper

Här följer metodtips för att använda datatyper i en serverlös SQL-pool.

Använda lämpliga datatyper

De datatyper som du använder i frågan påverkar prestanda och samtidighet. Du kan få bättre prestanda om du följer dessa riktlinjer:

  • Använd den minsta datastorleken som rymmer det största möjliga värdet.
    • Om den maximala teckenvärdelängden är 30 tecken använder du en teckendatatyp med längden 30.
    • Om alla teckenkolumnvärden har en fast storlek använder du tecken eller nchar. Annars använder du varchar eller nvarchar.
    • Om det maximala heltalskolumnvärdet är 500 använder du smallint eftersom det är den minsta datatypen som kan hantera det här värdet. Mer information finns i heltalsdatatypintervall.
  • Använd om möjligt varchar och char i stället för nvarchar och nchar.
    • Använd varchar-typen med viss UTF8-sortering om du läser data från Parquet, Azure Cosmos DB, Delta Lake eller CSV med UTF-8-kodning.
    • Använd varchar-typen utan UTF8-sortering om du läser data från CSV-filer som inte är Unicode-filer (till exempel ASCII).
    • Använd nvarchar-typen om du läser data från en CSV UTF-16-fil.
  • Använd heltalsbaserade datatyper om det är möjligt. SORT-, JOIN- och GROUP BY-åtgärder slutförs snabbare på heltal än på teckendata.
  • Om du använder schemainferens kontrollerar du härledda datatyper och åsidosätter dem uttryckligen med de mindre typerna om möjligt.

Kontrollera härledda datatyper

Schemainferens hjälper dig att snabbt skriva frågor och utforska data utan att känna till filscheman. Kostnaden för den här bekvämligheten är att härledda datatyper kan vara större än de faktiska datatyperna. Den här avvikelsen inträffar när det inte finns tillräckligt med information i källfilerna för att se till att rätt datatyp används. Parquet-filer innehåller till exempel inte metadata om maximal kolumnlängd för tecken. Så serverlös SQL-pool härleder den som varchar(8000).

Tänk på att situationen kan vara annorlunda om de delade hanterade och externa Spark-tabellerna exponeras i SQL-motorn som externa tabeller. Spark-tabeller tillhandahåller olika datatyper än Synapse SQL-motorerna. Du hittar mappning mellan Spark-tabelldatatyper och SQL-typer här.

Du kan använda den systemlagringsprocedur sp_describe_first_results_set för att kontrollera de resulterande datatyperna i frågan.

I följande exempel visas hur du kan optimera härledda datatyper. Den här proceduren används för att visa härledda datatyper:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Här är resultatuppsättningen:

is_hidden column_ordinal name system_type_name max_length
0 1 vendor_id varchar(8000) 8000
0 2 pickup_datetime datetime2(7) 8
0 3 passenger_count heltal 4

När du känner till de härledda datatyperna för frågan kan du ange lämpliga datatyper:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Filteroptimering

Här följer metodtips för att använda frågor i en serverlös SQL-pool.

Skicka jokertecken till lägre nivåer i sökvägen

Du kan använda jokertecken i sökvägen för att köra frågor mot flera filer och mappar. Serverlös SQL-pool visar filer i ditt lagringskonto, från och med den första asterisken (*), med hjälp av lagrings-API:et. Filer som inte matchar den angivna sökvägen visas inte. Om du begränsar den initiala listan med filer kan prestandan förbättras om det finns många filer som matchar den angivna sökvägen upp till det första jokertecknet.

Använda filnamns- och filsökvägsfunktioner för att rikta in sig på specifika partitioner

Data organiseras ofta i partitioner. Du kan instruera serverlös SQL-pool att fråga efter specifika mappar och filer. Detta minskar antalet filer och mängden data som frågan behöver läsa och bearbeta. En extra bonus är att du får bättre prestanda.

Mer information finns i filnamns - och filsökvägsfunktionerna och se exemplen för att fråga efter specifika filer.

Dricks

Skicka alltid resultatet av filsökvägs- och filnamnsfunktionerna till lämpliga datatyper. Om du använder teckendatatyper bör du använda rätt längd.

Funktioner som används för partitionseliminering, filsökväg och filnamn stöds för närvarande inte för externa tabeller, förutom de som skapas automatiskt för varje tabell som skapas i Apache Spark för Azure Synapse Analytics.

Om dina lagrade data inte är partitionerade kan du överväga att partitionera dem. På så sätt kan du använda dessa funktioner för att optimera frågor som riktar sig mot dessa filer. När du frågar partitionerade Apache Spark för Azure Synapse-tabeller från en serverlös SQL-pool riktar frågan automatiskt endast in sig på nödvändiga filer.

Använd korrekt sortering för att använda predikat-pushdown för teckenkolumner

Data i en Parquet-fil ordnas i radgrupper. Serverlös SQL-pool hoppar över radgrupper baserat på det angivna predikatet i WHERE-satsen, vilket minskar I/O. Resultatet är ökad frågeprestanda.

Predikat-pushdown för teckenkolumner i Parquet-filer stöds endast för Latin1_General_100_BIN2_UTF8 sortering. Du kan ange sortering för en viss kolumn med hjälp av en WITH-sats. Om du inte anger den här sorteringen med hjälp av en WITH-sats används databassorteringen.

Optimera upprepade frågor

Här följer metodtips för att använda CETAS i en serverlös SQL-pool.

Använda CETAS för att förbättra frågeprestanda och kopplingar

CETAS är en av de viktigaste funktionerna i en serverlös SQL-pool. CETAS är en parallell åtgärd som skapar externa tabellmetadata och exporterar SELECT-frågeresultatet till en uppsättning filer i ditt lagringskonto.

Du kan använda CETAS för att materialisera vanliga delar av frågor, till exempel anslutna referenstabeller, till en ny uppsättning filer. Du kan sedan ansluta till den här externa tabellen i stället för att upprepa vanliga kopplingar i flera frågor.

När CETAS genererar Parquet-filer skapas statistik automatiskt när den första frågan riktar sig mot den här externa tabellen. Resultatet är bättre prestanda för efterföljande frågor som måltabellen genereras med CETAS.

Fråga Azure-data

Med serverlösa SQL-pooler kan du fråga efter data i Azure Storage eller Azure Cosmos DB med hjälp av externa tabeller och funktionen OPENROWSET. Kontrollera att du har rätt behörighet för lagringen.

Fråga CSV-data

Lär dig hur du kör frågor mot en enskild CSV-fil eller -mappar och flera CSV-filer. Du kan också köra frågor mot partitionerade filer

Fråga Parquet-data

Lär dig hur du kör frågor mot Parquet-filer med kapslade typer. Du kan också köra frågor mot partitionerade filer.

Fråga Delta Lake

Lär dig hur du kör frågor mot Delta Lake-filer med kapslade typer.

Fråga Azure Cosmos DB-data

Lär dig hur du kör frågor mot Azure Cosmos DB-analysarkivet. Du kan använda en onlinegenerator för att generera WITH-satsen baserat på ett Azure Cosmos DB-exempeldokument. Du kan skapa vyer ovanpå Azure Cosmos DB-containrar.

Fråga efter JSON-data

Lär dig hur du kör frågor mot JSON-filer. Du kan också köra frågor mot partitionerade filer.

Skapa vyer, tabeller och andra databasobjekt

Lär dig hur du skapar och använder vyer och externa tabeller eller konfigurerar säkerhet på radnivå. Om du har partitionerade filer kontrollerar du att du använder partitionerade vyer.

Kopiera och transformera data (CETAS)

Lär dig hur du lagrar frågeresultat till lagring med hjälp av CETAS-kommandot.

Nästa steg