Configuration requise du sous-système d’E/S Microsoft SQL Server pour la base de données tempdb

Cet article présente les exigences du sous-système d’E/S pour la base de données tempdb dans SQL Server.

Version du produit d’origine : Microsoft SQL Server
Numéro de base de connaissances d’origine : 917047

Résumé

Microsoft SQL Server exige que le sous-système d’E/S utilisé pour stocker les bases de données système et utilisateur respecte entièrement les exigences wal (Write-Ahead Logging) via des principaux d’E/S spécifiques. Ces exigences sont nécessaires pour respecter les propriétés ACID des transactions : Atomic, Consistent, Isolé et Durable. Des informations détaillées sur les exigences de conformité des sous-systèmes d’E/S sont fournies dans les principes de base des E/S SQL Server.

La liste suivante est un résumé rapide des exigences :

  • L’ordre d’écriture doit être conservé.
  • La cohérence d’écriture dépendante doit être conservée.
  • Les écritures doivent toujours être sécurisées dans/sur un support stable.
  • La prévention des E/S déchirées doit se produire.

La maintenance de durabilité reste essentielle 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 exigences d’E/S critiques pour les bases de données SQL Server.

Exigence d’E/S Brève description Système ou utilisateur tempdb
Ordre d’écriture

Cohérence d’écriture dépendante		 Capacité du sous-système à maintenir l’ordre correct des opérations d’écriture. Cela peut être particulièrement important pour les solutions de mise en miroir, les exigences de cohérence des groupes et l’utilisation du protocole WAL SQL Server. Requis Recommandé
Lecture après écriture Capacité du sous-système à traiter les demandes de lecture avec la dernière image de données lorsque la lecture est émise une fois l’écriture terminée. Requis Requis
Survie en panne La possibilité pour les données de rester entièrement intactes (Durable) sur une panne, telle qu’un redémarrage du système. Requis Non applicable
Prévention des E/S déchirées Capacité du système à éviter de fractionner des demandes d’E/S individuelles. Requis Recommandé
Réécriture du secteur Le secteur ne peut être écrit que dans son intégralité et ne peut pas être réécrit en raison d’une demande d’écriture sur un secteur voisin. * Déconseillé, autorisé uniquement si transactionnel * Déconseillé, autorisé uniquement si transactionnel
Données renforcées L’attente que lorsqu’une demande d’écriture ou une opération FlushFileBuffers est terminée, les données ont été enregistrées sur un support stable. Requis Non applicable
Alignement et taille du secteur physique 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 limites alignées sur le secteur physique et dans plusieurs de la taille du secteur. Requis Requis

Les réécritures du secteur transactionnel impliquent des opérations entièrement journalisées par le sous-système permettant à un secteur d’être entièrement déplacé, remplacé ou restauré dans l’image d’origine. Ces réécritures sont généralement déconseillées en raison de la surcharge supplémentaire nécessaire pour effectuer ces actions. Par exemple, il s’agit d’un defragmentation utilitaire qui déplace les données de fichier. Le secteur d’origine du fichier ne peut pas être remplacé par le nouvel emplacement du secteur tant que le nouveau secteur et les données ne sont pas sécurisés. Le remapping du secteur doit se produire de manière transactionnelle afin que toute défaillance, y compris une panne d’alimentation, provoque la recréation 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, en respectant ainsi les autres locataires d’E/S SQL Server.

Survie en panne

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

Opérations de réécriture du secteur transactionnel

Pour garantir la réussite des processus de récupération, tels que la restauration et la récupération d’incident, les enregistrements de journal doivent être correctement stockés sur un support stable avant que la page de données ne soit stockée et ne peut pas être réécrite sans respecter les propriétés transactionnelles. Cela nécessite que le sous-système et SQL Server conservent des attributs spécifiques, tels que l’ordre d’écriture, les écritures alignées sur le secteur et les écritures dimensionnées, ainsi que d’autres attributs de sécurité d’E/S décrits dans les documents mentionnés précédemment. Pour la base de données tempdb, la récupération d’incident n’est pas nécessaire, car la base de données est toujours initialisée au démarrage de SQL Server. Toutefois, la base de données tempdb nécessite 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 agir strictement conformément 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 en tant que disque physique fournissant des fonctionnalités de lecture après écriture. Les opérations de réécriture du secteur des transactions peuvent être une exigence supplémentaire d’implémentations 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 renforcés. Une défaillance pendant ce type de réécriture peut entraîner l’inutilisable de la base de données, ce qui endommage les données déjà considérées comme sécurisées par SQL Server.

Remarque

SQL Server a étendu la prise en charge ou la compression aux bases de données en lecture seule et aux groupes de fichiers. Pour plus d’informations, consultez la documentation en ligne de SQL Server.

Les opérations de réécriture du secteur transactionnel sont pertinentes pour toutes les bases de données SQL Server qui incluent la base de données tempdb. Une variété croissante de technologies de stockage étendues utilisent des appareils et des utilitaires capables de réécrire des données que SQL Server considère comme sécurisées. Par exemple, certaines des technologies émergentes effectuent une mise en cache en mémoire ou une compression des données. Afin d’éviter des dommages graves à la base de données, toute réécriture du secteur doit disposer d’une prise en charge transactionnelle complète de telle sorte que, si une défaillance se produit, les données sont restaurées dans les images de secteur précédentes. Cela garantit que SQL Server n’est jamais exposé à une interruption inattendue ou à une condition de dommages aux données.

Vous pouvez peut-être placer la base de données tempdb sur des sous-systèmes spécialisés, tels que des disques RAM, un état solide ou d’autres implémentations à grande vitesse 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 Informations supplémentaires doivent être pris en compte lorsque vous évaluez ces options.

Remarque

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

Plus d’informations

Plusieurs facteurs doivent être soigneusement étudiés 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, mais n’est pas limitée aux décisions d’empreinte mémoire, de plan de requête et d’E/S. Le réglage et l’implémentation appropriés de la base de données tempdb peuvent améliorer la scalabilité et la réactivité d’un système. Cette section décrit les principaux facteurs de détermination des besoins de stockage pour la base de données tempdb.

Sous-systèmes à haute vitesse

Il existe différentes 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 de produits pour garantir une conformité totale avec les besoins d’E/S SQL Server.

Un disque RAM est un exemple courant d’une telle implémentation. Les disques RAM installent les pilotes nécessaires et permettent à la partie du disque RAM principal d’apparaître sous la forme et de fonctionner comme n’importe quel lecteur de disque attaché au système. Tous les sous-systèmes d’E/S doivent fournir une conformité complète avec les exigences d’E/S 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 RAM 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 RAM comme base pour la discussion, mais des résultats similaires se produisent dans d’autres implémentations à grande vitesse.

Sécurité des E/S

La conformité de la lecture après l’écriture et les écritures du secteur transactionnel est obligatoire. Ne déployez jamais SQL Server sur un système qui ne prend pas entièrement en charge les exigences d’E/S SQL Server, ou vous risquez de endommager et de perdre vos données.

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

Les tables temporaires sont similaires à toutes les autres tables d’une base de données. Ils sont mis en cache par le pool de mémoires tampons et gérés par des opérations d’écriture différées. Le stockage de pages de table temporaires sur un disque RAM entraîne une mise en cache de RAM double, une dans le pool de mémoires tampons et une sur le disque RAM. Cela supprime directement la taille totale possible du pool de mémoires tampons et diminue généralement les performances de SQL Server.

Abandon de la RAM

Le disque RAM désigne une partie de la RAM principale comme le nom l’indique. Il existe plusieurs implémentations de disques RAM et de caches de fichiers basés sur la RAM disponibles. Certains activent également des opérations de stockage d’E/S physiques. L’élément clé du cache de fichiers basé sur la RAM est qu’il s’éloigne directement de la mémoire physique qui peut être utilisée par SQL Server. L’ajout d’un cache de fichiers basé sur la RAM améliore toujours les performances de l’application et ne diminue pas les autres performances de requête ou d’application.

Paramétrer d’abord

Une application doit paramétrer pour supprimer les tris et hachages inutiles et indésirables susceptibles d’entraîner l’utilisation de la base de données tempdb. Plusieurs fois, l’ajout d’un index peut supprimer la nécessité du tri ou du hachage dans le plan complètement, ce qui entraîne des performances optimales sans nécessiter l’utilisation de la base de données tempdb.

Points d’avantages possibles

Les avantages de la mise en place de la base de données tempdb sur un système à grande vitesse ne peuvent être déterminés qu’à l’aide de tests et de mesures rigoureux des charges de travail d’application. La charge de travail doit être étudiée attentivement pour les caractéristiques dont la base de données tempdb peut tirer parti, 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 travail 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 travail 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, ce qui réduit les exigences 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 actuelles 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 d’applications. Nous vous recommandons vivement d’effectuer des tests avec la solution préférée pour déterminer les gains possibles et évaluer les exigences de sécurité des E/S avant un tel déploiement.

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

  • Les tables temporaires sont conservées par les routines courantes du pool de mémoires tampons pour les pages de données et ne présentent généralement pas d’avantages en matière de performances des implémentations de sous-systèmes spécialisés.
  • La base de données tempdb est utilisée comme zone de travail pour les hachages et les tris. La réduction de 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 lignes de base de référence avec et sans la base de données tempdb stockée sur le sous-système à grande 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 hachages ou de tables temporaires, puis vérifient que ces requêtes ne sont pas affectées de manière négative. Lorsque vous évaluez le système, les indicateurs de performances suivants peuvent être utiles.

Indicateur Description/utilisation
Lectures et écritures de pages L’amélioration des performances de l’E/S de base de données tempdb peut modifier le taux de lectures et d’écritures de pages pour les bases de données utilisateur en raison de la latence réduite associée aux E/S de base de données tempdb. Pour les pages de base de données utilisateur, le nombre global ne doit pas varier entre la même charge de travail.
Octets de lecture et d’écriture physiques dans la base de données tempdb Si vous déplacez la base de données tempdb vers un appareil, tel qu’un disque RAM, augmente les E/S réelles de la base de données tempdb, cela indique que la mémoire retirée du pool de mémoires tampons entraîne une augmentation de l’activité de base de données tempdb. Ce modèle est un indicateur indiquant que l’espérance de vie 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 baisse de l’espérance de vie des pages peut indiquer une augmentation des exigences d’E/S physiques pour une base de données utilisateur. La diminution du taux peut probablement indiquer que la mémoire retirée du pool de mémoires tampons force les pages de base de données à quitter le pool de mémoires tampons prématurément. Combinez-les avec les autres indicateurs et testez pour comprendre pleinement les limites des paramètres.
Débit global
Utilisation de l’UC
Évolutivité
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 reproductibles qui peuvent être mises à l’échelle pour déterminer la façon dont le débit est affecté.

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 pousser les 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 vrais tests de contrainte et les futurs tests de prédiction de charge sont encouragés.
Actions de création de fichiers de travail et de table de travail Si vous déplacez la base de données tempdb vers un appareil, tel qu’un disque RAM, modifie le plan de requête en augmentant le nombre ou la taille des fichiers professionnels ou des tables de travail, il indique que la mémoire retirée du pool de mémoires tampons entraîne une augmentation de l’activité de base de données tempdb. Ce modèle indique que l’espérance de vie des pages de base de données peut également être affectée de manière négative.

Exemple de réécriture du secteur transactionnel

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

Supposons qu’un fournisseur de disques RAM 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 fichiers comme si le secteur était aligné et dimensionné afin que SQL Server ne soit pas conscient et correctement sécurisé de l’implémentation sous-jacente. Examinez l’exemple de compression plus proche.

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

L’appareil peut effectuer les actions suivantes pour sécuriser les données du secteur 1 lorsqu’elle est combinée aux données du secteur 2.

  • Bloquez toutes les écritures dans les secteurs 1 et 2.
  • Décompressez le secteur 1 dans une zone de travail, en laissant le stockage du secteur 1 actuel comme données actives à récupérer.
  • Compressez les secteurs 1 et 2 dans un nouveau format de stockage.
  • Bloquez toutes les lectures et écritures des secteurs 1 et 2.
  • Échangez l’ancien stockage pour les secteurs 1 et 2 avec un nouveau stockage. Si la tentative d’échange échoue (restauration) :
    • Restaurez le stockage d’origine pour les secteurs 1 et 2.
    • Supprimez les secteurs 1 et 2 données combinées de la zone de travail.
    • Échec de l’opération d’écriture du secteur 2.
  • Débloquer les lectures et les écritures pour les secteurs 1 et 2.

La possibilité de fournir des mécanismes de verrouillage autour des modifications du secteur et de restaurer les modifications lorsque la tentative d’échange de secteur échoue est considérée comme conforme à la transition. Pour les implémentations qui utilisent le stockage physique pour un stockage étendu, il inclut les aspects appropriés du journal des transactions pour sécuriser et restaurer les modifications appliquées aux structures sur disque pour maintenir l’intégrité des fichiers de base de données SQL Server.

Tout appareil qui permet la réécriture des secteurs doit prendre en charge les réécritures de manière transactionnelle afin que SQL Server ne soit pas exposé à la perte de données.

Remarque

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

Soyez prudent 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 démarre pas. Si la base de données tempdb ne peut pas être créée, démarrez SQL Server à l’aide du 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’instruction ALTER DATABASE et la MODIFY FILE clause pour modifier les noms de fichiers physiques de chaque fichier de la base de données tempdb pour faire référence au nouvel emplacement physique, tel que 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 produits partenaires ne sont pas une garantie de compatibilité ou de 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 certifiera pas la compatibilité ou l’adéquation à un usage particulier dans SQL Server.

Soutien

Si vous utilisez un sous-système avec SQL Server qui prend en charge les garanties d’E/S pour l’utilisation de base de données transactionnelle comme décrit dans cet article, Microsoft fournit la prise en charge des applications SQL Server et SQL Server. Toutefois, les problèmes liés ou provoqués par le sous-système sont référencés par le fabricant.

Pour les problèmes liés à la base de données tempdb, Support Microsoft Services vous demande de déplacer la base de données tempdb. Contactez votre fournisseur d’appareils pour vérifier que vous avez correctement déployé et configuré l’appareil pour l’utilisation de la base de données transactionnelle.

Microsoft ne certifiera pas ou ne valide pas que les produits tiers fonctionnent correctement avec SQL Server. En outre, Microsoft ne fournit aucune garantie, garantie ou déclaration de l’adéquation d’un produit tiers à utiliser avec SQL Server.

références

Pour plus d’informations, consultez :

  • Last updated on