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

  • Article

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

Version d’origine du produit : Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2012 SQL Server 2014
Numéro de la 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 de journalisation Write-Ahead (WAL) via des principaux d’E/S spécifiques. Ces exigences sont nécessaires pour respecter les propriétés ACID des transactions : Atomique, Cohérent, Isolé et Durable. Les informations sur les exigences de conformité du sous-système d’E/S sont fournies dans les références suivantes :

SQL Server 2000 Principes de base des E/Shttps://technet.microsoft.com/library/cc966500.aspx

Remarque

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

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

  • L’ordre des écritures 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 doit se produire.

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

Conditions requises pour les E/S Brève description Système ou utilisateur tempdb
Classement des écritures

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 de groupe et SQL Server’utilisation du protocole WAL. Obligatoire Recommandé
Lecture après écriture Capacité du sous-système à traiter les demandes de lecture avec l’image de données la plus récente lorsque la lecture est émise une fois l’écriture terminée. Obligatoire Obligatoire
Survie en cas de panne Possibilité pour les données de rester entièrement intactes (durable) pendant une panne, comme 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 de 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 proche. * Déconseillé, autorisé uniquement en cas de transaction * Déconseillé, autorisé uniquement en cas de transaction
Données renforcées L’attente selon laquelle, lorsqu’une demande d’écriture ou une opération FlushFileBuffers est terminée avec succès, les données sont enregistrées sur un média stable. Obligatoire 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, ce qui permet SQL Server d’effectuer des écritures sur des limites alignées sur le secteur physique et dans des multiples de la taille du secteur. Obligatoire Obligatoire

Les réécritures de secteur transactionnel impliquent des opérations entièrement journalisées par le sous-système, ce qui permet à un secteur d’être entièrement déplacé, remplacé ou restauré vers 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 de telles actions. Un utilitaire qui déplace les données du fichier en est un defragmentation exemple. 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 afin que toute défaillance, 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 permet de maintenir les autres locataires de SQL Server E/S.

Survie en cas de panne

La base de données tempdb est une zone de travail pour SQL Server et est reconstruite à chaque SQL Server démarrage. 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 sur incident, les enregistrements de journal doivent être correctement stockés sur un support stable avant le stockage de la page de données et ne peuvent pas être réécrites 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 le classement des écritures, les écritures alignées et dimensionnées par secteur, et d’autres attributs de sécurité d’E/S tels que décrits dans les documents mentionnés précédemment. Pour la base de données tempdb, la récupération sur incident n’est pas nécessaire, car la base de données est toujours initialisée pendant SQL Server démarrage. 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 en stricte conformité avec les protocoles de lecteur de disque établis. De toutes les 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 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 au cours de ce type de réécriture peut rendre la base de données inutilisable et endommager des données qui SQL Server déjà considérées comme sécurisées.

Remarque

SQL Server support étendu ou compression 2005 pour lire uniquement les bases de données et les groupes de fichiers. Pour plus d’informations, consultez la documentation en ligne de SQL Server 2005.

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 qui peuvent réécrire des données que SQL Server considère comme sécurisées. Par exemple, certaines des technologies émergentes effectuent la mise en cache en mémoire ou la compression des données. Afin d’éviter des dommages graves à la base de données, toute réécriture de secteur doit disposer d’une prise en charge transactionnelle complète de telle sorte qu’en cas de défaillance, les données sont restaurées sur les images de secteur précédentes. Cela garantit que SQL Server n’est jamais exposé à une interruption inattendue ou à des dommages de données.

Vous pouvez 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 à 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 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 à l’é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 cible iSCSI et cluster de basculement ne peuvent pas être utilisées sur le même hôte.

Informations supplémentaires

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, sans s’y limiter, l’empreinte 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 la scalabilité et la réactivité d’un système. Cette section décrit les facteurs clés pour déterminer les besoins de stockage pour la base de données tempdb.

Sous-systèmes à haut débit

Il existe diverses implémentations de sous-système à haut débit disponibles sur le marché qui fournissent SQL Server exigences de protocole de sous-système d’E/S, mais qui ne fournissent pas de durabilité du média.

Importante

Vérifiez toujours auprès du fournisseur du produit pour garantir la conformité totale avec SQL Server besoins en matière d’E/S.

Un disque RAM est un exemple courant d’une telle implémentation. Les disques RAM installent les pilotes nécessaires et permettent à une partie de la main disque RAM d’apparaître 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é totale avec les exigences SQL Server d’E/S. Toutefois, il est évident qu’un disque RAM n’est pas un média 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 de discussion, mais des résultats similaires se produisent dans d’autres implémentations à haut débit.

Sécurité des E/S

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

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

Les tables temporaires sont comme 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 des pages de table temporaires sur un disque RAM entraîne une mise en cache double de la RAM, une dans le pool de mémoires tampons et une autre 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 main RAM comme son nom l’indique. Plusieurs implémentations de disques RAM et de caches de fichiers basés sur la RAM sont disponibles. Certains activent également les opérations de stockage d’E/S physiques. L’élément clé du cache de fichiers basé sur la RAM est qu’il retire directement de la mémoire physique qui peut être utilisée par SQL Server. Ayez toujours des preuves solides que l’ajout d’un cache de fichiers basé sur la RAM améliore les performances de l’application et ne diminue pas les performances des autres requêtes ou applications.

Régler 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. Souvent, l’ajout d’un index peut supprimer complètement le besoin de tri ou de hachage dans le plan, ce qui entraîne des performances optimales sans nécessiter l’utilisation de la base de données tempdb.

Points d’avantage possibles

Les avantages de placer la base de données tempdb sur un système à haut débit ne peuvent être déterminés que par des tests rigoureux et des mesures des charges de travail d’application. La charge de travail doit être soigneusement étudiée pour connaître 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 conjointement 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 2005, réduisant ainsi 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 prendre en compte les niveaux de mémoire et les activités de requête actuelles lors de la prise de décisions de plan de requête qui impliquent l’utilisation d’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 de l’application. Nous vous recommandons vivement de terminer les 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 diverses activités impliquant les tris, les hachages, le magasin de versions de lignes et les tables temporaires :

  • Les tables temporaires sont gérées par les routines de pool de mémoires tampons courantes pour les pages de données et ne présentent généralement pas d’avantages en matière de performances liés aux 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 d’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 bases de référence avec et sans la base de données tempdb stockée sur le sous-système à haut débit pour comparer les avantages. Une partie des tests 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 confirmer que ces requêtes ne sont pas affectées négativement. 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 des E/S de la 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 la 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 appareil, tel qu’un disque RAM, augmente les E/S réelles pour 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 la 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.
Espérance de vie des pages Une diminution 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 débit peut probablement indiquer que la mémoire retirée du pool de mémoires tampons force les pages de base de données à quitter prématurément le pool de mémoires tampons. Combinez avec les autres indicateurs et testez pour comprendre pleinement les limites des paramètres.
Débit global
Utilisation du processeur
Évolutivité
Temps de réponse		 L’objectif principal d’un changement de configuration de base de données tempdb est d’augmenter le débit global. Vos tests doivent inclure une combinaison de charges de travail reproductibles qui peuvent faire l’objet d’un scale-out pour déterminer l’impact du débit.

Une implémentation de disque RAM basée sur la compression peut bien fonctionner 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. De vrais tests de contrainte et de futurs tests de prédiction de charge sont encouragés.
Actions de création de fichiers de travail et de table de travail Si le déplacement de 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 de travail ou des tables de travail, cela indique que la mémoire extraite du pool de mémoires 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 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éveloppe la sécurité des données requise par SQL Server bases de données.

Supposons qu’un fournisseur de disque 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 fichier comme si le secteur était aligné et dimensionné afin SQL Server n’est pas au courant et est correctement sécurisé de l’implémentation sous-jacente. Examinez l’exemple de compression de plus près.

  • Le secteur 1 est écrit sur l’appareil et 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’elles sont combinées avec les données du secteur 2.

  • Bloquer 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.
  • Bloquer toutes les lectures et écritures des secteurs 1 et 2.
  • Échangez l’ancien stockage pour les secteurs 1 et 2 avec le nouveau stockage. Si la tentative d’échange échoue (restauration) :
    • Restaurez le stockage d’origine pour les secteurs 1 et 2.
    • Supprimez les données combinées des secteurs 1 et 2 de la zone de travail.
    • Échec de l’opération d’écriture du secteur 2.
  • Débloquer les lectures et écritures pour les secteurs 1 et 2.

La possibilité de fournir des mécanismes de verrouillage autour des modifications de secteur et de restaurer les modifications en cas d’échec de la tentative d’échange de secteur est considérée comme conforme de manière transitoire. Pour les implémentations qui utilisent le stockage physique pour la sauvegarde étendue, elle inclurait les aspects appropriés du journal des transactions pour sécuriser et restaurer les modifications appliquées aux structures sur disque afin de maintenir l’intégrité des fichiers de base de données SQL Server.

Tout appareil qui active la réécriture de 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

La instance de SQL Server est redémarrée en cas d’échecs d’E/S en ligne et de restauration 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 certifie pas la compatibilité ou l’adéquation à un usage 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 des bases de données transactionnelles, comme décrit dans cet article, Microsoft prend en charge les applications SQL Server et SQL Server. Toutefois, les problèmes liés au sous-système ou causés par celui-ci sont adressés au 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 le fournisseur de votre appareil pour vérifier que vous avez correctement déployé et configuré l’appareil pour une utilisation de base de données transactionnelle.

Microsoft ne certifie 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 pour une utilisation avec SQL Server.

References

Pour plus d’informations, reportez-vous aux articles suivants de la Base de connaissances Microsoft :