MSSQLSERVER_833

Si applica a:SQL Server Istanza gestita di SQL di Azure

Dettagli

Spiegazione

Questo messaggio indica che SQL Server ha emesso una richiesta di lettura o scrittura dal disco e che la richiesta ha richiesto più di 15 secondi per la restituzione. SQL Server segnala questo errore e indica un problema con il sottosistema di I/O. Un sistema di gestione di database (DBMS), ad esempio SQL Server, si basa sulla tempestività delle operazioni di input e output dei file (I/O). Uno degli elementi seguenti può causare operazioni di I/O bloccate o bloccate e influire negativamente sulla velocità di risposta e sulle prestazioni di SQL Server:

• Hardware difettoso
• Hardware configurato in modo non corretto
• Impostazioni del firmware
• Filtrare i driver
• Compressione
• Bug
• Altre condizioni nel percorso di I/O

Questi problemi di I/O possono causare il comportamento seguente:

• Blocco.
• Contesa di latch e timeout.
• Tempo di risposta lento.
• Estensione dei limiti delle risorse.
• È anche possibile notare altri sintomi associati a questo messaggio, ad esempio:
  • Tempi di attesa elevati per le attese PAGEIOLATCH.
  • Avvisi o errori nel registro eventi di sistema.
  • Indicazioni dei problemi di latenza del disco nei contatori di monitoraggio del sistema.

Quando un'operazione di I/O è in sospeso per 15 secondi o più, SQL Server esegue i passaggi seguenti:

1. Rileva che un'operazione è in sospeso.

2. Scrive un messaggio informativo nel log degli errori di SQL Server come descritto nella sezione Dettagli.

   Nella tabella seguente viene fornita una spiegazione a sezioni diverse di questo messaggio informativo:

Possibili cause

Il messaggio informativo indica che il carico corrente potrebbe riscontrare una delle condizioni seguenti:

• Il carico di lavoro supera le funzionalità del percorso di I/O a causa di errori di configurazione del sottosistema di I/O (SAN, NAS e collegato diretto) o perché è stata raggiunta la capacità hardware.
• Il carico di lavoro supera le funzionalità di sistema correnti, ad esempio I/O, CPU e HBA.
• Il percorso di I/O non funziona correttamente. Potrebbe trattarsi di un problema del firmware o di un driver.
• Il percorso di I/O presenta componenti hardware non funzionanti.
• Problema di prestazioni a livello di sistema operativo.
• Intervento del driver di filtro nel processo di I/O o nel percorso di archiviazione dei file di database. Ad esempio, programma antivirus.

SQL Server registra l'ora in cui ha avviato una richiesta di I/O e registra l'ora di completamento dell'I/O. Se la differenza è di 15 secondi o più, viene rilevata questa condizione. Significa anche che SQL Server non è la causa della condizione di I/O ritardata descritta e restituita da questo messaggio. Questa condizione è nota come I/O bloccata. La maggior parte delle richieste del disco si verificano entro la velocità tipica del disco. Questa velocità tipica del disco è spesso nota come tempo di ricerca del disco. Il tempo di ricerca del disco per la maggior parte dei dischi standard è pari a 10 millisecondi o meno. Pertanto, 15 secondi è un lungo periodo di tempo per il percorso di I/O di sistema per tornare a SQL Server. Per altri dettagli, vedere la sezione Altre informazioni .

Azione utente

Risolvere l'errore eseguendo la procedura seguente:

1. Esaminare il registro eventi di sistema per i messaggi di errore correlati all'hardware.
2. Esaminare i log specifici dell'hardware, se disponibili. Usare i metodi e le tecniche necessari per determinare la causa del ritardo nel sistema operativo, nei driver o nell'hardware di I/O.
3. Aggiornare tutti i driver di dispositivo e il firmware o eseguire altre diagnostica associate al sottosistema di I/O.
4. L'accesso al disco può essere rallentato dai driver di filtro, ad esempio un programma antivirus. Per aumentare la velocità di accesso, escludere i file di dati di SQL Server specificati nel messaggio di errore dalle analisi antivirus attive. Per altre informazioni, vedere Come scegliere il software antivirus da eseguire nei computer che eseguono SQL Server (microsoft.com).For more information, see How to choose antivirus software to run on computers that are running SQL Server (microsoft.com).
   • Usare l'utilità della riga di comando fltmc.exe per eseguire query su tutti i driver di filtro installati nel sistema e per comprendere le funzioni eseguite nel percorso di archiviazione dei file di database.
5. Utilizzare il Monitor prestazioni per esaminare i contatori seguenti:
   • Media secondi/trasf. disco
   • Lunghezza media coda del disco
   • Lunghezza corrente coda del disco
6. È anche possibile usare funzionalità come la registrazione ETW di Storport per misurare la latenza delle richieste effettuate a un'unità disco. Un altro kit di risoluzione dei problemi di I/O su disco simile è disponibile come profilo predefinito di Windows Performance Recorder.
7. Monitorare sys.dm_io_virtual_file_stats e scegliere il livello di archiviazione e le operazioni di I/O al secondo appropriati per la velocità effettiva di archiviazione.

Per una procedura guidata per la diagnosi e la risoluzione dei problemi di prestazioni di SQL Server che si verificano a causa di problemi di I/O, vedere Risolvere i problemi di prestazioni lente di SQL Server causati da problemi di I/O.

Altre informazioni

I/O bloccato e I/O bloccato

I/O bloccato

L'I/O bloccato è definito come richiesta di I/O non completata. Spesso, l'I/O bloccato indica un IRP bloccato. Per risolvere una condizione di I/O bloccata, è in genere necessario riavviare il computer o eseguire un'azione simile. Una condizione di I/O bloccata indica in genere uno dei problemi seguenti:

• Hardware difettoso.
• Bug in un componente del percorso di I/O.

I/O bloccato

Le operazioni di I/O bloccate vengono definite come richiesta di I/O completate o che richiedono un tempo eccessivo per il completamento. Il comportamento di I/O bloccato si verifica in genere a causa di uno dei motivi seguenti:

• Configurazione hardware.
• Impostazioni del firmware.
• Un problema del driver di filtro che richiede assistenza dall'hardware o dal fornitore del software per tracciare e risolvere.

SQL Server ha bloccato l'I/O e ha bloccato la registrazione e la creazione di report di I/O

Il supporto di SQL Server gestisce molti casi ogni anno che comportano problemi di I/O bloccati o bloccati. Questi problemi di I/O vengono visualizzati in modi diversi. I problemi di I/O sono alcuni dei più difficili da diagnosticare ed eseguire il debug e richiedono tempi e risorse significativi per il debug da Microsoft e dal cliente. La creazione di report e la registrazione delle richieste di I/O sono progettate per ogni file. Il rilevamento e la segnalazione delle richieste di I/O bloccate e bloccate sono due azioni separate.

Registrazione

Esistono due momenti in cui si verifica un'azione di record in SQL Server. Il primo è quando l'operazione di I/O viene completata. Il secondo momento è quando viene eseguito il writer lazy. Quando viene eseguito il writer differita, controlla tutti i dati in sospeso e le richieste di I/O dei file di log in sospeso. Se la richiesta di I/O supera la soglia di 15 secondi, si verifica un'operazione di record.

Creazione di report

La creazione di report si verifica in intervalli di cinque minuti o più a parte. La creazione di report si verifica quando viene effettuata la richiesta di I/O successiva nel file. Se si è verificata un'azione di record e sono passati cinque minuti o più dall'ultimo report, il messaggio informativo indicato nella sezione Dettagli viene scritto nel log degli errori di SQL Server.

La soglia di 15 secondi non è modificabile. Tuttavia, è possibile disabilitare il rilevamento di I/O bloccato bloccato usando il flag di traccia 830, anche se non è consigliabile eseguire questa operazione.

È possibile disabilitare il rilevamento per le operazioni di I/O bloccate e bloccate usando il flag di traccia 830. Per abilitare questo flag ogni volta che SQL Server viene avviato, usare il parametro di avvio -T830. Per disabilitare il rilevamento per un'istanza di SQL Server attualmente in esecuzione, usare l'istruzione seguente:

    dbcc traceon(830, -1)

Questa impostazione è valida solo per la durata del processo di SQL Server.

Rilevamento del pacchetto di richiesta di I/O (IRP)

SQL Server usa le chiamate API Di Microsoft Windows standard per leggere e scrivere dati. Ad esempio, SQL Server usa le funzioni seguenti:

• WriteFile
• ReadFile
• WriteFileScatter
• ReadFileGather

La richiesta di lettura o scrittura viene gestita da Windows come pacchetto di richiesta di I/O ( IRP). Per determinare lo stato dell'IRP, usare entrambe le funzionalità seguenti:

• Supporto Windows
• Registrazione ETW di Storport

È consigliabile verificare la presenza di eventuali aggiornamenti disponibili per gli elementi seguenti:

• The BIOS
• Firmware
• Qualsiasi altro componente del percorso di I/O

Contattare i fornitori di hardware prima di eseguire altre azioni di debug. La sessione di debug prevede probabilmente un componente driver, firmware o driver di filtro di terze parti.

Azioni del piano di query e prestazioni del sistema

Nel complesso, le prestazioni del sistema possono svolgere un ruolo chiave nell'elaborazione di I/O. È consigliabile considerare l'integrità generale del sistema durante l'analisi dei report delle operazioni di I/O bloccate o bloccate. Carichi eccessivi possono causare un rallentamento del sistema complessivo, inclusa l'elaborazione di I/O. Il comportamento del sistema quando si verifica il problema può essere un fattore chiave per determinare la causa radice del problema. Ad esempio, se l'utilizzo della CPU aumenta o rimane elevato mentre si verifica il problema, può indicare che un processo di sistema usa così tanta CPU che altri processi sono interessati negativamente.

Contatori delle prestazioni

Per monitorare le prestazioni di I/O, esaminare i contatori delle prestazioni seguenti per informazioni specifiche sul percorso di I/O:

• Media secondi/trasf. disco
• Lunghezza media coda del disco
• Lunghezza corrente coda del disco

Ad esempio, il tempo medio di trasferimento del disco in un computer che esegue SQL Server è in genere inferiore a 15 millisecondi. Se il valore Media sec/trasferimento del disco sale, indica che il sottosistema di I/O non è ottimale per mantenere il passo con la richiesta di I/O.

Prestare attenzione quando si usano i contatori delle prestazioni perché SQL Server sfrutta appieno le funzionalità di I/O asincrone che eserebbero fortemente la lunghezza della coda del disco. Pertanto, le lunghezze della coda del disco più lunghe da sole non indicano un problema.

In Monitoraggio di sistema di Windows è possibile esaminare il contatore "Disco fisico: Byte disco/sec" per ogni disco interessato e confrontare la frequenza di attività con i contatori "Process: I/O Data Bytes/Sec" e "Process: I/O Other Bytes/sec" per ogni processo. A tale scopo, è possibile determinare se un set specifico di processi genera richieste di I/O eccessive. Vari altri contatori correlati all'I/O nell'oggetto Process rivelano informazioni più granulari. Se si determina che un'istanza di SQL Server è responsabile di un carico di I/O eccessivo nel server, vedere la sezione successiva sugli indici e sul parallelismo. Per una discussione dettagliata sul rilevamento e la risoluzione dei colli di bottiglia di I/O, vedere Risolvere i problemi di prestazioni lente di SQL Server causati da problemi di I/O.

Indici e parallelismo

Spesso si verificano picchi di I/O perché manca un indice. Questo comportamento può eseguire il push grave del percorso di I/O. Un passaggio che usa l'ITW (Index Turning Wizard) può aiutare a risolvere l'utilizzo di operazioni di I/O nel sistema. Se una query trae vantaggio da un indice anziché da un'analisi di tabella o se usa un ordinamento o un hash, il sistema può ottenere i vantaggi seguenti:

• Viene eseguita una riduzione dell'I/O fisico necessaria per completare l'azione che crea direttamente vantaggi in termini di prestazioni per la query.
• È necessario modificare un numero minore di pagine nella cache dei dati. Di conseguenza, le pagine presenti nella cache dei dati rimangono rilevanti per le query attive.
• Gli ordinamenti e gli hash vengono utilizzati perché un indice potrebbe non essere presente o perché le statistiche non sono aggiornate. È possibile ridurre l'uso e la contesa di tempdb aggiungendo uno o più indici.
• Una riduzione viene eseguita in risorse, operazioni parallele o entrambe. Poiché SQL Server non garantisce l'esecuzione di query parallele e il carico nel sistema viene considerato, è consigliabile ottimizzare tutte le query per l'esecuzione seriale. Per ottimizzare una query, aprire Query Analyzer e impostare il valore sp_configure dell'opzione max degree of parallelism su 1. Se tutte le query vengono ottimizzate per essere eseguite tempestivamente come operazione seriale, l'esecuzione parallela è spesso un risultato migliore. Tuttavia, l'esecuzione parallela viene spesso selezionata perché la quantità di dati è di grandi dimensioni. Per un indice mancante, potrebbe essere necessario eseguire un ordinamento di grandi dimensioni. Più ruoli di lavoro che eseguono l'operazione di ordinamento creeranno una risposta più rapida. Tuttavia, questa azione può aumentare notevolmente la pressione sul sistema. Le richieste di lettura di grandi dimensioni provenienti da molti ruoli di lavoro possono causare un burst di I/O insieme a un maggiore utilizzo della CPU. Una query può spesso essere ottimizzata per l'esecuzione più veloce e usare meno risorse se viene aggiunto un indice o se si verifica un'altra azione di ottimizzazione.

Esempi pratici del supporto di SQL Server

Gli esempi seguenti sono stati gestiti dal supporto di SQL Server e dal supporto per l'escalation di Windows. Questi esempi sono destinati a fornire un frame di riferimento e consentono di impostare le aspettative relative alle situazioni di I/O bloccate e bloccate. Forniscono anche un framework per comprendere come un sistema può essere interessato o può rispondere. Nessun hardware o set specifico di driver comporta rischi specifici o un aumento del rischio rispetto a un altro. Tutti i sistemi sono gli stessi in questo senso.

Esempio 1: scrittura del log bloccata per 45 secondi

Un tentativo di scrittura di un file di log di SQL Server si blocca periodicamente per circa 45 secondi. La scrittura del log non viene completata in modo tempestivo. Questo comportamento crea una condizione di blocco che causa timeout del client di 30 secondi.

L'applicazione ha inviato un commit a SQL Server e il commit si blocca come scrittura del log in sospeso. Questo comportamento fa sì che la query continui a contenere blocchi e blocchi le richieste in ingresso da altri client. Altri client iniziano quindi a scadere. Questo complica il problema perché l'applicazione non esegue il rollback delle transazioni aperte quando si verifica un timeout della query. In questo modo vengono create centinaia di transazioni aperte che contengono blocchi. Pertanto, si verifica una grave situazione di blocco.

Per altre informazioni sulla gestione e il blocco delle transazioni, vedere l'articolo della Microsoft Knowledge Base seguente: 224453 Informazioni e risoluzione dei problemi di blocco di SQL Server

L'applicazione servizi un sito Web tramite il pool di connessioni. Man mano che più connessioni diventano bloccate, il sito Web crea più connessioni. Queste connessioni vengono bloccate e il ciclo continua.

Il completamento della scrittura del log richiede circa 45 secondi. Tuttavia, in questo momento, viene eseguito il backup di centinaia di connessioni. I problemi di blocco causano diversi minuti di tempo di ripristino per SQL Server e l'applicazione. In combinazione con i problemi dell'applicazione, la condizione di I/O bloccata ha un effetto molto negativo sul sistema.

Risoluzione

Il problema viene rilevato in una richiesta di I/O bloccata in un driver HBA (Host Bus Adapter). Il computer dispone di più schede HBA con supporto per il failover. Quando un HBA è dietro o non comunica con la rete SAN (Area Network) di Archiviazione, il valore di timeout "riprova prima del failover" è configurato su 45 secondi. Quando il timeout supera, la richiesta di I/O viene instradata al secondo HBA. Il secondo HBA gestisce la richiesta e viene completato rapidamente. Per evitare tali condizioni di stallo, il produttore dell'hardware consiglia un'impostazione di "ripetizione dei tentativi prima del failover" di cinque secondi.

Esempio 2: Intervento del driver di filtro

Molti programmi software antivirus e prodotti di backup usano driver di filtro I/O. Questi driver di filtro I/O diventano parte dello stack di richieste di I/O e hanno accesso alla richiesta IRP. Il Servizio Supporto Tecnico Clienti Microsoft ha riscontrato diversi problemi da bug che creano condizioni di I/O bloccate o condizioni di I/O bloccate in un'implementazione del driver di filtro.

Una di queste condizioni è un driver di filtro per l'elaborazione del backup che consente il backup dei file aperti quando si verifica il backup. L'amministratore di sistema ha incluso la directory del file di dati di SQL Server nelle selezioni di backup dei file. Quando si verifica il backup, il backup tenta di raccogliere l'immagine corretta del file al momento dell'avvio del backup. In questo modo si ritarda le richieste di I/O. Le richieste di I/O sono autorizzate a completare solo una alla volta quando il software li gestisce.

All'avvio del backup, le prestazioni di SQL Server vengono ridotte drasticamente quando l'I/O di SQL Server viene forzato a completarne uno alla volta. Quello alla volta è tale che l'operazione di I/O non può essere eseguita in modo asincrono, che complica il problema. Pertanto, quando SQL Server prevede di pubblicare una richiesta di I/O e continuare, il ruolo di lavoro è bloccato nella chiamata di lettura o scrittura fino al completamento della richiesta di I/O. Le azioni del driver di filtro disabilitano in modo efficace le attività di elaborazione, ad esempio SQL Server read-ahead. Inoltre, un altro bug nel driver di filtro lascia quello alla volta le azioni nel processo, anche quando il backup viene completato. L'unico modo per ripristinare le prestazioni di SQL Server consiste nel riavviare SQL Server in modo che l'handle di file venga rilasciato e riacquisito senza l'interazione del driver di filtro.

Risoluzione

Per risolvere questo problema, i file di dati di SQL Server vengono rimossi dal processo di backup dei file. Il produttore del software ha corretto il problema che ha lasciato il file nella modalità "uno alla volta".

Esempio 3: Errori nascosti

Molti sistemi di fascia alta hanno percorsi di I/O multicanale per gestire il bilanciamento del carico o attività simili. Il supporto tecnico Microsoft ha riscontrato problemi con il software di bilanciamento del carico in cui una richiesta di I/O ha esito negativo, ma il software non gestisce correttamente la condizione di errore. Il software può tentare tentativi infiniti. L'operazione di I/O si blocca e SQL Server non può completare l'azione specificata. Analogamente alla condizione di scrittura del log descritta in precedenza, possono verificarsi molti comportamenti di sistema scarsi dopo un'operazione di questo tipo.

Risoluzione

Per risolvere il problema, riavviare SQL Server. Tuttavia, a volte è necessario riavviare il sistema operativo per ripristinare l'elaborazione. È anche consigliabile ottenere un aggiornamento software dal fornitore di I/O.

Esempio 4: Archiviazione remota, mirroring e unità raid

Molti sistemi usano il mirroring o adottano passaggi simili per evitare la perdita di dati. Alcuni sistemi che usano il mirroring sono basati su software e alcuni sono basati su hardware. La situazione in genere individuata da supporto tecnico Microsoft per questi sistemi è aumentata la latenza.

Un aumento del tempo di I/O complessivo si verifica al termine dell'I/O prima che venga considerato completo. Per le installazioni mirror remote, i tentativi di rete possono diventare coinvolti. Quando si verificano errori di unità e il sistema raid viene ricompilato, il modello di I/O può anche essere interrotto.

Risoluzione

Sono necessarie impostazioni di configurazione rigorose per ridurre la latenza ai mirror o alle operazioni di ricompilazione raid.

Esempio 5: Compressione

Microsoft non supporta i file di dati e i file di log di SQL Server nelle unità compresse. La compressione NTFS non è sicura per SQL Server perché la compressione NTFS interrompe il protocollo WAL (Write Ahead Logging). La compressione NTFS richiede anche un aumento dell'elaborazione per ogni operazione di I/O. La compressione crea un comportamento simile a "uno alla volta" che causa gravi problemi di prestazioni.

Risoluzione

Per risolvere questo problema, decomprimere i dati e i file di log.

Per altre informazioni, vedere Supporto per i database nei volumi compressi.

Punti dati aggiuntivi

PAGEIOLATCH_* e le attese writelog in sys.dm_os_wait_stats dmv (Dynamic Management Views) sono indicatori chiave per analizzare le prestazioni del percorso di I/O. Se si osserva un numero elevato di attese PAGEIOLATCH, significa che SQL Server è in attesa del sottosistema di I/O. Una determinata quantità di attese PAGEIOLATCH è un comportamento tipico e previsto. Tuttavia, se i tempi di attesa PAGEIOLATCH medi sono costantemente superiori a 10 millisecondi, è necessario esaminare il motivo per cui il sottosistema di I/O è sotto pressione. Per altre informazioni, vedere i documenti seguenti:

• Risolvi i problemi di rallentamento delle prestazioni di SQL Server causati da problemi di I/O
• sys.dm_os_waiting_tasks (Transact-SQL)
• sys.dm_exec_requests
• Repository dei tipi di attesa di SQL Server

Riferimenti

• Usare DISKSPD per testare le prestazioni di archiviazione del carico di lavoro
• 826433 diagnostica di SQL Server aggiunta per rilevare problemi di I/O non segnalati a causa di letture non aggiornati o scritture perse
• Algoritmi di registrazione e archiviazione dei dati

SQL Server richiede che i sistemi supportino il "recapito garantito 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 motore di database requisiti di input/output.

Per altre informazioni sugli errori di I/O, vedere il capitolo 2 della pagina relativa alle nozioni fondamentali sull'I/O in Microsoft SQL Server.