SQL Server Architectuur (Verklaard)

MS SQL Server is een client-serverarchitectuur. Het MS SQL Server-proces start met de clienttoepassing die een verzoek verstuurt. De SQL Server accepteert, verwerkt en beantwoordt het verzoek met verwerkte gegevens. Laten we de volledige architectuur hieronder in detail bespreken:

Zoals het onderstaande diagram laat zien, zijn er drie belangrijke componenten in SQL Server Archistructuur:

1. Protocollaag
2. Relationele motor
3. Opslag-engine

Protocollaag – SNI

MS SQL SERVER PROTOCOL LAYER ondersteunt 3 soorten clientservers Archistructuur. We zullen beginnen met “Drie soorten clientservers Archistructuur” die MS SQL Server ondersteunt.

Gedeelde herinnering

Laten we een gespreksscenario in de vroege ochtend eens heroverwegen.

MOM en TOM – Hier waren Tom en zijn moeder op dezelfde logische plek, namelijk bij hen thuis. Tom kon om koffie vragen en mama kon het warm serveren.

MS SQL-SERVER – Here MS SQL server biedt GEDEELD GEHEUGENPROTOCOL. Hier CLIËNT en MS SQL server die op dezelfde machine draait. Beide kunnen communiceren via het Shared Memory-protocol.

Analogie: Laten we entiteiten in de bovenstaande twee scenario's in kaart brengen. We kunnen Tom eenvoudig toewijzen aan Client, Mom aan SQL-server, Home aan Machine en Verbale communicatie aan Shared Memory Protocol.

Vanaf het configuratie- en installatiebureau:

Voor verbinding met lokale DB – In Studio voor SQL-beheer, De optie "Servernaam" zou kunnen zijn

“”

"lokale host"

"127.0.0.1"

“Machine\Instance”

TCP / IP

Bedenk nu eens dat Tom 's avonds in feeststemming is. Hij wil een koffie bestellen bij een bekende Coffee Shop. De koffieshop ligt op 10 km afstand van zijn huis.

Hier bevinden Tom en Starbuck zich op verschillende fysieke locaties. Tom thuis en Starbucks op de drukke markt. Ze communiceren via een mobiel netwerk. Op dezelfde manier biedt MS SQL SERVER de mogelijkheid om te communiceren via TCP / IP-protocol, waarbij CLIENT en MS SQL Server op afstand van elkaar zijn en op een aparte machine zijn geïnstalleerd.

Analogie: Laten we entiteiten in de bovenstaande twee scenario's in kaart brengen. We kunnen Tom eenvoudig toewijzen aan de client, Starbuck aan de SQL-server, de thuis-/marktplaats aan de externe locatie en ten slotte het mobiele netwerk aan het TCP/IP-protocol.

Opmerkingen van de balie van Configuratie/installatie:

• In SQL Management Studio – Voor verbinding via TCP\IP moet de optie “Servernaam” “Machine\Instance van de server” zijn.
• SQL-server gebruikt poort 1433 in TCP/IP.

Benoemde pijpen

Nu wilde Tom eindelijk 's avonds een lichtgroene thee drinken, die haar buurvrouw, Sierra, heel goed bereidde.

Here Tom en zijn Buur, Sierra, zitten in hetzelfde Fysiek locatie, elkaars buur zijn. Ze communiceren via Intra-netwerk. Evenzo MS SQL-SERVER biedt de mogelijkheid om te communiceren via de Genoemd Pijp protocol. Hier de KLANT en MS SQL-SERVER zijn in verbinding via LAN.

Analogie: Laten we entiteiten in de bovenstaande twee scenario's in kaart brengen. We kunnen Tom eenvoudig toewijzen aan Client, Sierra aan SQL-server, Neighbor aan LAN en tenslotte Intra-netwerk aan Named Pipe Protocol.

Opmerkingen van de balie van Configuratie/installatie:

• Voor aansluiting via benoemde leiding. Deze optie is standaard uitgeschakeld en moet worden ingeschakeld door SQL Configuration Manager.

Wat is TDS?

Nu we weten dat er drie soorten Client-Server zijn Architecture, laten we een blik werpen op TDS:

• TDS staat voor Tabular Data Stream.
• Alle drie de protocollen gebruiken TDS-pakketten. TDS is ingekapseld in netwerkpakketten. Dit maakt gegevensoverdracht mogelijk van de clientmachine naar de servermachine.
• TDS werd voor het eerst ontwikkeld door Sybase en is nu eigendom van Microsoft

Relationele motor

De Relationele Engine wordt ook wel de Queryprocessor genoemd. Het heeft de SQL Server componenten die bepalen wat een query precies moet doen en hoe dit het beste kan worden gedaan. Het is verantwoordelijk voor de uitvoering van gebruikersquery's door gegevens op te vragen bij de opslagengine en de geretourneerde resultaten te verwerken.

Zoals afgebeeld in de Architectural Diagram er zijn 3 belangrijke componenten van de relationele motor. Laten we de componenten in detail bestuderen:

CMD-parser

Gegevens die eenmaal zijn ontvangen van Protocol Layer, worden vervolgens doorgegeven aan de Relational Engine. “CMD-parser” is het eerste onderdeel van Relational Engine dat de querygegevens ontvangt. De belangrijkste taak van CMD Parser is het controleren van de query Syntactische en semantische fouten. Eindelijk, het genereert een queryboom. Laten we het in detail bespreken.

Syntactische controle:

• Net als elke andere programmeertaal heeft MS SQL ook de vooraf gedefinieerde set trefwoorden. Bovendien heeft SQL Server zijn eigen grammatica die SQL Server begrijpt.
• SELECT, INSERT, UPDATE en vele andere behoren tot de voorgedefinieerde trefwoordenlijsten van MS SQL.
• CMD Parser voert syntactische controle uit. Als de invoer van gebruikers deze taalsyntaxis of grammaticaregels niet volgt, geeft een fout terug.

Voorbeeld: Laten we zeggen dat een Rus naar een Japans restaurant ging. Hij bestelt fastfood in de Russische taal. Helaas verstaat de ober alleen Japans. Wat zou het meest voor de hand liggende resultaat zijn?

Het antwoord is: de ober kan de bestelling niet verder verwerken.

Er mag geen enkele afwijking zijn in de grammatica of taal die de SQL-server accepteert. Als dit het geval is, kan de SQL-server dit niet verwerken en zal er daarom een ​​foutmelding worden geretourneerd.

We zullen meer leren over MS SQL-query's in de komende tutorials. Maar overweeg hieronder de meest elementaire querysyntaxis als

SELECT * from <TABLE_NAME>;

Om nu een idee te krijgen van wat syntactisch doet, zegt u of de gebruiker de basisquery uitvoert, zoals hieronder:

SELECR * from <TABLE_NAME>

Houd er rekening mee dat de gebruiker in plaats van 'SELECT' 'SELECR' heeft getypt.

Resultaat: DE CMD-parser parseert deze verklaring en genereert de foutmelding. Omdat “SELECR” niet de vooraf gedefinieerde trefwoordnaam en grammatica volgt. Hier verwachtte CMD Parser “SELECT.”

Semantische controle:

• Dit wordt uitgevoerd door Normalizer.
• In de eenvoudigste vorm controleert het of de kolomnaam en de tabelnaam die wordt opgevraagd, bestaan ​​in het schema. En als het bestaat, bind het dan aan Query. Dit staat ook bekend als Bindend.
• Complexiteit neemt toe wanneer gebruikersquery's VIEW bevatten. Normalizer voert de vervanging uit met de intern opgeslagen viewdefinitie en nog veel meer.

Laten we dit begrijpen met behulp van het onderstaande voorbeeld:

SELECT * from USER_ID

Resultaat: DE CMD-parser parseert deze instructie voor semantische controle. De parser genereert een foutmelding omdat Normalizer de gevraagde tabel (USER_ID) niet zal vinden omdat deze niet bestaat.

Zoekopdrachtboom maken:

• Deze stap genereert een andere uitvoeringsboom waarin de query kan worden uitgevoerd.
• Merk op dat alle verschillende bomen dezelfde gewenste output hebben.

Optimizer

Het werk van de optimizer is het maken van een uitvoeringsplan voor de zoekopdracht van de gebruiker. Dit is het plan dat bepaalt hoe de gebruikersquery wordt uitgevoerd.

Houd er rekening mee dat niet alle zoekopdrachten zijn geoptimaliseerd. Er wordt geoptimaliseerd voor DML-opdrachten (Data Modification Language), zoals SELECT, INSERT, DELETE en UPDATE. Dergelijke zoekopdrachten worden eerst gemarkeerd en vervolgens naar de optimalisatie gestuurd. DDL-opdrachten zoals CREATE en ALTER zijn niet geoptimaliseerd, maar in plaats daarvan in een interne vorm gecompileerd. De querykosten worden berekend op basis van factoren zoals CPU-gebruik, geheugengebruik en invoer-/uitvoerbehoeften.

De rol van Optimizer is het vinden van de goedkoopste, niet het beste, kosteneffectieve uitvoeringsplan.

Voordat we dieper ingaan op de technische details van Optimizer, bekijken we het onderstaande praktijkvoorbeeld:

Voorbeeld:

Stel dat u een online bankrekening wilt openen. U kent al een bank waarbij het maximaal 2 dagen duurt om een ​​rekening te openen. Maar je hebt ook een lijst met 20 andere banken, wat al dan niet minder dan 2 dagen kan duren. U kunt met deze banken in gesprek gaan om te bepalen welke banken er minder dan 2 dagen over doen. Nu kan het zijn dat u geen bank vindt, wat minder dan twee dagen duurt, en er gaat extra tijd verloren door de zoekactiviteit zelf. Beter was het geweest om bij de eerste bank zelf een rekening te openen.

Conclusie: Het is belangrijker om verstandig te selecteren. Om precies te zijn, kies welke optie is de beste, niet de goedkoopste.

Zo ook MS SQL Optimizer werkt op ingebouwde exhaustieve/heuristische algoritmen. Het doel is om de query runtime te minimaliseren. Alle Optimizer-algoritmen zijn fatsoen van Microsoft en een geheim. Hoewel, Hieronder staan ​​de stappen op hoog niveau die worden uitgevoerd door MS SQL Optimizer. Zoekopdrachten naar optimalisatie volgen drie fasen, zoals weergegeven in het onderstaande diagram:

Fase 0: Zoeken naar triviaal plan:

• Dit staat ook bekend als Pre-optimalisatiefase.
• Voor sommige gevallen kan er maar één praktisch, werkbaar plan zijn, bekend als een triviaal plan. Er is geen noodzaak om een ​​geoptimaliseerd plan te maken. De reden hiervoor is dat meer zoeken zou resulteren in het vinden van hetzelfde runtime-uitvoeringsplan. En dat ook nog eens met de extra kosten van het zoeken naar een geoptimaliseerd plan, wat helemaal niet nodig was.
• Als er geen triviaal plan wordt gevonden, dan 1^st Fase begint.

Fase 1: Zoeken naar transactieverwerkingsplannen

• Dit omvat het zoeken naar Eenvoudig en complex plan.
• Eenvoudig zoeken naar plan: gegevens uit het verleden van de kolom en de index die betrokken zijn bij de query, worden gebruikt voor statistische analyse. Dit bestaat meestal uit, maar is niet beperkt tot, één Index Per tafel.
• Als het simpele plan niet gevonden wordt, wordt er gezocht naar een complexer plan. Het gaat om Multiple Index per table.

Fase 2: Parallelle verwerking en optimalisatie.

• Als geen van de bovenstaande strategieën werkt, zoekt Optimizer naar mogelijkheden voor parallelle verwerking. Dit is afhankelijk van de verwerkingsmogelijkheden en configuratie van de Machine.
• Als dat nog steeds niet mogelijk is, begint de laatste optimalisatiefase. Het uiteindelijke optimalisatiedoel is nu het vinden van alle andere mogelijke opties om de zoekopdracht op de beste manier uit te voeren. Laatste optimalisatiefase Algorithms zijn Microsoft Fatsoen.

Query-uitvoerder

Query-uitvoerder belt Toegangsmethode. Het biedt een uitvoeringsplan voor de logica voor het ophalen van gegevens die nodig is voor de uitvoering. Zodra gegevens zijn ontvangen van Storage Engine, wordt het resultaat gepubliceerd naar de protocollaag. Tenslotte worden de gegevens naar de eindgebruiker gestuurd.

Opslag-engine

Het werk van de Storage Engine is het opslaan van gegevens in een opslagsysteem zoals Disk of SAN en het ophalen van de gegevens wanneer dat nodig is. Voordat we dieper ingaan op de opslagengine, gaan we eerst kijken hoe gegevens worden opgeslagen Database en soort bestanden beschikbaar.

Gegevensbestand en omvang:

Gegevensbestand slaat gegevens fysiek op in de vorm van gegevenspagina's, waarbij elke gegevenspagina een grootte heeft van 8 KB en de kleinste opslageenheid in SQL Server vormt. Deze gegevenspagina's zijn logisch gegroepeerd om gebieden te vormen. Aan geen enkel object is een pagina toegewezen in SQL Server.

Het onderhoud van het object gebeurt via uitbreidingen. De pagina heeft een sectie genaamd de Paginakop met een grootte van 96 bytes, waarin de metadata-informatie over de pagina wordt weergegeven, zoals het paginatype, het paginanummer, de grootte van de gebruikte ruimte, de grootte van de vrije ruimte en de aanwijzer naar de volgende pagina en de vorige pagina. , enz.

Bestandstypen

1. Primair bestand

• Elke database bevat één primair bestand.
• Hierin worden alle belangrijke gegevens opgeslagen met betrekking tot tabellen, weergaven, triggers, enz.
• Extensie is .mdf meestal, maar kan elke extensie hebben.

2. Secundair bestand

• De database kan al dan niet meerdere secundaire bestanden bevatten.
• Dit is optioneel en bevat gebruikersspecifieke gegevens.
• Extensie is .naf meestal, maar kan elke extensie hebben.

3. Logbestand

• Ook bekend als Write ahead-logboeken.
• Extensie is .ldf
• Gebruikt voor transactiebeheer.
• Dit wordt gebruikt om te herstellen van ongewenste gevallen. Voer een belangrijke taak uit: terugdraaien naar niet-vastgelegde transacties.

Storage Engine bestaat uit 3 componenten; laten we ze in detail bekijken.

Toegangsmethode

Het fungeert als een interface tussen de query-uitvoerder en Buffer Manager-/transactielogboeken.

Access Method zelf voert geen enkele uitvoering uit.

De eerste actie is om te bepalen of de query:

1. Selecteer Verklaring (DDL)
2. Non-Select-verklaring (DDL & DML)

Afhankelijk van het resultaat doorloopt de toegangsmethode de volgende stappen:

1. Als de vraag is DDL, SELECT-instructie, wordt de query doorgegeven aan de Buffer Manager voor verdere verwerking.
2. En als je vraagt ​​of DDL, NON-SELECT-instructie, wordt de query doorgegeven aan Transaction Manager. Dit omvat meestal de UPDATE-instructie.

Buffer Manager

Buffer manager beheert de kernfuncties voor onderstaande modules:

• Plancache
• Gegevens parseren: Buffer cache en gegevensopslag
• Vuile pagina

We zullen Plan leren, Buffer en Gegevenscache in deze sectie. We behandelen vuile pagina's in de sectie Transactie.

Plancache

• Bestaand queryplan: De buffermanager controleert of het uitvoeringsplan zich in de opgeslagen Plan Cache bevindt. Als dat zo is, worden de query plan cache en de bijbehorende datacache gebruikt.
• Eerste keer cacheplan: Waar komt de bestaande Plan-cache vandaan? Als het eerste query-uitvoeringsplan wordt uitgevoerd en complex is, is het zinvol om het op te slaan in de Plane-cache. Dit zorgt voor snellere beschikbaarheid wanneer SQL Server de volgende keer dezelfde query ontvangt. Het is dus niets anders dan de query zelf waarvan de Plan-uitvoering wordt opgeslagen als deze voor de eerste keer wordt uitgevoerd.

Gegevens parseren: Buffer cache en gegevensopslag

Buffer beheerder toegang geeft tot de benodigde gegevens. Hieronder zijn twee benaderingen mogelijk, afhankelijk van het feit of er gegevens in de datacache aanwezig zijn of niet:

Buffer Cache – Zacht parseren:

Buffer Manager zoekt naar gegevens in Buffer in Data cache. Indien aanwezig, dan wordt deze Data gebruikt door Query Executor. Dit verbetert de prestaties omdat het aantal I/O-bewerkingen wordt verminderd bij het ophalen van data uit de cache in vergelijking met het ophalen van data uit Data storage.

Gegevensopslag – harde parsering:

Als er geen gegevens aanwezig zijn in Buffer Manager dan vereist Er wordt gezocht naar gegevens in Gegevensopslag. Het slaat ook gegevens op in de datacache voor toekomstig gebruik.

Vuile pagina

Het wordt opgeslagen als verwerkingslogica van Transaction Manager. We zullen dit in detail leren in het gedeelte Transactiebeheer.

Transactiemanager

Transaction Manager wordt aangeroepen wanneer de toegangsmethode bepaalt dat Query een Non-Select-instructie is.

Logboekbeheer

• Log Manager houdt alle updates in het systeem bij via logboeken in Transactielogboeken.
• Logboeken hebben Registreert volgnummer met de transactie-ID en het gegevenswijzigingsrecord.
• Dit wordt gebruikt voor het bijhouden van Transactie vastgelegd en Transactie teruggedraaid.

Slotbeheerder

• Tijdens de transactie hebben de bijbehorende gegevens in de gegevensopslag de status Vergrendeld. Dit proces wordt afgehandeld door Lock Manager.
• Dit proces zorgt ervoor consistentie en isolatie van gegevens. Ook bekend als ACID-eigenschappen.

Uitvoeringsproces

• Log Manager start het loggen en Lock Manager vergrendelt de bijbehorende gegevens.
• De kopie van de gegevens wordt bewaard in de Buffer cache.
• Een kopie van de gegevens die bijgewerkt moeten worden, wordt bewaard in de logbuffer. Alle gebeurtenisupdates worden uitgevoerd in de databuffer.
• Pagina's waarop de gegevens worden opgeslagen, worden ook wel Vuile pagina's.
• Controlepunt en vooruitschrijvende registratie: Dit proces wordt uitgevoerd en markeert de hele pagina, van vuile pagina's tot schijf, maar de pagina blijft in de cache. De frequentie is ongeveer 1 run per minuut. Maar de pagina wordt eerst naar de gegevenspagina van het logbestand gepusht Buffer loggen. Dit staat bekend als Schrijf vooruit loggen.
• Luie schrijver: De vuile pagina kan in het geheugen blijven. Wanneer de SQL-server een enorme belasting waarneemt en Buffer geheugen nodig is voor een nieuwe transactie, het bevrijdt Dirty Pages uit de cache. Het werkt op LRU – Het minst recent gebruikte algoritme voor het opschonen van een pagina van de bufferpool naar de schijf.

Samenvatting

• Drie soorten clientservers Archier bestaan: 1) Gedeeld geheugen 2) TCP/IP 3) Named Pipes
• TDS, ontwikkeld door Sybase en nu eigendom van Microsoft, is een pakket dat is ingekapseld in netwerkpakketten voor gegevensoverdracht van de clientmachine naar de servermachine.
• Relational Engine bevat drie belangrijke componenten:CMD-parser: Dit is verantwoordelijk voor syntactische en semantische fouten en genereert uiteindelijk een queryboom.Optimizer: De rol van de optimizer is het vinden van het goedkoopste, niet het beste, kosteneffectieve uitvoeringsplan.

  Query-uitvoerder: De query-uitvoerder roept de Access Method aan en biedt een uitvoeringsplan voor de logica voor het ophalen van gegevens die nodig is voor de uitvoering.

• Er zijn drie typen bestanden: primair bestand, secundair bestand en logbestanden.
• Storage Engine: Heeft de volgende belangrijke componentenToegangsmethode: Deze component bepaalt of de query een Select- of Non-Select-instructie is. Roept Buffer en Transfer Manager dienovereenkomstig.Buffer Manager: Buffer manager beheert kernfuncties voor Plan Cache, Data Parsing & Dirty Page.

  Transactiebeheerder: Het beheert niet-selectietransacties met behulp van Log- en Lock Managers. Vergemakkelijkt ook de belangrijke implementatie van Write Ahead-logboekregistratie en Lazy writers.