Architecture SQL Server (expliquée)

MS SQL Server est une architecture client-serveur. Le processus MS SQL Server démarre avec l'application client qui envoie une demande. Le serveur SQL accepte, traite et répond à la demande avec les données traitées. Discutons en détail de l'ensemble de l'architecture présentée ci-dessous :

Comme le montre le diagramme ci-dessous, il existe trois composants principaux dans l'architecture SQL Server :

  1. Couche de protocole
  2. Moteur relationnel
  3. Moteur de stockage
Architecture du serveur SQL
Diagramme d'architecture du serveur SQL

Couche de protocole – SNI

MS SQL SERVER PROTOCOL LAYER prend en charge 3 types d'architecture client-serveur. Nous commencerons par «Trois types d'architecture client-serveur» pris en charge par MS SQL Server.

La memoire partagée

Reconsidérons un scénario de conversation tôt le matin.

Couche de protocole - SNI

MAMAN et TOM – Ici Tom et sa Maman, étaient au même endroit logique, c'est à dire chez eux. Tom a pu demander du café et maman a pu le servir chaud.

SERVEUR MS SQL – Ici MS SQL le serveur fournit PROTOCOLE DE MÉMOIRE PARTAGÉE. Ici CLIENT et MS SQL serveur exécuté sur la même machine. Les deux peuvent communiquer via le protocole de mémoire partagée.

Analogie: Cartographions les entités dans les deux scénarios ci-dessus. Nous pouvons facilement mapper Tom au client, Mom au serveur SQL, Home à la machine et la communication verbale au protocole de mémoire partagée.

Depuis le bureau de configuration et d'installation :

Pour la connexion à la base de données locale – Entrée Studio de gestion SQL, l'option « Nom du serveur » pourrait être

"."

"hôte local"

"127.0.0.1"

« Machine\Instance »

Couche de protocole - SNI

TCP / IP

Considérons maintenant que le soir, Tom est d'humeur à faire la fête. Il veut un café commandé dans un café réputé. Le café est situé à 10 km de son domicile.

TCP / IP

Ici, Tom et Starbuck se trouvent dans des emplacements physiques différents. Tom à la maison et Starbucks sur le marché animé. Ils communiquent via le réseau cellulaire. De même, MS SQL SERVER offre la possibilité d'interagir via Protocole TCP / IP, où CLIENT et MS SQL Server sont distants l'un de l'autre et installés sur une machine distincte.

Analogie: Cartographions les entités dans les deux scénarios ci-dessus. Nous pouvons facilement mapper Tom au client, Starbuck au serveur SQL, la place de marché/domicile à un emplacement distant et enfin le réseau cellulaire au protocole TCP/IP.

Notes du bureau de configuration/installation :

  • Dans SQL Management Studio – Pour la connexion via TCP\IP, l'option « Nom du serveur » doit être « Machine\Instance du serveur ».
  • Le serveur SQL utilise le port 1433 dans TCP/IP.

TCP / IP

Pipes Nommées

Finalement, le soir, Tom voulait prendre un thé vert clair que sa voisine Sierra prépare très bien.

Pipes Nommées

Ici Tom et ses Voisin, Sierra, sont dans le même Physique emplacement, étant voisin l'un de l'autre. Ils communiquent via Intra-réseau. De même, le SERVEUR MS SQL offre la possibilité d'interagir via le Canal nommé protocole. Ici le CLIENT et SERVEUR MS SQL sont en connexion via LAN.

Analogie: Cartographions les entités dans les deux scénarios ci-dessus. Nous pouvons facilement mapper Tom au client, Sierra au serveur SQL, Voisin au LAN et enfin Intra-réseau au Named Pipe Protocol.

Notes du bureau de configuration/installation :

  • Pour la connexion via un canal nommé. Cette option est désactivée par défaut et doit être activée par le gestionnaire de configuration SQL.

Qu'est-ce que TDS?

Maintenant que nous savons qu'il existe trois types d'architecture client-serveur, jetons un coup d'œil à TDS :

  • TDS signifie Flux de données tabulaires.
  • Les 3 protocoles utilisent des paquets TDS. TDS est encapsulé dans des paquets réseau. Cela permet le transfert de données de la machine client vers la machine serveur.
  • TDS a d'abord été développé par Sybase et appartient désormais à Microsoft

Moteur relationnel

Le moteur relationnel est également connu sous le nom de processeur de requêtes. Il a la SQL Server des composants qui déterminent exactement ce qu'une requête doit faire et comment elle peut être exécutée au mieux. Il est responsable de l'exécution des requêtes des utilisateurs en demandant des données au moteur de stockage et en traitant les résultats renvoyés.

Comme le montre le schéma architectural, il y a 3 composants majeurs du moteur relationnel. Étudions les composants en détail :

Analyseur CMD

Les données une fois reçues de Protocol Layer sont ensuite transmises au moteur relationnel. « Analyseur CMD » est le premier composant du moteur relationnel à recevoir les données de la requête. La tâche principale de CMD Parser est de vérifier la requête pour Erreur syntaxique et sémantique. Enfin, il génère un arbre de requête. Discutons en détail.

Analyseur CMD

Vérification syntaxique :

  • Comme tous les autres langages de programmation, MS SQL dispose également d’un ensemble prédéfini de mots-clés. De plus, SQL Server possède sa propre grammaire que SQL Server comprend.
  • SELECT, INSERT, UPDATE et bien d’autres appartiennent aux listes de mots clés prédéfinies MS SQL.
  • CMD Parser effectue une vérification syntaxique. Si la saisie des utilisateurs ne suit pas ces règles de syntaxe ou de grammaire du langage, elle renvoie une erreur.

Mise en situation : Disons qu'un Russe est allé dans un restaurant japonais. Il commande un fast-food en russe. Malheureusement, le serveur ne comprend que le japonais. Quel serait le résultat le plus évident ?

La réponse est : le serveur n’est pas en mesure de poursuivre le traitement de la commande.

Il ne devrait y avoir aucun écart dans la grammaire ou le langage accepté par le serveur SQL. Si c'est le cas, le serveur SQL ne peut pas le traiter et renverra donc un message d'erreur.

Nous en apprendrons davantage sur les requêtes MS SQL dans les prochains didacticiels. Pourtant, considérons ci-dessous la syntaxe de requête la plus basique comme

SELECT * from <TABLE_NAME>;

Maintenant, pour avoir une idée de ce que fait la syntaxe, disons si l'utilisateur exécute la requête de base comme ci-dessous :

SELECR * from <TABLE_NAME>

Notez qu'au lieu de « SELECT », l'utilisateur a tapé « SELECR ».

Résultat: L'analyseur CMD analysera cette instruction et générera le message d'erreur. Comme « SELECR » ne suit pas le nom du mot-clé et la grammaire prédéfinis. Ici, CMD Parser attendait « SELECT ».

Vérification sémantique :

  • Ceci est effectué par Normalizer.
  • Dans sa forme la plus simple, il vérifie si le nom de la colonne et le nom de la table interrogée existent dans le schéma. Et s'il existe, liez-le à Query. Ceci est également connu sous le nom Fixations.
  • Avecplexity augmente lorsque les requêtes des utilisateurs contiennent VIEW. Normalizer effectue le remplacement par la définition de vue stockée en interne et bien plus encore.

Comprenons cela à l'aide de l'exemple ci-dessous -

SELECT * from USER_ID

Résultat: L'analyseur CMD analysera cette instruction pour une vérification sémantique. L'analyseur générera un message d'erreur car Normalizer ne trouvera pas la table demandée (USER_ID) car elle n'existe pas.

Créer un arbre de requête :

  • Cette étape génère différents arbres d'exécution dans lesquels la requête peut être exécutée.
  • Notez que tous les différents arbres ont le même résultat souhaité.

Optimizer

Le travail de l'optimiseur consiste à créer un plan d'exécution pour la requête de l'utilisateur. C'est le plan qui déterminera la manière dont la requête de l'utilisateur sera exécutée.

Notez que toutes les requêtes ne sont pas optimisées. L'optimisation est effectuée pour les commandes DML (Data Modification Language) telles que SELECT, INSERT, DELETE et UPDATE. Ces requêtes sont d'abord marquées puis envoyées à l'optimiseur. Les commandes DDL comme CREATE et ALTER ne sont pas optimisées, mais elles sont plutôt compilées dans un formulaire interne. Le coût de la requête est calculé en fonction de facteurs tels que l'utilisation du processeur, l'utilisation de la mémoire et les besoins en entrées/sorties.

Le rôle de l'optimiseur est de trouver le plan d'exécution le moins cher, mais pas le meilleur et le plus rentable.

Avant d'entrer dans les détails plus techniques d'Optimizer, considérons l'exemple réel ci-dessous :

Mise en situation :

Disons que vous souhaitez ouvrir un compte bancaire en ligne. Vous connaissez déjà une banque qui met au maximum 2 jours pour ouvrir un compte. Mais vous avez également une liste de 20 autres banques, ce qui peut ou non prendre moins de 2 jours. Vous pouvez commencer à dialoguer avec ces banques pour déterminer lesquelles prennent moins de 2 jours. Désormais, vous ne trouverez peut-être pas de banque en moins de 2 jours, et du temps supplémentaire sera perdu en raison de l'activité de recherche elle-même. Il aurait été préférable d'ouvrir un compte auprès de la première banque elle-même.

Conclusion: Il est plus important de choisir judicieusement. Pour être précis, choisissez lequel l’option est la meilleure, pas la moins chère.

De même, MS SQL Optimizer fonctionne sur des algorithmes exhaustifs/heuristiques intégrés. L’objectif est de minimiser le temps d’exécution des requêtes. Tous les algorithmes de l'Optimizer sont propriété de Microsoft et un secret. Bien que, Vous trouverez ci-dessous les étapes de haut niveau effectuées par MS SQL Optimizer. Les recherches d'optimisation suivent trois phases, comme indiqué dans le diagramme ci-dessous :

Optimizer

Phase 0 : Recherche d'un plan trivial :

  • Ceci est également connu comme Étape de pré-optimisation.
  • Dans certains cas, il ne peut y avoir qu’un seul plan pratique et réalisable, appelé plan trivial. Il n’est pas nécessaire de créer un plan optimisé. La raison en est que rechercher davantage aboutirait à trouver le même plan d’exécution au moment de l’exécution. Cela aussi avec le coût supplémentaire de la recherche d'un plan optimisé qui n'était pas du tout nécessaire.
  • Si aucun plan Trivial n'est trouvé, alors 1st La phase commence.

Phase 1 : Recherche de plans de traitement des transactions

  • Cela inclut la recherche de Simple et Complex Plan.
  • Recherche de plan simple : les données passées de la colonne et de l'index impliquées dans la requête seront utilisées pour l'analyse statistique. Cela consiste généralement, mais sans s'y limiter, à un index par table.
  • Pourtant, si le plan simple n'est pas trouvé, alors plus de complex Le plan est recherché. Cela implique plusieurs index par table.

Phase 2 : Traitement parallèle et optimisation.

  • Si aucune des stratégies ci-dessus ne fonctionne, Optimizer recherche les possibilités de traitement parallèle. Cela dépend des capacités de traitement et de la configuration de la machine.
  • Si cela n’est toujours pas possible, la phase finale d’optimisation commence. Désormais, l’objectif final de l’optimisation est de trouver toutes les autres options possibles pour exécuter la requête de la meilleure façon. Phase d'optimisation finale Les algorithmes sont Microsoft Convenance.

Exécuteur de requête

Exécuteur de requête

Appels de l'exécuteur de requêtes Méthode d'accès. Il fournit un plan d'exécution pour la logique de récupération des données requise pour l'exécution. Une fois les données reçues du moteur de stockage, le résultat est publié sur la couche de protocole. Enfin, les données sont envoyées à l'utilisateur final.

Moteur de stockage

Le travail du moteur de stockage consiste à stocker les données dans un système de stockage tel qu'un disque ou un SAN et à récupérer les données en cas de besoin. Avant d'approfondir le moteur de stockage, voyons comment les données sont stockées dans Base de données et type de fichiers disponibles.

Fichier de données et étendue :

Moteur de stockage

Le fichier de données stocke physiquement les données sous forme de pages de données, chaque page de données ayant une taille de 8 Ko, formant la plus petite unité de stockage de SQL Server. Ces pages de données sont regroupées logiquement pour former des extensions. Aucun objet ne se voit attribuer une page dans SQL Server.

La maintenance de l'objet se fait via des extensions. La page comporte une section appelée En-tête de page d'une taille de 96 octets, contenant les informations de métadonnées sur la page telles que le type de page, le numéro de page, la taille de l'espace utilisé, la taille de l'espace libre et le pointeur vers la page suivante et la page précédente. , etc.

Les types de fichiers

Les types de fichiers

  1. Fichier principal
  • Chaque base de données contient un fichier principal.
  • Celui-ci stocke toutes les données importantes liées aux tables, vues, déclencheurs, etc.
  • L'extension est .mdf généralement, mais peut avoir n'importe quelle extension.
  1. Fichier secondaire
  • La base de données peut contenir ou non plusieurs fichiers secondaires.
  • Ceci est facultatif et contient des données spécifiques à l’utilisateur.
  • L'extension est .naf généralement, mais peut avoir n'importe quelle extension.
  1. Fichier journal
  • Également connu sous le nom de journaux d’écriture anticipée.
  • L'extension est .ldf
  • Utilisé pour la gestion des transactions.
  • Ceci est utilisé pour récupérer de toute instance indésirable. Effectuez une tâche importante de restauration des transactions non validées.

Le moteur de stockage comporte 3 composants ; examinons-les en détail.

Méthode d'accès

Il agit comme une interface entre l'exécuteur de requêtes et Buffer Manager/Transaction Logs.

La méthode d'accès elle-même n'effectue aucune exécution.

La première action consiste à déterminer si la requête est :

  1. Sélectionnez l'instruction (DDL)
  2. Instruction non-sélection (DDL et DML)

En fonction du résultat, la méthode d'accès prend la suitewing étapes:

  1. Si la requête est DDL, instruction SELECT, la requête est transmise au Gestionnaire de tampon pour un traitement ultérieur.
  2. Et si je demande si Instruction DDL, NON-SELECT, la requête est transmise à Transaction Manager. Cela inclut principalement l'instruction UPDATE.

Méthode d'accès

Gestionnaire de tampon

Le gestionnaire de tampon gère les fonctions de base des modules ci-dessous :

  • Cacher les forfaits
  • Analyse des données : cache tampon et stockage des données
  • Page sale

Nous apprendrons le plan, le tampon et le cache de données dans cette section. Nous couvrirons les pages sales dans la section Transaction.

Gestionnaire de tampon

Cacher les forfaits

  • Plan de requête existant : Le gestionnaire de tampon vérifie si le plan d'exécution est présent dans le cache de plan stocké. Si oui, le cache du plan de requête et son cache de données associé sont utilisés.
  • Plan de cache pour la première fois : D'où vient le cache du plan existant ?Si le plan d'exécution de la première requête est en cours d'exécution et est complex, il est logique de le stocker dans le cache Plane. Cela garantira une disponibilité plus rapide la prochaine fois que le serveur SQL recevra la même requête. Il ne s'agit donc que de la requête elle-même, quelle exécution du plan est stockée si elle est exécutée pour la première fois.

Analyse des données : cache tampon et stockage de données

Le gestionnaire de tampon permet d'accéder aux données requises. Ci-dessous, deux approches sont possibles selon que des données existent ou non dans le cache de données :

Cache tampon – Analyse logicielle :

Cache tampon - Analyse logicielle

Buffer Manager recherche les données dans le tampon dans le cache de données. Si elles sont présentes, ces données sont utilisées par Query Executor. Cela améliore les performances car le nombre d'opérations d'E/S est réduit lors de la récupération des données du cache par rapport à la récupération des données du stockage de données.

Stockage des données – Analyse matérielle :

Stockage de données – Analyse matérielle

Si les données ne sont pas présentes dans Buffer Manager, les données requises sont recherchées dans le stockage de données. Il stocke également les données dans le cache de données pour une utilisation ultérieure.

Page sale

Il est stocké en tant que logique de traitement de Transaction Manager. Nous l'apprendrons en détail dans la section Transaction Manager.

Gestionnaire de transactions

Gestionnaire de transactions

Transaction Manager est appelé lorsque la méthode d'accès détermine que Query est une instruction Non-Select.

Gestionnaire de journaux

  • Log Manager garde une trace de toutes les mises à jour effectuées dans le système via les journaux dans les journaux de transactions.
  • Les journaux ont Enregistre le numéro de séquence avec l'ID de transaction et l'enregistrement de modification des données.
  • Ceci est utilisé pour garder une trace de Transaction validée et annulation de transaction.

Gestionnaire de verrouillage

  • Pendant la transaction, les données associées dans le stockage de données sont à l'état verrouillé. Ce processus est géré par Lock Manager.
  • Ce processus garantit cohérence et isolation des données. Également connues sous le nom de propriétés ACIDE.

Processus d'exécution

  • Log Manager démarre la journalisation et Lock Manager verrouille les données associées.
  • La copie des données est conservée dans le cache Buffer.
  • La copie des données censées être mises à jour est conservée dans le tampon de journal et tous les événements mettent à jour les données dans le tampon de données.
  • Les pages qui stockent les données sont également appelées Pages sales.
  • Journalisation des points de contrôle et des écritures anticipées : Ce processus s'exécute et marque toutes les pages des pages sales sur le disque, mais la page reste dans le cache. La fréquence est d'environ 1 passage par minute. Mais la page est d'abord poussée vers la page de données du fichier journal à partir du journal Buffer. C'est ce qu'on appelle Journalisation d'écriture anticipée.
  • Écrivain paresseux : La page sale peut rester en mémoire. Lorsque le serveur SQL observe une charge énorme et que de la mémoire tampon est nécessaire pour une nouvelle transaction, il libère les pages sales du cache. Il fonctionne sur Lru – Algorithme le moins récemment utilisé pour nettoyer la page du pool de tampons vers le disque.

Résumé

  • Il existe trois types d'architecture client-serveur : 1) Mémoire partagée 2) TCP/IP 3) Canaux nommés
  • TDS, développé par Sybase et maintenant propriété de Microsoft, est un paquet encapsulé dans des paquets réseau pour le transfert de données de la machine client vers la machine serveur.
  • Le moteur relationnel contient trois composants principaux :Analyseur CMD : Ceci est responsable des erreurs syntaxiques et sémantiques et génère enfin un arbre de requête.Optimiseur: Le rôle de l’optimiseur est de trouver le plan d’exécution le moins cher, et non le meilleur, le plus rentable.

    Exécuteur de requêtes : L'exécuteur de requête appelle la méthode d'accès et fournit un plan d'exécution pour la logique de récupération de données requise pour l'exécution.

  • Il existe trois types de fichiers : fichier principal, fichier secondaire et fichiers journaux.
  • Moteur de stockage : a suiviwing composants importantsMéthode d'accès: Ce composant détermine si la requête est une instruction Select ou Non-Select. Appelle Buffer et Transfer Manager en conséquence.Gestionnaire de tampon : Le gestionnaire de tampon gère les fonctions de base pour Plan Cache, Data Parsing et Dirty Page.

    Gestionnaire de transactions : Il gère les transactions non sélectionnées à l'aide des gestionnaires de journaux et de verrouillage. Facilite également la mise en œuvre importante de la journalisation Write Ahead et des rédacteurs Lazy.