Le operazioni che attivano un'analisi del pool di buffer possono essere eseguite lentamente su computer con memoria di grandi dimensioni

In questo articolo viene descritto come l'analisi del pool SQL Server buffer potrebbe richiedere molto tempo per completare l'analisi nei computer con memoria di grandi dimensioni.

Si applica a:   SQL Server
Numero KB originale:   4566579

Sintomi

Alcune operazioni in Microsoft SQL Server attivano un'analisi del pool di buffer , ovvero la cache che archivia le pagine di database in memoria. Nei sistemi con una grande quantità di RAM (1 TB di memoria o superiore), l'analisi del pool di buffer potrebbe richiedere molto tempo. Ciò rallenta l'operazione che ha attivato l'analisi.

Operazioni che causano un'analisi del pool di buffer

Ecco alcune operazioni che possono attivare un'analisi del pool di buffer:

  • Avvio database
  • Arresto o riavvio del database
  • Failover del gruppo di disponibilità
  • Rimozione del database (eliminazione)
  • Rimozione di file da un database
  • Backup completo o differenziale del database
  • Ripristino del database
  • Ripristino del registro delle transazioni
  • Ripristino online
  • DBCC CHECKDB o DBCC CHECKTABLE operazione

Il registro degli errori indica che un'analisi ha richiesto molto tempo

A partire da SQL Server 2016 SP3, SQL Server 2017 CU23 e SQL Server 2019 CU9,è stato aggiunto un messaggio di errore al registro errori di SQL Server per indicare che un'analisi del pool di buffer ha richiesto molto tempo (10 secondi o più):

Buffer Pool scan took 14 seconds: database ID 7, command 'BACKUP DATABASE', operation 'FlushCache', scanned buffers 115, total iterated buffers 204640239, wait time 0 ms. Per ulteriori https://go.microsoft.com/fwlink/?linkid=2132602 informazioni, vedere ' ' .

Evento esteso per diagnosticare un'analisi lunga

Inoltre, a partire dalle stesse build SQL Server 2016 SP3, SQL Server 2017 CU23 e SQL Server 2019 CU9 è stato introdotto l'evento buffer_pool_scan_complete Extended per facilitare l'identificazione delle analisi lunghe del pool di buffer.

Se un'analisi richiede più di 1 secondo, L'evento XEvent verrà registrato come segue quando l'evento è abilitato.

name database_id elapsed_time_ms comando operazione scanned_buffers total_iterated_buffers
buffer_pool_scan_complete 7 1308 BACKUP DATABASE FlushCache 243 19932814

Nota

La soglia è in XEvent è più piccola per consentire di acquisire informazioni con una granularità più fine.

Soluzione alternativa

Attualmente non è possibile eliminare questo problema. Se un'operazione deve essere completata rapidamente, cancellare il pool di buffer utilizzando i comandi seguenti prima di eseguirla.

  1. Esecuzione CHECKPOINT in ogni database

    USE <DatabaseName>
    CHECKPOINT
    GO
    

    Se il SQL Server ospita più database, ripetere i CHECKPOINT comandi per tutti i database utente.

  2. Dopo aver eseguito il checkpoint di tutti i database nel server, ridurre le dimensioni del pool di buffer con il comando seguente:

    DBCC DROPCLEANBUFFERS
    

    Avviso

    L'eliminazione di buffer puliti dal pool di buffer può comportare una riduzione significativa, ma temporanea, delle prestazioni. Questo comando rimuove dalla memoria tutte le pagine di database non modificati, causando la ri lettura dei dati dai file di database su disco da parte delle successive esecuzioni di query. Questo processo di accesso ai dati tramite I/O su disco causa un rallentamento delle query. Tuttavia, una volta che le pagine vengono nuovamente lette nella cache, SQL Server continuerà a leggerle da lì.

  3. Eseguire l'operazione che determina un'analisi del pool di buffer, ad esempio il backup completo del database.

Altre informazioni

Per ulteriori informazioni sui problemi che possono verificarsi in pool di buffer di grandi dimensioni, vedere SQL Server : RAM di grandi dimensioni e checkpoint del database.