Best practices voor toegewezen SQL-pools in Azure Synapse Analytics

  • Artikel

Dit artikel bevat een verzameling aanbevolen procedures om u te helpen optimale prestaties te bereiken voor toegewezen SQL-pools in Azure Synapse Analytics. Als u met een serverloze SQL-pool werkt, raadpleegt u de aanbevolen procedures voor serverloze SQL-pools voor specifieke richtlijnen. Hieronder vindt u basisrichtlijnen en belangrijke gebieden waar u zich kunt richten wanneer u uw oplossing bouwt. In elke sectie maakt u kennis met een concept en verwijst u vervolgens naar meer gedetailleerde artikelen die betrekking hebben op het concept.

Toegewezen SQL-pools laden

Zie Richtlijnen voor het laden van gegevens voor toegewezen SQL-pools.

Kosten verlagen met onderbreken en schalen

Zie Compute beheren voor meer informatie over het verlagen van de kosten via onderbreken en schalen.

Statistieken bijhouden

Toegewezen SQL-pool kan worden geconfigureerd voor het automatisch detecteren en maken van statistieken voor kolommen. De queryplannen die door de optimizer zijn gemaakt, zijn slechts zo goed als de beschikbare statistieken.

U wordt aangeraden AUTO_CREATE_STATISTICS voor uw databases in te schakelen en de statistieken dagelijks of na elke belasting bijgewerkt te houden om ervoor te zorgen dat statistieken over kolommen die in uw query's worden gebruikt, altijd up-to-date zijn.

Als u de onderhoudstijd van statistieken wilt verkorten, moet u selectief zijn over welke kolommen statistieken hebben of de meest frequente update nodig hebben. U kunt bijvoorbeeld datumkolommen bijwerken waarin nieuwe waarden dagelijks kunnen worden toegevoegd. Focus op het hebben van statistieken voor kolommen die betrokken zijn bij joins, kolommen die worden gebruikt in de WHERE-component en kolommen die in GROUP BY zijn gevonden.

Meer informatie over statistieken vindt u in de artikelen Tabelstatistieken beheren, CREATE STATISTICS en UPDATE STATISTICS.

Queryprestaties afstemmen

INSERT-instructie in batches groeperen

Een eenmalige belasting van een kleine tabel met een INSERT-instructie, zoals INSERT INTO MyLookup VALUES (1, 'Type 1')de beste benadering, afhankelijk van uw behoeften. Als u echter de hele dag duizenden of miljoenen rijen moet laden, is het waarschijnlijk dat singleton INSERTS niet optimaal is.

Een manier om dit probleem op te lossen is door één proces te ontwikkelen dat naar een bestand schrijft en vervolgens een ander proces om dit bestand periodiek te laden. Raadpleeg het artikel INSERT voor meer informatie.

PolyBase gebruiken om snel gegevens te laden en te exporteren

Toegewezen SQL-pool ondersteunt het laden en exporteren van gegevens via verschillende hulpprogramma's, waaronder Azure Data Factory, PolyBase en BCP. Voor kleine hoeveelheden gegevens waarbij prestaties niet belangrijk zijn, kunnen al deze toepassingen aan uw vereisten voldoen.

Notitie

PolyBase is de beste keuze wanneer u grote hoeveelheden gegevens laadt of exporteert of snellere prestaties nodig hebt.

PolyBase-loads kunnen worden uitgevoerd met behulp van CTAS of INSERT INTO. CTAS minimaliseert transactielogboekregistratie en is de snelste manier om uw gegevens te laden. Azure Data Factory ondersteunt ook PolyBase-belastingen en kan prestaties bereiken die vergelijkbaar zijn met CTAS. PolyBase ondersteunt verschillende bestandsindelingen, waaronder Gzip-bestanden.

Als u de doorvoer wilt maximaliseren bij het gebruik van Gzip-tekstbestanden, moet u bestanden opsplitsen in 60 of meer bestanden om de parallelle uitvoering van uw belasting te maximaliseren. Voor een snellere totale doorvoer, kunt u overwegen gegevens gelijktijdig te laden. Aanvullende informatie die relevant is voor deze sectie is opgenomen in de volgende artikelen:

Externe tabellen laden en vervolgens query’s uitvoeren

PolyBase is niet optimaal voor query's. PolyBase-tabellen voor toegewezen SQL-pools ondersteunen momenteel alleen Azure Blob-bestanden en Azure Data Lake-opslag. Deze bestanden hebben geen rekenresources die er back-ups van maken. Als gevolg hiervan kunnen toegewezen SQL-pools dit werk niet offloaden en moet het hele bestand lezen door het te laden zodat tempdb de gegevens kunnen worden gelezen.

Als u meerdere query's hebt voor het uitvoeren van query's op deze gegevens, is het beter om deze gegevens eenmaal te laden en query's de lokale tabel te gebruiken. Verdere PolyBase-richtlijnen zijn opgenomen in de handleiding voor het gebruik van PolyBase-artikel .

Grote tabellen distribueren met hash

Tabellen worden standaard gedistribueerd middels Round Robin. Met deze standaardinstelling kunnen gebruikers eenvoudig tabellen maken zonder te hoeven bepalen hoe hun tabellen moeten worden gedistribueerd. Round Robin-tabellen kunnen voldoende presteren voor sommige workloads. In de meeste gevallen biedt een distributiekolom echter betere prestaties.

Het meest voorkomende voorbeeld van een tabel die door een kolom wordt gedistribueerd, presteert beter dan een round robin-tabel wanneer twee grote feitentabellen worden samengevoegd.

Als u bijvoorbeeld een orderstabel hebt die is gedistribueerd door order_id en een transactietabel die ook wordt gedistribueerd door order_id, wordt deze query een passthrough-query wanneer u de ordertabel aan de transactietabel op order_id koppelt. Bewerkingen voor gegevensverplaatsing worden vervolgens geëlimineerd. Minder stappen betekent een snellere query. Minder gegevensverplaatsing maakt query’s ook sneller.

Tip

Wanneer u een gedistribueerde tabel laadt, moeten uw binnenkomende gegevens niet worden gesorteerd op de distributiesleutel. Als u dit doet, vertraagt u uw belasting.

De onderstaande artikelkoppelingen geven u aanvullende informatie over het verbeteren van de prestaties via het selecteren van een distributiekolom. Ook vindt u informatie over het definiëren van een gedistribueerde tabel in de WITH-component van uw CREATE TABLE-instructie:

Niet te veel partities maken

Hoewel het partitioneren van gegevens effectief kan zijn voor het onderhouden van uw gegevens via het schakelen tussen partities of het optimaliseren van scans door partitieverwijdering, kunnen te veel partities uw query's vertragen. Vaak werkt een strategie voor het partitioneren van hoge granulariteit die goed werkt in SQL Server mogelijk niet goed in een toegewezen SQL-pool.

Te veel partities kunnen de effectiviteit van geclusterde columnstore-indexen verminderen als elke partitie minder dan 1 miljoen rijen heeft. Toegewezen SQL-pools partitioneren uw gegevens automatisch in 60 databases. Als u dus een tabel met 100 partities maakt, is het resultaat 6000 partities. Elke workload is anders, dus het beste advies is om te experimenteren met partitioneren om te zien wat het beste werkt voor uw workload.

Een van de opties waarmee u rekening moet houden, is het gebruik van een granulariteit die lager is dan wat u hebt geïmplementeerd met BEHULP van SQL Server. U kunt bijvoorbeeld wekelijkse of maandelijkse partities gebruiken in plaats van dagelijkse partities.

Meer informatie over partitionering vindt u in het artikel Tabelpartitionering .

Transactiegrootten minimaliseren

INSERT-, UPDATE- en DELETE-instructies worden uitgevoerd in een transactie. Wanneer ze mislukken, moeten ze worden teruggedraaid. Als u het potentieel voor een lange terugdraaiactie wilt verminderen, minimaliseert u waar mogelijk transactiegrootten. Het minimaliseren van transactiegrootten kan worden uitgevoerd door INSERT-, UPDATE- en DELETE-instructies in delen te delen. Als u bijvoorbeeld een INSERT hebt die u verwacht 1 uur te duren, kunt u de INSERT opsplitsen in vier delen. Elke uitvoering wordt vervolgens ingekort tot 15 minuten.

Tip

Maak gebruik van speciale instructies voor minimale registratie, zoals CTAS, TRUNCATE, DROP TABLE en INSERT, om tabellen leeg te maken, zodat het risico op terugdraaien wordt verkleind.

Een andere manier om terugdraaiacties te voorkomen, is door alleen-metagegevensbewerkingen, zoals schakelen tussen partities, te gebruiken voor gegevensbeheer. In plaats van bijvoorbeeld een DELETE-instructie uit te voeren om alle rijen in een tabel te verwijderen waarin de order_date zich in oktober 2001 bevond, kunt u uw gegevens maandelijks partitioneren. Vervolgens kunt u de partitie uitschakelen met gegevens voor een lege partitie uit een andere tabel (zie ALTER TABLE-voorbeelden).

Voor tabellen die niet zijn gepartitioneerd, kunt u een CTAS gebruiken om de gegevens te schrijven die u in een tabel wilt bewaren in plaats van DELETE te gebruiken. Als een CTAS dezelfde tijd in beslag neemt, is het veel veiliger om uit te voeren, omdat deze minimale transactielogboekregistratie heeft en indien nodig snel kan worden geannuleerd.

Meer informatie over inhoud met betrekking tot deze sectie vindt u in de onderstaande artikelen:

Queryresultaten verkleinen

Door queryresultaten te verkleinen, kunt u problemen aan de clientzijde voorkomen die worden veroorzaakt door grote queryresultaten. U kunt de query bewerken om het aantal geretourneerde rijen te verminderen. Met sommige hulpprogramma's voor het genereren van query's kunt u de syntaxis 'top N' toevoegen aan elke query. U kunt ook CETAS het queryresultaat naar een tijdelijke tabel gebruiken en vervolgens PolyBase-export gebruiken voor de verwerking op downlevel.

De kleinst mogelijke kolomgrootte gebruiken

Wanneer u uw DDL definieert, gebruikt u het kleinste gegevenstype dat uw gegevens ondersteunt, zodat de queryprestaties worden verbeterd. Deze aanbeveling is met name belangrijk voor CHAR- en VARCHAR-kolommen. Als de langste waarde in een kolom 25 tekens is, definieert u uw kolom als VARCHAR(25). U kunt alle tekenkolommen beter niet volgens een grote standaardlengte definiëren. Definieer bovendien kolommen als VARCHAR wanneer dat alles is wat nodig is in plaats van NVARCHAR te gebruiken.

Zie de artikelen Tabeloverzicht, Tabelgegevenstypen en CREATE TABLE voor een gedetailleerdere beoordeling van essentiële concepten die relevant zijn voor de bovenstaande informatie.

Tijdelijke heap-tabellen gebruiken voor tijdelijke gegevens

Wanneer u tijdelijk gegevens op toegewezen SQL-pools plaatst, worden heap-tabellen over het algemeen sneller verwerkt. Als u alleen gegevens laadt om deze te fasen voordat u meer transformaties uitvoert, is het laden van de tabel naar een heap-tabel sneller dan het laden van de gegevens in een geclusterde columnstore-tabel.

Het laden van gegevens naar een tijdelijke tabel wordt ook veel sneller geladen dan het laden van een tabel naar permanente opslag. Tijdelijke tabellen beginnen met een '#' en zijn alleen toegankelijk voor de sessie die deze heeft gemaakt. Daarom kunnen ze alleen in beperkte scenario's werken. Heap-tabellen worden gedefinieerd in het WITH-component van een CREATE TABLE-instructie. Als u wel een tijdelijke tabel gebruikt, vergeet dan niet ook statistieken voor de tijdelijke tabel te maken.

Zie de artikelen Tijdelijke tabellen, CREATE TABLE en CREATE TABLE AS SELECT voor meer informatie.

Geclusterde columnstore-tabellen optimaliseren

Geclusterde columnstore-indexen zijn een van de meest efficiënte manieren om uw gegevens op te slaan in een toegewezen SQL-pool. Tabellen in een toegewezen SQL-pool worden standaard gemaakt als Geclusterde ColumnStore. Een goede segmentkwaliteit is belangrijk om de beste resultaten te behalen voor query’s voor columnstore-tabellen. Wanneer rijen naar columnstore-tabellen worden geschreven onder geheugendruk, kan dit ten koste gaan van de kwaliteit van columnstore-segmenten.

Segmentkwaliteit kan worden gemeten door het aantal rijen in een gecomprimeerde rijgroep. Zie de oorzaken van slechte columnstore-indexkwaliteit in het artikel Tabelindexen voor stapsgewijze instructies voor het detecteren en verbeteren van segmentkwaliteit voor geclusterde columnstore-tabellen.

Omdat columnstore-segmenten van hoge kwaliteit belangrijk zijn, is het een goed idee om gebruikers-id's te gebruiken die zich in de gemiddelde of grote resourceklasse bevinden voor het laden van gegevens. Als u lagere datawarehouse-eenheden gebruikt, wilt u een grotere resourceklasse toewijzen aan uw laadgebruiker.

Columnstore-tabellen pushen doorgaans geen gegevens naar een gecomprimeerd columnstore-segment totdat er meer dan 1 miljoen rijen per tabel zijn. Elke toegewezen SQL-pooltabel wordt gedistribueerd in 60 verschillende distributies. Als zodanig profiteren columnstore-tabellen niet van een query, tenzij de tabel meer dan 60 miljoen rijen heeft.

Tip

Voor tabellen met minder dan 60 miljoen rijen is het mogelijk dat een columnstore-index niet de optimale oplossing is.

Als u uw gegevens partitioneert, moet elke partitie 1 miljoen rijen hebben om te profiteren van een geclusterde columnstore-index. Voor een tabel met 100 partities moet deze ten minste 6 miljard rijen hebben om te profiteren van een geclusterde kolommenopslag (60 distributies 100 partities 1 miljoen rijen).

Als uw tabel niet 6 miljard rijen heeft, hebt u twee hoofdopties. Verminder het aantal partities of overweeg in plaats daarvan een heap-tabel te gebruiken. Het kan ook de moeite waard zijn om te experimenteren om te zien of er betere prestaties kunnen worden verkregen met behulp van een heap-tabel met secundaire indexen in plaats van een columnstore-tabel.

Query’s worden sneller uitgevoerd voor een columnstore-tabel als u alleen de kolommen selecteert die u nodig hebt. Meer informatie over tabel- en columnstore-indexen vindt u in de onderstaande artikelen:

Een grotere resourceklasse gebruiken om queryprestaties te verbeteren

SQL-pools gebruiken resourcegroepen als een manier om geheugen toe te wijzen aan query's. In eerste instantie worden alle gebruikers toegewezen aan de kleine resourceklasse, die 100 MB geheugen per distributie verleent. Er zijn altijd 60 distributies. Elke distributie krijgt minimaal 100 MB. De totale geheugentoewijzing voor het hele systeem is 6000 MB of net minder dan 6 GB.

Bepaalde query’s, zoals grote samenvoegingen of loads naar geclusterde columnstore-tabellen, profiteren van grotere geheugentoewijzingen. Sommige query's, zoals pure scans, hebben geen voordeel. Het gebruik van grotere resourceklassen heeft invloed op gelijktijdigheid. Houd dus rekening met deze feiten voordat u al uw gebruikers verplaatst naar een grote resourceklasse.

Raadpleeg het artikel Resourceklassen voor workloadbeheer voor meer informatie over resourceklassen.

Kleinere resourceklasse gebruiken om de gelijktijdigheid te verhogen

Als u een lange vertraging in gebruikersquery's ziet, worden uw gebruikers mogelijk uitgevoerd in grotere resourceklassen. Dit scenario bevordert het verbruik van gelijktijdigheidssites, waardoor andere query's in de wachtrij kunnen worden geplaatst. Als u wilt bepalen of gebruikersquery's in de wachtrij staan, voert u uit SELECT * FROM sys.dm_pdw_waits om te zien of er rijen worden geretourneerd.

De resourceklassen voor workloadbeheer en sys.dm_pdw_waits artikelen bieden u meer informatie.

Gebruik DMV’s om uw query’s te controleren en te optimaliseren

Toegewezen SQL-pools hebben verschillende DMV's die kunnen worden gebruikt om de uitvoering van query's te bewaken. In het onderstaande bewakingsartikel wordt stapsgewijze instructies beschreven voor het weergeven van details van een uitgevoerde query. Als u snel query’s wilt vinden in de DMV's, kunt u proberen de optie LABEL te gebruiken bij uw query’s. Zie de artikelen in de onderstaande lijst voor meer gedetailleerde informatie:

Volgende stappen

Zie ook het artikel Probleemoplossing voor veelvoorkomende problemen en oplossingen.

Als u informatie nodig hebt die niet is opgegeven in dit artikel, kunt u op de microsoft Q&A-vragenpagina zoeken naar Azure Synapse om vragen te stellen aan andere gebruikers en aan de Azure Synapse Analytics-productgroep.

We controleren het forum regelmatig om er zeker van te zijn dat uw vragen worden beantwoord door een andere gebruiker of een van ons. Als u liever uw vragen over Stack Overflow stelt, hebben we ook een Azure Synapse Analytics Stack Overflow-forum.