L'option de base de données auto_close

L'option de base de données auto_close (ou en français "fermeture automatique") : dans quel cas l'utiliser ? Bonne ou mauvaise configuration ?


Nous verrons ici que cette option n'est pas entièrement inutile...

L’option de base de données auto_close permet de fermer automatiquement les bases de données lorsqu’aucun utilisateur n’y est plus connecté : la base est toujours présente, mais toutes les ressources sont libérées et les fichiers sont fermés.
Cette option est désactivée par défaut, et son activation est très généralement considérée comme une mauvaise pratique, pour les motifs suivants :

  • A chaque fermeture de la base, toute la mémoire allouée à cette base dans le buffer cache (pages de données) est libérée. Les performances d’un SGBDR étant très liées au taux de présence des données en mémoire, les performances seront potentiellement diminuées.
  • A chaque fermeture de la base, toute la mémoire allouée à cette base dans le cache de procédures est libérée. Les plans d’exécution compilés sont donc supprimés et une prochaine exécution nécessitera une nouvelle compilation.

Activer l'option auto_close ne semble donc présenter que des inconvénients... Il existe cependant certains rares cas où il peut être utile de l'activer :

SQL Server permet en théorie d'héberger 32767 bases de données sur la même instance. Certains progiciels (dont je ne citerai pas le nom), issus d'anciens systèmes basés sur des fichiers multiplient à outrance le nombre de bases : une base de données par client, par exemple !

  • Sur un serveur avec plusieurs milliers de bases de données, un trop grand nombre de bases ouvertes (dont la plupart sont inutilisées) surchargent la mémoire inutilement et le serveur fonctionnera nettement mieux avec l'option auto_close activée...
  • Notez aussi que l'option auto_close permettra aussi de réduire le temps d'arrêt et de démarrage du service SQL Server. Selon mes tests, avec seulement 2000 bases de données auto_close désactivé, un redémarrage d'une instance prend environ 6 minutes : 3 mn pour l'arrêt et 3 pour le démarrage. Imaginez donc qu'avec 10000 bases, il faut 30 minutes pour redémarrer !  En activant l'option auto_close pour toutes les bases, le démarrage est quasi instantané...

Mais n'oublions pas les inconvénients majeurs : 

  • La perte de l'usage de la mémoire : les caches étant vidés à chaque fermeture, le risque est d'accroitre les IO disque et l'utilisation du CPU pour la compilation des plans d'exécution. Et c'est sans intérêt d'avoir un serveur bien pourvu en mémoire si on n'utilise aussi mal les caches...
  • Le temps perdu à ouvrir la base lorsqu'on s'y connecte et à la fermer lorsqu'on s'en déconnecte : au delà du problème évident de performances, cela génère en plus à chaque opération un message dans l'errorlog, qui devient avec cette pollution totalement illisible. Et ce délai rend aussi extrêmement lente (voire totalement inutilisable) l'interface graphique SQL Server Management Studio : avec 2000 bases option auto_close activée, le rafraichissement de la liste des bases dans l'explorateur d'objet prend plus de 8 minutes ! Vous voilà donc condamnés à SQLCMD et la ligne de commandes...

Moralité : l'option auto_close peut-être utile pour des serveurs hébergeant des centaines ou milliers de bases de données, mais elle est fortement déconseillée dans les autres cas...

Vos bases ont-elles l'option auto_close activée ? Lancez la requête suivante :

SELECT name, is_auto_close_on FROM master.sys.databases