Configurer les paramètres tempdb pour Azure SQL Managed Instance

S’applique à :Azure SQL Managed Instance

Cet article vous apprend à configurer vos tempdb paramètres pour Azure SQL Managed Instance.

Azure SQL Managed Instance vous permet de configurer les éléments suivants :

• Nombre de tempdb fichiers
• L'incrément de croissance des tempdb fichiers
• Taille tempdb maximale

Les paramètres tempdb persistent une fois que votre instance a redémarré, est mise à jour ou s'il existe un basculement.

Vue d’ensemble

tempdb est l'une des bases de données système par défaut qui est fournie avec Azure SQL Managed Instance. La structure d'une tempdb autre base de données utilisateur est la même que toute autre structure de base de données utilisateur. La différence est qu'étant donné que tempdb est utilisé pour le stockage non modifiable, les transactions sont journalisées au minimum.

tempdb ne peut pas être supprimé, détaché, mis hors connexion, renommé ou restauré. La tentative d'exécution d'une de ces opérations retourne une erreur. tempdb est régénéré lors de chaque démarrage de l'instance de serveur et tous les objets qui peuvent avoir été créés dans tempdb pendant une session précédente ne sont pas conservés lorsque le service redémarre, après une opération de gestion des mises à jour d'instance ou un basculement.

La charge de travail dans tempdb diffère des charges de travail dans d'autres bases de données utilisateur ; les objets et les données sont fréquemment créés et détruits et il existe une concurrence extrêmement élevée. Il n'y en a qu'une tempdb pour chaque instance managée. Même si vous avez plusieurs bases de données et applications qui se connectent à l'instance, elles utilisent toutes la même base de données tempdb. Les services peuvent rencontrer une contention lorsqu'ils essaient d'allouer des pages dans une tempdb à utilisation intensive. Selon le degré de contention, les requêtes et les requêtes qui impliquent tempdb pourraient ne plus répondre. C'est pourquoi tempdb est essentiel pour les performances du service.

Nombre de tempdb fichiers

L'augmentation du nombre de fichiers de données de tempdb crée une ou plusieurs pages GAM et SGAM pour chaque fichier de données, ce qui permet d'améliorer la concurrence de tempdb et de réduire la contention de pages PFC. Toutefois, l'augmentation du nombre de fichiers de données tempdb peut avoir d'autres implications en matière de performances. Vous devez donc tester soigneusement avant l'implémentation en production.

Par défaut, Azure SQL Managed Instance crée 12 fichiers de données tempdb et 1 fichier journal tempdb, mais il est possible de modifier cette configuration.

La modification du nombre de fichiers tempdb présente les limitations suivantes :

• Le nom logique du nouveau fichier n'est pas sensible à la casse, il comporte au maximum 16 caractères et ne comporte pas d'espaces.
• Le nombre maximal de fichiers tempdb est de 128.

Notes

Il n'est pas nécessaire de redémarrer le serveur après avoir ajouté de nouveaux fichiers ; cependant, les fichiers les plus vides seront remplis avec une priorité plus élevée et l'algorithme de répartition uniforme pour l'allocation des pages sera perdu jusqu'à ce que le système soit rééquilibré.

Vous pouvez utiliser SQL Server Management Studio (SSMS) et Transact-SQL (T-SQL) pour modifier le nombre de fichiers pour tempdb dans Azure SQL Managed Instance.

SQL Server Management Studio (SSMS)

Vous pouvez utiliser SQL Server Management Studio (SSMS) pour modifier le nombre de fichiers tempdb. Pour ce faire, procédez comme suit :

1. Connectez-vous à votre instance managée dans SSMS.

2. Développez les bases de données dans l'explorateur d'objets, puis développez les bases de données système.

3. Faites un clic droit sur tempdb et sélectionnez Propriétés.

4. Sélectionnez Fichiers sous Sélectionner une page pour afficher le nombre existant de fichiers tempdb.

5. Pour ajouter un fichier, choisissez Ajouter, puis fournissez des informations sur le nouveau fichier de données dans la ligne.

   

6. Pour supprimer un fichier tempdb, choisissez le fichier que vous souhaitez supprimer de la liste des fichiers de base de données, puis sélectionnez Supprimer.

Transact-SQL (T-SQL)

Vous pouvez utiliser Transact-SQL (T-SQL) pour vérifier le nombre de fichiers tempdb existants et ajouter ou supprimer des fichiers.

Pour compter le nombre de tous les fichiers tempdb existants, utilisez la commande suivante :

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files

Pour compter uniquement le nombre de tous les fichiers de données tempdb, utilisez la commande suivante :

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files where type = 0

Utilisez la commande suivante pour ajouter un nouveau fichier tempdb :

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name')

Pour supprimer un fichier tempdb existant, utilisez la commande suivante :

ALTER DATABASE tempdb REMOVE FILE [file_name]

Incrément de croissance

La croissance du fichier tempdb peut avoir un impact sur les performances des requêtes utilisant tempdb. Par conséquent, les incréments de croissance des fichiers de données tempdb trop petits peuvent entraîner une fragmentation de l'étendue, tandis que les incréments trop volumineux peuvent entraîner une croissance lente ou un échec de croissance s'il n'y a pas suffisamment d'espace pour que la croissance se produise. La valeur optimale pour les incréments de croissance de fichiers tempdb dépend de votre charge de travail.

Les incréments de croissance par défaut pour SQL Managed Instance sont de 254 Mo pour les fichiers de données tempdb et 64 Mo pour les fichiers journaux tempdb, mais vous pouvez configurer des incréments de croissance pour s'adapter à votre charge de travail et régler vos performances.

Tenez compte des éléments suivants :

• Le paramètre de croissance de fichier prend en charge les unités suivantes pour int_growth_increment : Ko, Mo, Go, To et %.
• Les incréments de croissance doivent être les mêmes pour tous les fichiers de données tempdb. Dans le cas contraire, l'algorithme de répartition uniforme qui alloue des pages peut être affecté.

Vous pouvez utiliser SQL Server Management Studio (SSMS) et Transact-SQL (T-SQL) pour modifier l'incrément de croissance de vos fichiers tempdb.

SQL Server Management Studio (SSMS)

Vous pouvez utiliser SQL Server Management Studio (SSMS) pour modifier l'incrément de croissance des fichiers tempdb. Pour ce faire, procédez comme suit :

1. Connectez-vous à votre instance managée dans SSMS.

2. Développez les bases de données dans l'explorateur d'objets, puis développez les bases de données système.

3. Faites un clic droit sur tempdb et sélectionnez Propriétés.

4. Sélectionnez Fichiers sous Sélectionner une page pour afficher le nombre existant de fichiers tempdb.

5. Choisissez les points de suspension (...) en regard d'un fichier de données pour ouvrir la fenêtre de boîte de dialogue Modifier les propriétés de croissance automatique.

6. Cochez la case en regard d'Activer la croissance automatique, puis modifiez vos paramètres de croissance automatique en spécifiant les valeurs de croissance de fichier, en pourcentage ou en mégaoctets.

   

7. Sélectionnez OK pour enregistrer vos paramètres.

Transact-SQL (T-SQL)

Utilisez la commande T-SQL suivante pour modifier les incréments de croissance de vos fichiers de données tempdb :

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])

Taille maximale

tempdbtaille est la taille de somme de tous les fichiers tempdb. La taille du fichier tempdb est un espace alloué (zéro) pour ce fichier tempdb. La taille de fichier initiale de tous les fichiers tempdb est de 16 Mo, qui correspond à la taille de tous les fichiers tempdb lorsque l'instance redémarre ou bascule. Une fois que l'espace utilisé d'un fichier de données tempdb atteint la taille du fichier, tous les fichiers de données tempdb augmentent automatiquement par leurs incréments de croissance configurés.

tempdbl'espace utilisé est la somme de l'espace utilisé de tous les fichiers tempdb. L'espace utilisé du fichier tempdb est égal à la partie de la taille de ce fichier tempdb occupée avec des informations non nulles. La somme de l'tempdbespace utilisé et de l'tempdbespace libre est égale à la taille tempdb.

Vous pouvez utiliser T-SQL pour déterminer l'espace actuel utilisé et libre pour vos fichiers tempdb.

Pour obtenir de l'espace utilisé, de l'espace libre et de la taille de vos fichiers de données tempdb, exécutez cette commande :

USE tempdb
SELECT SUM((allocated_extent_page_count)*1.0/128) AS TempDB_used_data_space_inMB, 
	SUM((unallocated_extent_page_count)*1.0/128) AS TempDB_free_data_space_inMB, 
	SUM(total_page_count*1.0/128) AS TempDB_data_size_inMB 
FROM sys.dm_db_file_space_usage

La capture d’écran suivante présente un exemple de cette opération :

Pour obtenir l'espace utilisé, l'espace libre et la taille de vos fichiers journaux tempdb, exécutez cette commande :

USE tempdb
SELECT used_log_space_in_bytes*1.0/1024/1024 AS TempDB_used_log_space_inMB,
     (total_log_size_in_bytes- used_log_space_in_bytes)*1.0/1024/1024 AS TempDB_free_log_space_inMB,
     total_log_size_in_bytes*1.0/1024/1024 AS TempDB_log_size_inMB
FROM sys.dm_db_log_space_usage

La capture d’écran suivante présente un exemple de cette opération :

La taille maximale tempdb est la limite après laquelle votre tempdb ne peut pas croître davantage.

La taille maximale tempdb dans SQL Managed Instance présente les limitations suivantes :

• Dans le niveau de service Usage général, la taille maximale pour tempdb est limitée à 24 Go/vCore (96 à 1 920 Go) et celle du fichier journal est de 120 Go.
• Dans le niveau de service Critique pour l'entreprise, tempdb concurrence d'autres bases de données pour les ressources, de sorte que le stockage réservé est partagé entre tempdb et d'autres bases de données. La taille maximale du fichier journal tempdb est de 2 To.

Les fichiers tempdb augmentent jusqu'à atteindre la limite maximale autorisée par le niveau de service, ou par la taille de fichier maximale tempdb configurée manuellement.

Vous pouvez utiliser SQL Server Management Studio (SSMS) et Transact-SQL (T-SQL) pour modifier la taille maximale de vos fichiers tempdb.

SQL Server Management Studio (SSMS)

Pour déterminer votre taille maximale tempdb actuelle dans SSMS, procédez comme suit :

1. Connectez-vous à votre instance managée dans SSMS.
2. Développez les bases de données dans l'explorateur d'objets, puis développez les bases de données système.
3. Faites un clic droit sur tempdb et sélectionnez Propriétés.
4. Dans la page Général, vérifiez la valeur Taille sous Base de données pour déterminer votre taille tempdb maximale. La valeur -1 indique que la taille maximale tempdb est illimitée.

Pour modifier votre taille maximale actuelle tempdb dans SSMS, procédez comme suit :

1. Connectez-vous à votre instance managée dans SSMS.
2. Développez les bases de données dans l'explorateur d'objets, puis développez les bases de données système.
3. Faites un clic droit sur tempdb et sélectionnez Propriétés.
4. Sélectionnez Fichiers sous Sélectionner une page pour afficher le nombre existant de fichiers tempdb.
5. Choisissez les points de suspension (...) en regard d'un fichier de données pour ouvrir la fenêtre de boîte de dialogue Modifier les propriétés de croissance automatique.
6. Modifiez vos paramètres de taille maximale tempdb en modifiant les valeurs sous Taille maximale du fichier.
7. Sélectionnez OK pour enregistrer vos paramètres.

Transact-SQL (T-SQL)

Pour déterminer la taille maximale tempdb, exécutez cette commande :

USE tempdb
SELECT name, max_size FROM sys.database_files

Pour obtenir la taille tempdb totale en Mo, exécutez cette commande :

USE tempdb
SELECT (SUM(size)*1.0/128) AS TempDB_size_InMB FROM sys.database_files

La capture d'écran ci-dessous présente un exemple de sortie :

Pour définir la taille maximale d'un nouveau fichier de données tempdb, exécutez la commande suivante :

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', MAXSIZE = int_maxsize[KB|MB|GB|TB])

Pour modifier la taille maximale d'un fichier existant tempdb, exécutez la commande suivante :

ALTER DATABASE tempdb MODIFY FILE (NAME = file_name, MAXSIZE = int_maxsize[KB|MB|GB|TB])

limites tempdb

Le tableau suivant définit les limites des différents paramètres de configuration tempdb :

Paramètre de configuration	Valeurs
Noms logiques de fichiers tempdb	16 caractères au maximum
Nombre de tempdb fichiers	128 fichiers maximum
Nombre de fichiers tempdb par défaut	13 (1 fichier journal + 12 fichiers de données)
Taille initiale des fichiers de données tempdb	16 Mo
Incrément de croissance par défaut des fichiers de données tempdb	256 octets
Taille initiale des fichiers journaux tempdb	16 Mo
Incrément de croissance par défaut des fichiers journaux tempdb	64 Mo
Taille initiale maximale tempdb	-1 (illimitée)
Taille maximale de tempdb	Jusqu'à la taille de stockage

Étapes suivantes

• Pour savoir comment créer votre première instance managée, consultez le Guide de démarrage rapide.
• Pour consulter la liste des fonctionnalités et les comparer, consultez Fonctionnalités SQL communes.
• Pour plus d’informations sur la configuration du réseau virtuel, consultez Configuration de réseau virtuel SQL Managed Instance.
• Pour obtenir un guide de démarrage rapide qui crée une instance managée et restaure une base de données à partir d’un fichier de sauvegarde, consultez Créer une instance managée.
• Pour accéder à un tutoriel expliquant comment utiliser Azure Database Migration Service pour la migration, consultez Migration SQL Managed Instance à l’aide d’Azure Database Migration Service.
• Pour une supervision avancée des performances de base de données SQL Managed Instance avec des informations de dépannage intégrées, consultez Superviser Azure SQL Managed Instance avec Azure SQL Analytics.
• Pour plus d’informations sur la tarification, voir Tarification SQL Database.