Les opérations qui déclenchent une analyse du pool de mémoires tampons peuvent s’exécuter lentement sur les ordinateurs à mémoire élevée

Cet article explique comment l’analyse du pool de mémoires tampons SQL Server peut prendre beaucoup de temps sur les ordinateurs de grande mémoire.

S’applique à :   SQL Server
Numéro de base de connaissances d’origine :   4566579

Symptômes

Certaines opérations dans Microsoft SQL Server déclencher une analyse du pool de mémoires tampons (le cache qui stocke les pages de base de données en mémoire). Sur les systèmes qui ont une grande quantité de RAM (1 To de mémoire ou plus), l’analyse du pool de mémoires tampons peut prendre beaucoup de temps. Cela ralentit l’opération qui a déclenché l’analyse.

Opérations qui provoquent une analyse du pool de mémoires tampons

Voici quelques opérations qui peuvent déclencher une analyse du pool de mémoires tampons :

  • Démarrage de la base de données
  • Arrêt ou redémarrage de la base de données
  • Basculement du groupe de disponibilité
  • Suppression de la base de données (suppression)
  • Suppression de fichiers d’une base de données
  • Sauvegarde complète ou différentielle de base de données
  • Restauration de la base de données
  • Restauration du journal des transactions
  • Restauration en ligne
  • DBCC CHECKDB ou DBCC CHECKTABLE opération

Le journal des erreurs indique qu’une analyse a pris beaucoup de temps

À compter de SQL Server 2016 SP3, SQL Server 2017 CU23 et SQL Server 2019 CU9, un message d’erreur a été ajouté au journal des erreurs SQL Server pour indiquer qu’une analyse du pool de mémoires tampons a pris beaucoup de temps (10 secondes ou plus) :

L’analyse du pool de mémoires tampons a pris 14 secondes : ID de base de données 7, commande « BACKUP DATABASE », opération « FlushCache », mémoires tampons analysées 115, nombre total de mémoires tampons itérées 204640239, temps d’attente de 0 ms. Pour plus d’informations, consultez «https://go.microsoft.com/fwlink/?linkid=2132602 ».

Événement étendu pour diagnostiquer une analyse longue

En outre, à partir des mêmes builds SQL Server 2016 SP3, SQL Server 2017 CU23 et SQL Server 2019 CU9, l’événement étendu buffer_pool_scan_complete a été introduit pour vous aider à identifier les analyses de pool de mémoires tampons longues.

Si une analyse prend plus d’une seconde, le XEvent est enregistré comme suit lorsque l’événement est activé.

name database_id elapsed_time_ms Commande Opération scanned_buffers total_iterated_buffers
buffer_pool_scan_complete 7 1308 BASE DE DONNÉES BACKUP FlushCache 243 19932814

Notes

Le seuil est dans le XEvent est plus petit pour vous permettre de capturer des informations avec une précision plus fine.

Solution de contournement

Avant SQL Server 2022, il n’y avait aucun moyen d’éliminer ce problème. Il n’est pas recommandé d’effectuer une action pour effacer le pool de mémoires tampons, car la suppression de mémoires tampons propres (DBCC DROPCLEANBUFFERS) du pool de mémoires tampons peut entraîner une dégradation significative des performances. La suppression des pages de base de données de la mémoire entraîne une nouvelle lecture des données des fichiers de base de données sur le disque par les exécutions de requêtes suivantes. Ce processus d’accès aux données par le biais d’E/S disque entraîne la lenteur des requêtes.

Dans SQL Server 2022, ce problème est atténué, car les analyses de pool de mémoires tampons sont parallélisées en utilisant plusieurs cœurs. Il y aura une tâche pour 8 millions de mémoires tampons (64 Go) où une analyse série sera toujours utilisée s’il y a moins de 8 millions de mémoires tampons. Pour plus d’informations, regardez l’analyse parallèle du pool de mémoires tampons.

Plus d’informations

Pour plus d’informations sur les problèmes qui peuvent se produire dans les pools de mémoires tampons volumineuses, consultez SQL Server : grande RAM et point de contrôle de base de données.