Guide du traitement des requêtes pour les tables mémoire optimisées

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

L'OLTP en mémoire introduit les tables mémoire optimisées et les procédures stockées compilées en mode natif dans SQL Server. Cet article présente le traitement des requêtes pour les tables mémoire optimisées et les procédures stockées compilées en mode natif.

Le document explique comment les requêtes sur les tables mémoire optimisées sont compilées et exécutées, notamment :

• Le pipeline de traitement des requêtes dans SQL Server pour les tables sur disque.

• Optimisation des requêtes : rôle des statistiques sur les tables mémoire optimisées et instructions de dépannage pour les plans de requête non optimaux.

• L'utilisation de Transact-SQL interprété pour accéder aux tables mémoire optimisées.

• Considérations relatives à l'optimisation des requêtes pour l'accès aux tables mémoire optimisées.

• Compilation et traitement d'une procédure stockée compilée en mode natif.

• Statistiques utilisées pour l'estimation de coût par l'optimiseur.

• Méthodes pour corriger des plans de requête non optimaux.

Exemple de requête

L'exemple suivant est utilisé pour illustrer les concepts de traitement des requêtes décrits dans cet article.

Considérons deux tables, Customer et Order. Le script Transact-SQL suivant contient les définitions des deux tables et des index associés, sur disque (au format traditionnel) :

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

Pour créer les plans de requête illustrés dans cet article, les deux tables ont été remplies avec les exemples de données tirés de la base de données Northwind, que vous pouvez télécharger sur Exemples de bases de données Northwind et pubs pour SQL Server 2000.

La requête suivante joint les tables Customer et Order, et retourne l'ID de la commande et les informations client associées :

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Le plan d'exécution estimé tel qu'affiché par SQL Server Management Studio est le suivant

Plan de requête pour joindre des tables sur disque.

À propos de ce plan de requête :

• Les lignes de la table Customer sont extraites de l’index clusterisé, qui constitue la structure de données principale et contient l’intégralité des données de la table.

• Les données de la table Order sont récupérées à l’aide de l’index non clusterisé sur la colonne CustomerID. Cet index contient la colonne CustomerID utilisée pour la jointure, et la colonne de clé primaire OrderID qui est retournée à l'utilisateur. Le retour de colonnes supplémentaires depuis la table Order nécessiterait des recherches dans l'index cluster de la table Order.

• L’opérateur logique Jointure interne est implémenté par l’opérateur physique Jointure de fusion. Les autres types de jointures physiques sont Boucles imbriquées et Jointure hachée. L’opérateur Merge Join exploite le fait que les deux index sont triés sur la colonne de jointure CustomerID.

Examinons une légère variante de cette requête, qui retourne toutes les colonnes de la table Order, et plus seulement OrderID :

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Le plan estimé pour cette requête est le suivant :

Plan de requête d'une jointure hachée des tables sur disque.

Dans cette requête, les lignes de la table Order sont récupérées à l’aide de l’index clusterisé. L’opérateur physique Hash Match est désormais utilisé pour la Inner Join. L’index clusterisé sur Order n’étant pas trié sur CustomerID, un Merge Join nécessiterait donc un opérateur de tri, ce qui nuirait aux performances. Remarquez le coût relatif de l’opérateur Hash Match (75 %) par rapport à celui de l’opérateur Merge Join dans l’exemple précédent (46 %). L’optimiseur aurait pu utiliser l’opérateur Correspondances de hash également dans l’exemple précédent, mais il a considéré que l’opérateur Jointure de fusion fournirait de meilleures performances.

Traitement des requêtes SQL Server pour les tables sur disque

Le diagramme suivant représente le flux de traitement des requêtes dans SQL Server pour les requêtes ad hoc :

Pipeline de traitement des requêtes SQL Server

Dans ce scénario :

1. L'utilisateur émet une requête.

2. L'analyseur et l'algébriseur construisent une arborescence de requête avec des opérateurs logiques en fonction du texte SQL Server soumis par l'utilisateur.

3. L'optimiseur crée un plan de requête optimisé qui contient des opérateurs physiques (par exemple, une jointure par boucles imbriquées). Après l'optimisation, le plan peut être stocké dans le cache du plan. Cette étape est ignorée si le cache du plan contient déjà un plan pour cette requête.

4. Le moteur d'exécution des requêtes traite une interprétation du plan de requête.

5. Pour chaque opérateur de recherche dans l’index, d’analyse d’index et d’analyse de table, le moteur d’exécution demande à Access Methods des lignes provenant des structures d’index et de table correspondantes.

6. Les méthodes d'accès récupèrent les lignes de l'index et les pages de données dans le pool de mémoires tampons, et chargent les pages à partir du disque dans le pool de mémoires tampons si nécessaire.

Pour le premier exemple de requête, le moteur d’exécution demande des lignes de l’index clusterisé sur Customer et de l’index non clusterisé sur Order aux méthodes d’accès. Les méthodes d'accès parcourent les structures d'index B-tree pour récupérer les lignes demandées. Dans ce cas, toutes les lignes sont récupérées lorsque le plan appelle des analyses d'index complètes.

Accès en Transact-SQL interprété aux tables optimisées pour la mémoire

Les lots ad hoc Transact-SQL et les procédures stockées sont également appelés Transact-SQL interprétés. Le terme « Interprété » désigne le fait que le plan de requête est interprété par le moteur d'exécution des requêtes pour chaque opérateur du plan de requête. Le moteur d'exécution lit l'opérateur et ses paramètres, et effectue l'opération.

Le Transact-SQL interprété peut être utilisé pour accéder à la fois à la mémoire optimisée et aux tables sur disque. L'illustration suivante montre le traitement des requêtes pour l'accès à Transact-SQL interprété aux tables mémoire optimisées :

Pipeline de traitement des requêtes pour l'accès en Transact-SQL interprété aux tables mémoire optimisées.

Comme illustré dans la figure, le pipeline de traitement des requêtes reste principalement inchangé :

• L'analyseur et l'algébriseur construisent l'arborescence de requête.

• L'optimiseur crée le plan d'exécution.

• Le moteur d'exécution de requête interprète le plan d'exécution.

La principale différence avec le pipeline traditionnel de traitement des requêtes (figure 2) est que les lignes des tables optimisées en mémoire ne sont pas récupérées depuis le buffer pool au moyen des méthodes d’accès. À la place, les lignes sont récupérées depuis les structures de données en mémoire via le moteur OLTP en mémoire. Les différences dans les structures de données obligent l'optimiseur à choisir des plans différents dans certains cas, comme illustré par l'exemple suivant.

Le script Transact-SQL suivant contient les versions mémoire optimisées des tables Order et Customer, utilisant des index de hachage :

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

Imaginons la même requête exécutée sur des tables mémoire optimisées :

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Le plan estimé est le suivant :

Plan de requête pour joindre des tables mémoire optimisées.

Observez les différences suivantes dans le plan pour la même requête sur des tables sur disque (figure 1) :

• Ce plan contient une analyse de table au lieu d'une analyse d'index cluster pour la table Customer :

  • La définition de la table ne contient pas d'index cluster.

  • Les index clusterisés ne sont pas pris en charge sur les tables optimisées en mémoire. À la place, chaque table mémoire optimisée doit avoir au moins un index non cluster et tous les index des tables mémoire optimisées accéder efficacement à toutes les colonnes de la table sans devoir les stocker dans l'index ou les référencer dans un index cluster.

• Ce plan contient une Hash Match plutôt qu’une Merge Join. Les index des tables Order et Customer sont des index de hachage, et ne sont donc pas triés. Une Jointure de fusion nécessiterait des opérateurs de tri qui diminueraient les performances.

Procédures stockées compilées en mode natif

Les procédures stockées compilées en mode natif sont des procédures stockées Transact-SQL qui sont compilées dans le code machine, au lieu d'être interprétées par le moteur d'exécution de requête. Le script suivant crée une procédure stockée compilée en mode natif qui exécute l'exemple de requête (dans la section Exemple de requête).

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

Les procédures stockées compilées en mode natif sont compilées lors de la création, tandis que les procédures stockées interprétées sont compilées lors de la première exécution. (Une partie de la compilation, en particulier l’analyse et l’algèbre, a lieu à la création. Cependant, pour les procédures stockées interprétées, l’optimisation des plans de requête a lieu à la première exécution.) La logique de recompilation est similaire. Les procédures stockées compilées en mode natif sont recompilées lors de leur première exécution si le serveur est redémarré. Les procédures stockées interprétées sont recompilées si le plan ne se trouve plus dans le cache des plans. Le tableau suivant résume les scénarios de compilation et de recompilation pour les procédures stockées compilées en mode natif et les procédures stockées interprétées :

Compilation et traitement des requêtes

Le diagramme ci-dessous illustre le processus de compilation des procédures stockées compilées en mode natif :

Procédures stockées compilées en mode natif.

Le processus se présente comme suit :

1. L’utilisateur émet une CREATE PROCEDURE instruction pour SQL Server.

2. L'analyseur et l'algébriseur créent un flux de traitement pour la procédure, ainsi que les arborescences des requêtes Transact-SQL dans la procédure stockée.

3. L'optimiseur crée des plans d'exécution de requête optimisés pour toutes les requêtes incluses dans la procédure stockée.

4. Le compilateur OLTP en mémoire prend en entrée le flux de traitement avec les plans de requête optimisés intégrés et génère une DLL contenant le code machine permettant d’exécuter la procédure stockée.

5. La DLL générée est chargée en mémoire.

L'appel d'une procédure stockée compilée en mode natif se traduit par l'appel à une fonction dans la DLL.

Exécution de procédures stockées compilées en mode natif.

L'appel d'une procédure stockée compilée en mode natif se présente comme suit :

1. L’utilisateur émet une instruction EXECusp_myproc.

2. L'analyseur extrait le nom et les paramètres de la procédure stockée.

   Si l’instruction a été préparée, par exemple à l’aide de sp_prep_exec, l’analyseur n’a pas besoin d’extraire le nom de la procédure et les paramètres au moment de l’exécution.

3. L'environnement d'exécution OLTP en mémoire localise le point d'entrée de la DLL pour la procédure stockée.

4. Le code machine dans la DLL est exécuté et les résultats sont retournés au client.

Détection des paramètres

Les procédures stockées en Transact-SQL interprété sont compilées à la première exécution, contrairement aux procédures stockées compilées en mode natif, qui sont compilées lors de la création. Lorsque des procédures stockées interprétées sont compilées au moment de l'appel, les valeurs des paramètres fournis pour cet appel sont utilisées par l'optimiseur lors de la génération du plan d'exécution. Cette utilisation des paramètres lors de la compilation est appelée « détection des paramètres ».

La détection des paramètres n'est pas utilisée pour compiler des procédures stockées compilées en mode natif. Tous les paramètres de la procédure stockée sont considérés comme ayant des valeurs UNKNOWN. À l’instar des procédures stockées interprétées, les procédures stockées compilées en mode natif prennent également en charge l’indicateur OPTIMIZE FOR . Pour plus d’informations, consultez Indicateurs de requête (Transact-SQL).

Récupération d'un plan d'exécution de requêtes pour les procédures stockées compilées en mode natif

Le plan d’exécution de requête pour une procédure stockée compilée en mode natif peut être récupéré à l’aide du plan d’exécution estimé dans Management Studio ou à l’aide de l’option SHOWPLAN_XML dans Transact-SQL. Par exemple :

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

Le plan d'exécution généré par l'optimiseur de requêtes se compose d'une arborescence dont les nœuds et les feuilles contiennent des opérateurs de requête. La structure de l'arborescence détermine l'interaction (le flux des lignes d'un opérateur vers un autre) entre les opérateurs. Dans la vue graphique de SQL Server Management Studio, le flux est de droite à gauche. Par exemple, le plan de requête de la figure 1 contient deux opérateurs de parcours d’index, qui transmettent des lignes à un opérateur de jointure par fusion. L'opérateur de jointure de fusion fournit des lignes à un opérateur de sélection. Enfin, l'opérateur de sélection retourne les lignes au client.

Opérateurs de requête dans des procédures stockées compilées en mode natif

Le tableau suivant récapitule les opérateurs de requête pris en charge dans les procédures stockées compilées en mode natif :

Statistiques des colonnes et jointures

SQL Server conserve les statistiques des valeurs des colonnes clés d’index pour vous aider à estimer le coût de certaines opérations, comme les analyses d’index et les recherches d’index. (SQL Server crée également des statistiques sur les colonnes clés qui ne sont pas des index si vous les créez explicitement ou si l'optimiseur de requête les crée en réponse à une requête avec un prédicat). La mesure principale de l'estimation de coût est le nombre de lignes traitées par un seul opérateur. Notez que pour les tables sur disque, le nombre de pages auxquelles un opérateur spécifique accède est important pour l'estimation du coût. Toutefois, comme le nombre de pages n'est pas important pour les tables mémoire optimisées (il est toujours de zéro), cette description se focalisera sur le nombre de lignes. L'estimation démarre avec les opérateurs de recherche d'index et d'analyse dans le plan, et est ensuite étendue pour inclure les autres opérateurs, comme l'opérateur de jointure. Le nombre estimé de lignes à traiter par un opérateur de jointure dépend de l'estimation des opérateurs d'index, de recherche et d'analyse sous-jacents. Pour l'accès interprété à Transact-SQL pour les tables optimisées en mémoire, vous pouvez consulter le plan d'exécution réel afin de voir la différence entre le nombre de lignes estimé et le nombre de lignes réel des opérateurs du plan.

Exemple de la figure 1 :

• L’analyse de l’index clusterisé sur Customer indique une estimation de 91 ; réel : 91.
• L’analyse de l’index non clusterisé sur CustomerID a estimé 830 lignes ; 830 lignes réelles.
• L'opérateur Merge Join a estimé 815 lignes, pour 830 réelles.

Les estimations des analyses d'index sont exactes. SQL Server conserve le nombre de lignes pour les tables sur disque. Les estimations des analyses complètes de la table et de l'index sont toujours exactes. L'estimation de la jointure est aussi relativement exacte.

Si ces estimations changent, le coût des différents choix de plan change aussi. Par exemple, si un des côtés de la jointure a un nombre estimé de lignes de 1, ou seulement quelques lignes, l'utilisation de jointures de boucles imbriquées est moins coûteuse. Considérez la requête suivante :

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

Après avoir supprimé toutes les lignes, sauf une dans la table Customer, le plan de requête suivant est généré :

À propos de ce plan de requête :

• L'opérateur Hash Match a été remplacé par un opérateur de jointure physique Nested Loops.
• L'analyse complète d'un index sur IX_CustomerID a été remplacée par une recherche d'index. Cela a abouti à l'analyse de 5 lignes, au lieu des 830 lignes requises pour l'analyse complète de l'index.

Voir aussi

Tables à mémoire optimisée