MS SQL Server - Architettura

Abbiamo classificato l'architettura di SQL Server nelle seguenti parti per una facile comprensione:

  • Architettura generale
  • Architettura della memoria
  • Architettura dei file di dati
  • Architettura dei file di registro

Architettura generale

Client - Dove è stata avviata la richiesta.

Query - Query SQL che è un linguaggio di alto livello.

Logical Units - Parole chiave, espressioni e operatori, ecc.

N/W Packets - Codice relativo alla rete.

Protocols - In SQL Server abbiamo 4 protocolli.

  • Memoria condivisa (per connessioni locali e scopi di risoluzione dei problemi).

  • Named pipe (per le connessioni che sono in connettività LAN).

  • TCP / IP (per le connessioni che sono in connettività WAN).

  • Adattatore interfaccia virtuale VIA (richiede hardware speciale da configurare dal fornitore e anche deprecato dalla versione SQL 2012).

Server - Dove sono stati installati i servizi SQL e risiedono i database.

Relational Engine- Qui è dove verrà eseguita la vera esecuzione. Contiene Query parser, Query optimizer e Query executor.

Query Parser (Command Parser) and Compiler (Translator) - Questo controllerà la sintassi della query e convertirà la query in linguaggio macchina.

Query Optimizer - Preparerà il piano di esecuzione come output prendendo come input query, statistiche e albero Algebrizer.

Execution Plan - È come una roadmap, che contiene l'ordine di tutti i passaggi da eseguire come parte dell'esecuzione della query.

Query Executor - Qui è dove la query verrà eseguita passo dopo passo con l'aiuto del piano di esecuzione e verrà contattato anche il motore di archiviazione.

Storage Engine - È responsabile della memorizzazione e del recupero dei dati sul sistema di archiviazione (disco, SAN, ecc.), Della manipolazione dei dati, del blocco e della gestione delle transazioni.

SQL OS- Si trova tra la macchina host (sistema operativo Windows) e SQL Server. Tutte le attività svolte sul motore di database sono curate da SQL OS. Il sistema operativo SQL fornisce vari servizi del sistema operativo, ad esempio la gestione della memoria si occupa di pool di buffer, buffer di log e rilevamento di deadlock utilizzando la struttura di blocco e blocco.

Checkpoint Process- Checkpoint è un processo interno che scrive tutte le pagine sporche (pagine modificate) dalla cache del buffer sul disco fisico. Oltre a questo, scrive anche i record di registro dal buffer di registro al file fisico. La scrittura di pagine sporche dalla cache del buffer al file di dati è anche nota come protezione avanzata delle pagine sporche.

È un processo dedicato e viene eseguito automaticamente da SQL Server a intervalli specifici. SQL Server esegue il processo di checkpoint per ogni database individualmente. Checkpoint aiuta a ridurre il tempo di ripristino per SQL Server in caso di arresto imprevisto o arresto anomalo del sistema \ Errore.

Checkpoint in SQL Server

In SQL Server 2012 sono disponibili quattro tipi di file checkpoints -

  • Automatic - Questo è il checkpoint più comune che viene eseguito come processo in background per assicurarsi che il database SQL Server possa essere ripristinato entro il limite di tempo definito dall'opzione Intervallo di ripristino - Configurazione server.

  • Indirect- Questa è una novità di SQL Server 2012. Anche questo viene eseguito in background, ma per soddisfare un tempo di ripristino di destinazione specificato dall'utente per il database specifico in cui è stata configurata l'opzione. Una volta selezionato Target_Recovery_Time per un dato database, questo sovrascriverà l'intervallo di ripristino specificato per il server ed eviterà il checkpoint automatico su tale DB.

  • Manual- Questo viene eseguito come qualsiasi altra istruzione T-SQL, una volta emesso il comando checkpoint verrà eseguito fino al suo completamento. Il checkpoint manuale viene eseguito solo per il database corrente. Puoi anche specificare Checkpoint_Duration che è facoltativo: questa durata specifica il tempo in cui desideri che il tuo checkpoint venga completato.

  • Internal- Come utente non puoi controllare il checkpoint interno. Emesso su operazioni specifiche come

    • Shutdown avvia un'operazione di checkpoint su tutti i database tranne quando l'arresto non è pulito (arresto con nowait).

    • Se il modello di ripristino viene modificato da Full \ Bulk-loggato a Semplice.

    • Durante il backup del database.

    • Se il tuo database è in un modello di ripristino semplice, il processo del punto di controllo viene eseguito automaticamente quando il registro è pieno al 70% o in base all'opzione del server-Intervallo di ripristino.

    • Anche il comando Alter database per aggiungere o rimuovere un file data \ log avvia un checkpoint.

    • Il checkpoint si verifica anche quando il modello di ripristino del DB viene registrato in massa e viene eseguita un'operazione di registrazione minima.

    • Creazione di snapshot DB.

  • Lazy Writer Process- Lazy writer invierà le pagine sporche su disco per un motivo completamente diverso, poiché deve liberare memoria nel pool di buffer. Ciò accade quando il server SQL è sottoposto a una pressione della memoria. Per quanto ne so, questo è controllato da un processo interno e non vi è alcuna impostazione per esso.

SQL Server monitora costantemente l'utilizzo della memoria per valutare il conflitto di risorse (o la disponibilità); il suo compito è assicurarsi che ci sia una certa quantità di spazio libero disponibile in ogni momento. Come parte di questo processo, quando rileva un conflitto di risorse di questo tipo, attiva Lazy Writer per liberare alcune pagine in memoria scrivendo pagine sporche su disco. Utilizza l'algoritmo di utilizzo meno recente (LRU) per decidere quali pagine devono essere scaricate sul disco.

Se Lazy Writer è sempre attivo, potrebbe indicare un collo di bottiglia della memoria.

Architettura della memoria

Di seguito sono riportate alcune delle caratteristiche salienti dell'architettura della memoria.

  • Uno degli obiettivi principali di progettazione di tutto il software di database è ridurre al minimo l'I / O del disco perché le operazioni di lettura e scrittura su disco sono tra le operazioni che richiedono più risorse.

  • La memoria in Windows può essere richiamata con Virtual Address Space, condivisa dalla modalità Kernel (modalità OS) e dalla modalità utente (applicazioni come SQL Server).

  • Lo "spazio degli indirizzi utente" di SQL Server è suddiviso in due aree: MemToLeave e Buffer Pool.

  • La dimensione di MemToLeave (MTL) e Buffer Pool (BPool) è determinata da SQL Server durante l'avvio.

  • Buffer managementè un componente chiave per ottenere un'elevata efficienza di I / O. Il componente di gestione del buffer è costituito da due meccanismi: il gestore del buffer per accedere e aggiornare le pagine del database e il pool del buffer per ridurre l'I / O del file del database.

  • Il pool di buffer è ulteriormente suddiviso in più sezioni. I più importanti sono la cache del buffer (indicata anche come cache dei dati) e la cache delle procedure.Buffer cachemantiene le pagine di dati in memoria in modo che i dati a cui si accede di frequente possano essere recuperati dalla cache. L'alternativa sarebbe leggere le pagine di dati dal disco. La lettura delle pagine di dati dalla cache ottimizza le prestazioni riducendo al minimo il numero di operazioni di I / O richieste che sono intrinsecamente più lente del recupero dei dati dalla memoria.

  • Procedure cachemantiene la stored procedure e i piani di esecuzione delle query per ridurre al minimo il numero di volte che devono essere generati i piani delle query. È possibile trovare informazioni sulla dimensione e l'attività all'interno della cache delle procedure utilizzando l'istruzione DBCC PROCCACHE.

Altre parti del pool di buffer includono:

  • System level data structures - Contiene dati a livello di istanza di SQL Server su database e blocchi.

  • Log cache - Riservato per la lettura e la scrittura delle pagine del registro delle transazioni.

  • Connection context- Ogni connessione all'istanza dispone di una piccola area di memoria per registrare lo stato corrente della connessione. Queste informazioni includono procedure memorizzate e parametri di funzione definiti dall'utente, posizioni del cursore e altro.

  • Stack space - Windows alloca lo spazio dello stack per ogni thread avviato da SQL Server.

Architettura dei file di dati

L'architettura del file di dati ha i seguenti componenti:

Gruppi di file

I file di database possono essere raggruppati in gruppi di file per scopi di allocazione e amministrazione. Nessun file può essere membro di più di un gruppo di file. I file di registro non fanno mai parte di un gruppo di file. Lo spazio di registrazione viene gestito separatamente dallo spazio dati.

Esistono due tipi di gruppi di file in SQL Server, primario e definito dall'utente. Il gruppo di file primario contiene il file di dati primario e qualsiasi altro file non assegnato in modo specifico a un altro gruppo di file. Tutte le pagine per le tabelle di sistema vengono allocate nel gruppo di file primario. I gruppi di file definiti dall'utente sono qualsiasi gruppo di file specificato utilizzando la parola chiave del gruppo di file nella dichiarazione di creazione del database o di modifica del database.

Un gruppo di file in ogni database funziona come gruppo di file predefinito. Quando SQL Server alloca una pagina a una tabella o un indice per cui non è stato specificato alcun gruppo di file al momento della creazione, le pagine vengono allocate dal gruppo di file predefinito. Per cambiare il gruppo di file predefinito da un gruppo di file a un altro gruppo di file, dovrebbe avere il ruolo db fisso db_owner.

Per impostazione predefinita, il gruppo di file primario è il gruppo di file predefinito. L'utente deve disporre del ruolo predefinito del database db_owner per eseguire il backup di file e gruppi di file individualmente.

File

I database hanno tre tipi di file: file di dati primario, file di dati secondario e file di registro. Il file di dati primario è il punto di partenza del database e punta agli altri file nel database.

Ogni database ha un file di dati primario. Possiamo fornire qualsiasi estensione per il file di dati primario, ma l'estensione consigliata è.mdf. Il file di dati secondario è un file diverso dal file di dati primario in quel database. Alcuni database possono avere più file di dati secondari. Alcuni database potrebbero non avere un singolo file di dati secondario. L'estensione consigliata per il file di dati secondario è.ndf.

I file di registro contengono tutte le informazioni di registro utilizzate per ripristinare il database. Il database deve avere almeno un file di registro. Possiamo avere più file di registro per un database. L'estensione consigliata per il file di registro è.ldf.

La posizione di tutti i file in un database viene registrata sia nel database master che nel file primario del database. La maggior parte delle volte, il motore di database utilizza il percorso del file dal database master.

I file hanno due nomi: logico e fisico. Il nome logico viene utilizzato per fare riferimento al file in tutte le istruzioni T-SQL. Il nome fisico è OS_file_name, deve seguire le regole del sistema operativo. I file di dati e di registro possono essere posizionati su file system FAT o NTFS, ma non possono essere inseriti su file system compressi. Possono esserci fino a 32.767 file in un database.

Estensioni

Le estensioni sono unità di base in cui lo spazio viene allocato a tabelle e indici. Un'estensione è di 8 pagine contigue o 64 KB. SQL Server ha due tipi di estensioni: Uniforme e Mista. Le estensioni uniformi sono costituite da un unico oggetto. Le estensioni miste sono condivise da un massimo di otto oggetti.

Pages

È l'unità fondamentale di archiviazione dei dati in MS SQL Server. La dimensione della pagina è di 8 KB. L'inizio di ogni pagina è un'intestazione di 96 byte utilizzata per memorizzare le informazioni di sistema come il tipo di pagina, la quantità di spazio libero sulla pagina e l'ID oggetto dell'oggetto proprietario della pagina. Esistono 9 tipi di pagine di dati in SQL Server.

  • Data - Righe di dati con tutti i dati tranne i dati di testo, ntext e immagine.

  • Index - Voci dell'indice.

  • Tex\Image - Dati di testo, immagine e ntext.

  • GAM - Informazioni sulle estensioni allocate.

  • SGAM - Informazioni sulle estensioni allocate a livello di sistema.

  • Page Free Space (PFS) - Informazioni sullo spazio libero disponibile nelle pagine.

  • Index Allocation Map (IAM) - Informazioni sulle estensioni utilizzate da una tabella o da un indice.

  • Bulk Changed Map (BCM) - Informazioni sulle estensioni modificate dalle operazioni di massa dall'ultima istruzione del registro di backup.

  • Differential Changed Map (DCM) - Informazioni sulle estensioni che sono cambiate dall'ultima istruzione del database di backup.

Architettura dei file di registro

Il log delle transazioni di SQL Server funziona logicamente come se il log delle transazioni fosse una stringa di record di log. Ciascun record di registro è identificato dal numero di sequenza del registro (LSN). Ogni record di log contiene l'ID della transazione a cui appartiene.

I record di registro per le modifiche dei dati registrano l'operazione logica eseguita o registrano le immagini prima e dopo dei dati modificati. L'immagine precedente è una copia dei dati prima che l'operazione venga eseguita; l'immagine dopo è una copia dei dati dopo che l'operazione è stata eseguita.

I passaggi per ripristinare un'operazione dipendono dal tipo di record di registro -

  • Operazione logica registrata.
    • Per portare avanti l'operazione logica, l'operazione viene eseguita di nuovo.
    • Per ripristinare l'operazione logica, viene eseguita l'operazione logica inversa.
  • Immagine prima e dopo registrata.
    • Per portare avanti l'operazione, viene applicata l'immagine successiva.
    • Per ripristinare l'operazione, viene applicata l'immagine precedente.

Diversi tipi di operazioni vengono registrati nel registro delle transazioni. Queste operazioni includono:

  • L'inizio e la fine di ogni transazione.

  • Ogni modifica dei dati (inserimento, aggiornamento o cancellazione). Ciò include le modifiche apportate dalle procedure memorizzate di sistema o dalle istruzioni DDL (Data Definition Language) a qualsiasi tabella, comprese le tabelle di sistema.

  • Ogni estensione e allocazione o de allocazione della pagina.

  • Creazione o eliminazione di una tabella o di un indice.

Vengono registrate anche le operazioni di rollback. Ogni transazione riserva spazio sul registro delle transazioni per assicurarsi che esista spazio sufficiente per supportare un rollback causato da un'istruzione di rollback esplicita o se si verifica un errore. Questo spazio riservato viene liberato quando la transazione è completata.

La sezione del file di log dal primo record di log che deve essere presente per un corretto rollback a livello di database all'ultimo record di log scritto è chiamata parte attiva del log o log attivo. Questa è la sezione del registro necessaria per un ripristino completo del database. Nessuna parte del registro attivo potrà mai essere troncata. L'LSN di questo primo record di log è noto come LSN di recupero minimo (LSN min).

Il Motore di database di SQL Server divide internamente ogni file di registro fisico in una serie di file di registro virtuali. I file di registro virtuali non hanno dimensioni fisse e non esiste un numero fisso di file di registro virtuali per un file di registro fisico.

Il Motore di database sceglie dinamicamente la dimensione dei file di registro virtuali durante la creazione o l'estensione dei file di registro. Il Motore di database tenta di mantenere un numero limitato di file virtuali. La dimensione o il numero dei file di registro virtuali non possono essere configurati o impostati dagli amministratori. L'unico momento in cui i file di log virtuali influiscono sulle prestazioni del sistema è se i file di log fisici sono definiti da valori di piccole dimensioni e growth_increment.

Il valore della dimensione è la dimensione iniziale per il file di registro e il valore growth_increment è la quantità di spazio aggiunta al file ogni volta che è necessario nuovo spazio. Se i file di registro raggiungono una dimensione grande a causa di molti piccoli incrementi, avranno molti file di registro virtuali. Ciò può rallentare l'avvio del database e anche registrare le operazioni di backup e ripristino.

Si consiglia di assegnare ai file di registro un valore di dimensione vicino alla dimensione finale richiesta e di avere anche un valore growth_increment relativamente grande. SQL Server utilizza un registro write-ahead (WAL), che garantisce che nessuna modifica ai dati venga scritta su disco prima che il record di registro associato venga scritto su disco. Ciò mantiene le proprietà ACID per una transazione.