Gérer l’espace des fichiers pour les bases de données dans Azure SQL Managed Instance

S’applique à : Azure SQL Managed Instance

Cet article explique comment surveiller et gérer des fichiers dans les bases de données dans Azure SQL Managed Instance. Nous examinons comment surveiller la taille du fichier de base de données, réduire le journal des transactions, agrandir un fichier journal des transactions et contrôler la croissance d’un fichier journal des transactions.

Cet article s’applique à Azure SQL Managed Instance. Même s’ils sont très similaires, pour plus d’informations sur la gestion de la taille des fichiers journaux de transactions dans SQL Server, consultez Gérer la taille du fichier journal des transactions.

Appréhender les types d’espace de stockage d’une base de données

Il est essentiel d’appréhender les quantités d’espace de stockage suivantes pour gérer l’espace de fichier d’une base de données.

Le schéma suivant illustre la relation entre les différents types d’espace de stockage d’une base de données.

Interroger une base de données unique pour des informations relatives à l’espace de stockage des fichiers

Utilisez la requête suivante sur sys.database_files pour retourner la quantité d’espace de données allouée de la base de données et la quantité d’espace alloué non utilisé. Le résultat de la requête est exprimé en Mo.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Surveiller l’utilisation de l’espace pour le journal

Surveillez l’utilisation de l’espace pour le journal à l’aide de sys.dm_db_log_space_usage. Cette vue de gestion dynamique retourne des informations sur la quantité d’espace journal utilisée et indique à quel moment le journal des transactions a besoin d’être tronqué.

Pour plus d’informations sur la taille actuelle d’un fichier journal, sa taille maximale et l’option de croissance automatique du fichier, vous pouvez également utiliser les colonnes size, max_size et growth de ce fichier journal dans sys.database_files.

Les mesures d’espace de stockage affichées dans les API de mesures basées sur Azure Resource Manager ne mesurent que la taille des pages de données utilisées. Pour obtenir des exemples, consultez get-metrics de PowerShell.

Réduire la taille du fichier journal

Pour réduire la taille physique d’un fichier journal physique en supprimant de l’espace inutilisé, vous devez réduire le fichier journal. Une réduction ne fait une différence que lorsqu’un fichier journal de transactions contient un espace inutilisé. Si le fichier journal est plein, probablement en raison de transactions ouvertes, examinez les causes du blocage de la troncation du journal des transactions.

Soyez conscient du possible impact négatif sur les performances que peut avoir la réduction des fichiers de la base de données. Consultez Maintenance des index après réduction. Dans de rares cas, les opérations de réduction peuvent être affectées par les sauvegardes de base de données automatisées. Si nécessaire, réessayez l’opération de réduction.

Avant de réduire le journal des transactions, gardez à l’esprit les facteurs pouvant retarder la troncation du journal. Si l’espace de stockage est à nouveau nécessaire après une réduction de journal, le journal des transactions croît de nouveau, introduisant une surcharge au niveau des performances pendant les opérations d’accroissement du journal. Pour plus d’informations, consultez les Recommandations.

Vous pouvez réduire un fichier journal uniquement quand la base de données est en ligne, et qu’au moins un fichier journal virtuel est libre. Dans certains cas, la réduction du journal peut n'être possible qu'après la troncation de journal suivante.

Certains facteurs (par exemple, une transaction longue) peuvent maintenir les fichiers journaux virtuels actifs pendant une période de temps prolongée, et peuvent limiter, voire empêcher, la réduction du journal. Pour plus d’informations, consultez Facteurs pouvant retarder la troncation du journal.

La réduction d’un fichier journal supprime un ou plusieurs fichiers journaux virtuels qui ne contiennent aucune partie du journal logique (autrement dit, des fichiers journaux virtuels inactifs). Quand vous réduisez un fichier journal de transactions, les fichiers journaux virtuels inactifs sont supprimés de la fin du fichier journal pour réduire le journal et le ramener à une taille proche de la taille cible.

Pour en savoir plus sur les opérations de réduction, consultez les documents suivants :

Réduire un fichier journal (sans réduire les fichiers de base de données)

• DBCC SHRINKFILE (Transact-SQL)

• Réduire un fichier

Surveiller les événements de réduction du fichier journal

• Log File Auto Shrink Event Class.

Contrôler l’espace pour le journal

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (reportez-vous aux colonnes size, max_size, et growth du fichier journal ou des fichiers journaux.)

Maintenance des index après réduction

Une fois que l’opération de réduction est effectuée sur les fichiers de données, les index peuvent devenir fragmentés. Cela réduit l’efficacité de l’optimisation des performances pour certaines charges de travail, comme les requêtes qui utilisent de grandes analyses. Si la dégradation des performances se produit une fois l’opération de réduction terminée, envisagez la maintenance des index pour reconstruire les index. N’oubliez pas que les reconstructions d’index demandent de l’espace libre dans la base de données, ce qui peut entraîner une augmentation de l’espace alloué et donc un affaiblissement de l’effet de réduction.

Pour plus d’informations sur la maintenance des index, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

Évaluer la densité des pages d’index

Si la troncation des fichiers de données n’entraîne pas une réduction suffisante de l’espace alloué, vous pouvez décider de réduire les fichiers de données pour récupérer l’espace inutilisé de ces fichiers. Toutefois, à titre d’étape facultative mais recommandée, vous devez d’abord déterminer la densité moyenne des pages des index dans la base de données. Pour une même quantité de données, la réduction est plus rapide si la densité des pages est élevée parce qu’elle devra déplacer moins de pages. Si la densité des pages est faible pour certains index, effectuez une maintenance sur ces index pour augmenter la densité des pages avant de réduire les fichiers de données. Cela permet également d’obtenir une réduction plus profonde de l’espace de stockage alloué.

Pour déterminer la densité des pages de tous les index de la base de données, utilisez la requête suivante. La densité des pages est signalée dans la colonne avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Si vous avez des index avec un nombre de pages élevé et avec une densité de page inférieure à 60-70 %, prévoyez de reconstruire ou de réorganiser ces index avant de réduire les fichiers de données.

Si vous avez plusieurs index avec une faible densité de page, vous pourrez peut-être les reconstruire en parallèle sur plusieurs sessions de base de données pour accélérer le processus. Assurez-vous toutefois de ne pas vous approcher des limites des ressources de base de données en le faisant, et laissez suffisamment de marge pour les charges de travail d’application. Surveillez la consommation des ressources (processeur, E/S de données, E/S de journal) dans le portail Azure ou en utilisant la vue sys.dm_db_resource_stats et démarrez d’autres reconstructions en parallèle seulement si l’utilisation des ressources sur chacune de ces dimensions reste considérablement inférieure à 100 %. Si l'utilisation de l'unité centrale, des entrées/sorties de données ou des entrées/sorties de journaux est de 100 %, vous pouvez augmenter l'échelle de la base de données pour avoir plus de cœurs d'unité centrale et augmenter le débit des entrées/sorties, ce qui permet des reconstructions parallèles supplémentaires pour terminer le processus plus rapidement.

Exemple de commande de reconstruction d’index

Voici un exemple de commande pour reconstruire un index et augmenter la densité de ses pages, en utilisant l’instruction ALTER INDEX :

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Cette commande lance une reconstruction d’index en ligne et reprenable. Cela permet aux charges de travail simultanées de continuer à utiliser la table pendant la reconstruction, et à vous de reprendre la reconstruction en cas d’interruption pour une raison ou une autre. Toutefois, ce type de reconstruction est plus lent qu’une reconstruction hors connexion, qui bloque l’accès à la table. Si aucune autre charge de travail n’a besoin d’accéder à la table pendant la reconstruction, définissez les options ONLINE et RESUMABLE sur OFF pour supprimer la clause WAIT_AT_LOW_PRIORITY.

Pour en savoir plus sur la maintenance des index, consultez Optimiser la maintenance des index pour améliorer les performances des requêtes et réduire la consommation des ressources.

Réduire plusieurs fichiers de données

Comme indiqué précédemment, une réduction avec un déplacement des données est un processus de longue haleine. Si la base de données contient plusieurs fichiers de données, vous pouvez accélérer le processus en réduisant plusieurs fichiers de données en parallèle. Pour ce faire, vous devez ouvrir plusieurs sessions de base de données et utiliser DBCC SHRINKFILE sur chaque session avec une valeur file_id différente. Tout comme avec la reconstruction des index vue précédemment, assurez-vous d’avoir suffisamment de marge de ressources (processeur, E/S de données, E/S de journal) avant de commencer chaque nouvelle commande de réduction parallèle.

L’exemple de commande suivant réduit le fichier de données avec file_id 4, en tentant de réduire la taille allouée à 52 000 Mo en déplaçant les pages au sein du fichier :

DBCC SHRINKFILE (4, 52000);

Si vous voulez réduire au minimum possible l’espace alloué au fichier, exécutez l’instruction sans spécifier la taille cible :

DBCC SHRINKFILE (4);

Si une charge de travail s’exécute en même temps qu’une réduction, elle peut commencer à utiliser l’espace de stockage libéré par la réduction avant que la réduction ne se termine et tronque le fichier. Dans ce cas, la réduction ne peut pas réduire l’espace alloué à la cible spécifiée.

Vous pouvez atténuer cela en réduisant chaque fichier en plus petites étapes. Cela signifie que dans la commande DBCC SHRINKFILE, vous définissez une cible légèrement plus petite que l’espace actuellement alloué au fichier. Par exemple, si l’espace alloué à un fichier avec file_id 4 est de 200 000 Mo et que vous voulez le réduire à 100 000 Mo, vous pouvez d’abord définir la cible sur 170 000 Mo :

DBCC SHRINKFILE (4, 170000);

Une fois cette commande terminée, elle aura tronqué le fichier et réduit sa taille allouée à 170 000 Mo. Vous pouvez ensuite répéter cette commande, en définissant d’abord la cible sur 140 000 Mo, puis sur 110 000 Mo, etc., jusqu’à ce que le fichier soit réduit à la taille souhaitée. Si la commande se termine mais que le fichier n’est pas tronqué, utilisez des étapes plus petites, par exemple 15 000 Mo au lieu de 30 000 Mo.

Pour surveiller la progression de la réduction de toutes les sessions de réduction exécutées en simultané, vous pouvez utiliser la requête suivante :

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Une fois que la réduction de tous les fichiers de données est exécutée, utilisez la requête d’utilisation d’espace pour déterminer la réduction résultante de la taille de stockage allouée. S’il existe toujours une grande différence entre l’espace utilisé et l’espace alloué, vous pouvez reconstruire les index. Cela peut augmenter temporairement l’espace alloué. Toutefois, une nouvelle réduction des fichiers de données après la reconstruction des index devrait aboutir à une réduction plus profonde de l’espace alloué.

Agrandir un fichier journal

Dans Azure SQL Managed Instance, ajoutez de l’espace à un fichier journal en agrandissant le fichier journal existant (si l’espace disque le permet). L’ajout d’un fichier journal à la base de données n’est pas pris en charge. Un seul fichier journal de transactions est suffisant, sauf si l’espace pour le journal est insuffisant, et que l’espace disque est également insuffisant sur le volume qui contient le fichier journal.

Pour agrandir le fichier journal, utilisez la clause MODIFY FILE de l’instruction ALTER DATABASE, en spécifiant la syntaxe SIZE et MAXSIZE. Pour plus d’informations, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

Pour plus d’informations, consultez les Recommandations.

Contrôler la croissance d’un fichier journal de transactions

Utilisez l’instruction ALTER DATABASE - Options de fichiers et de groupes de fichiers (Transact-SQL) pour gérer la croissance d’un fichier journal de transactions. Notez ce qui suit :

• Pour changer la taille actuelle du fichier selon les unités Ko, Mo, Go et To, utilisez l’option SIZE.
• Pour changer l’incrément de croissance, utilisez l’option FILEGROWTH. Une valeur 0 indique que la croissance automatique est désactivée et qu'aucun espace supplémentaire n'est autorisé.
• Pour contrôler la taille maximale d’un fichier journal en Ko, Mo, Go et To ou affecter la valeur UNLIMITED à la croissance, utilisez l’option MAXSIZE.

Recommandations

Voici une série de recommandations générales à suivre pendant l’utilisation de fichiers journaux de transactions :

• L’incrément de croissance automatique du journal des transactions, tel que défini par l’option FILEGROWTH, doit être suffisamment grand pour anticiper les besoins des transactions de la charge de travail. L'incrément de croissance d'un fichier journal doit être suffisamment important pour éviter une expansion fréquente. Une bonne approche pour dimensionner correctement un journal des transactions consiste à contrôler la quantité de journal occupée pendant :

  • Le temps nécessaire pour exécuter une sauvegarde complète, étant donné que les sauvegardes de fichier journal ne peuvent pas se produire tant qu’elle n’est pas terminée
  • Le temps nécessaire pour les opérations de maintenance des index les plus volumineux
  • Le temps nécessaire pour exécuter le lot le plus volumineux dans une base de données

• Quand vous définissez la croissance automatique pour les fichiers journaux et de données à l’aide de l’option FILEGROWTH, il peut être préférable de le faire en size plutôt qu’en percentage, pour permettre un meilleur contrôle du taux de croissance, car le pourcentage exprime une quantité en constante augmentation.

  • Dans Azure SQL Managed Instance, l’initialisation instantanée de fichiers peut bénéficier d’événements de croissance du journal des transactions allant jusqu’à 64 Mo. L’incrément par défaut de la taille de croissance automatique pour les nouvelles bases de données est de 64 Mo. Les événements de croissance automatique des fichiers journaux des transactions qui sont supérieurs à 64 Mo ne peuvent pas bénéficier de l’initialisation instantanée des fichiers.
  • En guise de bonne pratique, ne définissez pas l’option FILEGROWTH sur une valeur supérieure à 1 024 Mo pour les journaux des transactions.

• Un incrément de croissance automatique réduit peut générer un nombre excessif de petits fichiers journaux virtuels et réduire le niveau de performance. Pour déterminer la distribution optimale des fichiers journaux virtuels pour la taille actuelle du journal des transactions de toutes les bases de données dans une instance donnée, ainsi que les incréments de croissance pour atteindre la taille nécessaire, consultez ce script pour analyser et corriger les fichiers journaux virtuels, fourni par la SQL Tiger Team.

• Un incrément de croissance automatique élevé peut causer deux problèmes :

  • Un incrément de croissance automatique élevé peut générer une pause dans la base de données pendant que le nouvel espace est alloué, ce qui peut entraîner des délais d'expiration lors des requêtes.
  • Un incrément de croissance automatique élevé peut générer un nombre insuffisant de fichiers journaux virtuels volumineux et affecter également le niveau de performance. Pour déterminer la distribution optimale des fichiers journaux virtuels pour la taille actuelle du journal des transactions de toutes les bases de données dans une instance donnée, ainsi que les incréments de croissance pour atteindre la taille nécessaire, consultez ce script pour analyser et corriger les fichiers journaux virtuels, fourni par la SQL Tiger Team.

• Même si la croissance automatique est activée, vous pouvez recevoir un message indiquant que le journal des transactions est complet, s’il ne peut pas croître suffisamment rapidement pour répondre aux besoins de votre requête. Pour plus d’informations sur le changement de l’incrément de croissance, consultez Options de fichiers et de groupes de fichiers ALTER DATABASE (Transact-SQL).

• Les fichiers journaux peuvent être définis de manière à se réduire automatiquement. Toutefois, cette configuration étant déconseillée, la propriété de base de données auto_shrink est définie sur FALSE par défaut. Si auto_shrink est définie sur la valeur TRUE, la troncation automatique réduit la taille d’un fichier uniquement quand plus de 25 pour cent de son espace est inutilisé.

  • Le fichier est réduit soit à la taille à laquelle seuls 25 % de ce dernier sont inutilisés soit à sa taille d'origine, selon la valeur la plus élevée.
  • Pour plus d’informations sur la modification du paramètre de la propriété auto_shrink, consultez Afficher ou modifier les propriétés d’une base de données et Options ALTER DATABASE SET (Transact-SQL).

Contenu connexe

• Sauvegardes automatisées dans Azure SQL Managed Instance
• Options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL)
• Vue d’ensemble des limites de ressources Azure SQL Managed Instance
• Résoudre des erreurs du journal des transactions avec Azure SQL Managed Instance