SQL Server Architecture (expliqué)

MS SQL Server repose sur une architecture client-serveur. Le processus MS SQL Server débute par l'envoi d'une requête par l'application cliente. Le serveur SQL Server accepte, traite et répond à la requête avec les données traitées. Examinons en détail l'architecture complète présentée ci-dessous :

Comme le montre le schéma ci-dessous, SQL Server comporte trois composants principaux. Archistructure :

1. Couche de protocole
2. Moteur relationnel
3. Moteur de stockage

Couche de protocole – SNI

La couche de protocoles SQL Server, également appelée interface réseau serveur (SNI), prend en charge trois types d'architecture client-serveur. Chaque protocole correspond à un scénario réseau différent. Il est essentiel de comprendre ces protocoles avant d'étudier le traitement interne des requêtes.

La memoire partagée

Prenons l'exemple d'une conversation matinale. Tom et sa mère se trouvent au même endroit logique : leur domicile. Tom demande un café et sa mère le lui sert immédiatement. De même, SQL Server utilise le protocole de mémoire partagée lorsque le client et le serveur s'exécutent sur la même machine. Ils communiquent alors via la mémoire partagée, sans aucune surcharge réseau.

Analogie: Tom correspond au Client, Maman correspond à SQL Server, Maison correspond à la Machine et la communication verbale correspond au protocole de mémoire partagée.

Notes de configuration : In Studio de gestion SQL, l’option « Nom du serveur » pour une connexion locale peut être « . », « localhost », « 127.0.0.1 » ou « Machine\Instance ».

TCP / IP

Imaginons maintenant que Tom souhaite prendre un café dans un café situé à 10 km de chez lui. Tom est à son domicile et le café se trouve dans un centre commercial animé. Ils communiquent via un réseau cellulaire. De même, SQL Server fournit… Protocole TCP / IP lorsque le client et le serveur SQL sont sur des machines distinctes connectées via un réseau.

Analogie: Tom correspond au client, le café correspond à SQL Server, la maison et le marché correspondent à des emplacements distants, et le réseau cellulaire correspond au protocole TCP/IP.

Notes de configuration : Dans SQL Management Studio, l'option « Nom du serveur » pour une connexion TCP/IP doit être « Machine\Instance du serveur ». SQL Server utilise par défaut le port 1433 pour les connexions TCP/IP.

Pipes Nommées

Enfin, Tom souhaite commander du thé vert à sa voisine Sierra. Ils habitent au même endroit et communiquent via un réseau local. De même, SQL Server utilise le protocole Named Pipe lorsque le client et le serveur sont connectés via un réseau local (LAN).

Analogie: Tom correspond au client, Sierra correspond à SQL Server, la relation de voisinage correspond au réseau local et le réseau interne correspond au protocole Named Pipe.

Notes de configuration : Les canaux nommés sont désactivés par défaut et doivent être activés via le Gestionnaire de configuration SQL.

Qu'est-ce que TDS?

Maintenant que les trois types d'architecture client-serveur sont clairs, voici un aperçu de TDS :

• TDS signifie Flux de données tabulaires.
• Les trois protocoles utilisent des paquets TDS.
• Le protocole TDS est encapsulé dans des paquets réseau, permettant le transfert de données de la machine cliente vers la machine serveur.
• TDS a été initialement développé par Sybase et appartient désormais à Microsoft.

Le tableau suivant compare les trois protocoles de connexion SQL Server :

Fonctionnalité	La memoire partagée	TCP / IP	Pipes Nommées
Portée du réseau	Même machine	Accès à distance (WAN/Internet)	LAN uniquement
Port par défaut	N/D	1433	445
Performances	Le plus rapide (sans surcharge réseau)	Bon (optimisé pour le WAN)	Bon (optimisé pour le réseau local)
Activé par défaut	Oui	Oui	Non
Meilleur cas d'utilisation	Développement et tests locaux	Accès distant à la production	Environnements LAN de confiance

La couche protocolaire gérant la communication réseau, l'étape suivante dans l'architecture de SQL Server consiste à traiter la requête elle-même. C'est là que le moteur relationnel entre en jeu.

Moteur relationnel

Le moteur relationnel, également appelé processeur de requêtes, contient les composants SQL Server qui déterminent les actions à effectuer lors d'une requête et la manière la plus efficace de l'exécuter. Il est chargé d'exécuter les requêtes utilisateur en demandant des données au moteur de stockage et en traitant les résultats obtenus.

Comme le montre le schéma architectural, le moteur relationnel comporte trois composants principaux :

Analyseur CMD

Les données reçues de la couche protocolaire sont transmises au moteur relationnel. L'analyseur syntaxique est le premier composant à recevoir les données de la requête. Sa fonction principale est de vérifier la présence d'erreurs syntaxiques et sémantiques dans la requête, puis de générer un arbre de requête.

Vérification syntaxique : Comme tout autre langage de programmation, SQL Server possède un ensemble prédéfini de mots clés et de règles de syntaxe. SELECT, INSERT, UPDATE et bien d'autres font partie de cette liste. L'analyseur syntaxique vérifie que les entrées respectent ces règles. Si la syntaxe saisie par l'utilisateur s'écarte de celle attendue, l'analyseur renvoie une erreur.

Exemple : Imaginez un Russe entrant dans un restaurant japonais et commandant en russe. Le serveur ne comprend que le japonais et ne peut pas prendre sa commande. De même, si un utilisateur saisit « SELECR » au lieu de « SELECT », l’analyseur de commandes renvoie une erreur car il ne reconnaît pas le mot-clé.

Vérification sémantique : Cette opération est effectuée par le normalisateur. Il vérifie si les noms de colonnes, les noms de tables et les autres objets interrogés existent bien dans le schéma. Si c'est le cas, le normalisateur les associe à la requête. Ce processus est également appelé liaison. Lorsqu'une requête utilisateur contient une vue, le normalisateur la remplace par la définition de vue stockée en interne.

Exemple : Fonctionnement SELECT * from USER_ID Cela provoquerait une erreur lors de la vérification sémantique si la table USER_ID n'existe pas dans la base de données.

Créer un arbre de requête : Cette étape génère différents arbres d'exécution représentant les diverses manières d'exécuter une requête. Tous les arbres produisent le même résultat souhaité.

Optimizer

L'optimiseur crée un plan d'exécution pour la requête de l'utilisateur. Ce plan détermine comment la requête sera exécutée. Toutes les requêtes ne sont pas optimisées. L'optimisation s'applique aux commandes DML (langage de modification des données) telles que SELECT, INSERT, DELETE et UPDATE. Les commandes DDL telles que CREATE et ALTER ne sont pas optimisées, mais 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, 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 plus économique, et non nécessairement le meilleur en soi.

Exemple : Imaginez que vous souhaitiez ouvrir un compte bancaire en ligne. Une banque garantit un délai maximal de deux jours. Vous disposez également d'une liste de 20 autres banques, dont le délai d'ouverture peut varier. Consulter ces 20 banques ne vous permettra peut-être pas de trouver une option plus rapide, et la recherche elle-même est chronophage. Il aurait été préférable de choisir la première banque. De la même manière, l'optimiseur SQL utilise des algorithmes exhaustifs et heuristiques pour minimiser le temps d'exécution des requêtes.

L'optimiseur effectue des recherches en trois phases :

Phase 0 : Recherche d'un plan trivial

Il s'agit de la phase de pré-optimisation. Pour certaines requêtes, il n'existe qu'un seul plan d'exécution pratique, appelé plan trivial. Il est inutile de poursuivre la recherche, car toute recherche supplémentaire aboutirait au même plan d'exécution, moyennant un coût additionnel.

Phase 1 : Recherche des plans de traitement des transactions

Cela inclut la recherche de plans d'exécution simples et complexes. La recherche de plans d'exécution simples utilise une analyse statistique des données de colonnes et d'index, généralement limitée à un seul index par table. Si aucun plan d'exécution simple n'est trouvé, une recherche plus complexe, impliquant plusieurs index par table, est effectuée.

Phase 2 : Traitement parallèle et optimisation

Si les stratégies précédentes ne permettent pas d'obtenir un plan adéquat, l'optimiseur recherche des possibilités de traitement parallèle en fonction des capacités de la machine. Si le traitement parallèle est impossible, une phase d'optimisation finale est lancée, exploitant toutes les options restantes pour trouver le meilleur plan d'exécution possible.

Exécuteur de requête

L'exécuteur de requêtes appelle la méthode d'accès du moteur de stockage. Il fournit un plan d'exécution contenant la logique d'extraction des données nécessaire à l'exécution. Une fois les données reçues du moteur de stockage, le résultat est publié sur la couche protocolaire et envoyé à l'utilisateur final.

Une fois que le moteur relationnel a déterminé comment exécuter une requête, le moteur de stockage prend en charge les opérations physiques sur les données. Cette couche gère le stockage, la mise en cache et la récupération des données sur le disque.

Moteur de stockage

Le moteur de stockage est chargé de stocker les données dans un système de stockage tel qu'un disque ou un SAN et de les récupérer en cas de besoin. Avant d'examiner les composants du moteur de stockage, il est important de comprendre comment les données sont physiquement stockées.

Fichiers de données et étendues

Les fichiers de données stockent physiquement les données sous forme de pages de données, chaque page ayant une taille de 8 Ko. Il s'agit de la plus petite unité de stockage. SQL ServerLes pages de données sont regroupées logiquement en étendues. Aucun objet n'est directement associé à une page ; la gestion s'effectue par le biais des étendues. Chaque page possède un en-tête (96 octets) contenant des métadonnées telles que le type de page, le numéro de page, l'espace utilisé, l'espace libre et des pointeurs vers les pages suivantes et précédentes.

Types de fichier

Fichier principal : Chaque base de données contient un fichier principal. Ce fichier stocke toutes les données importantes relatives aux tables, vues, déclencheurs et autres objets. Son extension est généralement .mdf, mais peut être quelconque.

Fichier secondaire : Une base de données peut contenir ou non plusieurs fichiers secondaires. Ces fichiers sont facultatifs et contiennent des données spécifiques à l'utilisateur. Leur extension est généralement .ndf, mais peut être quelconque.

Fichier journal : Également appelés journaux de transactions (Write-Ahead Logs). Leur extension est .ldf. Ces fichiers journaux servent à la gestion des transactions, à la récupération après des incidents indésirables et à l'annulation des transactions non validées.

Le moteur de stockage comporte trois composants principaux. Chacun joue un rôle spécifique dans la gestion de l'accès aux données et de leur intégrité.

Méthode d'accès

La méthode d'accès sert d'interface entre l'exécuteur de requêtes et le Buffer Gestionnaire ou journaux de transactions. Il n'exécute pas lui-même les requêtes, mais détermine leur type :

• Si la requête est une Instruction SELECT (DML), il est transmis au Buffer Responsable du traitement ultérieur.
• Si la requête est une Instruction non SELECT (DDL et DML)Elle est ensuite transmise au gestionnaire de transactions. Ce dernier traite principalement les instructions UPDATE, INSERT et DELETE.

Buffer Gérant

Le Buffer Le gestionnaire gère les fonctions principales du cache de plan, de l'analyse des données et de la gestion des pages modifiées.

Cacher les forfaits

Plan de requête existant : Le Buffer Le gestionnaire vérifie si le plan d'exécution existe dans le cache de plans. Si c'est le cas, le plan de requête mis en cache et son cache de données associé sont utilisés directement.

Plan de première cache : Si le plan d'exécution d'une requête initiale est complexe, il est stocké dans le cache de plans. Cela garantit une disponibilité plus rapide lors de la prochaine exécution de la même requête sur SQL Server.

Analyse des données : Buffer Cache et stockage de données

Le Buffer Le gestionnaire donne accès aux données requises. Deux approches sont possibles selon que les données existent ou non dans le cache :

Buffer Cache – Analyse syntaxique souple

Le Buffer Le gestionnaire recherche des données dans le Buffer Cache. Si les données sont présentes, l'exécuteur de requêtes les utilise directement. Cela améliore les performances car la récupération des données depuis le cache nécessite moins d'opérations d'E/S que leur récupération depuis le disque.

Stockage de données – Analyse syntaxique approfondie

Si les données ne sont pas présentes dans le Buffer Dans le cache, les données requises sont recherchées dans le stockage de données sur disque. Ces données sont ensuite stockées dans le cache de données pour une utilisation ultérieure.

Gestionnaire de transactions

Le gestionnaire de transactions est appelé lorsque la méthode d'accès détermine qu'une requête n'est pas une instruction SELECT. Il garantit la cohérence et la durabilité des données grâce à plusieurs sous-composants :

Gestionnaire de journaux

Le gestionnaire de journaux conserve track de toutes les mises à jour effectuées dans le système sont consignées dans les journaux de transactions. Chaque entrée de journal contient un numéro de séquence, l'identifiant de transaction et l'enregistrement de modification des données. Ce mécanisme tracks transactions validées et annulées.

Gestionnaire de verrouillage

Lors d'une transaction, les données associées stockées passent à un état verrouillé. Le gestionnaire de verrouillage gère ce processus, garantissant la cohérence et l'isolation des données. Ces propriétés sont également connues sous le nom d'ACID (Atomicité, Cohérence, Isolation, Durabilité).

Processus d'exécution

Le processus d'exécution suit les étapes suivantes :

1. Le gestionnaire de journaux démarre l'enregistrement et le gestionnaire de verrouillage verrouille les données associées.
2. Une copie des données est conservée dans le Buffer Cache
3. Une copie des données à mettre à jour est conservée dans le journal. Bufferet tous les événements mettent à jour les données dans les données Buffer.
4. Les pages qui stockent des données modifiées sont appelées Pages sales.

Point de contrôle et journalisation anticipée

Le processus de point de contrôle s'exécute environ une fois par minute et marque toutes les pages modifiées pour écriture sur le disque. Cependant, la page est d'abord transférée vers la page de données du fichier journal depuis le Buffer Journalisation. Ce mécanisme est connu sous le nom de journalisation anticipée. Les pages modifiées restent dans le cache même après avoir été écrites sur le disque.

Paresseux Writer

Lorsque SQL Server détecte une charge importante et que de la mémoire tampon est nécessaire pour les nouvelles transactions, il libère les pages modifiées du cache. Writer Il utilise l'algorithme LRU (Least Recently Used) pour nettoyer les pages du pool de mémoire tampon et les transférer sur le disque.

Comment SQL Server traite une requête de bout en bout

Comprendre chaque couche individuellement est précieux, mais observer comment elles interagissent permet d'avoir une vision d'ensemble. Lorsqu'une application cliente envoie une requête SQL, la séquence suivante se produit :

Le Couche de protocole reçoit la requête via la mémoire partagée, TCP/IP ou les canaux nommés et l'encapsule dans un paquet TDS. Moteur relationnel puis prend le relais : l’analyseur CMD vérifie la syntaxe et la sémantique, l’optimiseur génère le plan d’exécution le moins coûteux et l’exécuteur de requêtes commence la récupération des données.

L'exécuteur de requêtes appelle le Moteur de stockage Méthode d'accès, qui achemine les requêtes SELECT vers le Buffer Gestionnaire et requêtes de modification adressées au Gestionnaire de transactions. Buffer Le gestionnaire vérifie le cache du plan et Buffer Le système effectue d'abord une mise en cache (analyse syntaxique souple). Si les données ne sont pas en cache, une lecture disque est effectuée (analyse syntaxique complète). Pour les opérations d'écriture, le gestionnaire de transactions coordonne le gestionnaire de journaux, le gestionnaire de verrous et le processus de point de contrôle afin de garantir la conformité ACID.

Une fois que le moteur de stockage a renvoyé les données demandées, le moteur relationnel formate l'ensemble de résultats et la couche de protocole le renvoie à l'application cliente via le même protocole TDS.

Comment choisir le protocole approprié pour les connexions à SQL Server

Le choix du protocole approprié dépend de la relation physique entre le client et le serveur, ainsi que des exigences de performance.

Utiliser la mémoire partagée Lorsque l'application cliente s'exécute sur la même machine que SQL Server, cette option est la plus rapide car elle élimine toute surcharge réseau. Elle est idéale pour le développement local, les tests et les déploiements sur une seule machine.

Utiliser TCP/IP Lorsque le client et le serveur sont installés sur des machines différentes connectées via un réseau étendu (WAN) ou Internet, ce protocole est le plus couramment utilisé en production. SQL Server écoute par défaut sur le port 1433 et ce protocole prend en charge les connexions chiffrées via TLS.

Utiliser des canaux nommés Lorsque le client et le serveur se trouvent sur le même réseau local de confiance et que les performances sur les réseaux internes sont prioritaires, les canaux nommés sont désactivés par défaut et doivent être activés via le Gestionnaire de configuration SQL Server. Bien que moins fréquents dans les déploiements modernes, ils restent utiles pour les applications intranet existantes.

FAQ