Partager via


Planification des capacités de tempdb

Mis à jour : 14 avril 2006

Cette rubrique décrit les modifications effectuées dans SQL Server 2005 pour la base de données système tempdb et 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.

Dans SQL Server 2005, tempdb nécessite plus d'espace disque que dans les versions précédentes de SQL Server. La raison en est due aux modifications suivantes :

  • certaines nouvelles fonctionnalités de SQL Server 2005 utilisent tempdb ;
  • certaines fonctionnalités qui utilisaient tempdb dans les versions antérieures de SQL Server peuvent requérir plus d'espace disque pour tempdb dans SQL Server 2005 ;
  • certaines fonctionnalités qui n'utilisaient pas tempdb dans les versions antérieures de SQL Server y ont recours dans SQL Server 2005.

En raison de ces modifications, il importe que vous fournissiez à tempdb un espace disque suffisant lors de la mise à niveau vers SQL Server 2005 afin de tenir compte de la charge de travail de production et des exigences d'espace requises par les fonctionnalités de SQL Server qui utilisent 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 permet de 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
  • Tables temporaires globales et 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 Pages et extensions.

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. Dans SQL Server 2005, il y a 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 ;
  • les versions de lignes générées par les transactions de modification des données pour les fonctionnalités suivantes : opérations d'index en ligne, déclencheurs AFTER et connexions MARS (Multiple Active Result Sets).

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

Dans SQL Server 2005, les optimisations d'importation en bloc sont disponibles lorsque les déclencheurs sont activés. SQL Server 2005 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 ».

Optimisation de l'importation en bloc

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.

Utilisation d'expressions de table communes

WITH common_table_expression (Transact-SQL)

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.

À propos de la sélection d'un type de curseur

Messagerie de base de données

Voir plus bas la section « Service Broker ».

Messagerie de base de données

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.

L'utilisation de l'espace disque pour DBCC CHECKDB augmente en raison des modifications suivantes apportées à l'opération DBCC CHECK :

  • les vérifications logiques introduites sont plus complètes ;
  • certaines structures sous-jacentes sont plus complexes à vérifier ;
  • des vérifications supplémentaires ont été introduites pour inclure les nouvelles fonctionnalités de SQL Server 2005.

Pour déterminer l'espace disque tempdb requis pour l'opération, exécutez DBCC CHECKDB WITH ESTIMATE_ONLY.

DBCC CHECKDB (Transact-SQL)

Optimisation des performances DBCC CHECKDB

Notifications d'événements

Voir plus bas la section « Service Broker ».

Présentation des notifications d'événements

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.

L'utilisation de l'espace disque pour les opérations d'index hors connexion qui n'ont pas recours aux fonctionnalités de SQL Server 2005 est identique à celle des versions précédentes de SQL Server.

Modifications de l'utilisation de tempdb dans SQL Server 2005

Dans SQL Server 2005, 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. Pour plus d'informations, consultez Espace disque requis pour les opérations DDL d'index.

tempdb et création d'index

Consignes spéciales pour les index partitionnés

Espace disque requis pour les opérations DDL d'index

Exemple d'espace disque d'un index

Fonctionnement des opérations d'index en ligne

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 lots 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.

Utilisation de types de données de valeur élevée

Connexions MARS (Multiple Active Result Sets)

Dans SQL Server 2005, plusieurs ensembles 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 ».

Using Multiple Active Result Sets (MARS)

Notifications de requête

Voir plus bas la section « Service Broker ».

Utilisation des notifications de requêtes

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.

Dans SQL Server 2005, quand 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.

Mise en mémoire cache et réutilisation du plan d'exécution

Contrôle des versions de ligne

Le contrôle des versions de ligne est une structure générale de SQL Server 2005 qui sert à prendre en charge les fonctionnalités suivantes :

  • Déclencheurs
  • Connexions MARS (Multiple Active Result Sets)
  • Opérations d'index spécifiant l'option ONLINE
  • Niveaux d'isolement des transactions basés sur le contrôle des versions de ligne :
    • Nouvelle implémentation du niveau d'isolement en lecture validée qui utilise le contrôle des versions de ligne pour assurer la cohérence de lecture au niveau de l'instruction.
    • Niveau d'isolement de la capture instantanée destiné à assurer la cohérence de lecture au niveau des transactions.

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 de capture instantanée qui démarre plus tard peut accéder aux anciennes versions. De même, s'il existe une transaction de capture instantanée 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 la capture instantanée.

La formule de base est la suivante :

[Taille de la banque des versions] = 2 *

[Données de la banque des versions générées par minute] *

[Durée d'exécution la plus longue (en minutes) de votre transaction]

Présentation des niveaux d'isolement basés sur le versioning de ligne

Utilisation de la ressource de 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 .

Présentation de 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. Dans SQL Server 2005, 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 requis 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 ».

Création de procédures stockées (moteur de base de données)

Tables temporaires et variables de table

  • Tables définies par l'utilisateur et index
  • Tables système et index
  • Tables temporaires globales et index
  • Tables temporaires locales et index
  • Variables de table
  • Tables renvoyées dans les fonctions table

Les tables temporaires et les variables de table sont stockées dans tempdb. L'espace disque requis 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.

Dans SQL Server 2005, les tables temporaires locales et les variables sont mises en cache lorsque les conditions suivantes sont satisfaites :

  • il n'y a pas création de contraintes nommées ;
  • les instructions DDL (Data Definition Language) affectant la table ne sont pas exécutées après la création de la table temporaire, comme les instructions CREATE INDEX ou CREATE STATISTICS ;
  • l'objet temporaire n'est pas créé à l'aide de SQL dynamique, comme : sp_executesql N'create table #t(a int)'.
  • l'objet temporaire est créé à l'intérieur d'un autre objet, comme une procédure stockée, un déclencheur, une fonction définie par l'utilisateur, ou correspond à la table de retour d'une fonction table, définie par l'utilisateur.

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 pages par table temporaire

* nombre moyen de tables temporaires par procédure

* nombre maximal d'exécutions simultanées de la procédure

CREATE TABLE (Transact-SQL)

Utilisation de variables et de paramètres (moteur de base de données)

DECLARE @local_variable (Transact-SQL)

Déclencheurs

Dans les versions antérieures de SQL Server, la logique des déclencheurs repose sur les enregistrements du journal et tempdb n'est pas utilisée. Dans SQL Server 2005, 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 bloc 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.

CREATE TRIGGER (Transact-SQL)

Optimisation de l'importation en bloc

Utilisation de la ressource de versioning de ligne

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.

Dans SQL Server 2005, 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 ».

Dans SQL Server 2005, 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 ».

CREATE FUNCTION (Transact-SQL)

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 requis pour tempdb est pratiquement proportionnel à la taille du document XML spécifié quand la procédure stockée est exécutée.

Type de données xml

sp_xml_preparedocument (Transact-SQL)

Interrogation de données XML à l'aide de OPENXML

Planification des capacités pour les mises à niveau vers SQL Server 2005

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 2005.

  1. Activez la croissance automatique de tempdb.
  2. 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.
  3. Exécutez les opérations de maintenance des index, comme leur reconstruction et la surveillance de l'espace dans tempdb.
  4. 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. Dans SQL Server 2005, 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

Tâches

Résolution des problèmes d'espace disque insuffisant dans tempdb

Concepts

Base de données tempdb
Optimisation des performances de la base de données tempdb

Autres ressources

Optimisation de bases de données
Utilisation de tempdb dans SQL Server 2005

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version Historique

14 avril 2006

Contenu mis à jour :
  • Clarification du moment auquel des requêtes d'expressions de table communes utilisent tempdb.