Concetti fondamentali sull'I/O di SQL Server

Si applica a:SQL ServerIstanza gestita di SQL di AzureSQL Server su macchine virtuali di Azure

Lo scopo principale di un database di SQL Server è l'archiviazione e il recupero dei dati. L'esecuzione di una quantità elevata di operazioni di input/output (I/O) su disco è pertanto una caratteristica fondamentale del motore di database. Poiché le operazioni di I/O nel disco possono usare molte risorse e richiedere un tempo relativamente lungo per il completamento, in SQL Server viene data grande importanza all'efficienza dell'I/O.

I sottosistemi di archiviazione per SQL Server vengono forniti in più fattori di forma, tra cui unità meccaniche e archiviazione a stato solido. Questo articolo fornisce informazioni dettagliate su come usare i principi di memorizzazione nella cache delle unità per migliorare l'I/O del motore di database.

SQL Server richiede che i sistemi supportino la distribuzione garantita a supporti stabili, come descritto nei requisiti del programma di affidabilità di I/O di SQL Server. Per altre informazioni sui requisiti di input e output per il motore di database di SQL Server, vedere Requisiti di input/output del disco di SQL Server motore di database.

I/O su disco

Il gestore del 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:

• L'input/output viene eseguito di norma 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. In alcune circostanze (ad esempio, le operazioni di I/O del log non allineate), possono verificarsi operazioni di I/O sincrone.

• 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 specificato di CPU. Negli ambienti SQL Server di fascia alta con elaborazione delle transazioni online (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 scatter-gather, che consente il trasferimento di dati da e verso aree di memoria non contigue. Questo significa che SQL Server può riempire o scaricare rapidamente la cache buffer evitando più richieste di I/O fisici.

Richieste di I/O lunghi

Il gestore del buffer segnala qualsiasi richiesta di I/O che rimane in attesa per almeno 15 secondi. Questo processo consente all'amministratore di sistema di distinguere i problemi di SQL Server e i problemi del sottosistema I/O. Il messaggio di errore MSSQLSERVER_833 viene restituito e visualizzato nel log degli errori di SQL Server nel modo seguente:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Un I/O lungo può essere una lettura o una scrittura; il messaggio non indica attualmente quale. I messaggi relativi a I/O lunghi sono avvisi e non messaggi di errore. Non indicano problemi con SQL Server ma con il sistema di I/O sottostante. I messaggi consentono all'amministratore di sistema di individuare più rapidamente la causa di tempi di risposta di SQL Server scarsi e di distinguere i problemi esterni al controllo di SQL Server.The messages help the system administrator find the cause of poor SQL Server response times more more more and distinguish problems that are outside the control of SQL Server. I messaggi relativi a richieste di I/O che impiegano molto tempo non richiedono alcuna azione. Tuttavia, è consigliabile che l'amministratore di sistema indaghi sul motivo per cui la richiesta di I/O ha impiegato così tanto tempo e se questo è giustificabile.

Cause delle richieste di I/O lunghe

Un messaggio di I/O lungo può indicare che un I/O è bloccato in modo permanente e non verrà mai completato (noto come I/O perso) o semplicemente che non è ancora completo. Non è possibile indicare dal messaggio quale scenario è il caso, anche se un I/O perso spesso porta a un timeout di latch.

Le operazioni di I/O lunghe indicano spesso un carico di lavoro di SQL Server troppo intenso per il sottosistema del disco. Le situazioni seguenti potrebbero indicare un sottosistema disco non adeguato:

• Più messaggi di I/O lunghi vengono visualizzati nel log degli errori durante un carico di lavoro elevato di SQL Server.
• I contatori del Monitor delle prestazioni indicano latenze prolungate del disco, lunghe code del disco o nessun tempo di inattività del disco.

Un componente nel percorso di I/O (ad esempio, un driver, un controller o un firmware) può causare operazioni di I/O lunghe posticipando continuamente la manutenzione di una richiesta di I/O precedente, a favore della manutenzione di richieste più recenti. Questo problema può verificarsi in ambienti interconnessi, ad esempio reti iSCSI e Fibre Channel (a causa di un errore di configurazione o di percorso). Lo strumento Performance Monitor può rendere difficile confermare questo problema perché la maggior parte delle operazioni di I/O viene eseguita tempestivamente. I carichi di lavoro che eseguono grandi quantità di operazioni di I/O sequenziali, come backup e ripristino, analisi delle tabelle, ordinamento, creazione di indici, caricamenti in massa e azzeramento dei file, possono aggravare le richieste di I/O lunghe.

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 potrebbe contenere un evento correlato che consente di individuare il problema.

Problemi di prestazioni di I/O causati da query inefficienti o driver di filtro

Le operazioni di I/O lente possono essere causate da query non scritte in modo efficiente o ottimizzate correttamente con indici e statistiche. Un altro fattore comune nella latenza di I/O è la presenza di programmi antivirus o di altri programmi di sicurezza che analizzano i file di database. Questo software di analisi può estendersi al livello di rete, che aggiunge la latenza di rete, a sua volta che influisce indirettamente sulla latenza del database. Anche se lo scenario descritto circa 15 secondi di I/O è più comune con i componenti hardware, i ritardi di I/O più brevi vengono osservati più frequentemente con query non ottimizzate o programmi antivirus non configurati correttamente.

Per informazioni dettagliate su come risolvere questi problemi, vedere Risolvere i problemi di prestazioni lente di SQL Server causati da problemi di I/O.

Per informazioni su come configurare la protezione antivirus in SQL Server, vedere Configurare il software antivirus per l'uso con SQL Server.

Memorizzazione nella cache di scrittura nei controller di archiviazione

I trasferimenti di I/O che non usano una cache possono richiedere molto più tempo sulle unità meccaniche a causa delle velocità di rotazione del disco rigido, il tempo meccanico necessario per spostare le teste dell'unità e altri fattori di limitazione. Le installazioni di SQL Server sono rivolte a sistemi che forniscono controller di caching. Questi controller disabilitano le cache su disco e forniscono cache multimediali stabili per soddisfare i requisiti di I/O di SQL Server. Evitano i problemi di prestazioni correlati all'accesso al disco e ai tempi di scrittura su disco tramite l'uso delle varie ottimizzazioni del controller di cache.

L'uso di un controller di archiviazione con cache di scrittura (detto anche cache write-back) può migliorare le prestazioni di SQL Server. I controller di cache della scrittura e i sottosistemi di archiviazione sono sicuri per SQL Server, se sono progettati per l'uso in un ambiente di sistema di gestione di database transazionali critici per i dati. Queste funzionalità di progettazione devono mantenere i dati memorizzati nella cache se si verifica un errore di sistema. L'uso di un alimentatore esterno non interruptibile (UPS) per ottenere questa protezione non è in genere sufficiente, perché possono verificarsi modalità di guasto non correlate all'alimentazione.

I controller di memorizzazione nella cache e i sottosistemi di archiviazione possono essere sicuri per l'uso da parte di SQL Server. La maggior parte delle nuove piattaforme server create appositamente che incorporano questi controller sono sicure. Tuttavia, è necessario rivolgersi al fornitore dell'hardware per assicurarsi che il sottosistema di archiviazione sia stato testato e approvato per l'uso in un ambiente RDBMS (Data Critical TransactionAl Relational Database Management System).

Linee guida per la sicurezza del sottosistema della cache

I controller di cache write-back possono migliorare le prestazioni se soddisfano requisiti di sicurezza specifici.

• Includere la cache supportata dalla batteria o la memoria non volatile, ad esempio NVDIMM o flash con supporto di super-capacitor.
• Essere certificato dal fornitore per gli ambienti di database OLTP critici per i dati.
• Fornire protezione che copre tutte le condizioni di errore, non solo la perdita di energia.

Registrazione write-ahead

Le istruzioni di modifica dei dati di SQL Server generano scritture di pagine logiche. È possibile creare un'immagine di questo flusso di scritture in due posizioni: il log e il database stesso. Per motivi di prestazioni, SQL Server rinvia le operazioni di scrittura nel database tramite il proprio sistema di buffer della cache. Il sistema rinvia solo momentaneamente le scritture nel log fino al tempo COMMIT. Non memorizza nella cache queste scritture nello stesso modo delle scritture di dati. Poiché le scritture di log per una determinata pagina vengono sempre prima delle scritture dei dati della pagina, il log viene talvolta definito write-ahead log (WAL).

Protocollo di registrazione write-ahead (WAL)

Il termine protocollo è un modo eccellente per descrivere il WAL. Il WAL usato da SQL Server è noto come ARIES (Algorithm for Recovery and Isolation Exploiting Semantics). Per maggiori informazioni, vedere Gestire il ripristino accelerato del database.

Si tratta di un set specifico e definito di passaggi di implementazione necessari per garantire che i dati vengano archiviati e scambiati correttamente e che possano essere ripristinati in uno stato noto in caso di errore. Proprio come una rete contiene un protocollo definito per lo scambio di dati in modo coerente e protetto, allo stesso modo WAL descrive il protocollo per proteggere i dati. Tutte le versioni di SQL Server aprono i file di log e di dati usando la funzione Win32 CreateFile. Il membro dwFlagsAndAttributes include l'opzione FILE_FLAG_WRITE_THROUGH quando viene aperto da SQL Server.

FLAG_FILE_SCRITURA_IMMEDIATA

SQL Server crea i file di database usando il flag FILE_FLAG_WRITE_THROUGH. Questa opzione indica al sistema di scrivere tramite qualsiasi cache intermedia e passare direttamente all'archiviazione. Il sistema può comunque memorizzare nella cache le operazioni di scrittura, ma non può svuotarle con pigrizia. Per ulteriori informazioni, vedere CreateFileA.

L'opzione FILE_FLAG_WRITE_THROUGH garantisce che quando un'operazione di scrittura restituisce il completamento corretto, i dati vengono archiviati correttamente nell'archiviazione stabile. Questa funzionalità è allineata alla specifica del protocollo Write-Ahead Logging (WAL) per garantire l'integrità dei dati. Molti dispositivi di archiviazione (NVMe, PCIe, SATA, ATA, SCSI e basati su IDE) contengono cache di onboarding di 512 KB, 1 MB e superiori. Le cache di archiviazione si basano in genere su un condensatore e non su una soluzione supportata dalla batteria. Questi meccanismi di memorizzazione nella cache non possono garantire scritture in un ciclo di alimentazione o in un punto di errore simile. Garantiscono solo il completamento delle operazioni di scrittura settoriali. Man mano che i dispositivi di archiviazione continuano ad aumentare di dimensioni, le cache diventano più grandi e possono esporre grandi quantità di dati durante un errore.

Per ulteriori informazioni sul supporto FUA da parte della distribuzione di Linux e sul relativo effetto su SQL Server, vedere SQL Server On Linux: Forced Unit Access (FUA) Internals (interni FUA - Forced Unit Access su SQL Server in Linux).

Integrità transazionale e recupero di SQL Server

L'integrità transazionale è uno dei concetti fondamentali di un sistema di database relazionale. Le transazioni sono unità atomiche di lavoro che sono completamente applicate o completamente annullate. Il log delle transazioni write-ahead di SQL Server è un componente fondamentale per l'implementazione dell'integrità transazionale.

Qualsiasi sistema di database relazionale deve anche gestire un concetto strettamente correlato all'integrità transazionale, che è il recupero da un errore di sistema non pianificato. Effetti non ideali del mondo reale possono causare questo errore. In molti sistemi di gestione del database, l'errore di sistema può comportare un lungo processo di recupero manuale diretto dall'uomo.

Al contrario, il meccanismo di ripristino di SQL Server è automatico e funziona senza intervento umano. Ad esempio, SQL Server potrebbe supportare un'applicazione di produzione cruciale e riscontrare un errore di sistema a causa di una momentanea fluttuazione dell'alimentazione. Al momento del ripristino dell'alimentazione, l'hardware del server viene riavviato, il software di rete viene caricato e inizializzato e sql Server viene riavviato. Durante l'inizializzazione di SQL Server, esegue automaticamente il processo di recupero in base ai dati nel log delle transazioni. Questo intero processo si verifica senza intervento umano. Quando le workstation client vengono riavviate, gli utenti trovano tutti i dati presenti, fino all'ultima transazione immessa.

L'integrità transazionale e il recupero automatico in SQL Server costituiscono una potente funzionalità di risparmio di tempo e lavoro. Se un controller di memorizzazione nella cache di scrittura non è progettato correttamente per l'uso in un ambiente DBMS transazionale critico per i dati, può compromettere la capacità di SQL Server di ripristinare, danneggiando potenzialmente il database. Questo problema può verificarsi se il controller intercetta le scritture del log delle transazioni di SQL Server e le memorizza nel buffer in una cache hardware nella scheda controller, ma non mantiene queste pagine scritte durante un errore di sistema.

Rischi della cache di scrittura su disco

La maggior parte dei controller di caching dei dispositivi di archiviazione esegue il caching in scrittura. Non è sempre possibile disabilitare la cache di scrittura.

Anche se il server usa un UPS, il dispositivo non garantisce la sicurezza delle scritture memorizzate nella cache. Possono verificarsi molti tipi di guasti di sistema che un gruppo di continuità non è in grado di risolvere. Ad esempio, un errore di parità di memoria, una trappola del sistema operativo o un effetto glitch dell'hardware che causa una reimpostazione del sistema può produrre un'interruzione del sistema non controllata. Un errore di memoria nella cache di scrittura hardware può comportare anche la perdita di informazioni di log vitali.

Un altro possibile problema correlato a un controller di memorizzazione nella cache di scrittura può verificarsi all'arresto del sistema. Non è insolito scorrere il sistema operativo o riavviare il sistema durante le modifiche alla configurazione. Anche se un operatore attento segue il consiglio del sistema operativo di attendere che tutte le attività di archiviazione terminino prima del riavvio del sistema, le scritture memorizzate nella cache possono ancora essere presenti nel controller. Quando si preme la combinazione di tasti Ctrl+Alt+Del o si preme un pulsante reset dell'hardware, le scritture memorizzate nella cache possono essere rimosse, danneggiando potenzialmente il database.

È possibile progettare una cache di scrittura hardware che tenga conto di tutte le possibili cause dell'eliminazione dei dati della cache dirty, che lo rende sicuro per l'uso da parte di un server di database. Alcune di queste funzionalità di progettazione includono l'intercettazione del segnale del bus RST (reimpostazione) per evitare la reimpostazione non controllata del controller della cache, il backup della batteria integrato e la memoria speculare o la memoria ECC (controllo e correzione degli errori). Rivolgersi al fornitore dell'hardware per assicurarsi che la cache di scrittura includa queste e tutte le altre funzionalità necessarie per evitare la perdita di dati.

Usare le cache di archiviazione con SQL Server

Un sistema di database è prima di tutto responsabile dell'archiviazione e del recupero accurato dei dati, anche in caso di errori di sistema inaspettati.

Il sistema deve garantire l'atomicità e la durabilità delle transazioni, tenendo conto dell'esecuzione corrente, delle molteplici transazioni e dei vari punti di guasto. Questa proprietà viene spesso definita proprietà ACID (Atomicità, Coerenza, Isolamento e Durabilità).

Questa sezione illustra le implicazioni delle cache di archiviazione. Per altre informazioni sulla memorizzazione nella cache e sulle discussioni sulla modalità di errore alternativa, vedere gli articoli seguenti:

• 86903 SQL Server e controller dei dischi in cache
• Descrizione degli algoritmi di registrazione e archiviazione dei dati che estendono l'affidabilità dei dati in SQL Server

Esaminare anche il contenuto archiviato seguente:

• Nozioni di base di I/O di SQL Server 2000
• Nozioni di base di I/O di SQL Server capitolo 2

I concetti in questi due articoli rimangono ampiamente applicabili alle versioni correnti di SQL Server.

Soluzioni di memorizzazione nella cache supportate dalla batteria

I sistemi di controllo della cache avanzata disabilitano la cache su disco e offrono una soluzione di caching funzionale con supporto da batteria. Queste cache possono mantenere i dati nella cache per diversi giorni e anche consentire il collocamento della scheda di memorizzazione nella cache in un secondo computer. Quando l'alimentazione viene ripristinata correttamente, i dati non scritti vengono cancellati completamente prima che venga consentito un ulteriore accesso ai dati. Molti di questi sistemi consentono di stabilire una percentuale di lettura e cache di scrittura per ottenere prestazioni ottimali. Alcuni sistemi contengono aree di archiviazione di memoria di grandi dimensioni. Alcuni fornitori di hardware forniscono sistemi di memorizzazione nella cache di unità supportate da batteria di fascia alta con più gigabyte di cache. Questi sistemi possono migliorare significativamente le prestazioni del database. Le soluzioni di memorizzazione nella cache supportate dalla batteria offrono la durabilità e la coerenza dei dati previsti da SQL Server.

Implementazioni del sottosistema di archiviazione

I sottosistemi di archiviazione esistono in molti tipi. Due esempi comuni sono RAID (array ridondante di dischi indipendenti) e SAN (rete di archiviazione). Questi sistemi usano in genere unità basate su SCSI. La sezione seguente descrive le considerazioni generali sull'archiviazione.

SCSI, SAS e NVMe

Dispositivi di archiviazione SCSI, SAS e NVMe:

• Sono in genere progettati per l'uso pesante.
• Vengono in genere destinati a implementazioni multiutente basate su server.
• In genere hanno tempi migliori per gli errori rispetto ad altre implementazioni.
• Contengono euristica sofisticata per facilitare la stima degli errori imminenti.

Dispositivo non-SCSI

Altre implementazioni di unità, ad esempio IDE, ATA e SATA:

• Sono in genere progettati per un utilizzo leggero o medio.
• Sono in genere destinati alle applicazioni a utente singolo.

I controller non SCSI basati su desktop richiedono una maggiore larghezza di banda del processore principale (CPU) e sono spesso limitati da un singolo comando attivo. Ad esempio, quando un'unità non SCSI modifica un blocco non valido, l'unità richiede l'attesa dei comandi host. Il bus ATA presenta un altro esempio: il bus ATA supporta due dispositivi, ma solo un singolo comando può essere attivo. Questa limitazione lascia inattivo un disco mentre l'altro gestisce il comando in sospeso. I sistemi RAID basati su tecnologie desktop possono tutti manifestare questi sintomi ed essere notevolmente influenzati dal risponditore più lento. A meno che questi sistemi non usino progettazioni avanzate, le prestazioni non sono efficienti quanto le prestazioni dei sistemi basati su SCSI.

Considerazioni sulle risorse di archiviazione

Un'unità o una matrice basata su desktop può essere una soluzione a basso costo per alcune situazioni. Ad esempio, se si configura un database di sola lettura per la creazione di report, non si riscontrano molti dei fattori di prestazioni di un database OLTP quando la memorizzazione nella cache delle unità è disabilitata.

Le dimensioni dei dispositivi di archiviazione continuano ad aumentare. Le unità a basso costo e ad alta capacità possono essere allettanti. Tuttavia, quando si configura il disco per SQL Server in relazione alle esigenze del tempo di risposta aziendale, valutare attentamente le considerazioni seguenti:

• Progettazione del percorso di accesso
• Requisito di disabilitare la cache su disco

Dischi rigidi meccanici

I dischi meccanici utilizzano piatti magnetici rotanti per l'archiviazione dei dati. Sono disponibili in diverse capacità e fattori di forma, ad esempio IDE, SATA, SCSI e SERIAL Attached SCSI (SAS). Alcuni dischi SATA includono costrutti di previsione degli errori. I dischi SCSI sono progettati per cicli di lavoro più pesanti e percentuali di errore ridotte.

I sistemi basati su IDE e ATA possono posticipare i comandi host quando eseguono attività come la regolazione di blocchi non valida, causando periodi di attività di I/O bloccate.

I vantaggi di SAS includono accodamento avanzato fino a 256 livelli, testa della coda e accodamento fuori ordine. Il backplane SAS è progettato in modo tale da consentire l'uso di unità SAS e SATA all'interno dello stesso sistema.

L'installazione di SQL Server dipende dalla capacità del controller di disabilitare la cache su disco e di fornire una cache di I/O stabile. La scrittura dei dati in vari dispositivi in modo non sequenziale non rappresenta un ostacolo per SQL Server, purché il controller fornisca le corrette capacità di caching sui supporti stabili. La complessità della progettazione del controller aumenta con le tecniche di sicurezza dei dati avanzata, come il mirroring.

Archiviazione a stato solido

Lo spazio di archiviazione a stato solido presenta vantaggi rispetto ai dischi rigidi meccanici (rotanti), ma è soggetto a molti degli stessi modelli di errore dei supporti rotanti. È possibile connettere l'archiviazione a stato solido al server usando varie interfacce, tra cui NVM Express (NVMe), PCI Express (PCIe) e SATA. Considerare i supporti a stato solido come supporti rotanti, e assicurarsi che siano presenti misure di sicurezza appropriate per gli errori di alimentazione, ad esempio un controller di memorizzazione nella cache supportato dalla batteria.

I problemi comuni causati da un errore di alimentazione includono:

• Corruzione del bit: i record mostrano degli errori di bit casuali.
• Scritture volanti: Registrazioni ben formate finiscono in un luogo inappropriato.
• Shorn scrive: le operazioni vengono eseguite parzialmente a un livello inferiore alla dimensione prevista del settore.
• Danneggiamento dei metadati: i metadati nel livello di conversione flash (FTL) sono danneggiati.
• Dispositivo non responsivo: il dispositivo non funziona affatto o per lo più non funziona.
• Impossibilità di serializzazione: lo stato finale dell'archiviazione non deriva da un ordine di operazione serializzabile.

512e

La maggior parte dei dispositivi di archiviazione a stato solido segnala dimensioni di settore a 512 byte, ma usa pagine da 4 KB all'interno dei blocchi di cancellazione da 1 MB. L'uso di settori allineati a 512 byte per il dispositivo di log di SQL Server può generare più attività di lettura/modifica/scrittura (RMW), che possono contribuire a ridurre le prestazioni e aumentare l'usura.

Raccomandazione: assicurarsi che il controller di memorizzazione nella cache conosca le dimensioni corrette della pagina del dispositivo di archiviazione e sia in grado di allineare correttamente le scritture fisiche all'infrastruttura di archiviazione a stato solido.

0xFFFFFFFF

Un drive appena formattato di solito contiene solo zeri. Un blocco cancellato di un dispositivo a stato solido è tutto 1s, il che rende una lettura grezza di un blocco cancellato composta interamente da caratteri 0xFF. Tuttavia, è insolito per un utente leggere un blocco cancellato durante le normali operazioni di I/O.

Uso di stampa per modelli

Una tecnica usata in passato consiste nello scrivere un modello noto sull'intero disco. Quindi, quando l'attività del database viene eseguita su quella stessa unità, è possibile rilevare un comportamento non corretto (lettura non aggiornata, scrittura non riuscita o lettura di offset errato) quando lo schema si manifesta in modo imprevisto.

Questa tecnica non funziona bene nell'archiviazione a stato solido. Le attività di cancellazione e RMW per le scritture eliminano definitivamente il modello. L'attività di Garbage Collection (GC) della memoria a stato solido, il livellamento dell'usura, i blocchi della lista proporzionale/riserve e altre ottimizzazioni tendono a far sì che le scritture vengano allocate in posizioni fisiche diverse, a differenza del riutilizzo dei settori nei supporti rotanti.

Firmware (software di sistema)

Il firmware usato nell'archiviazione a stato solido è solitamente complesso in confronto con le controparti dei supporti rotanti. Molte unità usano più nuclei di elaborazione per gestire le richieste ricevute e le attività di garbage collection. Assicurarsi di mantenere aggiornato il firmware del dispositivo a stato solido per evitare problemi noti.

Danni ai dati in lettura e livellamento dell'usura

Un comune approccio di garbage collection (GC) per l'archiviazione a stato solido aiuta a prevenire danni ripetuti ai dati letti. Quando si legge ripetutamente la stessa cella, è possibile che l'attività degli elettroni possa fuoriuscire e causare danni alle cellule vicine. L'archiviazione a stato solido protegge i dati con vari livelli di codice di correzione di errore (ECC) e altri meccanismi.

Uno di questi meccanismi riguarda il livellamento dell'usura. L'archiviazione a stato solido tiene traccia dell'attività di lettura e scrittura sul dispositivo di archiviazione. La garbage collection può determinare aree sensibili o posizioni che si usurano più velocemente rispetto ad altre posizioni. Ad esempio, il GC determina che un blocco si trova in uno stato di sola lettura e quindi deve essere spostato. Questo spostamento è in genere verso un blocco con più usura, in modo che il blocco originale possa essere utilizzato per le operazioni di scrittura. Questo processo consente di bilanciare l'usura nel dispositivo di archiviazione, ma sposta i dati di sola lettura in una posizione che ha più usura e aumenta matematicamente le probabilità di guasto, anche se leggermente.

Un altro effetto collaterale del livellamento dell'usura può verificarsi con SQL Server. Supponiamo di eseguire DBCC CHECKDB e che restituisca un errore. Se viene eseguito una seconda volta, c’è una piccola possibilità che DBCC CHECKDB restituisca un modello diverso o aggiuntivo di errori, perché l'attività di GC di archiviazione a stato solido potrebbe apportare modifiche tra le esecuzioni di DBCC CHECKDB.

Errore del sistema operativo 665 e deframmentazione

I supporti rotanti devono mantenere i blocchi vicini tra loro per ridurre lo spostamento della testa dell'unità e aumentare le prestazioni. Lo spazio di archiviazione a stato solido non ha una testa fisica, che elimina il tempo di ricerca. Molti dispositivi a stato solido sono progettati per consentire operazioni parallele su blocchi diversi. La deframmentazione dei supporti a stato solido non è quindi necessaria. Le attività seriali sono i migliori modelli di I/O per massimizzare la produttività di I/O nei dispositivi di archiviazione a stato solido.

Raccomandazioni:

• Usare un controller appropriato supportato da batteria progettato per ottimizzare le attività di scrittura. Questa scelta può migliorare le prestazioni, diminuire l'usura delle unità e abbassare i livelli di frammentazione fisica.

• Considerare l'utilizzo del file system ReFS per evitare le limitazioni degli attributi NTFS.

• Assicurarsi che le impostazioni di crescita dei file siano appropriate.

Per maggiori informazioni sulla risoluzione dei problemi per l'errore di sistema operativo 665 in relazione alla frammentazione, vedere Gli errori di sistema operativo 665 e 1450 sono riportati per i file di SQL Server.

Compressione

Finché l'unità mantiene la finalità di supporti stabili, la compressione può estendere la durata dell'unità e potrebbe influire positivamente sulle prestazioni. Tuttavia, alcuni firmware potrebbero già comprimere i dati in modo invisibile. Ricordarsi di testare nuovi scenari di archiviazione prima di distribuirli nell'ambiente di produzione.

Riepilogo

• Gestire procedure e processi di backup e ripristino di emergenza appropriati.
• Mantenere aggiornato il firmware.
• Ascoltare attentamente le indicazioni del produttore dell'hardware.

Configurazione della cache delle unità

Per usare un'unità con SQL Server, disabilitare la cache dell'unità. Per impostazione predefinita, la cache dell'unità è abilitata. In Windows Server, utilizzare la scheda Proprietà disco>Hardware>Criteri per disabilitare la memorizzazione nella cache di scrittura a livello di sistema operativo.

Non basarsi solo sulle impostazioni a livello di sistema operativo. Alcune unità ignorano le impostazioni di Windows e richiedono utilità fornite dal produttore o impostazioni del firmware per disabilitare la memorizzazione nella cache di scrittura. Tramite gli strumenti del fornitore, verificare che la memorizzazione nella cache di scrittura sia effettivamente disabilitata.

Considerazioni sulla cache e SQLIOSim

Per verificare le garanzie di durabilità transazionale, convalidare il sottosistema di I/O usando SQLIOSim prima di passare all'ambiente di produzione. Questa utilità simula un'attività di lettura e scrittura asincrona pesante in un dispositivo dati simulato e in un dispositivo di log. Per altre informazioni, vedere Usare l'utilità SQLIOSim per simulare l'attività di SQL Server in un sottosistema del disco.

Molti produttori di PC ordinano le unità con la cache di scrittura disabilitata. Tuttavia, il test mostra che questa condizione potrebbe non essere sempre il caso, quindi è consigliabile testarla sempre completamente. Per eventuali domande sullo stato di memorizzazione nella cache del dispositivo di archiviazione, contattare il produttore e ottenere l'utilità appropriata per disabilitare le operazioni di memorizzazione nella cache di scrittura. Nei supporti di archiviazione meno recenti potrebbero essere necessarie anche le impostazioni dei jumper.

SQL Server richiede che i sistemi supportino la distribuzione garantita a supporti stabili, come descritto nei requisiti del programma di affidabilità di I/O di SQL Server. Per altre informazioni sui requisiti di input e output per il motore di database di SQL Server, vedere Requisiti di input/output del disco di SQL Server motore di database.

Rischi di memorizzazione nella cache in scrittura non corretti

Quando si abilita la memorizzazione nella cache di scrittura senza misure di sicurezza appropriate, alcuni sottosistemi di archiviazione confermano che le operazioni di scrittura vengono completate prima che i dati vengano scritti in modo sicuro in supporti durevoli. Se si verifica un errore di alimentazione o di sistema, questa condizione può comportare:

• Perdita di dati, in cui le transazioni di cui è stato eseguito il commit non diventano mai persistenti.
• Danneggiamento del database a causa di garanzie di ordine di scrittura compromesse.

Contenuto correlato

• Guida sull'architettura di pagina ed extent
• Guida all'architettura di gestione della memoria
• Archiviazione: Procedure consigliate per le prestazioni per SQL Server nelle macchine virtuali di Azure
• SQL Server su Linux: Internals dell'Accesso Forzato all'Unità (FUA)
• Nozioni di base di I/O di SQL Server, capitolo 2