Requisiti del sottosistema di I/O di Microsoft SQL Server per il database tempdb

  • Articolo

Questo articolo presenta i requisiti del sottosistema di I/O per il database tempdb in SQL Server.

Versione originale del prodotto: Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2012 SQL Server 2014
Numero KB originale: 917047

Riepilogo

Microsoft SQL Server richiede che il sottosistema di I/O usato per archiviare i database di sistema e utente rispetti completamente i requisiti di registrazione Write-Ahead tramite entità di I/O specifiche. Questi requisiti sono necessari per rispettare le proprietà ACID delle transazioni: Atomic, Consistent, Isolated e Durable. I dettagli sui requisiti di conformità del sottosistema di I/O sono disponibili nei riferimenti seguenti:

Nozioni di base su I/O SQL Server 2000https://technet.microsoft.com/library/cc966500.aspx

Nota

Questo articolo si applica anche a SQL Server 2005 e versioni successive.

L'elenco seguente è un riepilogo rapido dei requisiti:

  • L'ordinamento di scrittura deve essere mantenuto.
  • La coerenza di scrittura dipendente deve essere mantenuta.
  • Le scritture devono essere sempre protette in/su supporti stabili.
  • La prevenzione di I/O strappata deve verificarsi.

La manutenzione della durabilità rimane fondamentale per tutti gli altri database, ma può essere rilassata per il database tempdb. Nella tabella seguente vengono riepilogati alcuni dei requisiti di I/O critici per i database SQL Server.

Requisito di I/O Breve descrizione Sistema o utente tempdb
Ordine di scrittura

Coerenza di scrittura dipendente		 Capacità del sottosistema di mantenere l'ordine corretto delle operazioni di scrittura. Ciò può essere particolarmente importante per le soluzioni di mirroring, i requisiti di coerenza dei gruppi e SQL Server l'uso del protocollo WAL. Obbligatorio Consigliata
Lettura dopo la scrittura Possibilità del sottosistema di gestire le richieste di lettura con l'immagine di dati più recente quando la lettura viene eseguita dopo il completamento di qualsiasi scrittura. Obbligatorio Obbligatorio
Sopravvivenza in caso di interruzione La possibilità per i dati di rimanere completamente intatti (Durable) in un'interruzione, ad esempio un riavvio del sistema. Obbligatorio Non applicabile
Prevenzione I/O strappata Possibilità del sistema di evitare la suddivisione di singole richieste di I/O. Obbligatorio Consigliata
Riscrittura del settore Il settore può essere scritto solo nella sua interezza e non può essere riscritto a causa di una richiesta di scrittura su un settore vicino. * Scoraggiato, consentito solo se transazionale * Scoraggiato, consentito solo se transazionale
Dati protetti Si prevede che quando una richiesta di scrittura o un'operazione FlushFileBuffers viene completata correttamente, i dati siano stati salvati in supporti stabili. Obbligatorio Non applicabile
Allineamento e dimensioni del settore fisico SQL Server interroga i percorsi di archiviazione dei file di dati e di log. Tutti i dispositivi sono necessari per supportare attributi di settore che consentono SQL Server di eseguire scritture su limiti fisici allineati al settore e in multipli delle dimensioni del settore. Obbligatorio Obbligatorio

Le riscritture del settore transazionale comportano operazioni completamente registrate dal sottosistema che consentono a un settore di essere completamente spostato, sostituito o sottoposto a rollback all'immagine originale. Queste riscritture sono in genere sconsigliate a causa del sovraccarico aggiuntivo necessario per eseguire tali azioni. Un esempio di questo potrebbe essere un'utilità defragmentation che sta spostando i dati del file. Il settore originale nel file non può essere sostituito con la nuova posizione del settore fino a quando il nuovo settore e i dati non vengono protetti. Il nuovo mapping del settore deve avvenire in modo transazionale, in modo che qualsiasi guasto, incluso un guasto di alimentazione, causi il ripristino dei dati originali. Assicurarsi di disporre di meccanismi di blocco disponibili durante questo tipo di processo per impedire l'accesso ai dati non validi, mantenendo così gli altri tenant di SQL Server I/O.

Sopravvivenza in caso di interruzione

Il database tempdb è un'area di lavoro per SQL Server e viene ricompilato a ogni avvio SQL Server. L'inizializzazione sostituisce qualsiasi necessità che i dati sopravvivono a un riavvio.

Operazioni di riscrittura del settore transazionale

Per garantire l'esito positivo dei processi di ripristino, ad esempio il rollback e il ripristino di arresto anomalo, i record di log devono essere archiviati correttamente su supporti stabili prima che la pagina dei dati venga archiviata e non possano essere riscritti senza rispettare le proprietà transazionali. Ciò richiede che il sottosistema e SQL Server mantengano attributi specifici, ad esempio l'ordinamento delle scritture, le scritture allineate al settore e le dimensioni e altri attributi di sicurezza di I/O descritti nei documenti indicati in precedenza. Per il database tempdb, il ripristino di arresto anomalo non è necessario perché il database viene sempre inizializzato durante SQL Server'avvio. Tuttavia, il database tempdb richiede ancora funzionalità di rollback. Di conseguenza, alcuni attributi del protocollo WAL possono essere rilassati.

Il percorso di archiviazione per il database tempdb deve agire in stretta conformità con i protocolli di unità disco stabiliti. In tutti i modi, il dispositivo in cui è archiviato il database tempdb deve apparire e fungere da disco fisico che fornisce funzionalità di lettura dopo scrittura. Le operazioni di riscrittura del settore delle transazioni possono essere un requisito aggiuntivo di implementazioni specifiche. Ad esempio, SQL Server non supporta le modifiche al database tramite la compressione del file system NTFS perché la compressione NTFS può riscrivere i settori del log che sono già stati scritti e considerati protetti da protezione avanzata. Un errore durante questo tipo di riscrittura può causare l'inutilizzabilità del database, danneggiando i dati che SQL Server già considerati sicuri.

Nota

SQL Server supporto esteso o compressione 2005 per database e gruppi di file di sola lettura. Per informazioni dettagliate, vedere la documentazione online SQL Server 2005.

Le operazioni di riscrittura del settore transazionale sono pertinenti a tutti i database SQL Server che includono il database tempdb. Un'ampia gamma di tecnologie di archiviazione estesa usa dispositivi e utilità in grado di riscrivere i dati che SQL Server considera sicuri. Ad esempio, alcune delle tecnologie emergenti eseguono la memorizzazione nella cache in memoria o la compressione dei dati. Per evitare gravi danni al database, qualsiasi riscrittura del settore deve avere il supporto transazionale completo in modo che, in caso di errore, i dati vengano riportati alle immagini di settore precedenti. Ciò garantisce che SQL Server non sia mai esposto a un'interruzione imprevista o a una condizione di danno dei dati.

È possibile inserire il database tempdb in sottosistemi speciali, ad esempio dischi RAM, stato solido o altre implementazioni ad alta velocità che non possono essere usate per altri database. Tuttavia, i fattori chiave presentati nella sezione Altre informazioni devono essere presi in considerazione quando si valutano queste opzioni.

Nota

I dischi locali negli ambienti cluster di failover sono supportati solo con implementazioni con stato solido o ad alta velocità. Questo perché il disco RAM può essere creato solo su una destinazione iSCSI. Inoltre, le funzionalità di destinazione e cluster di failover iSCSI non possono essere usate nello stesso host.

Ulteriori informazioni

Quando si valuta il percorso di archiviazione del database tempdb, è necessario studiare attentamente diversi fattori. Ad esempio, l'utilizzo del database tempdb comporta, ma non è limitato a, il footprint di memoria, il piano di query e le decisioni di I/O. L'ottimizzazione e l'implementazione appropriate del database tempdb possono migliorare la scalabilità e la velocità di risposta di un sistema. Questa sezione illustra i fattori chiave per determinare le esigenze di archiviazione per il database tempdb.

Sottosistemi ad alta velocità

Sul mercato sono disponibili diverse implementazioni di sottosistema ad alta velocità che forniscono requisiti di protocollo del sottosistema di I/O SQL Server, ma che non forniscono durabilità dei supporti.

Importante

Verificare sempre con il fornitore del prodotto di garantire la piena conformità alle esigenze di I/O SQL Server.

Un disco RAM è un esempio comune di tale implementazione. I dischi RAM installano i driver necessari e consentono a parte del disco RAM principale di apparire come e funzionare come qualsiasi unità disco collegata al sistema. Tutti i sottosistemi di I/O devono garantire la piena conformità con i requisiti di I/O SQL Server. Tuttavia, è ovvio che un disco RAM non è un supporto durevole. Pertanto, un'implementazione come un disco RAM può essere usata solo come posizione del database tempdb e non può essere usata per altri database.

Chiavi da considerare prima dell'implementazione e della distribuzione

Esistono diversi punti da considerare prima della distribuzione del database tempdb in questo tipo di sottosistema. Questa sezione usa un disco RAM come base per la discussione, ma risultati simili si verificano in altre implementazioni ad alta velocità.

Sicurezza di I/O

La conformità delle operazioni di lettura dopo scrittura e scrittura del settore transazionale è un must. Non distribuire mai SQL Server in alcun sistema che non supporta completamente i requisiti di I/O SQL Server o si rischia danni e perdita dei dati.

Pagine già memorizzate nella cache (cache a ram doppia)

Le tabelle temporanee sono come tutte le altre tabelle di un database. Vengono memorizzati nella cache dal pool di buffer e gestiti da operazioni di scrittura lazy. L'archiviazione di pagine di tabella temporanee in un disco RAM comporta una doppia memorizzazione nella cache della RAM, una nel pool di buffer e una nel disco RAM. Ciò elimina direttamente le dimensioni totali possibili del pool di buffer e in genere riduce le prestazioni di SQL Server.

Rinuncia alla RAM

Il disco RAM definisce una parte della RAM principale come suggerisce il nome. Sono disponibili diverse implementazioni di dischi RAM e cache di file basati su RAM. Alcuni consentono anche operazioni di backup di I/O fisiche. L'elemento chiave della cache dei file basata su RAM è che elimina direttamente la memoria fisica che può essere usata da SQL Server. L'aggiunta di una cache di file basata su RAM migliora sempre le prestazioni dell'applicazione e non riduce le prestazioni di altre query o applicazioni.

Ottimizzare per primo

Un'applicazione deve ottimizzare per rimuovere gli ordinamenti e gli hash non necessari e indesiderati che potrebbero causare l'uso del database tempdb. Molte volte l'aggiunta di un indice può rimuovere completamente la necessità dell'ordinamento o dell'hash nel piano, determinando prestazioni ottimali senza richiedere l'uso del database tempdb.

Possibili punti di vantaggio

I vantaggi derivanti dall'inserimento del database tempdb in un sistema ad alta velocità possono essere determinati solo tramite rigorosi test e misurazioni dei carichi di lavoro dell'applicazione. Il carico di lavoro deve essere studiato attentamente per le caratteristiche di cui il database tempdb può trarre vantaggio e la sicurezza di I/O deve essere confermata prima della distribuzione.

Le operazioni di ordinamento e hash interagiscono con i gestori di memoria SQL Server per determinare le dimensioni dell'area di lavoro in memoria per ogni operazione di ordinamento o hash. Non appena i dati di ordinamento o hash superano l'area di lavoro in memoria allocata, i dati possono essere scritti nel database tempdb. Questo algoritmo è stato espanso nel SQL Server 2005, riducendo i requisiti di utilizzo del database tempdb rispetto alle versioni precedenti di SQL Server.

Attenzione

SQL Server è progettato per tenere conto dei livelli di memoria e delle attività di query correnti quando si prendono decisioni del piano di query che implicano l'uso di operazioni di database tempdb. Pertanto, i miglioramenti delle prestazioni variano in modo significativo in base ai carichi di lavoro e alla progettazione delle applicazioni. È consigliabile completare i test con la soluzione preferita per determinare i possibili miglioramenti e valutare i requisiti di sicurezza di I/O prima di tale distribuzione.

SQL Server usa il database tempdb per gestire varie attività che coinvolgono ordinamenti, hash, l'archivio delle versioni delle righe e le tabelle temporanee:

  • Le tabelle temporanee vengono gestite dalle routine comuni del pool di buffer per le pagine di dati e in genere non presentano vantaggi in termini di prestazioni dalle implementazioni di sottosistemi speciali.
  • Il database tempdb viene usato come area di lavoro per gli hash e gli ordinamenti. La riduzione della latenza di I/O per tali operazioni può essere utile. Tuttavia, è necessario sapere che l'aggiunta di un indice per evitare un hash o un ordinamento può offrire un vantaggio simile.

Eseguire baseline con e senza il database tempdb archiviato nel sottosistema ad alta velocità per confrontare i vantaggi. Parte del test deve includere query sul database utente che non comportano ordinamenti, hash o tabelle temporanee e quindi verificare che queste query non siano interessate negativamente. Quando si valuta il sistema, possono essere utili gli indicatori di prestazioni seguenti.

Indicatore Descrizione/utilizzo
Letture e scritture di pagine Il miglioramento delle prestazioni degli I/O del database tempdb può modificare la frequenza di letture e scritture di pagine per i database utente a causa della latenza ridotta associata all'I/O del database tempdb. Per le pagine del database utente, il numero complessivo non deve variare nello stesso carico di lavoro.
Byte fisici di lettura e scrittura nel database tempdb Se lo spostamento del database tempdb in un dispositivo, ad esempio un disco RAM, aumenta l'I/O effettivo per il database tempdb, indica che la memoria sottratta al pool di buffer causa un aumento dell'attività del database tempdb. Questo modello indica che anche l'aspettativa di vita delle pagine di database può essere influenzata in modo negativo.
Aspettativa di vita della pagina Un calo dell'aspettativa di vita della pagina può indicare un aumento dei requisiti di I/O fisico per un database utente. La riduzione della frequenza potrebbe probabilmente indicare che la memoria sottratta al pool di buffer sta forzando le pagine del database a uscire prematuramente dal pool di buffer. Combinare con gli altri indicatori e testare per comprendere completamente i limiti dei parametri.
Velocità effettiva complessiva
Utilizzo CPU
Scalabilità
Ora risposta		 L'obiettivo principale di una modifica alla configurazione del database tempdb è aumentare la velocità effettiva complessiva. I test devono includere una combinazione di carichi di lavoro ripetibili che possono essere ridimensionati per determinare il modo in cui viene influenzata la velocità effettiva.

Un'implementazione del disco RAM basata su compressione può funzionare bene con 10 utenti. Tuttavia, con un carico di lavoro maggiore, questo può spingere i livelli di CPU oltre i livelli desiderati e avere effetti negativi sul tempo di risposta quando i carichi di lavoro sono elevati. Sono incoraggiati i veri test di stress e i test di stima del carico futuri.
File di lavoro e azioni di creazione di tabelle di lavoro Se lo spostamento del database tempdb in un dispositivo, ad esempio un disco RAM, modifica il piano di query aumentando il numero o le dimensioni dei file di lavoro o delle tabelle di lavoro, indica che la memoria sottratta al pool di buffer sta causando un aumento dell'attività del database tempdb. Questo modello indica che anche l'aspettativa di vita delle pagine di database può essere influenzata in modo negativo.

Esempio di riscrittura del settore transazionale

Nell'esempio seguente viene elaborata la sicurezza dei dati richiesta dai database SQL Server.

Si supponga che un fornitore di dischi RAM usi un'implementazione di compressione in memoria. L'implementazione deve essere incapsulata correttamente fornendo l'aspetto fisico del flusso di file come se il settore fosse allineato e ridimensionato in modo SQL Server non sia a conoscenza e protetto correttamente dall'implementazione sottostante. Esaminare più da vicino l'esempio di compressione.

  • Il settore 1 viene scritto nel dispositivo ed è compresso per risparmiare spazio.
  • Il settore 2 viene scritto nel dispositivo ed è compresso con il settore 1 per risparmiare spazio.

Il dispositivo può eseguire le azioni seguenti per proteggere i dati del settore 1 quando vengono combinati con i dati del settore 2.

  • Blocca tutte le scritture nei settori 1 e 2.
  • Decomprimere il settore 1 in un'area di lavoro, lasciando l'attuale settore 1 come dati attivi da recuperare.
  • Comprimere i settori 1 e 2 in un nuovo formato di archiviazione.
  • Blocca tutte le letture e le scritture dei settori 1 e 2.
  • Scambiare l'archiviazione precedente per i settori 1 e 2 con la nuova risorsa di archiviazione. Se il tentativo di scambio ha esito negativo (rollback):
    • Ripristinare l'archiviazione originale per i settori 1 e 2.
    • Rimuovere i dati combinati dei settori 1 e 2 dall'area di lavoro.
    • Esito negativo dell'operazione di scrittura del settore 2.
  • Sbloccare le letture e le scritture per i settori 1 e 2.

La possibilità di fornire meccanismi di blocco per le modifiche del settore e di ripristinare le modifiche quando il tentativo di scambio del settore non riesce è considerata provvisoriamente conforme. Per le implementazioni che usano l'archiviazione fisica per il backup esteso, include gli aspetti appropriati del log delle transazioni per proteggere e ripristinare le modifiche applicate alle strutture su disco per mantenere l'integrità dei file di database SQL Server.

Qualsiasi dispositivo che abilita la riscrittura dei settori deve supportare le riscritture in modo transazionale in modo che SQL Server non sia esposto alla perdita di dati.

Nota

L'istanza di SQL Server viene riavviata quando si verificano errori di I/O online e rollback nel database tempdb.

Prestare attenzione quando si sposta il database tempdb

Prestare attenzione quando si sposta il database tempdb perché se non è possibile creare il database tempdb, SQL Server non verrà avviato. Se non è possibile creare il database tempdb, avviare SQL Server usando il parametro di avvio (-f) e spostare il database tempdb in un percorso valido.

Per modificare il percorso fisico del database tempdb, seguire questa procedura:

  1. Usare l'istruzione ALTER DATABASE e la MODIFY FILE clausola per modificare i nomi di file fisici di ogni file nel database tempdb in modo da fare riferimento al nuovo percorso fisico, ad esempio il nuovo disco.

    ALTER DATABASE tempdb MODIFY FILE 
(name = tempdev, filename = 'C:\MyPath\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE 
(name = templog, filename = 'C:\MyPath\templog.ldf')

  2. Arrestare e quindi riavviare SQL Server.

Le certificazioni dei prodotti partner non sono una garanzia di compatibilità o sicurezza

Un prodotto di terze parti o un fornitore specifico può ricevere una certificazione del logo Microsoft. Tuttavia, la certificazione del partner o un logo Microsoft specifico non certifica la compatibilità o l'idoneità per uno scopo specifico in SQL Server.

Supporto tecnico

Se si usa un sottosistema con SQL Server che supporta le garanzie di I/O per l'uso del database transazionale come descritto in questo articolo, Microsoft fornirà il supporto per applicazioni basate su SQL Server e SQL Server. Tuttavia, i problemi con o causati dal sottosistema verranno indirizzati al produttore.

Per i problemi relativi al database tempdb, supporto tecnico Microsoft Services chiederà di rilocare il database tempdb. Contattare il fornitore del dispositivo per verificare di aver distribuito e configurato correttamente il dispositivo per l'uso del database transazionale.

Microsoft non certifica o convalida che i prodotti di terze parti funzionino correttamente con SQL Server. Inoltre, Microsoft non fornisce alcuna garanzia, garanzia o dichiarazione dell'idoneità di un prodotto di terze parti per l'uso con SQL Server.

Riferimenti

Per ulteriori informazioni, vedere i seguenti articoli della Microsoft Knowledge Base: