Spiegazione dell'architettura di SQL Server: Named Pipes, Optimizer, Buffer Manager

Sommario:

Anonim

MS SQL Server è un'architettura client-server. Il processo di MS SQL Server inizia con l'applicazione client che invia una richiesta. SQL Server accetta, elabora e risponde alla richiesta con dati elaborati. Discutiamo in dettaglio l'intera architettura mostrata di seguito:

Come illustrato nel diagramma seguente, ci sono tre componenti principali nell'architettura di SQL Server:

  1. Livello di protocollo
  2. Motore relazionale
  3. Storage Engine
Diagramma dell'architettura di SQL Server

Discutiamo in dettaglio di tutti e tre i moduli principali sopra. In questo tutorial imparerai.

  • Livello protocollo - SNI
    • Memoria condivisa
    • TCP / IP
    • Named Pipes
    • Cos'è TDS?
  • Motore relazionale
    • Parser CMD
    • Optimizer
    • Query Executor
  • Storage Engine
    • Tipi di file
    • Metodo di accesso
    • Buffer Manager
    • Plan Cache
    • Analisi dei dati: cache buffer e archiviazione dei dati
    • Responsabile delle transazioni

Livello protocollo - SNI

LIVELLO DI PROTOCOLLO MS SQL SERVER supporta 3 tipi di architettura client server. Inizieremo con " Tre tipi di architettura client server" supportati da MS SQL Server.

Memoria condivisa

Riconsideriamo uno scenario di conversazione mattutino.

MAMMA e TOM - Qui Tom e sua mamma, erano nello stesso posto logico, cioè a casa loro. Tom ha potuto chiedere il caffè e la mamma ha potuto servirlo caldo.

SERVER MS SQL - Qui il server MS SQL fornisce il PROTOCOLLO DI MEMORIA CONDIVISA . Qui CLIENT e MS SQL server vengono eseguiti sulla stessa macchina. Entrambi possono comunicare tramite protocollo di memoria condivisa.

Analogia: consente di mappare le entità nei due scenari precedenti. Possiamo facilmente mappare Tom al client, mamma al server SQL, casa alla macchina e comunicazione verbale al protocollo di memoria condivisa.

Dalla scrivania di configurazione e installazione:

Per la connessione al database locale: in SQL Management Studio, potrebbe essere l'opzione "Nome server"

"."

"localhost"

"127.0.0.1"

"Macchina \ Istanza"

TCP / IP

Ora considera la sera, Tom è in vena di festa. Vuole un caffè ordinato da un noto Coffee Shop. La caffetteria si trova a 10 km da casa sua.

Qui Tom e Starbuck si trovano in una posizione fisica diversa. Tom a casa e Starbucks nell'affollato mercato. Comunicano tramite rete cellulare. Allo stesso modo, MS SQL SERVER fornisce la capacità di interagire tramite protocollo TCP / IP, dove CLIENT e MS SQL Server sono remoti tra loro e installati su una macchina separata.

Analogia: consente di mappare le entità nei due scenari precedenti. Possiamo facilmente mappare Tom al client, Starbuck al server SQL, la casa / mercato alla posizione remota e infine la rete cellulare al protocollo TCP / IP.

Note dal desk di Configurazione / installazione:

  • In SQL Management Studio: per la connessione tramite TCP \ IP, l'opzione "Nome server" deve essere "Macchina \ Istanza del server".
  • Il server SQL utilizza la porta 1433 in TCP / IP.

Named Pipes

Adesso finalmente, la sera, Tom voleva prendere un tè verde chiaro che la sua vicina, Sierra, preparava molto bene.

Qui Tom e il suo vicino , Sierra, si trovano nella stessa posizione fisica, essendo vicini l'uno dell'altro. Stanno comunicando tramite la rete Intra. Allo stesso modo, MS SQL SERVER fornisce la capacità di interagire tramite il protocollo Named Pipe . Qui il CLIENT e MS SQL SERVER sono in connessione tramite LAN .

Analogia: consente di mappare le entità nei due scenari precedenti. Possiamo facilmente mappare Tom su Client, Sierra su SQL server, Neighbor su LAN e infine rete Intra su Named Pipe Protocol.

Note dal desk di Configurazione / installazione:

  • Per collegamento tramite tubo denominato. Questa opzione è disabilitata per impostazione predefinita e deve essere abilitata da SQL Configuration Manager.

Cos'è TDS?

Ora che sappiamo che esistono tre tipi di architettura client-server, diamo un'occhiata a TDS:

  • TDS è l'acronimo di Tabular Data Stream.
  • Tutti e 3 i protocolli utilizzano pacchetti TDS. TDS è incapsulato in pacchetti di rete. Ciò consente il trasferimento dei dati dalla macchina client alla macchina server.
  • TDS è stato sviluppato per la prima volta da Sybase ed è ora di proprietà di Microsoft

Motore relazionale

Il motore relazionale è anche noto come Query Processor. Ha i componenti di SQL Server che determinano cosa deve fare esattamente una query e come può essere eseguita al meglio. È responsabile dell'esecuzione delle query dell'utente richiedendo i dati dal motore di archiviazione ed elaborando i risultati restituiti.

Come illustrato nel diagramma architettonico, ci sono 3 componenti principali del motore relazionale. Studiamo in dettaglio i componenti:

Parser CMD

I dati una volta ricevuti da Protocol Layer vengono quindi passati a Relational Engine. "CMD Parser" è il primo componente di Relational Engine a ricevere i dati Query. Il compito principale di CMD Parser è controllare la query per errori sintattici e semantici. Infine, genera un albero delle query . Parliamo in dettaglio.

Controllo sintattico:

  • Come ogni altro linguaggio di programmazione, anche MS SQL dispone del set predefinito di parole chiave. Inoltre, SQL Server ha una propria grammatica che SQL Server comprende.
  • SELECT, INSERT, UPDATE e molti altri appartengono agli elenchi di parole chiave predefinite di MS SQL.
  • CMD Parser esegue il controllo sintattico. Se l'input degli utenti non segue la sintassi della lingua o le regole grammaticali, restituisce un errore.

Esempio: diciamo che un russo è andato in un ristorante giapponese. Ordina fast food in lingua russa. Sfortunatamente, il cameriere capisce solo il giapponese. Quale sarebbe il risultato più ovvio?

La risposta è: il cameriere non è in grado di elaborare ulteriormente l'ordine.

Non dovrebbe esserci alcuna deviazione nella grammatica o nella lingua accettata da SQL Server. In caso affermativo, il server SQL non può elaborarlo e quindi restituirà un messaggio di errore.

Impareremo di più sulla query MS SQL nei prossimi tutorial. Tuttavia, considera di seguito la sintassi delle query di base come 

SELECT * from ;

Ora, per avere la percezione di ciò che fa la sintattica, diciamo se l'utente esegue la query di base come di seguito:

SELECR * from

Si noti che invece di "SELEZIONA" l'utente ha digitato "SELECR".

Risultato: il CMD Parser analizzerà questa istruzione e lancerà il messaggio di errore. Poiché "SELECR" non segue il nome della parola chiave predefinita e la grammatica. Qui CMD Parser si aspettava "SELEZIONA".

Controllo semantico:

  • Questa operazione viene eseguita da Normalizer .
  • Nella sua forma più semplice, controlla se il nome della colonna, il nome della tabella interrogato esistono nello schema. E se esiste, collegalo a Query. Questo è anche noto come Binding .
  • La complessità aumenta quando le query degli utenti contengono VIEW. Normalizer esegue la sostituzione con la definizione della vista memorizzata internamente e molto altro ancora.

Capiamo questo con l'aiuto dell'esempio seguente:

SELECT * from USER_ID

Risultato: THE CMD Parser analizzerà questa istruzione per il controllo semantico. Il parser lancerà un messaggio di errore poiché Normalizer non troverà la tabella richiesta (USER_ID) poiché non esiste.

Crea albero delle query:

  • Questo passaggio genera un albero di esecuzione diverso in cui è possibile eseguire la query.
  • Notare che tutti i diversi alberi hanno lo stesso output desiderato.

Optimizer

Il lavoro dell'ottimizzatore consiste nel creare un piano di esecuzione per la query dell'utente. Questo è il piano che determinerà come verrà eseguita la query dell'utente.

Tieni presente che non tutte le query sono ottimizzate. L'ottimizzazione viene eseguita per i comandi DML (Data Modification Language) come SELECT, INSERT, DELETE e UPDATE. Tali query vengono prima contrassegnate e quindi inviate all'ottimizzatore. I comandi DDL come CREATE e ALTER non sono ottimizzati, ma sono invece compilati in un form interno. Il costo della query viene calcolato in base a fattori come l'utilizzo della CPU, l'utilizzo della memoria e le esigenze di input / output.

Il ruolo dell'ottimizzatore è trovare il piano di esecuzione più economico, non il migliore e conveniente.

Prima di passare ai dettagli più tecnici di Optimizer, considera l'esempio di vita reale riportato di seguito:

Esempio:

Supponiamo che tu voglia aprire un conto bancario online. Conosci già una banca che impiega al massimo 2 giorni per aprire un conto. Ma hai anche un elenco di altre 20 banche, che possono richiedere o meno meno di 2 giorni. Puoi iniziare a interagire con queste banche per determinare quali banche impiegano meno di 2 giorni. Ora potresti non trovare una banca che impiega meno di 2 giorni e c'è tempo aggiuntivo perso a causa dell'attività di ricerca stessa. Sarebbe stato meglio aprire un conto presso la prima banca stessa.

Conclusione: è più importante selezionare con saggezza. Per essere precisi, scegli quale opzione è la migliore, non la più economica.

Allo stesso modo, MS SQL Optimizer funziona su algoritmi esaustivi / euristici integrati. L'obiettivo è ridurre al minimo il tempo di esecuzione delle query. Tutti gli algoritmi di Optimizer sono proprietà di Microsoft e un segreto. Tuttavia , di seguito sono riportati i passaggi di alto livello eseguiti da MS SQL Optimizer. Le ricerche di ottimizzazione seguono tre fasi come mostrato nel diagramma seguente:

Fase 0: ricerca del piano banale:

  • Questo è anche noto come fase di pre-ottimizzazione .
  • In alcuni casi, potrebbe esserci un solo piano pratico e realizzabile, noto come piano banale. Non è necessario creare un piano ottimizzato. Il motivo è che la ricerca di più comporterebbe la ricerca dello stesso piano di esecuzione del runtime. Anche questo con il costo aggiuntivo della ricerca di un piano ottimizzato che non era affatto richiesto.
  • Se nessun piano Trivial trovato, poi 1 ° fase inizia.

Fase 1: ricerca dei piani di elaborazione delle transazioni

  • Ciò include la ricerca del piano semplice e complesso .
  • Ricerca semplice in pianta: i dati precedenti della colonna e dell'indice coinvolti nella query verranno utilizzati per l'analisi statistica. Questo di solito consiste ma non è limitato a un indice per tabella.
  • Tuttavia, se il piano semplice non viene trovato, viene cercato un piano più complesso. Coinvolge più indici per tabella.

Fase 2: elaborazione e ottimizzazione parallele.

  • Se nessuna delle strategie precedenti funziona, Optimizer cerca le possibilità di elaborazione parallela. Ciò dipende dalle capacità di elaborazione e dalla configurazione della macchina.
  • Se ciò non è ancora possibile, inizia la fase di ottimizzazione finale. Ora, l'obiettivo finale dell'ottimizzazione è trovare tutte le altre opzioni possibili per eseguire la query nel migliore dei modi. Fase di ottimizzazione finale Gli algoritmi sono di proprietà di Microsoft.

Query Executor

L'esecutore di query chiama il metodo di accesso. Fornisce un piano di esecuzione per la logica di recupero dei dati richiesta per l'esecuzione. Una volta ricevuti i dati da Storage Engine, il risultato viene pubblicato nel livello del protocollo. Infine, i dati vengono inviati all'utente finale.

Storage Engine

Il lavoro dello Storage Engine consiste nell'archiviare i dati in un sistema di archiviazione come Disk o SAN e recuperare i dati quando necessario. Prima di approfondire il motore di archiviazione, diamo un'occhiata a come i dati vengono archiviati nel database e al tipo di file disponibili.

File di dati ed estensione:

File di dati, memorizza fisicamente i dati sotto forma di pagine di dati, con ciascuna pagina di dati con una dimensione di 8 KB, formando l'unità di archiviazione più piccola in SQL Server. Queste pagine di dati sono raggruppate logicamente per formare estensioni. A nessun oggetto viene assegnata una pagina in SQL Server.

La manutenzione dell'oggetto viene eseguita tramite estensioni. La pagina ha una sezione chiamata Intestazione pagina con una dimensione di 96 byte, contenente le informazioni sui metadati sulla pagina come il tipo di pagina, il numero di pagina, la dimensione dello spazio utilizzato, la dimensione dello spazio libero e il puntatore alla pagina successiva e alla pagina precedente , eccetera.

Tipi di file

  1. File primario
  • Ogni database contiene un file primario.
  • Questo memorizza tutti i dati importanti relativi a tabelle, viste, trigger, ecc.
  • L'estensione è. mdf di solito ma può essere di qualsiasi estensione.
  1. File secondario
  • Il database può contenere o meno più file secondari.
  • Questo è facoltativo e contiene dati specifici dell'utente.
  • L'estensione è. ndf di solito ma può essere di qualsiasi estensione.
  1. File di registro
  • Noto anche come registri di scrittura in anticipo.
  • L'estensione è. ldf
  • Utilizzato per la gestione delle transazioni.
  • Viene utilizzato per eseguire il ripristino da eventuali istanze indesiderate. Eseguire un'importante attività di rollback alle transazioni non salvate.

Storage Engine ha 3 componenti; esaminiamoli in dettaglio.

Metodo di accesso

Funge da interfaccia tra l'esecutore di query e il gestore del buffer / i registri delle transazioni.

Il metodo di accesso stesso non esegue alcuna esecuzione.

La prima azione è determinare se la query è:

  1. Seleziona istruzione (DDL)
  2. Istruzione Non Select (DDL e DML)

A seconda del risultato, il metodo di accesso esegue i seguenti passaggi:

  1. Se la query è DDL , istruzione SELECT, la query viene passata a Buffer Manager per un'ulteriore elaborazione.
  2. E se si interroga se DDL, istruzione NON-SELECT , la query viene passata a Transaction Manager. Ciò include principalmente l'istruzione UPDATE.

Buffer Manager

Il gestore del buffer gestisce le funzioni principali per i moduli seguenti:

  • Plan Cache
  • Analisi dati: cache buffer e archiviazione dati
  • Pagina sporca

Impareremo il piano, il buffer e la cache dei dati in questa sezione. Tratteremo le pagine sporche nella sezione Transazioni.

Plan Cache

  • Piano di query esistente: il gestore del buffer controlla se il piano di esecuzione è presente nella cache dei piani archiviata. Se Sì, viene utilizzata la cache del piano di query e la cache dei dati associata.
  • Piano cache per la prima volta: da dove viene la cache del piano esistente?

    Se il primo piano di esecuzione della query viene eseguito ed è complesso, è opportuno memorizzarlo nella cache del piano. Ciò garantirà una disponibilità più rapida quando la prossima volta che il server SQL riceve la stessa query. Quindi, non è nient'altro che la query stessa che l'esecuzione del piano viene archiviata se viene eseguita per la prima volta.

Analisi dei dati: cache buffer e archiviazione dei dati

Il gestore del buffer fornisce l'accesso ai dati richiesti. Di seguito sono possibili due approcci a seconda che i dati esistano o meno nella cache dei dati:

Buffer Cache - Soft Parsing:

Buffer Manager cerca i dati nel buffer nella cache dei dati. Se presente, questi dati vengono utilizzati da Query Executor. Ciò migliora le prestazioni poiché il numero di operazioni di I / O viene ridotto durante il recupero dei dati dalla cache rispetto al recupero dei dati dall'archiviazione dei dati.

Archiviazione dati - Hard Parsing:

Se i dati non sono presenti in Buffer Manager, i dati richiesti vengono cercati in Data Storage. Se memorizza anche i dati nella cache dei dati per un utilizzo futuro.

Pagina sporca

Viene archiviato come logica di elaborazione di Transaction Manager. Impareremo in dettaglio nella sezione Transaction Manager.

Responsabile delle transazioni

Transaction Manager viene richiamato quando il metodo di accesso determina che Query è un'istruzione Non-Select.

Log Manager

  • Log Manager tiene traccia di tutti gli aggiornamenti effettuati nel sistema tramite i registri nei registri delle transazioni.
  • I registri hanno il numero di sequenza dei registri con l'ID transazione e il record di modifica dei dati .
  • Viene utilizzato per tenere traccia della transazione confermata e del rollback della transazione .

Lock Manager

  • Durante la transazione, i dati associati nell'archivio dati si trovano nello stato di blocco. Questo processo è gestito da Lock Manager.
  • Questo processo garantisce la coerenza e l'isolamento dei dati . Conosciute anche come proprietà ACID.

Processo di esecuzione

  • Log Manager avvia la registrazione e Lock Manager blocca i dati associati.
  • La copia dei dati viene conservata nella cache del buffer.
  • La copia dei dati che si suppone debba essere aggiornata viene mantenuta nel buffer di registro e tutti gli eventi aggiornano i dati nel buffer dei dati.
  • Le pagine che memorizzano i dati sono anche note come pagine sporche .
  • Checkpoint e registrazione in anticipo di scrittura: questo processo viene eseguito e contrassegna tutta la pagina da Pagine sporche a disco, ma la pagina rimane nella cache. La frequenza è di circa 1 esecuzione al minuto, ma la pagina viene prima inviata alla pagina Dati del file di registro dal registro del buffer. Questo è noto come Write Ahead Logging.
  • Scrittore pigro: la pagina sporca può rimanere in memoria. Quando il server SQL rileva un carico enorme ed è necessaria la memoria buffer per una nuova transazione, libera le pagine sporche dalla cache. Funziona su LRU - Algoritmo meno utilizzato di recente per la pulizia della pagina dal pool di buffer al disco.

Sommario:

  • Esistono tre tipi di architettura client server: 1) memoria condivisa 2) TCP / IP 3) pipe denominate
  • TDS, sviluppato da Sybase e ora di proprietà di Microsoft, è un pacchetto incapsulato in pacchetti di rete per il trasferimento dei dati dalla macchina client alla macchina server.
  • Il motore relazionale contiene tre componenti principali:

    Parser CMD: è responsabile dell'errore sintattico e semantico e infine genera un albero delle query.

    Ottimizzatore: il ruolo dell'ottimizzatore è trovare il piano di esecuzione più economico, non il migliore e conveniente.

    Esecutore di query: L'esecutore di query chiama il metodo di accesso e fornisce un piano di esecuzione per la logica di recupero dei dati richiesta per l'esecuzione.

  • Esistono tre tipi di file File primario, File secondario e File di registro.
  • Storage Engine: ha i seguenti componenti importanti

    Metodo di accesso: questo componente determina se la query è l'istruzione Select o Non-Select. Richiama Buffer e Transfer Manager di conseguenza.

    Buffer Manager: Buffer manager gestisce le funzioni principali per Plan Cache, Data Parsing e Dirty Page.

    Gestore transazioni: Gestisce transazioni non selezionate con l'aiuto dei gestori di log e blocchi. Inoltre, facilita l'implementazione importante della registrazione Write Ahead e dei Lazy writer.