Gestione del buffer
Lo scopo principale di un database di SQL Server è l'archiviazione e il recupero dei dati. L'utilizzo elevato di I/O nel disco è pertanto una caratteristica fondamentale di Motore di database. Poiché le operazioni di I/O nel disco possono utilizzare molte risorse e richiedere un tempo relativamente lungo per il loro completamento, in SQL Server viene data grande importanza all'efficienza dell'I/O. La gestione del buffer è un elemento chiave per il raggiungimento di tale efficienza. Il componente di gestione del buffer è costituito da due meccanismi, ovvero Gestione buffer che consente di accedere alle pagine del database e aggiornarle, e la cache buffer (detta anche pool di buffer) che consente di ridurre l'I/O del file di database.
Funzionamento della gestione del buffer
Un buffer è una pagina da 8 KB in memoria, delle stesse dimensioni di una pagina di dati o di indice. La cache buffer è quindi suddivisa in pagine da 8 KB. Gestione buffer consente di gestire le funzioni per la lettura delle pagine di dati o di indice dai file su disco del database nella cache buffer e la riscrittura sul disco delle pagine modificate. Una pagina rimane nella cache del buffer fino a quando per Gestione buffer non è necessaria l'area del buffer per leggere un maggior numero di dati. I dati vengono riscritti sul disco solo se vengono modificati. I dati nella cache buffer possono essere modificati più volte prima di venire riscritti sul disco. Per ulteriori informazioni, vedere Lettura di pagine e Scrittura di pagine.
All'avvio, SQL Server calcola le dimensioni dello spazio degli indirizzi virtuali per la cache buffer in base a diversi parametri, ad esempio la quantità di memoria fisica del sistema, il numero massimo configurato per i thread del server e vari parametri di avvio. SQL Server riserva questa quantità calcolata di spazio degli indirizzi virtuali di processo (detta memoria massima) alla cache buffer, ma acquisisce solo la quantità di memoria fisica necessaria per il carico corrente. Questa operazione di acquisizione è anche denominata esecuzione del commit. È possibile eseguire una query nelle colonne bpool_commit_target e bpool_committed della vista del catalogo sys.dm_os_sys_info per restituire rispettivamente il numero di pagine riservate come memoria massima e il numero di pagine di cui viene attualmente eseguito il commit nella cache buffer.
L'intervallo tra l'avvio di SQL Server e il momento in cui la cache buffer ottiene la memoria massima è detto processo di avvio. Durante questo periodo, le richieste di lettura riempiono i buffer in base alle necessità. Ad esempio, una richiesta di lettura di pagina singola riempie una singola pagina del buffer. Questo significa che il processo di avvio dipende dal numero e dal tipo di richieste del client. Il processo di avvio viene reso più rapido mediante la trasformazione delle richieste di lettura di pagina singola in richieste di otto pagine allineate. Ciò consente un completamento del processo di avvio molto più rapido, in particolare nei computer con molta memoria.
Gestione buffer utilizza la maggior parte della memoria nel processo di SQL Server e coopera pertanto con Gestione memoria per consentire agli altri componenti di utilizzare i relativi buffer. Gestione buffer interagisce essenzialmente con i componenti seguenti:
Strumento di gestione delle risorse, per controllare l'utilizzo globale della memoria e, nelle piattaforme a 32 bit, l'utilizzo dello spazio degli indirizzi.
Gestore database e il sistema operativo di SQL Server (SQLOS) per operazioni di I/O di file a basso livello.
Strumento di gestione dei log per la registrazione write-ahead.
Funzionalità supportate
Gestione buffer supporta le funzionalità seguenti:
Gestione buffer è compatibile con NUMA (Non-Uniform Memory Access). Le pagine della cache buffer vengono distribuite tra i nodi NUMA hardware. Ciò consente a un thread di accedere a una pagina del buffer allocata nel nodo NUMA locale anziché dalla memoria esterna. Per ulteriori informazioni, vedere Modifiche che consentono il supporto NUMA in SQL Server. Per comprendere la modalità di assegnazione delle pagine di memoria dalla cache buffer quando si utilizza NUMA, vedere Espansione e riduzione del pool di buffer in configurazione NUMA.
Gestione buffer supporta l'aggiunta di memoria a caldo. Ciò consente agli utenti di aggiungere memoria fisica senza riavviare il server. Per ulteriori informazioni, vedere Aggiunta di memoria a caldo.
Gestione buffer supporta l'allocazione dinamica della memoria in piattaforme Microsoft Windows XP e Windows 2003 a 32 bit quando è attiva la modalità AWE. L'allocazione dinamica della memoria consente a Motore di database di acquisire e rilasciare memoria in modo efficiente nella cache buffer per il supporto del carico di lavoro corrente. Per ulteriori informazioni, vedere Gestione della memoria dinamica.
Gestione buffer supporta pagine di grandi dimensioni su piattaforme a 64 bit. Le dimensioni delle pagine sono specifiche della versione di Windows utilizzata. Per ulteriori informazioni, vedere la documentazione di Windows.
Gestione buffer offre strumenti di diagnostica aggiuntivi esposti tramite le viste a gestione dinamica. È possibile utilizzare queste viste per monitorare varie risorse del sistema operativo specifiche di SQL Server. Ad esempio, è possibile utilizzare la vista sys.dm_os_buffer_descriptors per monitorare le pagine nella cache buffer. Per ulteriori informazioni, vedere Funzioni a gestione dinamica e DMV relative al sistema operativo di SQL Server (Transact-SQL).
I/O su disco
Gestione buffer esegue solo letture e scritture sul database. Altre operazioni su file e database, ad esempio apertura, chiusura, estensione e compattazione vengono eseguite dai componenti del gestore database e di File Manager.
Le operazioni di I/O su disco eseguite da Gestione buffer hanno le caratteristiche seguenti:
Tutti gli I/O vengono eseguiti in modo asincrono. In questo modo, il thread che esegue la chiamata può continuare l'elaborazione mentre l'operazione di I/O viene eseguita in background.
Tutti gli I/O vengono eseguiti nei thread che eseguono la chiamata a meno che non sia in uso l'opzione affinity I/O. L'opzione affinity I/O mask associa l'I/O su disco di SQL Server a un subset di CPU specificato. Negli ambienti SQL Server di fascia alta con elaborazione delle transazioni in linea (OLTP), questa estensione può migliorare le prestazioni dei thread di SQL Server che generano operazioni di I/O.
Gli I/O di più pagine vengono eseguiti con un I/O non sequenziale, che consente di inserire i dati in aree di memoria non contigue o estrarli da tali aree. Questo significa che SQL Server può riempire o scaricare rapidamente la cache buffer evitando richieste multiple di I/O fisici.
Richieste di I/O lunghi
Gestione buffer segnala qualsiasi richiesta di I/O che rimane in attesa per almeno 15 secondi. In questo modo, l'amministratore di sistema può distinguere tra problemi di SQL Server e problemi del sottosistema I/O. Il messaggio di errore 833 viene restituito e visualizzato nel log degli errori di SQL Server nel modo seguente:
Rilevate %d occorrenze di richieste di I/O che impiegano più di %d secondi per essere completate nel file [%ls] nel database [%ls] (%d). L'handle di file del sistema operativo è 0x%p. Offset dell'ultimo I/O lungo: %#016I64x.
Un I/O lungo può essere un'operazione di lettura o scrittura. Questa indicazione non viene attualmente inclusa nel messaggio. I messaggi relativi a I/O lunghi sono avvisi e non messaggi di errore. Non indicano infatti problemi che si verificano in SQL Server ma vengono restituiti per consentire all'amministratore di sistema di individuare in modo più rapido la causa dei tempi di risposta lenti di SQL Server, nonché distinguere i problemi esterni al controllo di SQL Server. I messaggi relativi a I/O lunghi non richiedono quindi alcuna azione. È comunque consigliabile che l'amministratore di sistema verifichi il motivo per il quale per la richiesta di I/O è stato necessario un tempo così prolungato e se esso è giustificato.
Cause delle richieste di I/O lunghi
Un messaggio di I/O lungo indica che un I/O è bloccato definitivamente e non verrà mai completato (I/O perso) o semplicemente che non è stato ancora completato. Non è possibile capire dal messaggio di quale scenario si tratta, sebbene un I/O perso spesso conduca a un timeout di latch.
Gli I/O lunghi indicano spesso un carico di lavoro di SQL Server troppo intenso per il sottosistema disco. Le situazioni seguenti possono indicare un sottosistema disco non adeguato:
Diversi messaggi di I/O lunghi vengono visualizzati nel log degli errori durante un carico di lavoro elevato di SQL Server.
I contatori Perfmon indicano latenze prolungate del disco, lunghe code del disco o nessun tempo di inattività del disco.
Gli I/O lunghi possono inoltre essere causati da un componente nel percorso di I/O (ad esempio, un driver, un controller o un firmware) che ritarda continuamente la risposta a una richiesta di I/O precedente privilegiando richieste più recenti, più vicine alla posizione corrente della testina. La tecnica comune di elaborazione delle richieste che dà priorità alle richieste più vicine alla posizione corrente della testina di lettura/scrittura è detta "accodamento dei comandi nativi". Questo può essere difficile da verificare mediante lo strumento Monitor di sistema di Windows (PERFMON.EXE) in quanto la maggioranza degli I/O vengono gestiti immediatamente. Le richieste di I/O lunghi possono essere ulteriormente complicate da carichi di lavoro che eseguono grandi quantità di I/O sequenziali, ad esempio backup e ripristino, scansioni delle tabelle, ordinamento, creazione di indici, caricamenti bulk e azzeramento dei file.
Gli I/O lunghi isolati apparentemente non correlati a una delle condizioni precedenti possono essere causati da un problema hardware o di driver. Il log eventi di sistema può contenere un evento correlato che consente di individuare il problema.
Rilevamento degli errori
Per le pagine di database sono disponibili due meccanismi facoltativi, ovvero la protezione delle pagine incomplete e la protezione dei checksum, che consentono di assicurare l'integrità della pagina dal momento in cui viene scritta sul disco fino a quando viene letta di nuovo. Questi meccanismi offrono una modalità indipendente di verifica della correttezza non solo dell'archiviazione dei dati, ma anche di componenti hardware quali controller, driver, cavi e perfino del sistema operativo. La protezione viene aggiunta alla pagina immediatamente prima della scrittura sul disco e viene verificata dopo la lettura della pagina dal disco.
Protezione delle pagine incomplete
La protezione delle pagine incomplete, disponibile in SQL Server 2000, rappresenta essenzialmente un modo di rilevare i danneggiamenti della pagina dovuti a interruzioni dell'alimentazione. Ad esempio, un'interruzione dell'alimentazione imprevista può fare sì che solo una parte di una pagina venga scritta su disco. Quando si utilizza la protezione delle pagine incomplete, alla fine di ogni settore da 512 byte della pagina viene inserita una firma a 2 bit (dopo aver copiato i due bit originali nell'intestazione di pagina). La firma alterna 01 e 10 binari a ogni scrittura, in modo che sia sempre possibile sapere quando solo una parte dei settori sia stata scritta sul disco. Se durante la successiva lettura della pagina un bit risulta nello stato non corretto, significa che la pagina non è stata scritta in modo corretto. Viene pertanto rilevata una pagina incompleta. Il rilevamento delle pagine incomplete utilizza risorse minime, ma non rileva tutti gli errori causati da problemi hardware del disco.
Protezione dei checksum
La protezione dei checksum, disponibile in SQL Server 2005, offre una più solida funzionalità di verifica dell'integrità dei dati. Un checksum viene calcolato per i dati in ogni pagina scritta e archiviato nell'intestazione di pagina. Ogni volta che una pagina con un checksum archiviato viene letta dal disco, il motore di database ricalcola il checksum per i dati della pagina e genera un errore 824 se il nuovo checksum è diverso da quello archiviato. La protezione dei checksum è in grado di rilevare più errori rispetto alla protezione delle pagine incomplete in quanto dipende da ogni byte della pagina. Tuttavia, richiede un utilizzo abbastanza elevato delle risorse. Quando il checksum è abilitato, gli errori causati da interruzioni dell'alimentazione e da hardware o firmware difettosi possono essere rilevati ogni volta che Gestione buffer legge una pagina dal disco.
Il tipo di protezione di pagina utilizzato è un attributo del database che contiene la pagina. La protezione dei checksum è la protezione predefinita per i database creati in SQL Server 2005 e versioni successive. Il meccanismo di protezione di pagina viene specificato al momento della creazione del database e può essere modificato utilizzando ALTER DATABASE. È possibile determinare l'impostazione corrente per la protezione di pagina eseguendo una query nella colonna page_verify_option della vista del catalogo sys.databases o nella proprietà IsTornPageDetectionEnabled della funzione DATABASEPROPERTYEX. Se l'impostazione relativa alla protezione di pagina viene modificata, la nuova impostazione non viene immediatamente estesa all'intero database. Il livello di protezione del database corrente viene infatti esteso alle pagine a ogni successiva scrittura. Ciò significa che il database potrebbe essere costituito da pagine con tipi di protezione diversi.