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

Article
12/14/2024

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

Résumé

Les paramètres de croissance automatique et d’autoshrink 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 ces paramètres. Cet article fournit des informations générales pour vous aider à définir ces paramètres pour votre environnement.

Voici quelques éléments à prendre en compte si vous décidez d’ajuster vos paramètres de croissance automatique et d’autoshrink.

Comment faire configurer les paramètres

Vous pouvez configurer ou modifier les paramètres de croissance automatique et autoshrink à l’aide de l’une des options suivantes :
- SQL Server Management Studio
- Une instruction ALTER DATABASE
  - Utiliser des options de fichier et de groupe de fichiers pour modifier les paramètres de croissance automatique
  - Utilisez les options SET pour configurer AUTO_SHRINK les paramètres.
Note

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

Vous pouvez également configurer l’option de 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' ]
```
N’oubliez pas 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 données et/ou fichiers journaux, vous devez définir les options sur chaque fichier. Selon votre environnement, vous pouvez se terminer par des paramètres différents pour chaque fichier de base de données.

Considérations relatives aux AUTO_SHRINK

AUTO_SHRINK est 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 à 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 fichiers journaux ou de données.

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

Si plusieurs bases de données subissent des 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 si vous utilisez les paramètres automatiques ou si vous augmentez et réduisez manuellement les fichiers fréquemment.
Après AUTO_SHRINK avoir réduit correctement les données ou le fichier journal, une opération DML ou DDL ultérieure peut ralentir considérablement si l’espace est nécessaire et les fichiers doivent croître.
La AUTO_SHRINK tâche en arrière-plan peut prendre des ressources lorsqu’il existe de nombreuses bases de données nécessitant une réduction.
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 développer au préalable. 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 besoin à nouveau. Cela peut empêcher une réduction et une 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 journal que 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 à la fin de la transaction inclut le temps nécessaire au journal des transactions pour augmenter d’après la quantité configurée. Si l’incrément de croissance est important ou qu’il y a un autre facteur qui l’entraîne pendant un certain temps, la requête dans laquelle vous ouvrez la transaction peut échouer en raison d’une erreur de délai d’attente. 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 que le journal augmente, d’autres transactions nécessitant une écriture dans le journal des transactions doivent également attendre que l’opération de croissance se termine.
Si vous avez de nombreuses croissances de fichiers dans vos fichiers journaux, vous pouvez avoir un trop grand nombre 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.

Note

Si vous combinez les options autogrow et autoshrink, vous pouvez 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 modifications fréquentes de taille de taille supérieure et descendante. Par exemple, vous pouvez exécuter une transaction qui entraîne la croissance du journal des transactions de 100 Mo au moment où elle est validée. Un certain temps après que 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 de 100 Mo. Dans cet exemple, vous créez une surcharge inutile et potentiellement une fragmentation du fichier journal, dont l’une peut affecter négativement les performances.

Si vous développez votre base de données par petits incréments, ou si vous la développez, puis réduisez-la, vous pouvez finir par la fragmentation du disque. La fragmentation de 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 sur votre système.

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

Meilleures pratiques pour la croissance automatique et l’autoshrink

Pour un système de production managé, vous devez envisager la croissance automatique pour être simplement une urgence pour une croissance inattendue. Ne gérez pas la croissance de vos données et journaux quotidiennement avec la croissance automatique.
Vous pouvez utiliser des alertes ou des programmes de surveillance pour surveiller les tailles de fichiers et développer des fichiers de manière proactive. Cela vous permet d’éviter la fragmentation et vous permet de déplacer ces activités de maintenance en heures creuses.
Autoshrink et autogrow doivent être soigneusement évalués par un administrateur de base de données formé (DBA) ; Ils ne doivent pas être laissés non gérés.
Votre incrément de croissance automatique doit être suffisamment grand 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 de 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 de chaque fichier pour empêcher tout fichier de croître jusqu’à un point où il utilise tout l’espace disque disponible.
Conservez la taille de vos transactions aussi petite que possible pour empêcher la croissance des fichiers non planifiés.

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 vérifier indépendamment 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 la possibilité de manquer d’espace, vous pouvez surveiller le compteur Analyseur de performances SQL Server : Databases 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 s’attendent à être disponibles et peuvent provoquer ces autres applications à rencontrer des problèmes.
L’incrément de croissance de votre journal des transactions doit être suffisamment grand pour rester à l’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 suffisamment 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 autoshrink. Au lieu de cela, il examine les bases de données disponibles et trouve le premier configuré pour autoshrink. Elle vérifie que la 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 autoshrink. En d’autres termes, SQL Server ne vérifie pas toutes les bases de données en même temps et les réduit en même temps. Il fonctionnera à travers les bases de données d’une mode tourniquet pour décaler la charge sur une période de temps. Par conséquent, selon le nombre de bases de données que vous avez configurées pour autoshrink sur une instance SQL Server particulière, il peut prendre plusieurs heures à compter du moment où la base de données atteint le seuil jusqu’à ce qu’elle diminue réellement.

Partager via