MS SQL Server - Architecture

Nous avons classé l'architecture de SQL Server dans les parties suivantes pour une compréhension facile -

  • Architecture générale
  • Architecture mémoire
  • Architecture des fichiers de données
  • Architecture des fichiers journaux

Architecture générale

Client - Où la demande a été lancée.

Query - Requête SQL qui est un langage de haut niveau.

Logical Units - Mots clés, expressions et opérateurs, etc.

N/W Packets - Code lié au réseau.

Protocols - Dans SQL Server, nous avons 4 protocoles.

  • Mémoire partagée (à des fins de connexions locales et de dépannage).

  • Canalisations nommées (pour les connexions qui sont en connectivité LAN).

  • TCP / IP (pour les connexions qui sont en connectivité WAN).

  • Adaptateur d'interface virtuelle VIA (nécessite un matériel spécial à configurer par le fournisseur et également obsolète à partir de la version SQL 2012).

Server - Où les services SQL ont été installés et les bases de données résident.

Relational Engine- C'est là que la véritable exécution se fera. Il contient un analyseur de requête, un optimiseur de requête et un exécuteur de requête.

Query Parser (Command Parser) and Compiler (Translator) - Cela vérifiera la syntaxe de la requête et la convertira en langage machine.

Query Optimizer - Il préparera le plan d'exécution en sortie en prenant la requête, les statistiques et l'arbre d'algébrizer en entrée.

Execution Plan - C'est comme une feuille de route, qui contient l'ordre de toutes les étapes à effectuer dans le cadre de l'exécution de la requête.

Query Executor - C'est là que la requête sera exécutée étape par étape à l'aide du plan d'exécution et le moteur de stockage sera également contacté.

Storage Engine - Il est responsable du stockage et de la récupération des données sur le système de stockage (disque, SAN, etc.), de la manipulation des données, du verrouillage et de la gestion des transactions.

SQL OS- Cela se situe entre la machine hôte (système d'exploitation Windows) et SQL Server. Toutes les activités effectuées sur le moteur de base de données sont prises en charge par SQL OS. SQL OS fournit divers services de système d'exploitation, tels que la gestion de la mémoire traite du pool de mémoire tampon, de la mémoire tampon du journal et de la détection de blocage à l'aide de la structure de blocage et de verrouillage.

Checkpoint Process- Checkpoint est un processus interne qui écrit toutes les pages sales (pages modifiées) du Buffer Cache sur le disque physique. En dehors de cela, il écrit également les enregistrements du journal du tampon du journal vers le fichier physique. L'écriture de pages sales du cache tampon vers un fichier de données est également connue sous le nom de durcissement des pages sales.

Il s'agit d'un processus dédié et s'exécute automatiquement par SQL Server à des intervalles spécifiques. SQL Server exécute le processus de point de contrôle pour chaque base de données individuellement. Checkpoint aide à réduire le temps de récupération de SQL Server en cas d'arrêt inattendu ou de panne système \ Failure.

Points de contrôle dans SQL Server

Dans SQL Server 2012, il existe quatre types de checkpoints -

  • Automatic - Il s'agit du point de contrôle le plus courant qui s'exécute en tant que processus en arrière-plan pour s'assurer que la base de données SQL Server peut être récupérée dans le délai défini par l'Intervalle de récupération - Option de configuration du serveur.

  • Indirect- Ceci est nouveau dans SQL Server 2012. Cela s'exécute également en arrière-plan, mais pour respecter un temps de récupération cible spécifié par l'utilisateur pour la base de données spécifique où l'option a été configurée. Une fois que Target_Recovery_Time pour une base de données donnée a été sélectionné, cela remplacera l'intervalle de récupération spécifié pour le serveur et évitera un point de contrôle automatique sur une telle base de données.

  • Manual- Celle-ci s'exécute comme n'importe quelle autre instruction T-SQL, une fois que vous émettez la commande de point de contrôle, elle s'exécute jusqu'à son achèvement. Le point de contrôle manuel s'exécute uniquement pour votre base de données actuelle. Vous pouvez également spécifier le Checkpoint_Duration qui est facultatif - cette durée spécifie l'heure à laquelle vous souhaitez que votre point de contrôle se termine.

  • Internal- En tant qu'utilisateur, vous ne pouvez pas contrôler le point de contrôle interne. Émis sur des opérations spécifiques telles que

    • Shutdown lance une opération de point de contrôle sur toutes les bases de données sauf lorsque l'arrêt n'est pas propre (shutdown with nowait).

    • Si le modèle de récupération passe de Full \ Bulk-logs à Simple.

    • Lors de la sauvegarde de la base de données.

    • Si votre base de données est dans un modèle de récupération simple, le processus de point de contrôle s'exécute automatiquement soit lorsque le journal est plein à 70%, soit en fonction de l'option Serveur - Intervalle de récupération.

    • La commande Alter database pour ajouter ou supprimer un fichier data \ log lance également un point de contrôle.

    • Le point de contrôle a également lieu lorsque le modèle de récupération de la base de données est journalisé en bloc et qu'une opération journalisée au minimum est effectuée.

    • Création d'instantanés de base de données.

  • Lazy Writer Process- L'écrivain paresseux poussera les pages sales sur le disque pour une raison entièrement différente, car il a besoin de libérer de la mémoire dans le pool de mémoire tampon. Cela se produit lorsque le serveur SQL est soumis à une pression de mémoire. Autant que je sache, cela est contrôlé par un processus interne et il n'y a aucun paramètre pour cela.

Le serveur SQL surveille constamment l'utilisation de la mémoire pour évaluer les conflits de ressources (ou la disponibilité); son travail est de s'assurer qu'une certaine quantité d'espace libre est disponible à tout moment. Dans le cadre de ce processus, lorsqu'il remarque une telle contention de ressources, il déclenche Lazy Writer pour libérer certaines pages en mémoire en écrivant des pages sales sur le disque. Il utilise l'algorithme le moins récent (LRU) pour décider quelles pages doivent être vidées sur le disque.

Si Lazy Writer est toujours actif, cela peut indiquer un goulot d'étranglement de la mémoire.

Architecture de la mémoire

Voici quelques-unes des principales caractéristiques de l'architecture de la mémoire.

  • L'un des principaux objectifs de conception de tous les logiciels de base de données est de minimiser les E / S disque, car les lectures et écritures sur disque sont parmi les opérations les plus gourmandes en ressources.

  • La mémoire dans Windows peut être appelée avec Virtual Address Space, partagée par le mode noyau (mode OS) et le mode utilisateur (application comme SQL Server).

  • «L'espace d'adressage utilisateur» de SQL Server est divisé en deux régions: MemToLeave et Buffer Pool.

  • La taille de MemToLeave (MTL) et du pool de tampons (BPool) est déterminée par SQL Server au démarrage.

  • Buffer managementest un élément clé pour atteindre une efficacité élevée des E / S. Le composant de gestion de la mémoire tampon comprend deux mécanismes: le gestionnaire de mémoire tampon pour accéder aux pages de base de données et les mettre à jour, et le pool de mémoire tampon pour réduire les E / S des fichiers de base de données.

  • Le pool de tampons est divisé en plusieurs sections. Les plus importants étant le cache de tampon (également appelé cache de données) et le cache de procédure.Buffer cacheconserve les pages de données en mémoire afin que les données fréquemment consultées puissent être extraites du cache. L'alternative serait de lire des pages de données à partir du disque. La lecture des pages de données à partir du cache optimise les performances en minimisant le nombre d'opérations d'E / S requises qui sont intrinsèquement plus lentes que la récupération des données de la mémoire.

  • Procedure cacheconserve la procédure stockée et les plans d'exécution de la requête pour minimiser le nombre de fois que les plans de requête doivent être générés. Vous pouvez trouver des informations sur la taille et l'activité dans le cache de procédures à l'aide de l'instruction DBCC PROCCACHE.

Les autres parties du pool tampon comprennent:

  • System level data structures - Contient les données au niveau de l'instance SQL Server sur les bases de données et les verrous.

  • Log cache - Réservé à la lecture et à l'écriture des pages du journal des transactions.

  • Connection context- Chaque connexion à l'instance dispose d'une petite zone de mémoire pour enregistrer l'état actuel de la connexion. Ces informations incluent la procédure stockée et les paramètres de fonction définis par l'utilisateur, les positions du curseur, etc.

  • Stack space - Windows alloue un espace de pile pour chaque thread démarré par SQL Server.

Architecture des fichiers de données

L'architecture du fichier de données comprend les composants suivants:

Groupes de fichiers

Les fichiers de base de données peuvent être regroupés en groupes de fichiers à des fins d'allocation et d'administration. Aucun fichier ne peut être membre de plusieurs groupes de fichiers. Les fichiers journaux ne font jamais partie d'un groupe de fichiers. L'espace du journal est géré séparément de l'espace de données.

Il existe deux types de groupes de fichiers dans SQL Server, principal et défini par l'utilisateur. Le groupe de fichiers principal contient le fichier de données principal et tous les autres fichiers non spécifiquement affectés à un autre groupe de fichiers. Toutes les pages des tables système sont allouées dans le groupe de fichiers principal. Les groupes de fichiers définis par l'utilisateur sont tous les groupes de fichiers spécifiés à l'aide du mot clé de groupe de fichiers dans l'instruction create database ou alter database.

Un groupe de fichiers dans chaque base de données fonctionne comme groupe de fichiers par défaut. Lorsque SQL Server alloue une page à une table ou un index pour lequel aucun groupe de fichiers n'a été spécifié lors de leur création, les pages sont allouées à partir du groupe de fichiers par défaut. Pour basculer le groupe de fichiers par défaut d'un groupe de fichiers vers un autre groupe de fichiers, il doit avoir le rôle de base de données fixe db_owner.

Par défaut, le groupe de fichiers principal est le groupe de fichiers par défaut. L'utilisateur doit avoir le rôle de base de données fixe db_owner afin de sauvegarder individuellement les fichiers et les groupes de fichiers.

Des dossiers

Les bases de données ont trois types de fichiers: fichier de données principal, fichier de données secondaire et fichier journal. Le fichier de données principal est le point de départ de la base de données et pointe vers les autres fichiers de la base de données.

Chaque base de données possède un fichier de données principal. Nous pouvons donner n'importe quelle extension pour le fichier de données principal, mais l'extension recommandée est.mdf. Le fichier de données secondaire est un fichier autre que le fichier de données principal de cette base de données. Certaines bases de données peuvent avoir plusieurs fichiers de données secondaires. Certaines bases de données peuvent ne pas avoir un seul fichier de données secondaire. L'extension recommandée pour le fichier de données secondaire est.ndf.

Les fichiers journaux contiennent toutes les informations de journal utilisées pour récupérer la base de données. La base de données doit avoir au moins un fichier journal. Nous pouvons avoir plusieurs fichiers journaux pour une base de données. L'extension recommandée pour le fichier journal est.ldf.

L'emplacement de tous les fichiers d'une base de données est enregistré à la fois dans la base de données principale et dans le fichier principal de la base de données. La plupart du temps, le moteur de base de données utilise l'emplacement du fichier de la base de données master.

Les fichiers ont deux noms: logique et physique. Le nom logique est utilisé pour faire référence au fichier dans toutes les instructions T-SQL. Le nom physique est le nom_fichier_OS, il doit suivre les règles du système d'exploitation. Les fichiers de données et journaux peuvent être placés sur des systèmes de fichiers FAT ou NTFS, mais ne peuvent pas être placés sur des systèmes de fichiers compressés. Une base de données peut contenir jusqu'à 32 767 fichiers.

Étendues

Les étendues sont une unité de base dans laquelle l'espace est alloué aux tables et aux index. Une extension correspond à 8 pages contiguës ou 64 Ko. SQL Server a deux types d'extensions: uniformes et mixtes. Les étendues uniformes sont constituées d'un seul objet. Les étendues mixtes sont partagées par jusqu'à huit objets.

Des pages

C'est l'unité fondamentale de stockage de données dans MS SQL Server. La taille de la page est de 8 Ko. Le début de chaque page est un en-tête de 96 octets utilisé pour stocker des informations système telles que le type de page, la quantité d'espace libre sur la page et l'identifiant d'objet de l'objet propriétaire de la page. Il existe 9 types de pages de données dans SQL Server.

  • Data - Lignes de données avec toutes les données à l'exception des données texte, ntext et image.

  • Index - Entrées d'index.

  • Tex\Image - Données texte, image et ntext.

  • GAM - Informations sur les extensions allouées.

  • SGAM - Informations sur les extensions allouées au niveau du système.

  • Page Free Space (PFS) - Informations sur l'espace libre disponible sur les pages.

  • Index Allocation Map (IAM) - Informations sur les étendues utilisées par une table ou un index.

  • Bulk Changed Map (BCM) - Informations sur les extensions modifiées par les opérations en bloc depuis la dernière instruction de journal de sauvegarde.

  • Differential Changed Map (DCM) - Informations sur les extensions qui ont changé depuis la dernière instruction de sauvegarde de la base de données.

Architecture des fichiers journaux

Le journal des transactions SQL Server fonctionne de manière logique comme si le journal des transactions était une chaîne d'enregistrements de journal. Chaque enregistrement de journal est identifié par un numéro de séquence de journal (LSN). Chaque enregistrement de journal contient l'ID de la transaction à laquelle il appartient.

Les enregistrements de journal pour les modifications de données enregistrent soit l'opération logique effectuée, soit les images avant et après des données modifiées. L'image avant est une copie des données avant que l'opération ne soit effectuée; l'image après est une copie des données après que l'opération a été effectuée.

Les étapes de récupération d'une opération dépendent du type d'enregistrement de journal -

  • Opération logique enregistrée.
    • Pour faire avancer l'opération logique, l'opération est à nouveau exécutée.
    • Pour annuler l'opération logique, l'opération logique inverse est exécutée.
  • Image avant et après enregistrée.
    • Pour faire avancer l'opération, l'image après est appliquée.
    • Pour annuler l'opération, l'image avant est appliquée.

Différents types d'opérations sont enregistrés dans le journal des transactions. Ces opérations comprennent -

  • Le début et la fin de chaque transaction.

  • Chaque modification de données (insertion, mise à jour ou suppression). Cela inclut les modifications apportées par les procédures stockées système ou les instructions DDL (Data Definition Language) à n'importe quelle table, y compris les tables système.

  • Chaque extension et allocation de page ou de répartition.

  • Créer ou supprimer une table ou un index.

Les opérations de restauration sont également enregistrées. Chaque transaction réserve de l'espace dans le journal des transactions pour s'assurer que suffisamment d'espace de journal existe pour prendre en charge une restauration provoquée par une instruction d'annulation explicite ou si une erreur est rencontrée. Cet espace réservé est libéré lorsque la transaction est terminée.

La section du fichier journal du premier enregistrement de journal qui doit être présente pour une restauration réussie à l'échelle de la base de données vers le dernier enregistrement de journal écrit est appelée la partie active du journal ou journal actif. Il s'agit de la section du journal requise pour une restauration complète de la base de données. Aucune partie du journal actif ne peut jamais être tronquée. Le LSN de ce premier enregistrement de journal est connu sous le nom de LSN de récupération minimum (LSN minimum).

Le moteur de base de données SQL Server divise chaque fichier journal physique en interne en un certain nombre de fichiers journaux virtuels. Les fichiers journaux virtuels n'ont pas de taille fixe et il n'y a pas de nombre fixe de fichiers journaux virtuels pour un fichier journal physique.

Le moteur de base de données choisit la taille des fichiers journaux virtuels de manière dynamique lors de la création ou de l'extension des fichiers journaux. Le moteur de base de données tente de conserver un petit nombre de fichiers virtuels. La taille ou le nombre de fichiers journaux virtuels ne peuvent pas être configurés ou définis par les administrateurs. Le seul moment où les fichiers journaux virtuels affectent les performances du système est lorsque les fichiers journaux physiques sont définis par des valeurs de petite taille et growth_increment.

La valeur de taille correspond à la taille initiale du fichier journal et la valeur growth_increment correspond à la quantité d'espace ajoutée au fichier chaque fois qu'un nouvel espace est requis. Si les fichiers journaux atteignent une grande taille en raison de nombreux petits incréments, ils auront de nombreux fichiers journaux virtuels. Cela peut ralentir le démarrage de la base de données et également consigner les opérations de sauvegarde et de restauration.

Nous vous recommandons d'attribuer aux fichiers journaux une valeur de taille proche de la taille finale requise et d'avoir également une valeur growth_increment relativement grande. SQL Server utilise un journal à écriture anticipée (WAL), qui garantit qu'aucune modification de données n'est écrite sur le disque avant que l'enregistrement de journal associé ne soit écrit sur le disque. Cela maintient les propriétés ACID pour une transaction.