Planification des capacités de tempdb
Cette rubrique fournit des instructions pour déterminer la quantité d'espace disque requise par tempdb. Cette rubrique comporte aussi des recommandations sur la configuration de tempdb en vue de l'optimisation de ses performances dans un environnement de production, ainsi que des informations sur la façon de surveiller l'utilisation de l'espace de tempdb.
Mode d'utilisation de tempdb
La base de données système tempdb constitue une ressource globale accessible par tous les utilisateurs connectés à une instance SQL Server. La base de données tempdb est utilisée pour stocker les objets suivants : objets utilisateur, objets internes et banques de versions.
Objets utilisateur
Les objets utilisateur sont explicitement créés par l'utilisateur. Ces objets peuvent se trouver dans l'étendue d'une session utilisateur ou dans celle de la routine au sein de laquelle l'objet est créé. Une routine peut consister en une procédure stockée, un déclencheur ou une fonction définie par l'utilisateur. Les objets utilisateur peuvent être l'un des éléments suivants :
Tables définies par l'utilisateur et index
Tables système et index
les tables temporaires globales et les index ;
Tables temporaires locales et index
Variables de tables
Tables renvoyées dans les fonctions table
Objets internes
Les objets internes sont créés, si nécessaire, par le moteur de base de données SQL Server pour traiter les instructions SQL Server. Les objets internes sont créés et supprimés au sein de l'étendue d'une instruction. Les objets internes peuvent être l'un des éléments suivants :
les tables de travail des opérations de curseur ou de mise en attente et le stockage temporaire d'objets LOB ;
les fichiers de travail correspondant aux opérations de jointures ou d'agrégations hachées ;
les résultats de tris intermédiaires pour les opérations de création ou de reconstruction d'index (si SORT_IN_TEMPDB est spécifié) ou pour certaines requêtes GROUP BY, ORDER BY ou UNION.
Chaque objet interne utilise un minimum de neuf pages, une page IAM et une extension composée de huit pages. Pour plus d'informations sur les pages et les extensions, consultez Fonctionnement des pages et étendues.
Banques de versions
Une banque de versions constitue un ensemble de pages de données contenant les lignes de données requises pour prendre en charge les fonctionnalités qui utilisent le contrôle des versions de ligne. Il existe deux banques de versions : une banque de versions commune et une banque de versions de construction d'index en ligne. Les banques de versions contiennent les éléments suivants :
les versions de lignes générées par les transactions de modification des données dans une base de données utilisant les niveaux d'isolement de versions de ligne instantanée ou d'isolement de versions de ligne en lecture validée ;
Versions de ligne qui sont générées par les transactions de modification de données pour les fonctionnalités telles que : opérations d'index en ligne, MARS (Multiple Active Result Sets) et déclencheurs AFTER.
Le tableau suivant répertorie les fonctionnalités SQL Server qui créent des objets utilisateur, des objets internes ou des versions de lignes dans tempdb. Chaque fois que cela est possible, les méthodes d'évaluation de l'utilisation de l'espace disque sont fournies.
Caractéristique |
Utilisation de tempdb |
Autres informations |
|---|---|---|
Opérations en bloc avec activation des déclencheurs |
Les optimisations d'importation en bloc sont disponibles lorsque les déclencheurs sont activés. SQL Server utilise le contrôle des versions de ligne pour les déclencheurs qui mettent à jour ou suppriment les transactions. Une copie de chaque ligne supprimée ou mise à jour vient s'ajouter à la banque de versions. Voir plus bas la section « Déclencheurs ». |
|
Requêtes d'expressions de table communes |
Une expression de table commune peut être considérée comme un ensemble de résultats provisoire défini dans l'étendue d'exécution d'une seule instruction SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Lorsque le plan de requête d'une requête d'expression de table commune utilise un opérateur Spool pour enregistrer des résultats de requêtes intermédiaires, le moteur de base de données crée une table de travail dans tempdb pour prendre en charge cette opération. |
|
Curseurs |
Les curseurs pilotés par jeux de clés et les curseurs statiques utilisent les tables de travail créées dans tempdb. Les curseurs pilotés par jeux de clés utilisent les tables de travail pour y stocker le jeu de clés identifiant les lignes d'un curseur. Les curseurs statiques utilisent une table de travail pour stocker l'ensemble des résultats complets du curseur. L'utilisation de l'espace disque pour les curseurs peut varier en fonction du plan de requête choisi. Si le plan de requête est identique à celui des versions antérieures de SQL Server, l'utilisation de l'espace disque est approximativement la même. |
|
Messagerie de base de données |
Voir plus bas la section « Service Broker ». |
|
DBCC CHECKDB |
DBCC CHECKDB utilise les tables de travail de tempdb pour le stockage des résultats intermédiaires et pour les opérations de tri. Pour déterminer l'espace disque tempdb nécessaire pour l'opération, exécutez DBCC CHECKDB WITH ESTIMATEONLY. |
|
Notifications d'événements |
Voir plus bas la section « Service Broker ». |
|
Index |
Lorsque vous créez ou reconstruisez un index (en ligne ou hors connexion) et activez l'option SORT_IN_TEMPDB (ON), vous demandez au moteur de base de données d'utiliser la base de données tempdb pour stocker les résultats de tri intermédiaires nécessaires à la construction de l'index. Quand l'option SORT_IN_TEMPDB est sélectionnée et que le tri est obligatoire, tempdb doit disposer d'un espace disque suffisant pour contenir l'index le plus grand augmenté de l'espace disque égal à la valeur de l'option index create memory. Pour plus d'informations, consultez Exemple d'espace disque d'un index. Il est possible de partitionner les tables et les index. Pour les index partitionnés, si l'option d'index SORT_IN_TEMPDB est spécifiée et que l'index est aligné avec la table de base, tempdb doit disposer d'un espace suffisant pour contenir les exécutions de tris intermédiaires de la partition la plus grande. Si l'index n'est pas aligné, tempdb doit disposer d'un espace suffisant pour contenir les exécutions de tris intermédiaires de toutes les partitions. Pour plus d'informations, consultez Consignes spéciales pour les index partitionnés. Les opérations d'index en ligne utilisent le contrôle des versions de ligne pour se préserver des conséquences liées aux modifications effectuées par d'autres transactions. Le contrôle des versions de ligne évite de demander des verrous de partage sur les lignes lues. Les opérations de suppression et de mise à jour d'utilisateurs en simultané lors d'opérations d'index en ligne nécessitent de l'espace pour les enregistrements de version dans tempdb. Lorsque les opérations d'index en ligne utilisent SORT_IN_TEMPDB et que le tri est obligatoire, tempdb doit également disposer de l'espace disque supplémentaire précédemment décrit pour les résultats de tri intermédiaires. Les opérations d'index en ligne qui créent, suppriment ou régénèrent un index cluster, exigent également un espace disque supplémentaire pour créer et gérer un index de mappage temporaire. Les opérations CREATE et UPDATE STATISTICS peuvent utiliser tempdb pour trier l'exemple de lignes pour la génération de statistiques. Pour plus d'informations, consultez Espace disque requis pour les opérations DDL d'index. |
Consignes spéciales pour les index partitionnés Espace disque requis pour les opérations DDL d'index |
Paramètres et variables de type de données LOB |
Les types de données LOB sont les suivants : varchar(max), nvarchar(max), varbinary(max)text, ntext, image et xml. Ces types peuvent atteindre jusqu'à 2 Go et être utilisés comme variables ou paramètres dans les procédures stockées, les fonctions définies par l'utilisateur, les traitements et les requêtes. Les paramètres et les variables ayant le type de données LOB utilisent la mémoire principale comme stockage si les valeurs sont petites. Cependant, les valeurs élevées sont stockées dans tempdb. Lorsque les paramètres et les variables LOB sont stockés dans tempdb, ils sont traités en tant qu'objets internes. Vous pouvez interroger la vue de gestion dynamique sys.dm_db_session_space_usage pour signaler les pages allouées aux objets internes pour une session donnée. Certaines fonctions de chaîne intrinsèques, comme SUBSTRING ou REPLICATE, peuvent nécessiter un stockage temporaire intermédiaire dans tempdb quand elles concernent des valeurs LOB. De même, lorsqu'un niveau d'isolement de transaction basé sur le contrôle des versions de ligne est activé sur la base de données et que des modifications sont effectuées sur des objets de grande taille, le fragment modifié du LOB est copié vers la banque des versions de tempdb. |
|
Connexions MARS (Multiple Active Result Sets) |
Plusieurs jeux de résultats actifs peuvent se produire sous une seule connexion ; celle-ci est communément appelée connexion MARS. Si une session MARS émet une instruction de modification de données (comme INSERT, UPDATE ou DELETE) alors qu'un jeu de résultats est actif, les lignes affectées par l'instruction de modification sont stockées dans la banque de versions de tempdb. Voir plus bas la section « Contrôle des versions de ligne ». |
|
Notifications de requête |
Voir plus bas la section « Service Broker ». |
|
Requêtes |
Les requêtes qui contiennent des instructions SELECT, INSERT, UPDATE et DELETE peuvent utiliser les objets internes pour stocker les résultats intermédiaires des jointures et agrégations hachées, ou des tris. Lorsqu'un plan d'exécution de requête est mis en cache, les tables de travail requises par le plan sont mises en cache. Lorsqu'une table de travail est mise en cache, la table est tronquée et neuf pages demeurent dans le cache en vue de leur réutilisation. Les performances de la prochaine exécution de la requête s'en trouvent améliorées. Si le système dispose d'une mémoire insuffisante, le moteur de base de données peut supprimer le plan d'exécution, ainsi que les tables de travail associées. |
|
Contrôle des versions de ligne |
Le contrôle des versions de ligne est une structure générale utilisée pour prendre en charge les fonctionnalités suivantes :
Les versions de ligne sont conservées dans la banque de versions de tempdb aussi longtemps qu'une transaction active doit y accéder. Le contenu de la banque de versions en cours se trouve dans sys.dm_tran_version_store. Les pages de la banque de versions sont suivies au niveau fichier, car il s'agit de ressources globales. Vous pouvez utiliser la colonne version_store_reserved_page_count de sys.dm_db_file_space_usage pour afficher la taille de la banque de versions. Le nettoyage de la banque de versions doit prendre en compte la plus longue transaction ayant besoin d'accéder à une version particulière. Vous pouvez déterminer la transaction la plus longue pour le nettoyage de la banque de versions en consultant la colonne elapsed_time_seconds dans sys.dm_tran_active_snapshot_database_transactions. Les compteurs Espace disponible dans tempdb (Ko) et Taille de la banque des versions (Ko) de l'objet Transactions peuvent être utilisés pour surveiller la taille et le taux de croissance de la banque des versions de ligne dans tempdb. Pour plus d'informations, consultez SQL Server, objet Transactions. Afin d'évaluer la quantité d'espace requise dans tempdb pour le contrôle des versions de ligne, vous devez d'abord prendre en compte le fait qu'une transaction active doit conserver l'ensemble de ses modifications dans la banque des versions. Cela signifie qu'une transaction d'instantané qui démarre plus tard peut accéder aux anciennes versions. De même, s'il existe une transaction d'instantané active, il est nécessaire de conserver l'ensemble des données de la banque de versions qui sont générées par les transactions actives au démarrage de l'instantané. La formule de base est la suivante : [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
Présentation des niveaux d'isolement basés sur le versioning de ligne |
Service Broker |
Service Broker permet aux développeurs d'élaborer des applications asynchrones, souples d'utilisation, dans lesquelles plusieurs composants indépendants travaillent conjointement pour accomplir une tâche. Ces composants d'applications échangent des messages contenant les informations requises pour exécuter la tâche. Service Broker utilise explicitement tempdb pour conserver le contexte du dialogue qui ne peut demeurer en mémoire. La taille est approximativement de 1 Ko par dialogue. De même, Service Broker utilise implicitement tempdb par la mise en cache des objets dans le contexte de l'exécution de la requête, comme les tables de travail utilisées pour les événements de la minuterie et les conversations fournies en arrière-plan. Messagerie de base de données, Notifications d'événements et Notifications de requêtes utilisent implicitement Service Broker. |
|
Procédures stockées |
Les procédures stockées peuvent créer des objets utilisateur comme les tables temporaires globales ou locales et leurs index, ou des paramètres. Les objets temporaires des procédures stockées peuvent être mis en cache pour optimiser les opérations de suppression et de création de ces objets. Ce comportement peut accroître l'espace disque nécessaire pour tempdb. Il est possible de stocker jusqu'à neuf pages par objet temporaire en vue de leur réutilisation. Voir plus bas la section « Tables temporaires et variables de table ». |
|
Tables temporaires et variables de table
|
Les tables temporaires et les variables de table sont stockées dans tempdb. L'espace disque nécessaire pour les tables temporaires est identique à celui des versions précédentes de SQL Server. La méthode d'évaluation de la taille d'une table temporaire est la même que celle utilisée pour une table standard. Pour plus d'informations, consultez Estimation de la taille d'une table. Une variable de table se comporte comme une variable locale. Une variable de table a pour type table et est principalement utilisée pour le stockage temporaire d'un ensemble de lignes retournées comme ensemble de résultats d'une fonction table. L'espace disque requis pour contenir une variable de table dépend de la taille de la variable déclarée et de la valeur stockée dans la variable. Les tables temporaires locales et les variables sont mises en cache lorsque les conditions suivantes sont satisfaites :
Quand une table temporaire ou une variable de table est mise en cache, l'objet temporaire n'est pas supprimé une fois son objectif atteint. Il est, de fait, tronqué. Neuf pages au plus sont stockées et réutilisées lors de la prochaine exécution de l'objet appelant. La mise en cache permet aux opérations de suppression et de création d'objets de s'exécuter très rapidement et réduit les conflits d'allocation des pages. Pour optimiser les performances, vous devez calculer l'espace disque requis pour les tables temporaires mises en cache ou les variables de table dans tempdb à l'aide de la formule suivante : 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
Utilisation de variables et de paramètres (moteur de base de données) |
Déclencheurs |
Les tables insérées et supprimées utilisées dans les déclencheurs AFTER sont créées dans tempdb. Autrement dit, les lignes mises à jour ou supprimées par le déclencheur font l'objet d'un contrôle de version. Sont incluses toutes les lignes modifiées par l'instruction ayant provoqué le déclencheur. Les lignes insérées par le déclencheur ne font pas l'objet d'un contrôle de version. Les déclencheurs INSTEAD OF utilisent tempdb de la même façon que les requêtes. L'espace disque requis pour les déclencheurs INSTEAD OF est identique à celui des versions précédentes de SQL Server. Voir plus haut la section « Requêtes ». Lorsque vous chargez en masse des données avec les déclencheurs activés, une copie de chaque ligne supprimée ou mise à jour est ajoutée à la banque des versions. |
|
Fonctions définies par l'utilisateur |
Les fonctions définies par l'utilisateur peuvent créer des objets utilisateur comme les tables temporaires globales ou locales et leurs index, les variables ou les paramètres. Par exemple, la table de retour d'une fonction table est stockée dans tempdb. Les types de données autorisés pour les paramètres et les valeurs de retour dans les fonctions scalaires et les fonctions table incluent la plupart des types de données LOB. Par exemple, une valeur de retour peut être de type xml ou varchar(max). Voir plus haut la section « Paramètres et variables de type de données LOB ». Les objets temporaires des fonctions table définies par l'utilisateur peuvent être mis en cache pour optimiser les opérations de suppression et de création de ces objets. Voir plus haut « Tables temporaires et variables de table ». |
|
XML |
Les variables et les paramètres de type xml peuvent utiliser jusqu'à 2 Go. Ils utilisent la mémoire principale comme stockage aussi longtemps que les valeurs sont basses. Cependant, les valeurs élevées sont stockées dans tempdb. Voir plus haut la section « Paramètres et variables de type de données LOB ». La procédure stockée système sp_xml_preparedocument crée une table de travail dans tempdb. L'analyseur XML utilise la table de travail pour stocker le document XML analysé. L'espace disque nécessaire pour tempdb est pratiquement proportionnel à la taille du document XML spécifié quand la procédure stockée est exécutée. |
Implémentation de XML dans SQL Server |
Planification des capacités pour les mises à niveau vers SQL Server
La détermination de la taille appropriée pour tempdb dans un environnement de production dépend de nombreux facteurs. Comme décrit plus haut dans cette rubrique, ces facteurs incluent la charge de travail existante et les fonctionnalités SQL Server utilisées. Il est recommandé d'analyser la charge de travail existante en effectuant les tâches suivantes dans un environnement de test SQL Server.
Activez la croissance automatique de tempdb.
Exécutez les requêtes individuelles ou les fichiers de trace de la charge de travail et surveillez l'utilisation de l'espace dans tempdb.
Exécutez les opérations de maintenance des index, comme leur reconstruction et la surveillance de l'espace dans tempdb.
Retenez les valeurs d'utilisation de l'espace des étapes précédentes pour prédire l'utilisation de la charge de travail totale, ajustez cette valeur en fonction de l'activité simultanée prévue et définissez la taille de tempdb en conséquence.
Pour plus d'informations sur la surveillance de l'espace dans tempdb, consultez Résolution des problèmes d'espace disque insuffisant dans tempdb. Pour plus d'informations sur l'estimation de tempdb pendant les opérations d'index, consultez Exemple d'espace disque d'un index.
Configuration de tempdb pour les environnements de production
Pour optimiser les performances de tempdb, suivez les instructions et les recommandations fournies dans Optimisation des performances de la base de données tempdb.
Mode de surveillance de l'utilisation de tempdb
Un espace disque insuffisant dans tempdb peut générer des perturbations significatives dans l'environnement de production SQL Server et empêcher les applications en cours d'exécution de terminer leurs opérations. Vous pouvez utiliser la vue de gestion dynamique sys.dm_db_file_space_usage pour surveiller l'espace disque utilisé par ces fonctionnalités dans les fichiers tempdb. En outre, pour surveiller l'activité d'allocation et de désallocation de pages dans tempdb au niveau des sessions ou des tâches, vous pouvez utiliser les vues de gestion dynamique sys.dm_db_session_space_usage et sys.dm_db_task_space_usage. Ces vues permettent d'identifier les requêtes, les tables temporaires et les variables de table qui utilisent un espace disque volumineux dans tempdb. Il existe également plusieurs compteurs permettant de surveiller l'espace libre disponible dans tempdb ainsi que les ressources qui utilisent tempdb. Pour plus d'informations, consultez Résolution des problèmes d'espace disque insuffisant dans tempdb.
Voir aussi