AUTO_CLOSE : la fermeture automatique d'une base

Qu’est-ce que l’option AUTO_CLOSE dans une base de données, et ce qu’elle fait quand la dernière connexion se termine

Le laboratoire

Trois conteneurs Linux jetables, SQL Server 2019 CU32-GDR, 2022 CU26 et 2025 CU9-GDR, édition Developer, 1,5 Go de mémoire pour l’instance. Deux bases neuves en récupération simple : acdb en AUTO_CLOSE ON, avec une table de 100 000 lignes de 1 Ko (14 600 pages environ), et autre en AUTO_CLOSE OFF, pour voir ce qui arrive aux bases voisines. Les mesures sont les mêmes sur les trois versions, sauf quand le texte dit le contraire.

Les requêtes de l’article se lancent depuis n’importe quelle base ; celles qui changent une option portent le nom de la base de démonstration, PachaDataFormation, à remplacer par le vôtre.

La fermeture

La page de référence décrit l’option en une phrase : la base est arrêtée proprement et ses ressources libérées après la sortie du dernier utilisateur (options d’ALTER DATABASE). Mesuré : une session lit toute la table de acdb, qui occupe alors environ 14 600 pages du buffer pool, puis se déconnecte. Au premier relevé, une seconde environ plus tard, acdb n’a plus aucune page en cache ni aucun plan, et cela sur les trois versions. Un relevé toutes les 10 ms place la fermeture entre 130 et 270 ms après la dernière requête.

Une session dont la base est la base courante la garde ouverte : une session restée inactive dans acdb a gardé ses pages pendant toute son attente, et elles ont disparu à sa déconnexion. Une transaction encore ouverte qui l’a lue depuis une autre base, par un nom en trois parties, la garde aussi : elle y tient un verrou SHARED_TRANSACTION_WORKSPACE sur la base (sys.dm_tran_locks) jusqu’au COMMIT. La même lecture sans transaction ne la garde pas : la base se referme aussitôt. Un pool de connexions, qui garde des sessions ouvertes entre deux requêtes, garde donc la base ouverte tant qu’une de ses connexions l’a pour base courante (Initial Catalog) ; un pool ouvert sur master qui lit la base par des noms en trois parties ne la garde pas.

La requête qui suit compte les pages en cache des bases en AUTO_CLOSE. Sur une base fermée, elle rend 0. Elle lit sys.dm_os_buffer_descriptors, qui demande la permission serveur VIEW SERVER STATE, ou VIEW SERVER PERFORMANCE STATE depuis SQL Server 2022 :

SELECT d.name,
       d.is_auto_close_on,
       d.collation_name,
       (SELECT COUNT(*)
        FROM sys.dm_os_buffer_descriptors AS b
        WHERE b.database_id = d.database_id) AS pages_en_cache
FROM sys.databases AS d
WHERE d.is_auto_close_on = 1;

Une base fermée garde l’état ONLINE dans sys.databases. Ce qui la trahit, c’est collation_name à NULL, comme le prévoit la documentation de la vue (sys.databases).

Le cache des plans

La page d’ALTER DATABASE écrit qu’une fermeture automatique vide le cache des plans de toute l’instance, avec le message « cachestore flush » dans le journal d’erreurs ; la page de la stratégie de bonnes pratiques va jusqu’à dire qu’elle le vide après chaque connexion. Ce n’est pas ce qui se mesure sur les trois versions :

Avant la fermeture de acdbAprès
plans de acdb présents0 plan de acdb
plans de procédure, ad hoc et préparés dans autre et mastertous, avec le même plan_handle
34 à 39 plans dans le cache de l’instanceun de plus (la requête de relevé)
aucun message « cachestore flush » au journal, relu plus de cinq minutes après les fermetures (le message est regroupé par périodes de cinq minutes)

La fermeture retire les plans de la base fermée, pas ceux des autres. Le vidage de toute l’instance a peut-être existé dans une version ancienne ; il ne se voit sur aucune des trois testées.

La réouverture

La documentation dit que la base se rouvre quand un utilisateur essaie de s’en servir, par un USE par exemple. Elle se rouvre aussi pour une requête lancée depuis une autre base avec un nom en trois parties, et se referme juste après. Chaque réouverture écrit une ligne Starting up database dans le journal d’erreurs. Filtrées sur le nom de la base, elles listent ses démarrages dans le journal courant (les journaux précédents sont archivés à chaque redémarrage de l’instance) : réouvertures, mais aussi création, restauration ou remise en ligne. Le second filtre est une sous-chaîne, et un nom court peut ramener d’autres bases :

EXEC sys.xp_readerrorlog 0, 1, N'Starting up database', N'PachaDataFormation';

Toutes les fonctions qui citent la base ne l’ouvrent pas. DATABASEPROPERTYEX sur une base fermée rend NULL pour les propriétés lues dans la base (Collation, par exemple, ou LastGoodCheckDbTime, propriété décrite dans la documentation de DATABASEPROPERTYEX) sans la rouvrir, alors que OBJECT_NAME avec un identifiant de base la rouvre, comme le dit sa page de référence, et OBJECT_ID avec un nom en trois parties aussi.

Le premier accès après une réouverture relit du disque tout ce qu’il touche. Le même comptage de la table de 100 000 lignes, lancé deux fois de suite dans un même lot ; le chronomètre part de la première instruction, la base est donc déjà rouverte, puisque le lot l’a ouverte en se compilant :

VersionPremier accès après réouvertureSecond accès
2019111 ms, 14 341 pages en lecture anticipée56 ms, aucune lecture physique
2022131 ms, 14 341 pages en lecture anticipée51 ms, aucune lecture physique
2025192 ms, 14 341 pages en lecture anticipée85 ms, aucune lecture physique

D’un essai à l’autre, le premier accès varie du simple au triple (de 114 à 323 ms sur 2022, de 145 à 301 ms sur 2025), le second reste stable. Mesuré depuis un lot précédent de la même session, réouverture comprise, le premier accès a pris de 225 à 253 ms sur 2025. Sur le disque local du laboratoire, la table entière se relit donc en un à trois dixièmes de seconde. Le premier utilisateur de la journée attend l’ouverture, puis la lecture des pages dont sa requête a besoin : peu de chose pour une recherche par clé, beaucoup pour une requête qui balaie une grosse table sur un stockage lent (aucune grosse base n’a été mesurée ici), et la documentation prévient que la réouverture peut provoquer des expirations de délai dans l’application.

Les connexions répétées

La page d’ALTER DATABASE affirme que le processus AUTO_CLOSE est asynchrone, et qu’ouvrir et fermer la base de façon répétée ne réduit pas les performances. Mesuré : cent connexions successives, chacune lançant une lecture d’une ligne par clé primaire puis se déconnectant, depuis l’intérieur du conteneur :

VersionBase en AUTO_CLOSE ONBase en AUTO_CLOSE OFFRéouvertures au journal
20196,5 s2,8 s23
20229,2 s4,0 s27
202542,6 s25,5 s95

Le nombre de réouvertures dépend de l’intervalle entre deux connexions. Une connexion sqlcmd prend environ 30 ms dans les conteneurs 2019 et 2022 : les connexions s’enchaînent avant que la base se ferme, et une sur quatre environ la trouve fermée. Elle prend environ 170 ms dans le conteneur 2025, assez pour que la base se ferme entre deux. Avec une pause de 0,15 à 0,3 s entre les connexions, 2022 rouvre aussi la base cent fois sur cent. Ce coût de connexion allonge aussi les durées absolues de 2025, pour les deux bases. Dans les trois cas, la même série a pris de 1,7 à 2,4 fois plus de temps sur la base fermable, et des reprises en ordre alterné ont donné de 1,8 à 2,6.

Une application qui ouvre une connexion par requête sans pool, ou un outil de supervision qui passe sur chaque base toutes les minutes, rouvre donc la base à chaque passage, relit ses pages, et allonge le journal d’erreurs d’une ligne à chaque fois.

Les bases contenues

La note de la page d’ALTER DATABASE dit que l’option n’est pas disponible dans une base contenue ; la page de sécurité des bases contenues conseille de ne pas les configurer en fermeture automatique, ce qui suppose qu’on le peut. C’est la seconde qui a raison : sur les trois versions, une base créée en CONTAINMENT = PARTIAL accepte SET AUTO_CLOSE ON, se ferme, et un utilisateur contenu qui s’y connecte la rouvre. La page de sécurité en donne le risque : chaque authentification d’un utilisateur contenu sur une base fermée oblige à l’ouvrir, ce qui en fait un moyen de consommer des ressources du serveur.

Les autres effets

D’autres articles du site ont mesuré des conséquences de la fermeture :

  • sys.dm_db_index_usage_stats repart de zéro à chaque fermeture, et un index peut sembler inutilisé alors qu’il sert tous les jours (index inutilisés) ;
  • le Query Store d’une base en AUTO_CLOSE perd une partie des exécutions à chaque fermeture (le query_id du Query Store) ;
  • l’espérance de vie des pages et le contenu du buffer pool se lisent mal sur une instance dont des bases se ferment (buffer pool et PLE) ;
  • une base qui a un groupe de fichiers à mémoire optimisée refuse l’option, message 10794 (In-Memory OLTP pour les tables de staging).

Les groupes de disponibilité demandent AUTO_CLOSE à OFF dans leurs prérequis, et la mise en miroir aussi.

L’édition Express

Sur l’édition Express, une base créée par CREATE DATABASE est en AUTO_CLOSE ON, alors que model est à OFF ; la même instruction sur une édition Developer donne OFF. Mesuré sur 2019, 2022 et 2025 dans l’article sur le query_id du Query Store, et documenté seulement par un billet archivé de l’équipe Express. Une base d’application déployée sur une édition Express et passée ensuite sur une autre édition par sauvegarde et restauration garde l’option : la sauvegarde de démonstration de ce site, restaurée sur une édition Developer, est arrivée en AUTO_CLOSE ON. Azure SQL Managed Instance fait exception et force l’option à OFF à la restauration, sans possibilité de la changer ensuite (RESTORE sur Managed Instance).

Quand l’activer

La documentation réserve l’option à quelques cas rares : une instance qui manque de mémoire pour garder ouvertes un grand nombre de bases, une ancienne instance 32 bits, une base de poste de travail qu’on veut copier comme un fichier. Dans tous les autres cas, et en particulier pour une base qu’une application interroge plusieurs fois par jour, l’option se désactive, sans redémarrage :

ALTER DATABASE [PachaDataFormation] SET AUTO_CLOSE OFF;

Pour trouver les bases concernées sur une instance :

SELECT name, is_auto_close_on, state_desc
FROM sys.databases
WHERE is_auto_close_on = 1;