Cheatsheet voor toegewezen SQL-pool (voorheen SQL DW) in Azure Synapse Analytics
Dit cheatsheet biedt nuttige tips en best practices voor het maken van uw toegewezen SQL-pooloplossingen (voorheen SQL DW).
De volgende afbeelding geeft het proces weer voor het ontwerpen van een datawarehouse met behulp van een toegewezen SQL-pool (voorheen SQL DW):
Query's en bewerkingen op tabellen
Als u van tevoren de primaire bewerkingen en query's kent die in uw datawarehouse kunnen worden uitgevoerd, kunt u prioriteiten stellen voor deze bewerkingen in uw datawarehouse-architectuur. Deze query's en bewerkingen zijn onder andere:
- Een of meerdere feitentabellen met dimensietabellen samenvoegen, de gecombineerde tabel filteren en vervolgens de resultaten aan een datamart toevoegen.
- Grote of kleine updates in uw feitelijke omzet aanbrengen.
- Alleen gegevens aan uw tabellen toevoegen.
Als u de typen bewerkingen van tevoren kent, kunt u het ontwerp van uw tabellen beter optimaliseren.
Gegevensmigratie
Laad uw gegevens eerst in Azure Data Lake Store of Azure Blob-opslag. Gebruik vervolgens de instructie COPY om uw gegevens in faseringstabellen te laden. Gebruik de volgende configuratie:
| Ontwerp | Aanbeveling |
|---|---|
| Distributie | Round robin |
| Indexeren | Heap |
| Partitionering | Geen |
| Resourceklasse | largerc of xlargerc |
Meer informatie over gegevensmigratie, gegevens laden, en het ELT-proces (extraheren, laden en transformeren).
Gedistribueerde of gerepliceerde tabellen
Gebruik de volgende strategieën, afhankelijk van de eigenschappen van de tabel:
| Type | Zeer geschikt voor... | Kijk uit met... |
|---|---|---|
| Gerepliceerd | * Kleine dimensietabellen in een stervormig schema met minder dan 2 GB aan opslagruimte na compressie (ca. 5x compressie) | * Veel schrijftransacties bevinden zich in de tabel (zoals invoegen, upsert, verwijderen, bijwerken) * U wijzigt de inrichting van Data Warehouse-eenheden (DWU) regelmatig * U gebruikt slechts 2-3 kolommen, maar de tabel bevat veel kolommen * U indexeer een gerepliceerde tabel |
| Round robin (standaard) | * Tijdelijke/faseringstabel * Geen duidelijke samenvoegsleutel of goede kandidaatkolom |
* Prestaties zijn traag vanwege gegevensverplaatsing |
| Hash | * Feitentabellen * Grote dimensietabellen |
* De distributiesleutel kan niet worden bijgewerkt |
Tips:
- Begin met round robin, maar ga voor een hashdistributiestrategie om te profiteren van een massively parallel-architectuur.
- Zorg ervoor dat algemene hashsleutels dezelfde gegevensindeling hebben.
- Distribueer niet in een varchar-indeling.
- Dimensietabellen met een algemene hashsleutel voor een feitentabel met regelmatige samenvoegbewerkingen kunnen met hash worden verdeeld.
- Gebruik sys.dm_pdw_nodes_db_partition_stats om eventuele asymmetrie in gegevens te analyseren.
- Gebruik sys.dm_pdw_request_steps om gegevensverplaatsingen achter query's te analyseren, de tijdbroadcast te bewaken en de opnamevolgorde van bewerkingen te wijzigen. Dit is handig om uw distributiestrategie te controleren.
Meer informatie over gerepliceerde tabellen en gedistribueerde tabellen.
Uw tabel indexeren
Indexeren is handig voor het snel lezen van tabellen. Er bestaat een unieke reeks technologieën die u op basis van uw behoeften kunt gebruiken:
| Type | Zeer geschikt voor... | Kijk uit met... |
|---|---|---|
| Heap | * Faseringstabel/tijdelijke tabel * Kleine tabellen met kleine zoekacties |
* Elke zoekopdracht scant de volledige tabel |
| Geclusterde index | * Tabellen met maximaal 100 miljoen rijen * Grote tabellen (meer dan 100 miljoen rijen) waarin slechts één of twee kolommen intensief worden gebruikt |
* Wordt gebruikt in een gerepliceerde tabel * U hebt complexe query's met meerdere join- en Group By-bewerkingen * U brengt updates aan in de geïndexeerde kolommen: het neemt geheugen in beslag |
| Geclusterde columnstore-index (CCI) (standaardinstelling) | * Grote tabellen (meer dan 100 miljoen rijen) | * Wordt gebruikt in een gerepliceerde tabel * U maakt enorme updatebewerkingen op uw tabel * U overpartitioneert uw tabel: rijgroepen omvatten geen verschillende distributieknooppunten en partities |
Tips:
- Mogelijk wilt u boven op een geclusterde index een niet-geclusterde index toevoegen aan een kolom die veel wordt gebruikt voor filteren.
- Wees voorzichtig met hoe u het geheugen van een tabel met CCI beheert. Wanneer u gegevens laadt, wilt u dat de gebruiker (of de query) profiteert van een grote resourceklasse. Voorkom bijsnijden en het maken van veel kleine gecomprimeerde rijgroepen.
- Bij Gen2 worden CCI-tabellen lokaal in de cache op de rekenknooppunten opgeslagen om de prestaties te maximaliseren.
- Bij CCI kunnen trage prestaties optreden vanwege slechte compressie van de rijgroepen. Als dit het geval is, herbouwt u uw CCI of ordent u deze opnieuw. U hebt ten minste 100.000 rijen per gecomprimeerde rijgroepen nodig. Ideaal is 1 miljoen rijen in een rijgroep.
- Afhankelijk van de frequentie en grootte van incrementeel laden, wilt u automatiseren wanneer u uw indexen opnieuw indeelt of herbouwt. Een lenteschoonmaak is altijd handig.
- Ga strategisch te werk bij het inkorten van een rijgroep. Hoe groot zijn de open rijgroepen? Hoeveel gegevens verwacht u dat er in de komende dagen worden geladen?
Meer informatie over indexen.
Partitionering
U kunt uw tabel partitioneren wanneer het een grote feitentabel is (groter dan 1 miljard rijen). De partitiesleutel moet in 99 procent van de gevallen worden gebaseerd op datum.
Met faseringstabellen waarvoor ELT is vereist, kan partitionering voordelen opleveren. Het vergemakkelijkt het beheer van de gegevenslevenscyclus. Zorg ervoor dat u uw feiten- of faseringstabel niet te veel partitioneert, met name in een geclusterde columnstore-index.
Meer informatie over partities.
Incrementeel laden
Als u uw gegevens incrementeel laadt, moet u eerst grotere resourceklassen toewijzen voor het laden van uw gegevens. Dit is vooral belangrijk bij het laden in tabellen met geclusterde columnstore-indexen. Zie Resourceklassen voor meer informatie.
We raden u aan PolyBase en ADF V2 te gebruiken voor het automatiseren van uw ELT-pijplijnen in uw datawarehouse.
Voor een grote batch met updates in uw historische gegevens kunt u een CTAS gebruiken om de gegevens te schrijven die u in een tabel wilt houden, in plaats van INSERT, UPDATE en DELETE te gebruiken.
Statistieken bijhouden
Het is belangrijk om uw statistieken bij te werken wanneer er significante wijzigingen optreden in uw gegevens. Zie statistieken bijwerken om te bepalen of aanzienlijke veranderingen zijn opgetreden. Bijgewerkte statistieken optimaliseren uw queryplannen. Als u vindt dat het onderhouden van al uw statistieken te lang duurt, kunt u selectiever zijn over welke kolommen statistieken bevatten.
U kunt ook de frequentie van de updates definiëren. Zo wilt u datumkolommen, waar nieuwe waarden kunnen zijn toegevoegd, misschien dagelijks bijwerken. U haalt het meeste voordeel uit statistieken bij kolommen die onderdeel uitmaken van samenvoegingen, kolommen met het WHERE-component en kolommen in GROUP BY.
Meer informatie over statistieken.
Resourceklasse
Resourcegroepen worden gebruikt om geheugen toe te wijzen aan query’s. Als u meer geheugenruimte nodig hebt voor het verbeteren van de query- of laadsnelheid, kunt u hogere resourceklassen toewijzen. Aan de andere kant hebben grotere resourceklassen een impact op de gelijktijdigheid. Denk hier goed over na voordat u alle gebruikers naar een grote resourceklasse verplaatst.
Als u merkt dat query's te lang duren, controleert u of uw gebruikers niet in grote resourceklassen worden uitgevoerd. Grote resourceklassen nemen veel gelijktijdigheidssleuven in beslag. Ze kunnen ervoor zorgen dat andere query's in de wachtrij komen.
Door het gebruik van Gen2 van een toegewezen SQL-pool (voorheen SQL DW) krijgt iedere resourceklasse uiteindelijk 2,5 keer zoveel geheugen als Gen1.
Meer informatie over het werken met resourceklassen en gelijktijdigheid.
Uw kosten verlagen
Een belangrijke functie van Azure Synapse is de mogelijkheid om rekenresources te beheren. U kunt de toegewezen SQL-pool (voorheen SQL DW) onderbreken wanneer u deze niet gebruikt, zodat er geen rekenresources in rekening worden gebracht. U kunt de schaal van resources aanpassen om te voldoen aan uw prestatievereisten. Voor onderbreken gebruikt u Azure Portal of PowerShell. Voor schaal aanpassen gebruikt u Azure Portal, PowerShell, T-SQL of een REST API.
U kunt nu op elk moment automatisch de schaal aanpassen met Azure Functions:
De architectuur optimaliseren voor betere prestaties
We raden u aan SQL Database en Azure Analysis Services in een hub-en-spoke-architectuur te gebruiken. Deze oplossing kan workloadisolatie bieden tussen verschillende gebruikersgroepen en tegelijkertijd geavanceerde beveiligingsfuncties van SQL Database en Azure Analysis Services gebruiken. Dit is ook een manier om grenzeloze gelijktijdigheid te bieden aan uw gebruikers.
Meer informatie over veelvoorkomende architecturen die profiteren van toegewezen SQL-pools (voorheen SQL DW) in Azure Synapse Analytics.
Met één muisklik uw spokes van de toegewezen SQL-pool (voorheen SQL DW) in SQL-databases implementeren: