SQL Server Architectuur (Verklaard)
Fiona Bruin
⚡ Slimme samenvatting
SQL Server ArchiDe architectuur volgt een client-servermodel dat is opgebouwd uit drie kernlagen: een protocollaag voor netwerkcommunicatie, een relationele engine voor het verwerken van query's en een opslagengine voor gegevensbeheer en -opvraging.
MS SQL Server heeft een client-serverarchitectuur. Het MS SQL Server-proces begint met een verzoek van de clientapplicatie. De SQL Server accepteert het verzoek, verwerkt het en beantwoordt het met de verwerkte gegevens. Laten we de volledige architectuur hieronder in detail bespreken:
Zoals het onderstaande diagram laat zien, bestaat SQL Server uit drie belangrijke componenten. Archistructuur:
- Protocollaag
- Relationele motor
- Opslag-engine
Protocollaag – SNI
De SQL Server Protocol Layer, ook wel bekend als de Server Network Interface (SNI), ondersteunt drie typen client-serverarchitectuur. Elk protocol is geschikt voor een ander netwerkscenario. Inzicht in deze protocollen is essentieel voordat we dieper ingaan op de interne verwerking van query's.
Gedeelde herinnering
Neem bijvoorbeeld een gesprek in de vroege ochtend. Tom en zijn moeder bevinden zich op dezelfde logische plek: thuis. Tom vraagt om koffie en zijn moeder serveert die direct. Op dezelfde manier biedt SQL Server het Shared Memory-protocol wanneer de client en de server op dezelfde machine draaien. Beide communiceren via gedeeld geheugen zonder extra netwerkbelasting.
Analogie: Tom staat voor de Client, Mom voor SQL Server, Home voor de Machine en verbale communicatie voor het Shared Memory-protocol.
Configuratie-opmerkingen: In Studio voor SQL-beheerDe optie "Servernaam" voor een lokale verbinding kan ".", "localhost", "127.0.0.1" of "Machine\Instance" zijn.
TCP / IP
Stel je nu voor dat Tom koffie wil van een koffiezaak die 10 km verderop ligt. Tom is thuis en de koffiezaak bevindt zich op een drukke markt. Ze communiceren via een mobiel netwerk. Op dezelfde manier biedt SQL Server de mogelijkheid om... TCP / IP-protocol wanneer de client en SQL Server zich op aparte machines bevinden die via een netwerk met elkaar zijn verbonden.
Analogie: Tom komt overeen met de klant, de coffeeshop met SQL Server, het huis en de marktplaats met externe locaties en het mobiele netwerk met het TCP/IP-protocol.
Configuratie-opmerkingen: In SQL Management Studio moet de optie 'Servernaam' voor een TCP/IP-verbinding 'Machine\Instantie van de server' zijn. SQL Server gebruikt standaard poort 1433 voor TCP/IP-verbindingen.
Benoemde pijpen
Tot slot wil Tom groene thee van zijn buurvrouw Sierra. Ze bevinden zich op dezelfde locatie, omdat ze buren zijn, en communiceren via een intern netwerk. Op dezelfde manier biedt SQL Server het Named Pipe-protocol wanneer de client en server via een lokaal netwerk (LAN) met elkaar verbonden zijn.
Analogie: Tom komt overeen met de client, Sierra met SQL Server, buren komen overeen met het LAN en het intra-netwerk komt overeen met het Named Pipe-protocol.
Configuratie-opmerkingen: Named Pipes is standaard uitgeschakeld en moet worden ingeschakeld via SQL Configuration Manager.
Wat is TDS?
Nu de drie typen client-serverarchitectuur duidelijk zijn, volgt hier een blik op TDS:
- TDS staat voor Tabular Data Stream.
- Alle drie de protocollen gebruiken TDS-pakketten.
- TDS is ingekapseld in netwerkpakketten, waardoor gegevensoverdracht van de clientmachine naar de servermachine mogelijk is.
- TDS werd oorspronkelijk ontwikkeld door Sybase en is nu eigendom van Microsoft.
De volgende tabel vergelijkt de drie SQL Server-verbindingsprotocollen:
| Kenmerk | Gedeelde herinnering | TCP / IP | Benoemde pijpen |
|---|---|---|---|
| Netwerkbereik | Dezelfde machine | Op afstand (WAN/internet) | Alleen LAN |
| Standaard poort | NB | 1433 | 445 |
| Prestaties | Snelst (geen netwerkoverhead) | Goed (geoptimaliseerd voor WAN) | Goed (geoptimaliseerd voor LAN) |
| Standaard ingeschakeld | Ja | Ja | Nee |
| Beste gebruiksgeval | Lokale ontwikkeling en testen | Productie op afstand toegang | Vertrouwde LAN-omgevingen |
De protocollaag verzorgt de netwerkcommunicatie, waarna de query zelf verder verwerkt wordt. Dit is waar de relationele engine het overneemt.
Relationele motor
De relationele engine wordt ook wel de queryprocessor genoemd. Deze bevat de SQL Server-componenten die bepalen wat een query moet doen en hoe deze het meest efficiënt kan worden uitgevoerd. De queryprocessor is verantwoordelijk voor het uitvoeren van gebruikersquery's door gegevens op te vragen bij de opslagengine en de geretourneerde resultaten te verwerken.
Zoals weergegeven in het architectuurdiagram, bestaat de relationele engine uit drie belangrijke componenten:
CMD-parser
De gegevens die van de protocollaag worden ontvangen, worden doorgegeven aan de relationele engine. De CMD-parser is het eerste onderdeel dat de querygegevens ontvangt. De belangrijkste taak van de parser is het controleren van de query op syntactische en semantische fouten en vervolgens het genereren van een queryboom.
Syntactische controle: Net als elke andere programmeertaal heeft SQL Server een vooraf gedefinieerde set trefwoorden en grammaticaregels. SELECT, INSERT, UPDATE en vele andere behoren tot deze lijst met vooraf gedefinieerde trefwoorden. De CMD Parser controleert of de invoer aan deze regels voldoet. Als de invoer van de gebruiker afwijkt van de verwachte syntaxis, geeft de parser een foutmelding.
Voorbeeld: Stel je voor dat een Rus een Japans restaurant binnenloopt en in het Russisch bestelt. De ober begrijpt alleen Japans en kan de bestelling niet verwerken. Op dezelfde manier geeft de CMD Parser een foutmelding als een gebruiker "SELECR" typt in plaats van "SELECT", omdat het trefwoord niet wordt herkend.
Semantische controle: Dit wordt uitgevoerd door de Normalizer. Deze controleert of de kolomnamen, tabelnamen en andere objecten die worden opgevraagd daadwerkelijk in het schema bestaan. Als ze bestaan, koppelt de Normalizer ze aan de query. Dit proces wordt ook wel binding genoemd. Wanneer gebruikersquery's een VIEW bevatten, vervangt de Normalizer deze door de intern opgeslagen viewdefinitie.
Voorbeeld: Hardlopen SELECT * from USER_ID Dit zou ertoe leiden dat de parser een foutmelding geeft tijdens de semantische controle als de tabel USER_ID niet in de database bestaat.
Zoekopdrachtboom maken: Deze stap genereert verschillende uitvoeringsbomen die de diverse manieren weergeven waarop een query kan worden uitgevoerd. Alle bomen produceren dezelfde gewenste uitvoer.
Optimizer
De optimizer maakt een uitvoeringsplan voor de query van de gebruiker. Dit plan bepaalt hoe de query wordt uitgevoerd. Niet alle query's worden geoptimaliseerd. Optimalisatie is van toepassing op DML-opdrachten (Data Modification Language) zoals SELECT, INSERT, DELETE en UPDATE. DDL-opdrachten zoals CREATE en ALTER worden niet geoptimaliseerd, maar worden gecompileerd naar een interne vorm.
De querykosten worden berekend op basis van factoren zoals CPU-gebruik, geheugengebruik en input/output-behoeften. De rol van de optimizer is om het goedkoopste en meest kostenefficiënte uitvoeringsplan te vinden, niet per se het allerbeste.
Voorbeeld: Stel je voor dat je een online bankrekening wilt openen. Bij één bank duurt dat maximaal 2 dagen. Je hebt ook een lijst met 20 andere banken die mogelijk sneller zijn. Het is niet zeker of je bij al die 20 banken een snellere optie vindt, en de zoektocht zelf kost tijd. Het was beter geweest om meteen voor de eerste bank te kiezen. Op dezelfde manier gebruikt de SQL Optimizer uitputtende en heuristische algoritmen om de uitvoeringstijd van query's te minimaliseren.
De optimizer doorloopt drie zoekfasen:
Fase 0: Zoeken naar een triviaal plan
Dit is de fase vóór de optimalisatie. Voor sommige query's bestaat er slechts één praktisch uitvoeringsplan, een zogenaamd triviaal plan. Verder zoeken is niet nodig, omdat elke extra zoekopdracht hetzelfde uitvoeringsplan zou opleveren, maar dan tegen extra kosten.
Fase 1: Zoeken naar transactieverwerkingsplannen
Dit omvat het zoeken naar zowel eenvoudige als complexe uitvoeringsplannen. De zoektocht naar een eenvoudig plan maakt gebruik van statistische analyse van kolom- en indexgegevens, doorgaans beperkt tot één index per tabel. Als er geen eenvoudig plan wordt gevonden, wordt een complexere zoekopdracht uitgevoerd waarbij meerdere indexen per tabel worden gebruikt.
Fase 2: Parallelle verwerking en optimalisatie
Als de voorgaande strategieën geen adequaat plan opleveren, zoekt de optimizer naar mogelijkheden voor parallelle verwerking op basis van de verwerkingscapaciteit van de machine. Als parallelle verwerking niet mogelijk is, begint een laatste optimalisatiefase waarin alle resterende opties worden gebruikt om het best mogelijke uitvoeringsplan te vinden.
Query-uitvoerder
De query-executor roept de toegangsmethode in de opslagengine aan. Deze levert een uitvoeringsplan met de logica voor het ophalen van gegevens die nodig is voor de uitvoering. Zodra de gegevens van de opslagengine zijn ontvangen, wordt het resultaat naar de protocollaag gepubliceerd en naar de eindgebruiker verzonden.
Nadat de relationele engine heeft bepaald hoe een query moet worden uitgevoerd, handelt de opslagengine de fysieke gegevensbewerkingen af. Deze laag beheert hoe gegevens worden opgeslagen, in de cache worden geplaatst en van de schijf worden opgehaald.
Opslag-engine
De opslagengine is verantwoordelijk voor het opslaan van gegevens in een opslagsysteem, zoals een schijf of SAN, en het ophalen ervan wanneer dat nodig is. Voordat we de componenten van de opslagengine bekijken, is het belangrijk om te begrijpen hoe gegevens fysiek worden opgeslagen.
Gegevensbestanden en -bereiken
Gegevensbestanden slaan gegevens fysiek op in de vorm van datapagina's, waarbij elke pagina een grootte heeft van 8 KB. Dit is de kleinste opslageenheid in SQL ServerGegevenspagina's zijn logisch gegroepeerd in extenten. Aan geen enkel object wordt direct een individuele pagina toegewezen; in plaats daarvan wordt het onderhoud via extenten uitgevoerd. Elke pagina heeft een paginaheader (96 bytes) met metadata zoals paginatype, paginanummer, gebruikte ruimte, vrije ruimte en verwijzingen naar de volgende en vorige pagina's.
Bestand types
Primair bestand: Elke database bevat één primair bestand. Dit bestand slaat alle belangrijke gegevens op met betrekking tot tabellen, weergaven, triggers en andere objecten. De extensie is doorgaans .mdf, maar kan elke extensie hebben.
Secundair bestand: Een database kan al dan niet meerdere secundaire bestanden bevatten. Deze zijn optioneel en bevatten gebruikersspecifieke gegevens. De extensie is doorgaans .ndf, maar kan elke extensie zijn.
Logbestand: Ook wel bekend als write-ahead logs. De extensie is .ldf. Logbestanden worden gebruikt voor transactiebeheer, herstel na ongewenste incidenten en het terugdraaien van niet-voltooide transacties.
De opslagengine bestaat uit drie hoofdcomponenten. Elk component speelt een specifieke rol in het beheer van gegevenstoegang en -integriteit.
Toegangsmethode
De toegangsmethode fungeert als interface tussen de query-uitvoerder en de Buffer Manager- of transactielogboeken. Deze voert de query niet zelf uit, maar bepaalt het type query:
- Als de query een SELECT-instructie (DML)het wordt doorgegeven aan de Buffer Manager voor verdere afhandeling.
- Als de query een Niet-SELECT-instructies (DDL en DML)Het wordt doorgegeven aan de transactiemanager. Dit omvat meestal UPDATE-, INSERT- en DELETE-instructies.
Buffer Manager
De Buffer De manager beheert de kernfuncties voor plancache, gegevensparsing en de afhandeling van gewijzigde pagina's.
Plancache
Bestaand queryplan: De Buffer De manager controleert of het uitvoeringsplan aanwezig is in de opgeslagen plancache. Zo ja, dan worden het in de cache opgeslagen queryplan en de bijbehorende datacache direct gebruikt.
Plan voor een eerste cache: Als een uitvoeringsplan voor een eerste query complex is, wordt het opgeslagen in de plancache. Dit zorgt ervoor dat het plan de volgende keer dat SQL Server dezelfde query ontvangt, sneller beschikbaar is.
Gegevens parseren: Buffer Cache en gegevensopslag
De Buffer De manager biedt toegang tot de benodigde gegevens. Afhankelijk van of de gegevens in de cache aanwezig zijn, zijn er twee benaderingen mogelijk:
Buffer Cache – Zachte parsing
De Buffer De manager zoekt naar gegevens in de Buffer Cache. Als de gegevens aanwezig zijn, gebruikt de query-uitvoerder ze direct. Dit verbetert de prestaties, omdat het ophalen van gegevens uit de cache minder I/O-bewerkingen vereist dan het ophalen van gegevens van de schijf.
Gegevensopslag – Harde parsing
Als er geen gegevens aanwezig zijn in de Buffer De benodigde gegevens worden opgezocht in de gegevensopslag op de schijf. De gegevens worden vervolgens ook in de datacache opgeslagen voor toekomstig gebruik.
Transactiemanager
De transactiemanager wordt aangeroepen wanneer de toegangsmethode vaststelt dat een query geen SELECT-instructie is. Deze zorgt voor gegevensconsistentie en -duurzaamheid door middel van verschillende subcomponenten:
Logboekbeheer
De logmanager bewaart track van alle updates die in het systeem worden uitgevoerd, worden opgeslagen in transactielogboeken. Elke logboekvermelding bevat een logboekvolgnummer, samen met de transactie-ID en de gegevenswijzigingsrecord. Dit mechanisme tracks voltooide en teruggedraaide transacties.
Slotbeheerder
Tijdens een transactie komen de bijbehorende gegevens in de opslag in een vergrendelde staat terecht. De Lock Manager beheert dit proces en zorgt voor gegevensconsistentie en -isolatie. Deze eigenschappen staan ook bekend als ACID (Atomiciteit, consistentie, isolatie, duurzaamheid).
Uitvoeringsproces
Het uitvoeringsproces verloopt volgens deze stappen:
- De Log Manager start met loggen en de Lock Manager vergrendelt de bijbehorende gegevens.
- Een kopie van de gegevens wordt bewaard in de Buffer Cache.
- Een kopie van de bij te werken gegevens wordt bewaard in het logboek. BufferEn alle gebeurtenissen werken de gegevens in de data bij. Buffer.
- Pagina's die gewijzigde gegevens opslaan, worden genoemd Vuile pagina's.
Checkpoint- en write-ahead-logging
Het checkpointproces wordt ongeveer één keer per minuut uitgevoerd en markeert alle gewijzigde pagina's om naar de schijf te worden geschreven. De pagina wordt echter eerst vanuit de logbestanden naar de datapagina van het logbestand verplaatst. Buffer Logboekregistratie. Dit mechanisme staat bekend als write-ahead logging. De gewijzigde pagina's blijven in de cache staan, zelfs nadat ze naar de schijf zijn geschreven.
lui Writer
Wanneer SQL Server een zware belasting detecteert en buffergeheugen nodig is voor nieuwe transacties, worden gewijzigde pagina's uit de cache verwijderd. De Lazy-methode Writer Het systeem maakt gebruik van het LRU-algoritme (Least Recently Used) om pagina's uit de bufferpool naar de schijf te verwijderen.
Hoe SQL Server een query van begin tot eind verwerkt
Het is waardevol om elke laag afzonderlijk te begrijpen, maar inzicht in hoe ze samenwerken verduidelijkt het complete plaatje. Wanneer een clientapplicatie een SQL-query verzendt, vindt de volgende reeks stappen plaats:
De Protocollaag Het ontvangt het verzoek via gedeeld geheugen, TCP/IP of named pipes en verpakt het in een TDS-pakket. Relationele motor Vervolgens neemt de CMD Parser het over: de CMD Parser controleert de syntaxis en semantiek, de Optimizer genereert het meest efficiënte uitvoeringsplan en de Query Executor begint met het ophalen van de gegevens.
De query-uitvoerder roept de Storage Engine's Toegangsmethode, die SELECT-query's doorstuurt naar de Buffer Manager- en wijzigingsvragen aan de transactiemanager. Buffer Manager controleert Plan Cache en Buffer Eerst wordt de data in de cache opgeslagen (soft parsing). Als de data niet in de cache staat, wordt de schijf gelezen (hard parsing). Voor schrijfbewerkingen coördineert de transactiemanager de logmanager, de vergrendelingsmanager en het checkpointproces om te zorgen voor naleving van de ACID-principes.
Zodra de opslagengine de gevraagde gegevens retourneert, formatteert de relationele engine de resultaatset en stuurt de protocollaag deze via hetzelfde TDS-protocol terug naar de clientapplicatie.
Hoe kies je het juiste protocol voor SQL Server-verbindingen?
De keuze voor het juiste protocol hangt af van de fysieke relatie tussen de client en de server, evenals van de prestatie-eisen.
Gebruik gedeeld geheugen Wanneer de clientapplicatie op dezelfde machine draait als SQL Server, is dit de snelste optie omdat alle netwerkoverhead wordt geëlimineerd. Het is ideaal voor lokale ontwikkeling, testen en implementaties op één machine.
Gebruik TCP/IP Wanneer de client en server zich op verschillende machines bevinden die via een WAN of internet met elkaar verbonden zijn, is dit het meest gebruikte protocol in productieomgevingen. SQL Server luistert standaard op poort 1433 en dit protocol ondersteunt versleutelde verbindingen via TLS.
Gebruik benoemde pijpen Wanneer de client en server zich op hetzelfde vertrouwde LAN bevinden en prestaties op interne netwerken prioriteit hebben, is Named Pipes standaard uitgeschakeld. Dit moet worden ingeschakeld via SQL Server Configuration Manager. Het komt minder vaak voor in moderne implementaties, maar blijft nuttig voor oudere intranetapplicaties.
