Guide de traitement des requêtes pour les tables Memory-Optimized

In-Memory OLTP introduit des tables optimisées en mémoire et des procédures stockées compilées en mode natif dans SQL Server. Cet article fournit une vue d’ensemble du 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 des tables optimisées en mémoire sont compilées et exécutées, notamment :

• 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, ainsi que des instructions pour résoudre les problèmes liés aux plans de requête incorrects.

• Utilisation de Transact-SQL interprété pour accéder aux tables en mémoire optimisées.

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

• Compilation et traitement de procédures stockées compilées en mode natif.

• Statistiques utilisées pour l’estimation des coûts par l’optimiseur.

• Manières de corriger les plans de requête incorrects.

Exemple de requête

L’exemple suivant sera utilisé pour illustrer les concepts de traitement des requêtes abordés dans cet article.

Nous considérons deux tables, Client et Commande. Le script Transact-SQL suivant contient les définitions de ces deux tables et index associés, sous leur forme de disque (traditionnelle) :

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 construire les plans de requête présentés dans cet article, les deux tables ont été remplies avec des exemples de données provenant de l’exemple de base de données Northwind, que vous pouvez télécharger à partir de Northwind et de pubs Sample Databases pour SQL Server 2000.

Considérez la requête suivante, qui joint les tables Customer and 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 la jointure de tables sur disque.

À propos de ce plan de requête :

• Les lignes de la table Customer sont récupérées à partir de l'index clusterisé, qui est la structure de données principale et contient l'intégralité des données de la table.

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

• L’opérateur Inner Join logique est implémenté par l’opérateur Merge Joinphysique . Les autres types de jointure physique sont Nested Loops et Hash Join. L’opérateur Merge Join tire parti du fait que les deux index sont triés sur la colonne de jointure, 'CustomerID'.

Considérez une légère variation sur cette requête, qui retourne toutes les lignes de la table Order, et non seulement OrderID :

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

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

Plan de requête pour une jointure de hachage de 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 Hash Match physique est maintenant utilisé pour le Inner Join. L'index cluster sur Order n’est pas trié sur CustomerID et nécessiterait donc un opérateur de tri Merge Join, ce qui affecterait les performances. Notez le coût relatif de l’opérateur Hash Match (75%) par rapport au coût de l’opérateur Merge Join dans l’exemple précédent (46%). L’optimiseur aurait considéré l’opérateur Hash Match également dans l’exemple précédent, mais a conclu que l’opérateur Merge Join a donné de meilleures performances.

Traitement des requêtes SQL Server pour les tables Disk-Based

Le diagramme suivant décrit 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ébrizer construisent une arborescence de requêtes avec des opérateurs logiques basés sur le texte Transact-SQL envoyé par l’utilisateur.

3. L’optimiseur crée un plan de requête optimisé contenant des opérateurs physiques (par exemple, jointure de 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 de plan contient déjà un plan pour cette requête.

4. Le moteur de traitement des requêtes exécute une interprétation du plan de requête.

5. Pour chaque recherche d’index, analyse d’index et opérateur d’analyse de table, le moteur d’exécution demande des lignes à partir des structures d’index et de table correspondantes à partir des méthodes Access.

6. Access Methods récupère les lignes de l’index et des pages de données dans le pool de mémoires tampons et charge les pages du disque dans le pool de mémoires tampons en fonction des besoins.

Pour le premier exemple de requête, le moteur d’exécution demande des lignes dans l’index cluster sur Customer et l’index non cluster sur Order from Access Methods. Access Methods traverse 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 car le plan nécessite des scans d'index complets.

Accès interprété Transact-SQL aux tables Memory-Optimized

Transact-SQL Les lots ad hoc et les procédures stockées sont également appelés Transact-SQL interprété. L’interprétation fait référence au fait que le plan de requête est interprété par le moteur d’exécution de requête 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.

Les Transact-SQL interprétés peuvent être utilisés pour accéder aux tables mémoire optimisées et sur disque. La figure suivante illustre l'illustration du traitement des requêtes pour l'accès Transact-SQL interprété aux tables optimisées pour la mémoire.

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

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

• L'analyseur et l'algébrizer construisent l'arbre 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 de traitement des requêtes traditionnel (figure 2) est que les lignes des tables à mémoire optimisée ne sont pas récupérées à partir du pool de mémoires tampons à l’aide de méthodes Access. Au lieu de cela, les lignes sont récupérées à partir des structures de données en mémoire via le moteur OLTP In-Memory. Les différences dans les structures de données entraînent le choix de différents plans dans certains cas, comme illustré par l’exemple suivant.

Le script Transact-SQL suivant contient des versions optimisées en mémoire des tables Order et Customer, à l’aide d’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  

Considérez 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 la jointure de tables mémoire optimisées.

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

• Ce plan contient une analyse de table plutôt qu’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 avec les tables en mémoire optimisée. Au lieu de cela, chaque table optimisée en mémoire doit avoir au moins un index non cluster et tous les index des tables mémoire optimisées peuvent accéder efficacement à toutes les colonnes de la table sans avoir à les stocker dans l’index ou à faire référence à un index cluster.

• Ce plan contient un Hash Match plutôt qu’un Merge Join. Les index sur les tables de Commande et de Client sont des index de hachage et ne sont donc pas ordonnés. Un Merge Join nécessiterait des opérateurs de tri qui réduiraient les performances.

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

Les procédures stockées compilées en mode natif sont Transact-SQL des procédures stockées qui sont compilées en code machine, plutôt qu'interprétées par le moteur d'exécution de requêtes. Le script suivant crée une procédure stockée compilée en mode natif qui exécute l’exemple de requête (à partir de 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 au moment 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ébrisation, ont lieu lors de la création. Toutefois, pour les procédures stockées interprétées, l’optimisation des plans de requête a lieu lors de 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 la première exécution de la procédure si le serveur est redémarré. Les procédures stockées interprétées sont recompilées si le plan n’est plus présent dans le cache. Le tableau suivant récapitule les cas de compilation et de recompilation pour les procédures stockées compilées et interprétées en mode natif :

	Compilé en mode natif	Interprété
Compilation initiale	Au moment de la création.	Lors de la première exécution.
Recompilation automatique	Lors de la première exécution de la procédure après un redémarrage d’une base de données ou d’un serveur.	Au redémarrage du serveur. Ou, éviction du cache de planification, généralement en fonction des modifications de schéma ou de statistiques, ou de la contrainte de mémoire.
Recompilation manuelle	Non pris en charge. La solution de contournement consiste à supprimer et recréer la procédure stockée.	Utilisez sp_recompile. Vous pouvez supprimer manuellement le plan du cache, par exemple via DBCC FREEPROCCACHE. Vous pouvez également créer la procédure stockée WITH RECOMPILE et la procédure stockée sera recompilée à chaque exécution.

Compilation et traitement des requêtes

Le diagramme suivant illustre le processus de compilation pour les procédures stockées compilées en mode natif :

Compilation native des procédures stockées.

Le processus est décrit comme suit :

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

2. L'analyseur et l'algébriseur établissent le flux de traitement de la procédure, ainsi que les arborescences des requêtes pour les 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 de la procédure stockée.

4. Le compilateur OLTP In-Memory prend le flux de traitement avec les plans de requête optimisés incorporés et génère une DLL qui contient le code de l’ordinateur pour l’exécution de 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 d’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 est décrit comme suit :

1. L’utilisateur émet une EXEC instruction usp_myproc.

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

   Si l’instruction a été préparée, par exemple en utilisant 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. Le runtime OLTP In-Memory localise le point d’entrée DLL pour la procédure stockée.

4. Le code de l’ordinateur dans la DLL est exécuté et les résultats de ceux-ci sont retournés au client.

Détection de paramètre

Les procédures stockées interprétées Transact-SQL sont compilées lors de la première exécution, contrairement aux procédures stockées compilées en mode natif, qui sont compilées au moment de la création. Lorsque des procédures stockées interprétées sont compilées lors 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 de paramètres pendant la compilation est appelée sniffing des paramètres.

La détection de paramètre 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. Comme les 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ête 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ête se compose d’une arborescence avec des opérateurs de requête sur les nœuds et des feuilles de l’arborescence. La structure de l’arborescence détermine l’interaction (le flux de lignes d’un opérateur à 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 d’analyse d’index, qui fournissent des lignes à un opérateur de jointure de fusion. L’opérateur de jointure de fusion fournit des lignes à l’opérateur de sélection. L'opérateur select retourne finalement 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 :

Opérateur	Exemple de requête
Sélectionner	SELECT OrderID FROM dbo.[Order]
INSERTION	INSERT dbo.Customer VALUES ('abc', 'def')
MISE À JOUR	UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'
Supprimer	DELETE dbo.Customer WHERE CustomerID='abc'
Calcul scalaire	Cet opérateur est utilisé à la fois pour les fonctions intrinsèques et les conversions de types. Toutes les fonctions et conversions de types ne sont pas prises en charge dans les procédures stockées compilées en mode natif. SELECT OrderID+1 FROM dbo.[Order]
Jointure de boucles imbriquées	Les boucles imbriquées sont le seul opérateur de jointure pris en charge dans les procédures stockées compilées en mode natif. Tous les plans qui contiennent des jointures utiliseront l’opérateur Boucles imbriquées, même si le plan pour la même requête exécutée en mode interprété Transact-SQL inclut une jointure de hachage ou de fusion. SELECT o.OrderID, c.CustomerID FROM dbo.[Order] o INNER JOIN dbo.[Customer] c
Trier	SELECT ContactName FROM dbo.Customer ORDER BY ContactName
Haut	SELECT TOP 10 ContactName FROM dbo.Customer
Tri supérieur	L’expression TOP (nombre de lignes à renvoyer) ne peut pas dépasser 8 000 lignes. Il y en a moins s'il y a également des opérateurs de jointure et d'agrégation dans la requête. Les jointures et l’agrégation réduisent généralement le nombre de lignes à trier, par rapport au nombre de lignes des tables de base. SELECT TOP 10 ContactName FROM dbo.Customer ORDER BY ContactName
Agrégat de flux	Notez que l’opérateur Hash Match n’est pas pris en charge pour l’agrégation. Par conséquent, toutes les agrégations dans les procédures stockées compilées en mode natif utilisent l’opérateur Stream Aggregate, même si le plan de la même requête utilise l’opérateur Hash Match dans les Transact-SQL interprétés. SELECT count(CustomerID) FROM dbo.Customer

Statistiques des colonnes et jointures

SQL Server gère les statistiques sur les valeurs des colonnes clés d’index pour vous aider à estimer le coût de certaines opérations, telles que l’analyse d’index et les recherches d’index. ( SQL Server crée également des statistiques sur des colonnes clés non indexées 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 métrique principale dans l’estimation des coûts est le nombre de lignes traitées par un opérateur unique. Notez que pour les tables basées sur disque, le nombre de pages accessibles par un opérateur particulier est important dans l’estimation des coûts. Toutefois, étant donné que le nombre de pages n’est pas important pour les tables optimisées en mémoire (il est toujours égal à zéro), cette discussion se concentre sur le nombre de lignes. L’estimation commence par les opérateurs de recherche et d’analyse d’index dans le plan, puis est é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 est basé sur l’estimation de l’index sous-jacent, de la recherche et des opérateurs d’analyse. Pour l'accès Transact-SQL interprété aux tables optimisées pour la mémoire, vous pouvez observer le plan d’exécution réel afin de voir la différence entre les nombres de lignes estimés et réels pour les opérateurs du plan.

Pour l’exemple de la figure 1,

• L’analyse de l’index cluster sur le client est estimée à 91 ; réel 91.

• L’analyse d’index non cluster sur CustomerID a estimé 830 ; réel 830.

• L’opérateur Merge Join a estimé 815 ; réel 830.

Les estimations des analyses d’index sont précises. SQL Server gère le nombre de lignes pour les tables sur disque. Les estimations pour les analyses complètes de tables et d'index sont toujours précises. L’estimation de la jointure est également assez précise.

Si ces estimations changent, les considérations relatives aux coûts des différentes alternatives de plan changent également. Par exemple, si l’un des côtés de la jointure a un nombre estimé de lignes de 1 ou seulement quelques lignes, l’utilisation d’une jointure de boucles imbriquées est moins coûteuse.

Voici le plan de la requête :

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, mais une dans la table Customer :

Concernant ce plan de requête :

• La correspondance par hachage a été remplacée par un opérateur de jointure physique de boucle imbriquée.

• L’analyse complète de l’index sur IX_CustomerID a été remplacée par une recherche d’index. Cela a entraîné l’analyse de 5 lignes, au lieu des 830 lignes requises pour l’analyse complète de l’index.

Statistiques et cardinalité pour les tables Memory-Optimized

SQL Server gère les statistiques au niveau des colonnes pour les tables mémoire optimisées. En outre, il conserve le nombre de lignes réel de la table. Toutefois, contrairement aux tables sur disque, les statistiques des tables mémoire optimisées ne sont pas automatiquement mises à jour. Par conséquent, les statistiques doivent être mises à jour manuellement après des modifications significatives dans les tables. Pour plus d’informations, consultez Statistiques pour Memory-Optimized Tables.

Voir aussi

Memory-Optimized tables