SQL Server Architecture (expliqué)

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 de Client-Serveur Architecture, 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 Archidiagramme tectural 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.

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.
• La complexité 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 :

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 1^st La 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 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.
• Toutefois, si le plan simple n'est pas trouvé, un plan plus complexe 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 finale d'optimisation Algorithms are Microsoft Convenance.

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 ainsi que type de fichiers disponibles.

Fichier de données et étendue :

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

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.

2. 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.

3. 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 Gestionnaire/journaux de transactions.

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 suit les étapes suivantes :

1. Si la requête est DDL, instruction SELECT, la requête est transmise au Buffer Gérant 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.

Buffer Gérant

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

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

Nous apprendrons Plan, Buffer et Cache de données dans cette section. Nous couvrirons les pages sales dans la section Transaction.

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 de plan existant ? Si le plan d'exécution de requête pour la première fois est exécuté et est complexe, il est logique de le stocker dans le cache de plan. 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 : Buffer cache et stockage de données

Buffer Le gestionnaire donne accès aux données requises. Ci-dessous, deux approches sont possibles selon que des données existent ou non dans le cache de données :

Buffer Cache – Analyse logicielle :

Buffer Le gestionnaire recherche des données dans Buffer 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 :

Si les données ne sont pas présentes dans Buffer Gestionnaire que requis Les données 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

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 Buffer cache
• 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 de Buffer enregistrer. 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 Buffer de la mémoire est nécessaire pour une nouvelle transaction, cela 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é

• Trois types de serveur client ArchiLa configuration existe : 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 : comporte les composants importants suivantsMéthode d'accès: Ce composant détermine si la requête est une instruction Select ou Non-Select. Invoque Buffer et Transfer Manager en conséquence.Buffer Gestionnaire: Buffer Le gestionnaire 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.