Considérations relatives aux paramètres de croissance automatique et de réduction automatique dans SQL Server

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 315512

Résumé

Les paramètres de croissance automatique et de réduction automatique par défaut sont appropriés sur de nombreux systèmes SQL Server. Toutefois, il existe des environnements dans lesquels vous devrez peut-être ajuster les paramètres de croissance automatique et d’autoshrink. Cet article fournit des informations générales pour vous guider quand sélectionner ces paramètres pour votre environnement.

Voici quelques éléments à prendre en compte si vous décidez de régler vos paramètres de croissance automatique et de réduction automatique.

Comment faire configurer les paramètres

  1. Vous pouvez configurer ou modifier les paramètres de croissance automatique et de réduction automatique à l’aide de l’un des éléments suivants :

    Remarque

    Pour plus d’informations sur la définition de ces paramètres au niveau du fichier de base de données, consultez Ajouter des fichiers de données ou des fichiers journaux à une base de données.

    Vous pouvez également configurer l’option croissance automatique lorsque vous créez une base de données.

    Pour afficher les paramètres actuels, exécutez la commande Transact-SQL suivante :

    sp_helpdb [ [ @dbname= ] 'name' ]
    
  2. Gardez à l’esprit que les paramètres de croissance automatique sont par fichier. Par conséquent, vous devez les définir à au moins deux emplacements pour chaque base de données (un pour le fichier de données principal et un pour le fichier journal principal). Si vous avez plusieurs fichiers de données et/ou fichiers journaux, vous devez définir les options sur chaque fichier. Selon votre environnement, vous pouvez terminer avec des paramètres différents pour chaque fichier de base de données.

Considérations relatives aux AUTO_SHRINK

AUTO_SHRINKest une option de base de données dans SQL Server. Lorsque vous activez cette option pour une base de données, cette base de données devient éligible pour la réduction par une tâche en arrière-plan. Cette tâche en arrière-plan évalue toutes les bases de données qui répondent aux critères de réduction et de réduction des données ou des fichiers journaux.

Vous devez évaluer soigneusement la définition de cette option pour les bases de données dans un SQL Server instance. Les opérations de croissance et de réduction fréquentes peuvent entraîner divers problèmes de performances.

  • Si plusieurs bases de données font l’objet d’opérations de réduction et de croissance fréquentes, cela entraîne facilement une fragmentation au niveau du système de fichiers. Cela peut avoir un impact grave sur les performances. Cela est vrai que vous utilisiez les paramètres automatiques ou que vous augmentez et réduisez manuellement les fichiers fréquemment.

  • Une AUTO_SHRINK fois que le fichier de données ou le fichier journal a été réduit, une opération DML ou DDL ultérieure peut ralentir considérablement si l’espace est nécessaire et que les fichiers doivent augmenter.

  • La AUTO_SHRINK tâche en arrière-plan peut prendre des ressources lorsque de nombreuses bases de données doivent être réduites.

  • La AUTO_SHRINK tâche en arrière-plan doit acquérir des verrous et d’autres synchronisations qui peuvent entrer en conflit avec d’autres activités d’application régulières.

Envisagez de définir des bases de données sur une taille requise et de les pré-développer. Laissez l’espace inutilisé dans les fichiers de base de données si vous pensez que les modèles d’utilisation de l’application en auront à nouveau besoin. Cela peut empêcher la réduction et la croissance fréquentes des fichiers de base de données.

Considérations relatives à AUTOGROW

  • Si vous exécutez une transaction qui nécessite plus d’espace de journal que ce qui est disponible et que vous avez activé l’option de croissance automatique pour le journal des transactions de cette base de données, le temps nécessaire à l’exécution de la transaction inclut le temps nécessaire au journal des transactions pour augmenter de la quantité configurée. Si l’incrément de croissance est important ou s’il existe un autre facteur qui le fait prendre beaucoup de temps, la requête dans laquelle vous ouvrez la transaction peut échouer en raison d’une erreur de délai d’expiration. Le même type de problème peut résulter d’une croissance automatique de la partie données de votre base de données.

  • Si vous exécutez une transaction volumineuse qui nécessite la croissance du journal, les autres transactions qui nécessitent une écriture dans le journal des transactions devront également attendre la fin de l’opération de croissance.

  • Si vous avez de nombreuses croissances de fichiers dans vos fichiers journaux, vous pouvez avoir un nombre excessivement élevé de fichiers journaux virtuels (VLF). Cela peut entraîner des problèmes de performances liés aux opérations de démarrage/en ligne de la base de données, à la réplication, à la mise en miroir et à la capture de données modifiées (CDC). En outre, cela peut parfois entraîner des problèmes de performances avec les modifications de données.

Remarque

Si vous combinez les options croissance automatique et autoshrink, vous risquez de créer une surcharge inutile. Assurez-vous que les seuils qui déclenchent les opérations de croissance et de réduction ne provoquent pas de changements fréquents de taille. Par exemple, vous pouvez exécuter une transaction qui entraîne une croissance du journal des transactions de 100 Mo au moment de la validation. Quelque temps après, l’autoshrink démarre et réduit le journal des transactions de 100 Mo. Ensuite, vous exécutez la même transaction et le journal des transactions augmente à nouveau de 100 Mo. Dans cet exemple, vous créez une surcharge inutile et potentiellement une fragmentation du fichier journal, ce qui peut affecter négativement les performances.

Si vous augmentez votre base de données par petits incréments, ou si vous la développez puis la réduisez, vous pouvez vous retrouver avec la fragmentation du disque. La fragmentation du disque peut entraîner des problèmes de performances dans certaines circonstances. Un scénario de petits incréments de croissance peut également réduire les performances de votre système.

Dans SQL Server, vous pouvez activer l’initialisation instantanée des fichiers. L’initialisation instantanée des fichiers accélère les allocations de fichiers uniquement pour les fichiers de données. L’initialisation instantanée des fichiers ne s’applique pas aux fichiers journaux. Pour plus d’informations, consultez Initialisation instantanée de fichier de base de données.

Meilleures pratiques pour la croissance automatique et l’autoshrink

  • Pour un système de production managé, vous devez considérer la croissance automatique comme une simple éventualité en cas de croissance inattendue. Ne gérez pas la croissance de vos données et des journaux au quotidien avec la croissance automatique.

  • Vous pouvez utiliser des alertes ou des programmes de surveillance pour surveiller la taille des fichiers et augmenter les fichiers de manière proactive. Cela vous permet d’éviter la fragmentation et de déplacer ces activités de maintenance vers des heures creuses.

  • L’autoshrink et la croissance automatique doivent être soigneusement évalués par un administrateur de base de données (DBA) formé ; Ils ne doivent pas être laissés non gérés.

  • Votre incrément de croissance automatique doit être suffisamment important pour éviter les pénalités de performances répertoriées dans la section précédente. La valeur exacte à utiliser dans votre paramètre de configuration et le choix entre une croissance en pourcentage et une croissance de taille en Mo spécifique dépend de nombreux facteurs dans votre environnement. Une règle générale que vous pouvez utiliser pour les tests consiste à définir votre paramètre de croissance automatique sur environ huit la taille du fichier.

  • Activez le \<MAXSIZE> paramètre pour chaque fichier afin d’éviter qu’un fichier ne se développe au point où il utilise tout l’espace disque disponible.

  • Conservez la taille de vos transactions aussi petite que possible pour empêcher la croissance non planifiée des fichiers.

Pourquoi dois-je m’inquiéter de l’espace disque si les paramètres de taille sont automatiquement contrôlés

  • Le paramètre de croissance automatique ne peut pas augmenter la taille de la base de données au-delà des limites de l’espace disque disponible sur les lecteurs pour lesquels les fichiers sont définis. Par conséquent, si vous vous appuyez sur la fonctionnalité de croissance automatique pour dimensionner vos bases de données, vous devez toujours case activée indépendamment de l’espace disque disponible. Le paramètre de croissance automatique est également limité par le MAXSIZE paramètre que vous sélectionnez pour chaque fichier. Pour réduire le risque de manque d’espace, vous pouvez surveiller le compteur Analyseur de performances SQL Server : Database Object : Data File(s) Size (Ko) et configurer une alerte lorsque la base de données atteint une certaine taille.

  • La croissance non planifiée des fichiers de données ou des fichiers journaux peut prendre de l’espace que d’autres applications attendent d’être disponible et peut entraîner des problèmes pour ces autres applications.

  • L’incrément de croissance de votre journal des transactions doit être suffisamment important pour rester en avance sur les besoins de vos unités de transaction. Même si la croissance automatique est activée, vous pouvez recevoir un message indiquant que le journal des transactions est plein, s’il ne peut pas croître assez rapidement pour répondre aux besoins de votre requête.

  • SQL Server ne teste pas constamment les bases de données qui ont atteint le seuil configuré pour l’autoshrink. Au lieu de cela, il examine les bases de données disponibles et trouve la première qui est configurée pour l’autoshrink. Il vérifie cette base de données et la réduit si nécessaire. Ensuite, il attend plusieurs minutes avant de vérifier la base de données suivante configurée pour l’autoshrink. En d’autres termes, SQL Server ne case activée pas toutes les bases de données à la fois et ne les réduit pas toutes en même temps. Il fonctionne dans les bases de données de manière tourniquet pour échelonner la charge sur une période donnée. Par conséquent, selon le nombre de bases de données que vous avez configurées pour l’autoshrink sur un SQL Server instance particulier, plusieurs heures peuvent s’effectuer entre le moment où la base de données atteint le seuil et la réduction effective.

References