Configurer les paramètres tempdb pour Azure SQL Managed Instance

S’applique à :Azure SQL Managed Instance

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

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

• Nombre de fichiers tempdb
• Incrément de croissance des fichiers tempdb
• Taille maximale tempdb

Les paramètres tempdb sont conservés une fois votre instance redémarrée, mise à jour ou en cas de 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 de tempdb est identique à celle de n’importe quelle autre structure de base de données utilisateur. La différence est que, puisque tempdb est utilisé pour le stockage non durable, les transactions sont journalisées minimalement.

tempdb ne peut pas être supprimé, détaché, mis hors connexion, renommé ou restauré. Tenter l’une de ces opérations retourne une erreur. tempdb est régénéré lors de chaque démarrage de l’instance. Les objets qui ont peut-être été créés pendant tempdb 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 la concurrence est extrêmement élevée. Il n’y en a qu’une tempdb pour chaque instance managée SQL. Même si plusieurs bases de données et applications se connectent à l’instance, elles utilisent toutes la même base de données tempdb. Les services peuvent rencontrer une contention lorsqu’ils tentent d’allouer des pages dans un tempdb fortement sollicité. Selon le degré de contention, les requêtes et les demandes qui impliquent tempdb peuvent ne plus répondre. C’est pourquoi tempdb est essentiel aux performances du service.

Nombre de fichiers tempdb

L'augmentation du nombre de fichiers de données tempdb crée une ou plusieurs pages GAM et SGAM pour chaque fichier de données, ce qui contribue à améliorer la concurrence tempdb et à réduire les conflits de pages PFS. Toutefois, l’augmentation du nombre de fichiers de données tempdb peut avoir d’autres conséquences sur les performances. Par conséquent, effectuez des tests approfondis avant d’implémenter en production.

Par défaut, Azure SQL Managed Instance crée 12 tempdb fichiers de données et un tempdb fichier journal, 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 ne respecte pas la casse, avec un maximum de 16 caractères et aucun espace.
• Nombre maximal de fichiers tempdb est de 128.

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.

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

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

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

ALTER DATABASE tempdb REMOVE FILE [file_name]

Incrément de croissance

La croissance des fichiers tempdb peut avoir un impact sur les performances des requêtes à l’aide de tempdb. Par conséquent, tempdb les incréments de croissance des fichiers de données 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 des 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 tempdb les fichiers de données et 64 Mo pour tempdb les fichiers journaux, 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 identiques pour tous les tempdb fichiers de données. Sinon, l’algorithme round-robin qui alloue des pages pourrait ê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.

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

Taille maximale

tempdbLa taille est la taille de somme de tous tempdb les fichiers. La taille de fichier tempdb est un espace alloué (zéro) pour ce fichier tempdb. La taille de fichier initiale pour tous les tempdb fichiers est de 16 Mo, qui correspond à la taille de tous les tempdb fichiers 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 incréments de croissance configurés.

L’espace utilisé tempdb est la somme de l’espace utilisé de tous les fichiers tempdb. tempdb l’espace utilisé est égal à la partie de la taille de ce tempdb fichier qui est occupée par des informations non nulles. La somme de tempdbl’espace utilisé et tempdbde l’espace libre est égale à la tempdb taille.

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

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

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 la commande suivante :

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 au-delà de laquelle votre tempdb ne peut plus croître.

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-1920 Go). La taille maximale du tempdb 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. Le stockage réservé est donc partagé entre tempdb d’autres bases de données jusqu’à la taille de stockage maximale allouée à l’instance. La taille maximale du tempdb fichier journal est de deux To.

Les fichiers tempdb augmentent jusqu’à ce qu’ils atteignent la limite maximale autorisée par le niveau de service ou par la taille maximale de fichier 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.

USE tempdb
SELECT name, max_size FROM sys.database_files

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

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

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

Limites de tempdb

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

Contenu connexe

• Démarrage rapide : Créer Azure SQL Managed Instance
• Comparaison des fonctionnalités : Azure SQL Database et Azure SQL Managed Instance
• architecture de connectivité pour Azure SQL Managed Instance
• Migration sql Managed Instance à l’aide de Database Migration Service
• Surveiller les charges de travail Azure SQL avec l’observateur de base de données (aperçu)
• Tarification de SQL Database