Explication de l'architecture SQL Server: canaux nommés, optimiseur, gestionnaire de tampons

Table des matières:

Anonim

MS SQL Server est une architecture client-serveur. Le processus MS SQL Server démarre avec l'application cliente qui envoie une demande. Le serveur SQL accepte, traite et répond à la demande avec des données traitées. Discutons en détail de toute l'architecture illustré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
Diagramme d'architecture SQL Server

Discutons en détail des trois principaux modules ci-dessus. Dans ce tutoriel, vous apprendrez.

  • Couche de protocole - SNI
    • La memoire partagée
    • TCP / IP
    • Tuyaux nommés
    • Qu'est-ce que TDS?
  • Moteur relationnel
    • Analyseur CMD
    • Optimiseur
    • Exécuteur de requêtes
  • Moteur de stockage
    • Types de fichier
    • Méthode d'accès
    • Gestionnaire de tampon
    • Planifier le cache
    • Analyse des données: cache tampon et stockage des données
    • Gestionnaire de transactions

Couche de protocole - SNI

LA COUCHE DE PROTOCOLE MS SQL SERVER prend en charge 3 types d'architecture client-serveur. Nous commencerons par « Trois types d'architecture client-serveur» que MS SQL Server prend en charge.

La memoire partagée

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

MOM 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, le serveur MS SQL fournit un PROTOCOLE DE MÉMOIRE PARTAGÉE . Ici, le CLIENT et le serveur MS SQL fonctionnent sur la même machine. Les deux peuvent communiquer via le protocole de mémoire partagée.

Analogie: permet de mapper les entités dans les deux scénarios ci-dessus. Nous pouvons facilement mapper Tom vers le client, la maman vers le serveur SQL, la maison vers la machine et la communication verbale vers le protocole de mémoire partagée.

Depuis le bureau de configuration et d'installation:

Pour la connexion à la base de données locale - Dans SQL Management Studio, l'option "Nom du serveur" peut être

"."

"localhost"

"127.0.0.1"

"Machine \ Instance"

TCP / IP

Maintenant, pensez au soir, Tom est d'humeur à faire la fête. Il veut un café commandé dans un café bien connu. Le café est situé à 10 km de son domicile.

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

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

Notes du bureau de configuration / installation:

  • Dans SQL Management Studio - Pour une 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.

Tuyaux nommés

Maintenant enfin la nuit, Tom a voulu prendre un thé vert clair que sa voisine, Sierra prépare très bien.

Ici, Tom et son voisin , Sierra, sont au même endroit physique, étant le voisin l'un de l'autre. Ils communiquent via le réseau intra. De même, MS SQL SERVER offre la possibilité d'interagir via le protocole Named Pipe . Ici, le CLIENT et MS SQL SERVER sont en connexion via LAN .

Analogie: permet de mapper 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 réseau intra au Named Pipe Protocol.

Notes du bureau de configuration / installation:

  • Pour une connexion via un tuyau 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 est l'acronyme de Tabular Data Stream.
  • 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 cliente vers la machine serveur.
  • TDS a d'abord été développé par Sybase et est maintenant détenu par Microsoft

Moteur relationnel

Le moteur relationnel est également connu sous le nom de processeur de requêtes. Il contient les composants SQL Server qui déterminent exactement ce qu'une requête doit faire et comment le faire 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 illustré dans le diagramme architectural, il existe 3 composants principaux du moteur relationnel. Étudions les composants en détail:

Analyseur CMD

Les données une fois reçues de la couche de protocole sont ensuite transmises au moteur relationnel. «CMD Parser» est le premier composant de Relational Engine à recevoir les données de requête. Le travail principal de CMD Parser est de vérifier la requête d' erreur syntaxique et sémantique. Enfin, il génère une arborescence de requêtes . Discutons en détail.

Contrôle syntaxique:

  • Comme tous les autres langages de programmation, MS SQL possède également l'ensemble prédéfini de mots-clés. En outre, SQL Server a sa propre grammaire que le serveur SQL comprend.
  • SELECT, INSERT, UPDATE et bien d'autres appartiennent aux listes de mots-clés prédéfinis MS SQL.
  • CMD Parser effectue une vérification syntaxique. Si l'entrée des utilisateurs ne suit pas ces règles de syntaxe ou de grammaire, elle renvoie une erreur.

Exemple: disons qu'un Russe est allé dans un restaurant japonais. Il commande de la restauration rapide en russe. Malheureusement, le serveur ne comprend que le japonais. Quel serait le résultat le plus évident?

La réponse est - le serveur est incapable de traiter la commande plus loin.

Il ne devrait y avoir aucun écart dans la grammaire ou la langue acceptée par le serveur SQL. Si tel 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 tutoriels. Cependant, considérez ci-dessous la syntaxe de requête la plus élémentaire comme 

SELECT * from ;

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

SELECR * from

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

Résultat: l' analyseur CMD analysera cette instruction et lancera 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, le nom de la table interrogé existent dans le schéma. Et s'il existe, liez-le à Query. Ceci est également connu sous le nom de Binding .
  • La complexité augmente lorsque les requêtes des utilisateurs contiennent VIEW. Le normalisateur effectue le remplacement avec 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 lancera un message d'erreur car Normalizer ne trouvera pas la table demandée (USER_ID) car elle n'existe pas.

Créer une arborescence de requêtes:

  • Cette étape génère une arborescence d'exécution différente dans laquelle la requête peut être exécutée.
  • Notez que tous les différents arbres ont la même sortie souhaitée.

Optimiseur

Le travail de l'optimiseur est de créer un plan d'exécution pour la requête de l'utilisateur. C'est le plan qui déterminera comment la requête 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) comme 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 sous une forme 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 d'entrée / sortie.

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

Avant de passer aux détails techniques de l'Optimizer, considérons ci-dessous un exemple réel:

Exemple:

Disons que vous souhaitez ouvrir un compte bancaire en ligne. Vous connaissez déjà une banque qui prend un maximum de 2 jours pour ouvrir un compte. Mais vous avez également une liste de 20 autres banques, ce qui peut prendre moins de 2 jours ou pas. Vous pouvez commencer à vous engager avec ces banques pour déterminer quelles banques prennent moins de 2 jours. Désormais, il se peut que vous ne trouviez pas de banque qui prend moins de 2 jours, et il y a du temps supplémentaire 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 sélectionner judicieusement. Pour être précis, choisissez la meilleure option, 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 d'Optimizer sont la propriété de Microsoft et un secret. Cependant , 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:

Phase 0: Recherche d'un plan trivial:

  • Ceci est également connu sous le nom de stade de pré-optimisation .
  • Dans certains cas, il ne peut y avoir qu'un seul plan pratique et réalisable, connu sous le nom de plan trivial. Il n'est pas nécessaire de créer un plan optimisé. La raison en est qu'une recherche supplémentaire aboutirait à trouver le même plan d'exécution d'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 la 1ère phase commence.

Phase 1: Recherche de plans de traitement des transactions

  • Cela inclut la recherche de plan simple et complexe .
  • Recherche de plan simple: les données antérieures de la colonne et de l'index impliqués 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.
  • Néanmoins, si le plan simple n'est pas trouvé, un plan plus complexe est recherché. Il implique plusieurs index par table.

Phase 2: traitement parallèle et optimisation.

  • Si aucune des stratégies ci-dessus ne fonctionne, l'Optimiseur recherche les possibilités de traitement parallèle. Cela dépend des capacités de traitement et de la configuration de la machine.
  • Si ce n'est toujours pas possible, la phase finale d'optimisation démarre. 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 finale d'optimisation Les algorithmes sont la propriété de Microsoft.

Exécuteur de requêtes

L'exécuteur de requête appelle la 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é dans la couche Protocol. 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 que disque ou SAN et à récupérer les données en cas de besoin. Avant de plonger dans le moteur de stockage, voyons comment les données sont stockées dans la base de données et le type de fichiers disponibles.

Fichier de données et étendue:

Fichier de données, stocke physiquement les données sous la forme de pages de données, chaque page de données ayant une taille de 8 Ko, formant la plus petite unité de stockage dans SQL Server. Ces pages de données sont regroupées logiquement pour former des étendues. Aucun objet ne reçoit de page dans SQL Server.

La maintenance de l'objet se fait via des étendues. 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.

Types de fichier

  1. Fichier primaire
  • Chaque base de données contient un fichier primaire.
  • Cela stocke toutes les données importantes liées aux tables, vues, déclencheurs, etc.
  • L'extension est. mdf généralement mais peut être de 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. ndf 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 à partir de toutes les instances indésirables. Effectuer 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. Select Statement (DDL)
  2. Instruction Non-Select (DDL et DML)

En fonction du résultat, la méthode d'accès suit les étapes suivantes:

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

Gestionnaire de tampon

Buffer Manager gère les fonctions de base des modules ci-dessous:

  • Planifier le cache
  • Analyse des données: cache tampon et stockage des données
  • Page sale

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

Planifier le cache

  • Plan de requête existant: le gestionnaire de tampons 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.
  • Premier plan de cache: d' où vient le cache de plan existant?

    Si le plan d'exécution de la requête pour la première fois est en cours d'exécution et est complexe, il est judicieux de le stocker dans le cache du plan. Cela garantira une disponibilité plus rapide la prochaine fois que le serveur SQL recevra la même requête. Donc, ce n'est rien d'autre que la requête elle-même dont l'exécution de Plan est stockée si elle est exécutée pour la première fois.

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

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

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 l'exécuteur de requêtes. Cela améliore les performances car le nombre d'opérations d'E / S est réduit lors de l'extraction de données à partir du cache par rapport à l'extraction de données à partir du stockage de données.

Stockage de données - Analyse matérielle:

Si les données ne sont pas présentes dans Buffer Manager que nécessaire, les données sont recherchées dans le stockage de données. If stocke également des données dans le cache de données pour une utilisation future.

Page sale

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

Gestionnaire de transactions

Le Gestionnaire de transactions 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 des journaux dans les journaux de transactions.
  • Les journaux ont un numéro de séquence de journaux avec l'ID de transaction et l'enregistrement de modification de données .
  • Ceci est utilisé pour garder une trace de la transaction validée et de l'annulation de transaction .

Gestionnaire de serrure

  • 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 la cohérence et l'isolation des données . Aussi connu sous le nom de propriétés ACID.

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 du tampon.
  • La copie des données censées être mises à jour est conservée dans la mémoire tampon du journal et tous les événements mettent à jour les données dans la mémoire tampon de données.
  • Les pages qui stockent les données sont également appelées pages sales .
  • Journalisation des points de contrôle et de l'écriture anticipée: ce processus s'exécute et marque toute la page des pages sales vers le disque, mais la page reste dans le cache. La fréquence est d'environ 1 exécution par minute, mais la page est d'abord poussée vers la page Données du fichier journal à partir du journal Buffer. Ceci est connu sous le nom de journalisation d'écriture anticipée.
  • Lazy Writer: La page sale peut rester en mémoire. Lorsque le serveur SQL observe une charge énorme et que 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 mémoire tampon au disque.

Résumé:

  • Il existe trois types d'architecture client-serveur: 1) Mémoire partagée 2) TCP / IP 3) Tuyaux nommés
  • TDS, développé par Sybase et maintenant détenu par Microsoft, est un paquet qui est encapsulé dans des paquets réseau pour le transfert de données de la machine cliente vers la machine serveur.
  • Relational Engine contient trois composants principaux:

    CMD Parser: il est responsable de l'erreur syntaxique et sémantique 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, pas le meilleur et le plus rentable.

    Exécuteur de requête: 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: contient les composants importants suivants

    Méthode d'accès: ce composant Déterminez si la requête est une instruction Select ou Non-Select. Appelle Buffer et Transfer Manager en conséquence.

    Buffer Manager: Buffer Manager gère les fonctions de base pour Plan Cache, Data Parsing & Dirty Page.

    Gestionnaire de transactions: il gère les transactions non sélectionnées avec l'aide des gestionnaires de journaux et de verrous. Facilite également l'implémentation importante de la journalisation Write Ahead et des rédacteurs Lazy.