Configuration requise pour le sous-système d’e/s de Microsoft SQL Server pour la base de données tempdb

Cet article présente la configuration requise pour le sous-système d’e/s pour la base de données tempdb dans SQL Server.

Version du produit d’origine :   Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2012 SQL Server 2014
Numéro de la base de connaissances initiale :   917047

Synthèse

Microsoft SQL Server requiert que le sous-système d’e/s utilisé pour stocker les bases de données système et utilisateur conservent entièrement les exigences de journalisation Write-Ahead (WAL) par le biais de principaux d’e/s. Ces exigences sont nécessaires pour honorer les propriétés ACID des transactions : atomique, cohérente, isolée et durable. Des détails sur les exigences de conformité du sous-système d’e/s sont fournis dans les références suivantes :

Notions de base relatives à SQL Server 2000 e/s https://technet.microsoft.com/library/cc966500.aspx

Notes

Cet article s’applique également à SQL Server 2005 et versions ultérieures.

La liste suivante est un résumé rapide de la configuration requise :

  • La commande d’écriture doit être conservée.
  • La cohérence des écritures dépendantes doit être maintenue.
  • Les écritures doivent toujours être sécurisées dans des médias stables ou sur un support stable.
  • Une prévention des e/s endommagées doit se produire.

La maintenance de durabilité reste critique pour toutes les autres bases de données, mais peut être assouplie pour la base de données tempdb. Le tableau suivant récapitule plusieurs des exigences d’e/s critiques pour les bases de données SQL Server.

Exigences d’e/s Brève description Système ou utilisateur tempdb
Ordre des écritures

Cohérence des écritures dépendantes
Capacité du sous-système à maintenir l’ordre correct des opérations d’écriture. Cela peut s’avérer particulièrement important pour les solutions de mise en miroir, les exigences de cohérence de groupe et l’utilisation du protocole SQL Server WAL. Obligatoire Recommandé
Lecture après écriture Capacité du sous-système à traiter les demandes de lecture avec la dernière image de données lors de la lecture d’une écriture après l’exécution d’une écriture. Obligatoire Obligatoire
Survie sur une panne Capacité de conservation totale des données (durable) sur une panne, telle qu’un redémarrage du système. Obligatoire Non applicable
Prévention des e/s déchirées Capacité du système à éviter de fractionner les demandes d’e/s individuelles. Obligatoire Recommandé
Réécriture des secteurs Le secteur ne peut être écrit qu’en totalité et ne peut pas être réécrit en raison d’une demande d’écriture sur un secteur avoisinant. * Déconseillée, autorisée uniquement si transactionnelle * Déconseillée, autorisée uniquement si transactionnelle
Données renforcées Dans l’attente de l’aboutissement d’une demande d’écriture ou d’une opération FlushFileBuffers, les données ont été enregistrées sur un support stable. Obligatoire Non applicable
Alignement et taille des secteurs physiques SQL Server interroge les emplacements de stockage des données et des fichiers journaux. Tous les appareils doivent prendre en charge les attributs de secteur permettant à SQL Server d’effectuer des écritures sur des frontières physiques alignées sur un secteur et dans des multiples de la taille de secteur. Obligatoire Obligatoire

Les réécritments de secteurs transactionnels impliquent des opérations journalisées complètes par le sous-système autorisant le déplacement, le remplacement ou la restauration d’un secteur vers l’image d’origine. Ces réécritures sont généralement déconseillées en raison de la charge supplémentaire requise pour effectuer ces actions. Par exemple, il peut s’agir d’un defragmentation utilitaire qui déplace les données de fichier. Le secteur d’origine dans le fichier ne peut pas être remplacé par le nouvel emplacement de secteur tant que le nouveau secteur et les données ne sont pas sécurisés. Le remappage du secteur doit se produire de manière transactionnelle de sorte que tout échec, y compris une panne de courant, entraîne le rétablissement des données d’origine. Assurez-vous que vous disposez de mécanismes de verrouillage disponibles pendant ce type de processus pour empêcher l’accès aux données non valides, ce qui a pour effet de maintenir les autres clients de l’e/s SQL Server.

Survie sur une panne

La base de données tempdb est une zone de brouillon pour SQL Server qui est reconstruite à chaque démarrage de SQL Server. L’initialisation remplace tout besoin de données pour survivre à un redémarrage.

Opérations de réécriture de secteur transactionnel

Pour garantir la réussite des processus de récupération, tels que la restauration et la récupération d’urgence, les enregistrements du journal doivent être correctement stockés sur un support stable avant que la page de données ne soit stockée et ne puisse pas être réécrite sans honorer les propriétés transactionnelles. Pour cela, le sous-système et SQL Server doivent conserver des attributs spécifiques, tels que l’ordre d’écriture, l’alignement sectoriel et les écritures dimensionnées, ainsi que d’autres attributs de sécurité des e/s décrits dans les documents mentionnés ci-dessus. Pour la base de données tempdb, la récupération d’urgence n’est pas nécessaire, car la base de données est toujours initialisée lors du démarrage de SQL Server. Toutefois, la base de données tempdb requiert toujours des fonctionnalités de restauration. Par conséquent, certains attributs du protocole WAL peuvent être assouplis.

L’emplacement de stockage de la base de données tempdb doit être strictement conforme aux protocoles de lecteur de disque établis. De toutes façons, l’appareil sur lequel la base de données tempdb est stockée doit apparaître et agir comme un disque physique fournissant des capacités de lecture après écriture. Les opérations de réécriture des secteurs de transaction peuvent être une exigence supplémentaire de mises en œuvre spécifiques. Par exemple, SQL Server ne prend pas en charge les modifications de base de données à l’aide de la compression du système de fichiers NTFS car la compression NTFS peut réécrire des secteurs du journal qui ont déjà été écrits et considérés comme étant renforcés. Un échec de ce type de réécriture peut empêcher la base de données d’être inutilisable, ce qui a déjà été considéré comme sécurisé par SQL Server.

Notes

La prise en charge ou compression étendue de SQL Server 2005 pour la lecture seule des bases de données et des groupes de fichiers. Pour plus d’informations, consultez la documentation en ligne de SQL Server 2005.

Les opérations de réécriture des secteurs transactionnelles sont pertinentes pour toutes les bases de données SQL Server qui incluent la base de données tempdb. Une multitude de technologies de stockage étendues utilisent des appareils et des utilitaires capables de réécrire les données que SQL Server considère comme sécurisés. Par exemple, certaines technologies émergentes effectuent une mise en cache en mémoire ou une compression des données. Afin d’éviter un sérieux dommage dans la base de données, toute réécriture des secteurs doit disposer d’une prise en charge transactionnelle complète de sorte que si une défaillance survient, les données sont restaurées vers les images du secteur précédent. Cela garantit que SQL Server n’est jamais exposé à une erreur d’interruption ou de dommage inattendue.

Vous pouvez placer la base de données tempdb sur des sous-systèmes spécialisés, tels que des disques virtuels, un état solide ou d’autres implémentations à haut débit qui ne peuvent pas être utilisées pour d’autres bases de données. Toutefois, les facteurs clés présentés dans la section « plus d’informations » doivent être pris en compte lors de l’évaluation de ces options.

Notes

Les disques locaux des environnements de clustering de basculement sont uniquement pris en charge avec des implémentations à grande vitesse ou à haut débit. Cela est dû au fait que le disque virtuel ne peut être créé que sur une cible iSCSI. En outre, les fonctionnalités de cible iSCSI et de cluster de basculement ne peuvent pas être utilisées sur le même hôte.

Plus d’informations

Plusieurs facteurs doivent être étudiés attentivement lorsque vous évaluez l’emplacement de stockage de la base de données tempdb. Par exemple, l’utilisation de la base de données tempdb implique, sans s’en limiter, l’encombrement mémoire, le plan de requête et les décisions d’e/s. Le réglage et l’implémentation appropriés de la base de données tempdb peuvent améliorer l’extensibilité et la réactivité d’un système. Cette section traite des facteurs clés en matière de détermination des besoins de stockage pour la base de données tempdb.

Sous-systèmes à grande vitesse

Il existe plusieurs implémentations de sous-système à grande vitesse disponibles sur le marché qui fournissent des exigences de protocole de sous-système d’e/s SQL Server, mais qui ne fournissent pas de durabilité du média.

Important

Vérifiez toujours auprès du fournisseur du produit qu’il garantit la conformité complète avec les besoins d’e/s de SQL Server.

Un disque RAM est un exemple courant d’une telle implémentation. Les disques virtuels installent les pilotes nécessaires et permettent à une partie du disque virtuel principal de s’afficher comme et fonctionnent comme n’importe quel lecteur de disque attaché au système. Tous les sous-systèmes d’e/s doivent assurer la conformité complète avec les exigences d’e/s de SQL Server. Toutefois, il est évident qu’un disque RAM n’est pas un support durable. Par conséquent, une implémentation telle qu’un disque virtuel peut uniquement être utilisée comme emplacement de la base de données tempdb et ne peut pas être utilisée pour une autre base de données.

Clés à prendre en compte avant l’implémentation et le déploiement

Il existe différents points à prendre en compte avant le déploiement de la base de données tempdb sur ce type de sous-système. Cette section utilise un disque virtuel comme base de discussion, mais des résultats similaires se produisent dans les autres implémentations à haut débit.

Sécurité des e/s

La conformité de la lecture après les opérations d’écriture et de secteur transactionnel est un. Ne déployez jamais SQL Server sur un système qui ne prend pas totalement en charge les conditions d’e/s SQL Server, ou vous risquez de perdre et de perdre vos données.

Pages déjà mises en cache (cache double RAM)

Les tables temporaires sont comme toutes les autres tables d’une base de données. Elles sont mises en cache par le pool de tampons et gérées par des opérations d’écriture différée. Le stockage des pages de table temporaires sur un disque RAM entraîne une mise en cache de double RAM, l’autre dans le pool de tampons et l’autre sur le disque virtuel. Cela prend directement la taille maximale possible du pool de mémoires tampons et réduit généralement les performances de SQL Server.

Abandon de la mémoire vive

Le disque virtuel désigne une partie de la RAM principale comme son nom l’indique. Il existe plusieurs implémentations de disques RAM et de caches de fichiers de RAM disponibles. Certains permettent également d’effectuer des opérations de sauvegarde d’e/s physiques. L’élément clé du cache de fichiers basé sur la mémoire RAM est qu’il récupère directement de la mémoire physique qui peut être utilisée par SQL Server. Les preuves de l’ajout d’un cache de fichiers basé sur la mémoire vive améliorent toujours les performances de l’application et ne réduisent pas les performances des requêtes ou des applications.

Réglage d’abord

Une application doit s’adapter pour supprimer les tris et les hachages inutiles et indésirables susceptibles d’entraîner l’utilisation de la base de données tempdb. Bien souvent, l’ajout d’un index peut éliminer le tri ou le hachage dans le plan, ce qui permet d’obtenir des performances optimales sans nécessiter l’utilisation de la base de données tempdb.

Points de profit possibles

Les avantages liés à la mise en place de la base de données tempdb sur un système à haut débit peuvent uniquement être déterminés via des tests rigoureux et des mesures des charges de travail d’application. La charge de travail doit être étudiée avec soin pour les caractéristiques dont la base de données tempdb peut bénéficier, et la sécurité des e/s doit être confirmée avant le déploiement.

Les opérations de tri et de hachage fonctionnent avec les gestionnaires de mémoire SQL Server pour déterminer la taille de la zone de brouillon en mémoire pour chaque opération de tri ou de hachage. Dès que les données de tri ou de hachage dépassent la zone de brouillon allouée en mémoire, les données peuvent être écrites dans la base de données tempdb. Cet algorithme a été développé dans SQL Server 2005, ce qui a réduit les besoins d’utilisation de la base de données tempdb par rapport aux versions antérieures de SQL Server.

Attention

SQL Server est conçu pour tenir compte des niveaux de mémoire et des activités de requête en cours lors de la prise de décisions de plan de requête impliquant l’utilisation des opérations de base de données tempdb. Par conséquent, les gains de performances varient considérablement en fonction des charges de travail et de la conception des applications. Nous vous recommandons vivement de suivre les tests à l’aide de la solution préférée pour déterminer les avantages possibles et évaluer les exigences en matière de sécurité des e/s avant un déploiement de ce type.

SQL Server utilise la base de données tempdb pour gérer diverses activités impliquant des tris, des hachages, la Banque de versions de ligne et des tables temporaires :

  • Les tables temporaires sont gérées par les routines de pool de tampons courants pour les pages de données et n’ont généralement pas d’avantages en termes de performances par les implémentations de sous-système de spécialité.
  • La base de données tempdb est utilisée comme un plan de montage pour les hachages et les tris. Réduire la latence des e/s pour ces opérations peut être bénéfique. Toutefois, sachez que l’ajout d’un index pour éviter un hachage ou un tri peut fournir un avantage similaire.

Exécutez des configurations de référence avec et sans la base de données tempdb stockée sur le sous-système à haute vitesse pour comparer les avantages. Une partie du test doit inclure des requêtes sur la base de données utilisateur qui n’impliquent pas de tris, de hachage ou de tables temporaires, puis confirment que ces requêtes ne sont pas affectées. Lorsque vous évaluez le système, les indicateurs de performances suivants peuvent être utiles.

Progression Description/utilisation
Lectures et écritures de pages Amélioration des performances de la base de données tempdb les e/s peuvent modifier la fréquence des lectures et écritures de pages pour les bases de données utilisateur en raison de la latence réduite associée à l’e/s de base de données tempdb. Pour les pages de base de données utilisateur, le nombre global ne doit pas varier dans la même charge de travail.
Octets de lecture et d’écriture physiques dans la base de données tempdb Si le déplacement de la base de données tempdb vers un périphérique, tel qu’un disque RAM, augmente les e/s réelles de la base de données tempdb, cela indique que la mémoire extraite du pool de tampons provoque l’augmentation de l’activité de la base de données tempdb. Ce modèle est un indicateur qui indique que la durée de vie de la page des pages de base de données peut également être affectée de manière négative.
Espérance de vie d’une page Une diminution de la durée de vie de la page peut indiquer une augmentation des besoins d’e/s physiques pour une base de données utilisateur. La diminution de la vitesse peut indiquer que la mémoire extraite du pool de tampons force les pages de base de données à quitter le pool de tampons prématurément. Combinez avec les autres indicateurs et test pour bien comprendre les limites des paramètres.
Débit global
Utilisation du processeur
Extensibilité
Temps de réponse
L’objectif principal d’une modification de configuration de base de données tempdb est d’augmenter le débit global. Vos tests doivent inclure un mélange de charges de travail répétitives pouvant être mises à l’horizontale afin de déterminer la façon dont le débit est affecté.

Un peu comme une implémentation de disque RAM basée sur la compression peut fonctionner correctement avec 10 utilisateurs. Toutefois, avec une charge de travail accrue, cela peut entraîner des niveaux de processeur au-delà des niveaux souhaités et avoir des effets négatifs sur le temps de réponse lorsque les charges de travail sont élevées. Les tests de contrainte et les tests de prévision de charge sont encouragés.
Actions de création de table de travail et de fichiers professionnels Si le déplacement de la base de données tempdb vers un périphérique, tel qu’un disque RAM, modifie le plan de requête en augmentant le nombre ou la taille des fichiers de travail ou des tables de travail, cela indique que la mémoire extraite du pool de tampons entraîne une augmentation de l’activité de la base de données tempdb. Ce modèle indique que l’espérance de vie de page des pages de base de données peut également être affectée d’une manière négative.

Exemple de réécriture de secteur transactionnel

L’exemple suivant illustre la sécurité des données requise par les bases de données SQL Server.

Supposons qu’un fournisseur de disque virtuel utilise une implémentation de compression en mémoire. L’implémentation doit être correctement encapsulée en fournissant l’apparence physique du flux de fichier comme si le secteur était aligné et dimensionné de sorte que SQL Server ne soit pas conscient et correctement sécurisé à partir de l’implémentation sous-jacente. Examinez l’exemple de compression plus près.

Action
Le secteur 1 est écrit sur l’appareil et compressé pour économiser de l’espace.
Le secteur 2 est écrit sur l’appareil et compressé avec le secteur 1 pour économiser de l’espace.

L’appareil peut effectuer les actions suivantes pour aider à sécuriser les données du secteur lorsque celles-ci sont combinées avec les données du secteur 2.

Action
Bloquer toutes les écritures dans les secteurs 1 et 2.
Décompressez le secteur 1 dans un plan de montage, en laissant le secteur actuel de stockage du secteur 1 comme les données actives à récupérer.
Compressez les secteurs 1 et 2 dans un nouveau format de stockage.
Bloquer toutes les lectures et écritures des secteurs 1 et 2.
Espace de stockage Exchange obsolète pour les secteurs 1 et 2 avec un nouveau stockage.
Si la tentative Exchange échoue (ROLLBACK) :
Restaurez l’emplacement de stockage d’origine des secteurs 1 et 2.
Supprimez les données combinées 1 et 2 du plan de montage.
Échec de l’opération d’écriture secteur 2.
Débloquer les lectures et écritures pour les secteurs 1 et 2.

La possibilité de fournir des mécanismes de verrouillage concernant les modifications du secteur et de restaurer les modifications en cas d’échec de la tentative de l’échange de secteur est considérée comme étant conforme à la transition. Pour les implémentations qui utilisent le stockage physique pour la sauvegarde étendue, cela inclut les aspects appropriés du journal des transactions pour aider à sécuriser et à restaurer les modifications appliquées aux structures sur disque afin de préserver l’intégrité des fichiers de base de données SQL Server.

Tout périphérique qui active la réécriture des secteurs doit prendre en charge les réécritures d’une manière transactionnelle afin que SQL Server ne soit pas exposé à une perte de données.

Notes

L’instance de SQL Server est redémarrée lorsque les e/s en ligne et les échecs de restauration se produisent dans la base de données tempdb.

Soyez vigilant lorsque vous déplacez la base de données tempdb.

Soyez prudent lorsque vous déplacez la base de données tempdb car si la base de données tempdb ne peut pas être créée, SQL Server ne pourra pas démarrer. Si la base de données tempdb ne peut pas être créée, démarrez SQL Server en utilisant le paramètre de démarrage (-f) et déplacez la base de données tempdb vers un emplacement valide.

Pour modifier l’emplacement physique de la base de données tempdb, procédez comme suit :

  1. Utilisez l' ALTER DATABASE instruction et la clause MODIFY file pour modifier les noms de fichier physiques de chaque fichier de la base de données tempdb de manière à faire référence au nouvel emplacement physique, comme le nouveau disque.

    Alter database tempdb modify file 
    (name = tempdev, filename = 'C:\MyPath\tempdb.mdf')
    
    Alter database tempdb modify file 
    (name = templog, filename = 'C:\MyPath\templog.ldf')
    
  2. Arrêtez puis redémarrez SQL Server.

Les certifications de produit partenaire ne sont pas garantes de la compatibilité ou de la sécurité

Un produit tiers ou un fournisseur particulier peut recevoir une certification de logo Microsoft. Toutefois, la certification partenaire ou un logo Microsoft spécifique ne certifie pas la compatibilité ou l’adéquation à un objectif particulier dans SQL Server.

Support

Si vous utilisez un sous-système avec SQL Server qui prend en charge les garanties d’e/s pour l’utilisation de bases de données transactionnelles, comme décrit dans cet article, Microsoft fournira une prise en charge pour les applications SQL Server et SQL Server. Toutefois, les problèmes avec ou causés par le sous-système seront renvoyés au fabricant.

Pour les problèmes liés à la base de données tempdb, les services de support technique Microsoft vous demandent de déplacer la base de données tempdb. Contactez votre fournisseur d’appareil pour vérifier que vous avez correctement déployé et configuré le périphérique pour l’utilisation de la base de données transactionnelle.

Microsoft ne certifie pas ou ne valide pas que les produits tiers fonctionnent correctement avec SQL Server. De plus, Microsoft n’offre aucune garantie, aucune garantie ni aucune déclaration de l’aptitude d’un produit tiers à être utilisée avec SQL Server.

Références

Pour plus d’informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la base de connaissances Microsoft :

Les informations contenues dans ce document représentent l’affichage actuel de Microsoft Corporation sur les problèmes abordés à la date de publication. Étant donné que Microsoft doit répondre aux conditions fluctuantes du marché, il ne doit pas être interprété comme un engagement de la part de Microsoft, et Microsoft ne peut pas garantir l’exactitude des informations présentées après la date de publication.

Ce livre blanc est fourni uniquement à titre d’information. MICROSOFT EXCLUT TOUTE GARANTIE, EXPRESSE, IMPLICITE OU LÉGALE, QUANT AUX INFORMATIONS CONTENUES DANS CE DOCUMENT.

L’utilisateur est tenu d’observer la réglementation relative aux droits d’auteur applicable dans son pays. Aucune partie de ce document ne peut être reproduite, stockée ou introduite dans un système de restitution, ou transmise à quelque fin ou par quelque moyen que ce soit (électronique, mécanique, photocopie, enregistrement ou autre) sans la permission expresse et écrite de Microsoft Corporation.

Microsoft peut détenir des brevets, avoir déposé des demandes d’enregistrement de brevets ou être titulaire de marques, droits d’auteur ou autres droits de propriété intellectuelle portant sur tout ou partie des éléments qui font l’objet du présent document. Sauf stipulation expresse contraire d’un contrat de licence écrit de Microsoft, la fourniture de ce document n’a pas pour effet de vous concéder une licence sur ces brevets, marques, droits d’auteur ou autres droits de propriété intellectuelle.

© 2006 Microsoft Corporation. Tous droits réservés.

Microsoft, Windows, Windows Server et SQL Server sont soit des marques de Microsoft Corporation, soit des marques déposées de Microsoft Corporation, aux États-Unis et/ou dans d’autres pays.

SQL Server exige que les systèmes prennent en charge la « remise garantie sur un support stable », comme indiqué dans la rubrique conditions requises pour le programme de fiabilité des e/s SQL Server. Pour plus d’informations sur les exigences en matière d’entrée et de sortie pour le moteur de base de données SQL Server, voir conditions d’entrée/sortie du moteur de base de données Microsoft SQL Server.