SQL Server-architectuur uitgelegd: Named Pipes, Optimizer, Buffer Manager

Inhoudsopgave:

Anonim

MS SQL Server is een client-server-architectuur. Het MS SQL Server-proces begint met het verzenden van een verzoek door de clienttoepassing. 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 hoofdcomponenten in SQL Server-architectuur:

  1. Protocollaag
  2. Relationele engine
  3. Opslagmotor
SQL Server-architectuurdiagram

Laten we alle drie bovenstaande hoofdmodules in detail bespreken. In deze tutorial leer je.

  • Protocollaag - SNI
    • Gedeelde herinnering
    • TCP / IP
    • Genoemde pijpen
    • Wat is TDS?
  • Relationele engine
    • CMD-parser
    • Optimizer
    • Query-uitvoerder
  • Opslagmotor
    • Bestand types
    • Toegangsmethode
    • Buffer Manager
    • Plan cache
    • Gegevensparsering: Buffercache en gegevensopslag
    • Transactiebeheerder

Protocollaag - SNI

MS SQL SERVER PROTOCOL LAYER ondersteunt 3 soorten Client Server Architectuur. We beginnen met " Three Type of Client Server Architecture" die MS SQL Server ondersteunt.

Gedeelde herinnering

Laten we een gespreksscenario in de vroege ochtend opnieuw bekijken.

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 - Hier biedt MS SQL- server GEDEELD GEHEUGENPROTOCOL . Hier draaien CLIENT en MS SQL- server op dezelfde machine. Beide kunnen communiceren via Shared Memory-protocol.

Analoog: hiermee kunnen entiteiten in de bovenstaande twee scenario's in kaart worden gebracht. We kunnen Tom eenvoudig toewijzen aan Client, Mom aan SQL-server, Home to Machine en Verbal Communication to Shared Memory Protocol.

Vanaf het bureau van configuratie en installatie:

Voor verbinding met lokale database - In SQL Management Studio kan de optie "Servernaam" zijn

​

"localhost"

"127.0.0.1"

"Machine \ Instance"

TCP / IP

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

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

Analoog: hiermee kunnen entiteiten in de bovenstaande twee scenario's in kaart worden gebracht. We kunnen Tom eenvoudig toewijzen aan de client, Starbuck aan de SQL-server, de thuis- / marktplaats naar de externe locatie en tenslotte het mobiele netwerk naar het TCP / IP-protocol.

Notities van de desk 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.

Genoemde pijpen

Nu 's avonds eindelijk, wilde Tom een ​​lichtgroene thee hebben die haar buurvrouw Sierra heel goed klaarmaakte.

Hier bevinden Tom en zijn buurman , Sierra, zich op dezelfde fysieke locatie, omdat ze elkaars buurman zijn. Ze communiceren via een intra-netwerk. Evenzo biedt MS SQL SERVER de mogelijkheid om te communiceren via het Named Pipe- protocol. Hier zijn de CLIENT en MS SQL SERVER in verbinding via LAN .

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

Notities van de desk van Configuratie / installatie:

  • Voor aansluiting via Named Pipe. 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-architectuur zijn, kunnen we een blik werpen op TDS:

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

Relationele engine

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 deze het beste kan worden gedaan. Het is verantwoordelijk voor het uitvoeren van gebruikersquery's door gegevens op te vragen bij de opslagengine en de resultaten die worden geretourneerd te verwerken.

Zoals weergegeven in het architecturale diagram zijn er 3 hoofdcomponenten van de relationele engine. Laten we de componenten in detail bestuderen:

CMD-parser

Gegevens die eenmaal zijn ontvangen van Protocol Layer, worden vervolgens doorgegeven aan Relational Engine. "CMD Parser" is het eerste onderdeel van Relational Engine dat de Query-gegevens ontvangt. De belangrijkste taak van CMD Parser is om de query op syntactische en semantische fouten te controleren. Ten slotte genereert het een queryboom . Laten we in detail bespreken.

Syntactische controle:

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

Voorbeeld: stel 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 afwijking zijn in grammatica of taal die door de SQL-server wordt geaccepteerd. Als dat het geval is, kan de SQL-server het niet verwerken en zal daarom een ​​foutmelding worden geretourneerd.

We zullen meer leren over MS SQL-query's in komende tutorials. Beschouw echter de onderstaande meest elementaire querysyntaxis als 

SELECT * from ;

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

SELECR * from

Merk op dat in plaats van 'SELECT' de gebruiker "SELECR" heeft getypt.

Resultaat: DE CMD Parser zal deze instructie ontleden en de foutmelding genereren. 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 kolomnaam, tabelnaam die wordt opgevraagd, in Schema voorkomen. En als het bestaat, bind het dan aan Query. Dit wordt ook wel bindend genoemd .
  • De complexiteit neemt toe wanneer gebruikersquery's VIEW bevatten. Normalizer voert de vervanging uit met de intern opgeslagen weergavedefinitie en nog veel meer.

Laten we dit begrijpen met behulp van het onderstaande voorbeeld -

SELECT * from USER_ID

Resultaat: DE CMD Parser zal deze verklaring ontleden voor semantische controle. De parser genereert een foutmelding omdat Normalizer de gevraagde tabel (USER_ID) niet kan vinden omdat deze niet bestaat.

Querystructuur 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 zal bepalen hoe de gebruikersvraag zal worden 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 vragen worden eerst gemarkeerd en vervolgens naar de optimizer gestuurd. DDL-opdrachten zoals CREATE en ALTER zijn niet geoptimaliseerd, maar worden 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 om het goedkoopste, niet het beste, kosteneffectieve uitvoeringsplan te vinden.

Bekijk, voordat we ingaan op meer technische details van Optimizer, het onderstaande voorbeeld uit de praktijk:

Voorbeeld:

Stel dat u een online bankrekening wilt openen. U kent al één bank die maximaal 2 dagen nodig heeft om een ​​rekening te openen. Maar u heeft ook een lijst met 20 andere banken, die al dan niet minder dan 2 dagen duren. U kunt met deze banken gaan samenwerken om te bepalen welke banken er minder dan 2 dagen over doen. Nu vindt u misschien geen bank die minder dan 2 dagen in beslag neemt en gaat er extra tijd verloren door de zoekactiviteit zelf. Het was beter geweest om zelf een rekening te openen bij de eerste bank.

Conclusie: het is belangrijker om verstandig te selecteren. Kies om precies te zijn welke optie het beste is, niet de goedkoopste.

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

Fase 0: zoeken naar Trivial Plan:

  • Dit wordt ook wel de pre-optimalisatiefase genoemd .
  • In sommige gevallen kan er maar één praktisch, werkbaar plan zijn, ook wel een triviaal plan genoemd. Het is niet nodig om een ​​geoptimaliseerd plan te maken. De reden is dat meer zoeken zou resulteren in het vinden van hetzelfde uitvoeringsplan voor de uitvoeringstijd. Dat ook met de extra kosten van Zoeken naar een geoptimaliseerd plan, dat helemaal niet nodig was.
  • Als er geen Trivial plan is gevonden, start de 1 e fase.

Fase 1: zoeken naar verwerkingsplannen voor transacties

  • Dit omvat het zoeken naar eenvoudig en complex plan .
  • Simple Plan Search: Past Data van de kolom en Index die bij Query zijn betrokken, zullen worden gebruikt voor statistische analyse. Deze bestaat meestal uit, maar is niet beperkt tot, één Index Per-tabel.
  • Maar als het eenvoudige plan niet wordt gevonden, wordt het complexere plan doorzocht. Het gaat om meerdere indexen per tafel.

Fase 2: parallelle verwerking en optimalisatie.

  • Als geen van de bovenstaande strategieën werkt, zoekt Optimizer naar mogelijkheden voor parallelle verwerking. Dit hangt af van de verwerkingsmogelijkheden en configuratie van de Machine.
  • Lukt dat nog steeds niet, dan start de laatste optimalisatiefase. Nu is het uiteindelijke doel van optimalisatie het vinden van alle andere mogelijke opties om de query op de beste manier uit te voeren. Laatste optimalisatiefase Algoritmen zijn Microsoft Propriety.

Query-uitvoerder

Query-uitvoerder roept toegangsmethode aan. 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. Ten slotte worden de gegevens naar de eindgebruiker gestuurd.

Opslagmotor

Het werk van de Storage Engine is om gegevens op te slaan in een opslagsysteem zoals Disk of SAN en de gegevens op te halen wanneer dat nodig is. Voordat we dieper ingaan op de Storage-engine, laten we eens kijken hoe gegevens worden opgeslagen in Database en welke soorten bestanden beschikbaar zijn.

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 extensies te vormen. Aan geen enkel object is een pagina toegewezen in SQL Server.

Het onderhoud van het object gebeurt via extents. De pagina heeft een sectie genaamd de paginakoptekst met een grootte van 96 bytes, met de metadata-informatie over de pagina, 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.

Bestand types

  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, maar kan elke extensie hebben.
  1. Secundair bestand
  • Database kan al dan niet meerdere secundaire bestanden bevatten.
  • Dit is optioneel en bevat gebruikersspecifieke gegevens.
  • Extensie is. ndf meestal maar kan elke extensie hebben.
  1. Logbestand
  • Ook bekend als vooruitschrijflogboeken.
  • Extensie is. ldf
  • Gebruikt voor transactiebeheer.
  • Dit wordt gebruikt om te herstellen van ongewenste gevallen. Voer een belangrijke taak uit van Rollback naar niet-vastgelegde transacties.

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

Toegangsmethode

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

Access Method zelf voert geen enkele uitvoering uit.

De eerste actie is om te bepalen of de vraag is:

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

Afhankelijk van het resultaat, voert de toegangsmethode de volgende stappen uit:

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

Buffer Manager

Buffer manager beheert kernfuncties voor onderstaande modules:

  • Plan cache
  • Gegevensanalyse: Buffercache en gegevensopslag
  • Vuile pagina

In deze sectie zullen we plannen, buffer en gegevenscache leren. We behandelen vuile pagina's in het gedeelte Transactie.

Plan cache

  • Bestaand queryplan: De buffermanager controleert of het uitvoeringsplan aanwezig is in de opgeslagen plancache. Zo ja, dan wordt de queryplan-cache en de bijbehorende datacache gebruikt.
  • Eerste cacheplan: waar komt de bestaande cachegeheugen van het plan vandaan?

    Als het plan voor de eerste uitvoering van een query wordt uitgevoerd en complex is, is het zinvol om het op te slaan in de Plane-cache. Dit zorgt voor een snellere beschikbaarheid wanneer de SQL-server de volgende keer dezelfde query ontvangt. Het is dus niets anders dan de vraag zelf welke planuitvoering wordt opgeslagen als deze voor de eerste keer wordt uitgevoerd.

Gegevensparsering: Buffercache en gegevensopslag

Buffer manager biedt toegang tot de benodigde gegevens. Hieronder zijn twee benaderingen mogelijk, afhankelijk van of er gegevens in de gegevenscache aanwezig zijn of niet:

Buffercache - Soft parsing:

Buffer Manager zoekt naar gegevens in buffer in gegevenscache. Indien aanwezig, worden deze gegevens gebruikt door Query Executor. Dit verbetert de prestaties aangezien het aantal I / O-bewerkingen wordt verminderd bij het ophalen van gegevens uit de cache in vergelijking met het ophalen van gegevens uit gegevensopslag.

Gegevensopslag - Hard parseren:

Als er geen gegevens in Buffer Manager aanwezig zijn dan vereist Gegevens worden doorzocht in Gegevensopslag. Het slaat ook gegevens op in de datacache voor toekomstig gebruik.

Vuile pagina

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

Transactiebeheerder

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

Log Manager

  • Log Manager houdt alle updates bij die in het systeem zijn gedaan via logboeken in transactielogboeken.
  • Logboeken hebben het volgnummer van het logboek met de transactie-ID en het gegevenswijzigingsrecord .
  • Dit wordt gebruikt om Transaction Committed en Transaction Rollback bij te houden .

Vergrendelen Manager

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

Uitvoeringsproces

  • Log Manager start met loggen en Lock Manager vergrendelt de bijbehorende gegevens.
  • De kopie van de gegevens wordt bewaard in de Buffercache.
  • Een kopie van de gegevens die moeten worden bijgewerkt, wordt bijgehouden in de logbuffer en alle gebeurtenissen werken de gegevens bij in de gegevensbuffer.
  • Pagina's waarin de gegevens worden opgeslagen, worden ook wel Dirty Pages genoemd .
  • Checkpoint en wegschrijven loggen: dit proces wordt uitgevoerd en markeert alle pagina's van vuile pagina's naar schijf, maar de pagina blijft in de cache. De frequentie is ongeveer 1 run per minuut. Maar de pagina wordt eerst doorgestuurd naar de datapagina van het logbestand vanuit het bufferlogboek. Dit staat bekend als Write Ahead Logging.
  • Lazy Writer: De vuile pagina kan in het geheugen blijven. Wanneer SQL-server een enorme belasting waarneemt en Buffer-geheugen nodig is voor een nieuwe transactie, maakt het Dirty Pages vrij uit de cache. Het werkt op LRU - Minst recentelijk gebruikt algoritme voor het opschonen van pagina van bufferpool naar schijf.

Overzicht:

  • Er zijn drie soorten Client Server Architectuur: 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 clientcomputer naar de servermachine.
  • Relational Engine bevat drie hoofdcomponenten:

    CMD-parser: dit is verantwoordelijk voor syntactische en semantische fouten en genereert uiteindelijk een queryboom.

    Optimizer: de rol van Optimizer is om het goedkoopste, niet het beste, kosteneffectieve uitvoeringsplan te vinden.

    Query-uitvoerder: Query-uitvoerder roept toegangsmethode aan en biedt een uitvoeringsplan voor logica voor het ophalen van gegevens die nodig is voor uitvoering.

  • Er zijn drie soorten bestanden: Primair bestand, Secundair bestand en Logboekbestanden.
  • Storage Engine: heeft de volgende belangrijke componenten

    Toegangsmethode: dit onderdeel Bepaalt of de query een Select-instructie of een Non-Select-instructie is. Roept dienovereenkomstig Buffer en Transfer Manager aan.

    Buffer Manager: Buffer Manager beheert kernfuncties voor Plan Cache, Data Parsing & Dirty Page.

    Transaction Manager: It manager Non-Select Transaction met behulp van Log en Lock Managers. Vergemakkelijkt ook de belangrijke implementatie van Write Ahead-logboekregistratie en Lazy writers.