SQLShack

Dans cet article, nous discuterons des meilleures pratiques à suivre afin de maintenir la base de données TempDB en bon état et d’empêcher toute croissance inattendue de la base de données, en plus de la procédure qui peut être suivie pour détecter cette croissance inattendue une fois survenue.

Aperçu de TempDB

La base de données TempDB est l’une des bases de données système SQL Server les plus importantes, utilisée pour stocker des objets utilisateur temporaires, tels que les tables temporaires définies par l’utilisateur ou renvoyées par l’exécution de fonctions à valeur de table, les procédures stockées temporaires, les variables de table ou les index.

En plus des objets utilisateur, TempDB sera utilisé pour stocker des objets internes créés par le moteur de base de données SQL Server lors des différentes opérations internes, telles que le tri intermédiaire, le spoulage, l’agrégation ou les opérations de curseur.

La base de données système TempDB est également utilisée pour stocker les versions des lignes afin de prendre en charge les fonctionnalités nécessitant un suivi des modifications effectuées sur les lignes de la table, telles que le niveau d’isolation des snapshots des transactions, les reconstructions d’index en ligne ou la fonction de jeux de résultats actifs multiples.

La base de données TempDB sera supprimée et recréée à chaque redémarrage du service SQL Server, en commençant par une nouvelle copie propre de la base de données. Sur cette base, toutes les objections de l’utilisateur et de la base de données interne stockées sur cette base de données seront automatiquement supprimées lorsque le service SQL Server sera redémarré ou lorsque la session où ces objets créés sera déconnectée. Par conséquent, les opérations de sauvegarde et de restauration ne sont pas disponibles pour la TempDB.

Pourquoi TempDB

La base de données système TempDB joue un rôle important dans le processus de réglage des performances de SQL Server. Parce qu’il est utilisé comme stockage de mise en cache pour stocker différents types d’objets de base de données utilisateur et pour stocker les objets internes du système afin d’accélérer les processus liés au moteur de base de données SQL Server.

En raison de ce rôle essentiel que joue la base de données système TempDB dans l’amélioration des performances globales des instances SQL Server, il est très important d’appliquer les meilleures pratiques de taille initiale, d’auto-croissance et d’emplacement sur les données et les fichiers journaux de la base de données TempDB. L’application de ces meilleures pratiques d’une manière qui s’adapte à votre charge de travail d’instance SQL Server empêchera d’étendre très fréquemment les données de la base de données TempDB et les fichiers journaux, en tenant compte du fait que le processus d’expansion des fichiers est un processus coûteux dans lequel SQL Server Engine demandera de l’espace supplémentaire au système d’exploitation, qui effectuera une mise à zéro sur cet espace avant de l’allouer à SQL Server Engine. Vous pouvez imaginer le temps et les ressources nécessaires à ce processus d’allocation.

Si vous appliquez déjà toutes les meilleures pratiques et qu’une opération de croissance inattendue s’est produite, vous pouvez accélérer le processus d’allocation en utilisant l’option d’initialisation instantanée du fichier de base de données, dans laquelle le système d’exploitation fera confiance au service SQL Server et allouera l’espace requis sans effectuer l’opération de gaspillage de temps et de ressources sur l’espace alloué.

Meilleures pratiques

Un certain nombre de bonnes pratiques de configuration de base de données système TempDB sont recommandées afin d’obtenir les meilleures performances de cette ressource partagée et de protéger votre instance SQL Server de toute croissance inattendue des données de la base de données TempDB ou des fichiers journaux susceptibles de remplir l’espace disque sous-jacent.

Ces configurations incluent:

• Le nombre de fichiers de données TempDB doit correspondre au nombre de processeurs logiques, jusqu’à huit fichiers, sur la machine sur laquelle l’instance SQL Server est installée. Si le nombre de processeurs logiques sur cette machine est supérieur à huit, définissez le nombre de fichiers de données sur huit, avec la possibilité de l’étendre par plusieurs de 4 au cas où vous auriez un problème de conflit TempDB
• Assurez-vous que les fichiers TempDB ont une taille égale dans le même groupe de fichiers pour fournir le plus haut degré possible d’efficacité des opérations parallèles, en raison du mécanisme d’écriture de remplissage proportionnel utilisé pour écrire les données dans les fichiers de données. Pour plus d’informations, vérifiez Comprendre l’algorithme de remplissage proportionnel SQL Server
• Configurez les données de la base de données TempDB et les fichiers journaux avec une taille initiale raisonnable et des quantités de croissance automatique qui correspondent à la quantité attendue de données qui seront écrites sur ces fichiers, et les différents types de charges de travail effectuées dans votre instance SQL Server, y compris les activités utilisateur et les opérations de maintenance d’index, afin d’éviter les petites quantités d’incrément fréquentes qui affectent les performances globales de l’instance
• Localisez les fichiers TempDB sur un sous-système d’E/ S rapide afin d’éviter tout Goulots d’étranglement d’E/S
• Localisez la TempDB sur un lecteur de disque séparé, loin des bases de données utilisateur et des autres bases de données système. De cette façon, les opérations de lecture et d’écriture lourdes effectuées sur TempDB n’affecteront pas ou ne seront pas affectées par la charge de travail des bases de données utilisateur, améliorant les performances globales de l’instance SQL Server

Pour plus d’informations sur les nouvelles améliorations des performances de TempDB, consultez le document de base de données TempDB.

Où appliquer

Avant la version SQL Server 2016, l’allocation de taille TempDB peut être effectuée après l’installation de l’instance SQL Server, à partir de la page Propriétés de la base de données. Où vous pouvez contrôler le nombre de fichiers de données de base de données qui correspondent à l’instance SQL Server actuelle, la taille initiale de chaque fichier de données de base de données et de journal, la valeur appropriée pour l’option de croissance automatique et l’emplacement des données de base de données et des fichiers journaux.

Pour effectuer ces modifications, développez le nœud Bases de données dans SQL Server Management Studio, développez le nœud Bases de données système, puis cliquez avec le bouton droit sur la base de données tempdb et choisissez l’option Propriétés comme ci-dessous:

Dans la fenêtre Propriétés de la base de données ouverte, parcourez la page Fichiers et vérifiez ou modifiez les différents paramètres des données de la base de données et des fichiers journaux indiqués ci-dessous:

À partir de la version SQL Server 2016, toutes ces modifications, avec les meilleures pratiques qui correspondent aux ressources de serveur actuelles déjà suggérées par l’assistant d’installation, peuvent être vérifiées et exécutées pendant le processus d’installation de l’instance SQL Server, à l’aide de l’Assistant d’installation SQL Server, sous Configurations de moteur de base de données/TempDB, comme indiqué ci-dessous:

Suivre la croissance de TempDB

Le suivi de la croissance des fichiers de base de données TemDB et des requêtes consommant les ressources TempDB peut être effectué à l’aide des compteurs de Surveillance des performances associés aux fichiers TempDB et de l’outil SQL Profiler qui écoute un type de charge de travail de base de données spécifique.

Un moyen plus convivial pour l’administrateur de base de données de suivre la croissance des fichiers de base de données TempDB consiste à interroger les vues de gestion dynamiques suivantes:

• sys.dm_db_file_space_usage qui renvoie les informations d’utilisation de l’espace pour chaque fichier de la base de données, sans montrer quelle session ou tâche a consommé cet espace
• sys.dm_db_session_space_usage qui renvoie le nombre de pages allouées et désallouées par chaque session
• sys. dm_db_task_space_usage qui renvoie le nombre de pages allouées et désallouées par chaque tâche

Notez que le résultat renvoyé par le système. dm_db_session_space_usage DMV résume le résultat renvoyé par le système.dm_db_task_space_usage DMV lorsque toutes les tâches sont terminées avec succès, en raison du fait que plusieurs tâches liées à la même requête seront exécutées sous la même session. Un autre avantage de ces DMV est qu’il affichera le type d’objet qui consomme les ressources TempDB et si cet objet est un objet utilisateur, tel qu’une table temporaire ou variable ou un objet interne, tel que la maintenance d’index ou l’opération de tri. Ainsi, en fonction du nombre de pages consommées par chaque objet, vous pouvez spécifier si TempDB est consommé par un objet interne ou utilisateur et optimiser ce consommateur.

sys.dm_db_file_space_usage

Le DMV sys.dm_db_file_space_usage peut être utilisé pour surveiller l’espace disque utilisé dans les fichiers de base de données TempDB. Les requêtes ci-dessous montrent l’espace non alloué, l’espace utilisé pour stocker les versions de lignes, l’espace consommé par les objets internes et enfin l’espace consommé par les objets utilisateur, comme suit:

À partir du résultat, qui est renvoyé par mon instance SQL Server locale, vous pouvez voir qu’il y a 60 Mo inutilisés dans les fichiers TempDB, avec seulement 0,5 Mo utilisé pour les objets internes et 1,5 Mo utilisé pour les objets utilisateur, comme indiqué ci-dessous:

sys.dm_db_task_space_usage et sys.dm_db_session_space_usage

sys.dm_db_task_space_usage et sys.dm_db_session_space Les DMV sont utilisés pour vérifier le nombre de pages allouées et désalloué par chaque tâche ou session dans la base de données TempDB. De cette façon, vous pourrez voir quel utilisateur ou quel objet interne consomme l’espace TempDB.

Par exemple, le résultat ci-dessous est renvoyé par l’interrogation du système.dm_db_task_space_usage DMV sous la base de données TempDB, où il renverra le nombre de pages allouées et désallouées pour chaque utilisateur ou tâche interne, comme suit:

Mais le DBA ne nécessitera pas autant de détails pour commencer à suivre la requête qui consomme les ressources TempDB. Nous devons donc résumer le résultat précédent pour chaque session, en utilisant la requête ci-dessous:

Et le résultat de consommation de pages résumées pour chaque session sera comme ci-dessous:

Le même résultat peut être renvoyé en interrogeant le système.dm_db_session_space_usage DMW, qui résume l’allocation et la désallocation des pages pour toutes les tâches exécutées sous la même session, comme indiqué ci-dessous:

Effectuons quelques actions sur la base de données TempDB en utilisant à la fois des objets internes et des objets utilisateur comme ci-dessous:

exécutent ensuite la requête consolidée ci-dessous, qui renvoie le nombre de pages allouées et désallouées par les objets internes et utilisateur, et affiche les deux opérations terminées (à l’aide de sys.dm_db_session_space_usage DMV) et les opérations inachevées (en utilisant sys.dm_db_task_space_usage DMV):

Et le résultat des opérations précédentes reflétera la sortie de la requête, indiquant le nombre de pages allouées et désallouées en créant la table temporaire de l’utilisateur et en triant les données pendant le processus de reconstruction de l’index, comme indiqué ci-dessous:

Pour obtenir le texte de la requête qui consomme les ressources TempDB, vous pouvez utiliser vos compétences de développement SQL en joignant le résultat du DMV ys.dm_db_task_space_usage avec d’autres DMV tels que sys.dm_exec_requests et sys. dm_exec_sessions avec la fonction de gestion dynamique sys.dm_exec_sql_text, où il affichera les requêtes qui consomment l’espace TempDB maintenant, comme dans cette requête.

Le résultat sera similaire à celui ci-dessous:

• Auteur
• Articles récents

Ahmad Yaseen est un ingénieur Big Data de Microsoft possédant une connaissance approfondie et une expérience dans les domaines de la BI SQL, de l’Administration et du développement de bases de données SQL Server.
Il est Expert en Solutions Certifiées Microsoft en Gestion et Analyse de Données, Associé de Solutions Certifiées Microsoft en Administration et Développement de Bases de Données SQL, Associé Développeur Azure et Formateur Certifié Microsoft.
De plus, il contribue avec ses conseils SQL dans de nombreux blogs.
Voir tous les messages de Ahmad Yaseen

Derniers articles par Ahmad Yaseen (tout voir)

• Questions et réponses d’entrevue Azure Data Factory – 11 février 2021
• Comment surveiller Azure Data Factory – 15 janvier 2021
• Utilisation du contrôle de source dans Azure Data Factory – Janvier 12, 2021