OLTP en mémoire dans Azure SQL Database

  • Article

S’applique à Azure SQL Database

Les technologies en mémoire d’Azure SQL Database vous permettent d’améliorer les performances de votre application, et potentiellement de réduire le coût de votre base de données. En utilisant les technologies en mémoire dans Azure SQL Database, vous pouvez réaliser des améliorations de performance avec différentes charges de travail.

Dans cet article, vous verrez deux exemples qui illustrent l’utilisation d’OLTP en mémoire, ainsi que des index columnstore dans Azure SQL Database.

Pour plus d'informations, consultez les pages suivantes :

Pour une démonstration plus simple mais intéressante des performances de l’OLTP en mémoire, consultez :

1. Installer l’exemple In-Memory OLTP

Vous pouvez créer l’exemple de base de données AdventureWorksLT en quelques clics dans le portail Azure. Les étapes de cette section expliquent comment enrichir votre base de données AdventureWorksLT d’objets OLTP en mémoire et démontrent les avantages au niveau des performances.

Procédure d’installation :

  1. Dans le portail Azure, créez une base de données Premium (DTU) ou Critique pour l’entreprise (vCore) sur un serveur. Définissez comme valeur Source l’exemple de base de données AdventureWorksLT. Pour obtenir des instructions détaillées, consultez Créer votre première base de données dans Azure SQL Database.

  2. Vous connecter à la base de données avec SQL Server Management Studio (SSMS).

  3. Copiez le script In-Memory OLTP Transact-SQL dans le Presse-papiers. Le script T-SQL crée les objets en mémoire nécessaires dans l’exemple de base de données AdventureWorksLT créé à l’étape 1.

  4. Collez le script T-SQL dans SSMS, puis exécutez-le. La MEMORY_OPTIMIZED = ON clause des CREATE TABLE instructions est cruciale. Par exemple :

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Erreur 40536

Si vous obtenez une erreur 40536 lorsque vous exécutez le script T-SQL, exécutez le script T-SQL suivant pour vérifier que la base de données prend en charge In-Memory :

SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');

Un résultat de 0 signifie que la technologie en mémoire n’est pas prise en charge, et un résultat de 1 signifie qu’elle l’est. Les technologies en mémoire sont disponibles dans les niveaux Azure SQL Database Premium (DTU) et Critique pour l’entreprise (vCores).

À propos des éléments créés à mémoire optimisée.

Tables : L’exemple contient les tables à mémoire optimisée suivantes :

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

Vous pouvez inspecter les tables à mémoire optimisée via l’Explorateur d’objets dans SSMS. Lorsque vous cliquez avec le bouton droit sur Tables, accédez à >Filtrer>les filtres Paramètres> Is mémoire optimisée. La valeur est égale à 1.

Vous pouvez aussi interroger les vues de catalogue, telles que :

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Procédure stockée compilée en mode natif : Vous pouvez inspecter SalesLT.usp_InsertSalesOrder_inmem via une requête de vue de catalogue :

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Exécuter l’exemple de charge de travail OLTP

La seule différence entre les deux procédures stockées est que la première utilise les versions à mémoire optimisée des tables, tandis que la deuxième utilise les tables sur disque régulières :

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

Dans cette section, vous apprendrez à utiliser l’utilitaire ostress.exe , pratique pour exécuter les deux procédures stockées à des niveaux de contrainte élevés. Vous pouvez comparer le temps d’exécution des deux contraintes.

Installer les utilitaires RML et ostress

Dans l’idéal, vous devez prévoir d’exécuter ostress.exe sur une machine virtuelle Azure. Vous créez une machine virtuelle Azure dans la même région Azure de votre AdventureWorksLT base de données. Toutefois, vous pouvez exécuter ostress.exe sur votre station de travail locale à la place, tant que vous pouvez vous connecter à votre base de données Azure SQL.

Sur la machine virtuelle (ou sur l’hôte que vous avez choisi d’utiliser), installez les utilitaires RML. Ceux-ci incluent ostress.exe.

Pour plus d'informations, consultez les pages suivantes :

Script pour ostress.exe

Cette section affiche le script T-SQL incorporé à la ligne de commande ostress.exe. Le script utilise des éléments créés par le script T-SQL installé précédemment.

Lorsque vous exécutez ostress.exe, nous vous recommandons de transmettre des valeurs de paramètre conçues pour :

  • Exécuter un grand nombre de connexions simultanées, en utilisant -n100.
  • Répéter chaque boucle de connexion une centaine de fois, en utilisant -r500.

Toutefois, vous pouvez commencer avec des valeurs plus petites, telles que -n10 et -r50 pour vous assurer que tout fonctionne.

Le script suivant insère un exemple de commande client avec cinq lignes dans les tablesà mémoire optimisée suivantes :

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Pour créer la version _ondisk du script T-SQL précédent pour ostress.exe, il suffit de remplacer les deux occurrences de la sous-chaîne _inmem par _ondisk. Ces remplacements affectent les noms des tables et des procédures stockées.

Commencer par exécuter la charge de travail de contrainte _inmem

Vous pouvez utiliser une fenêtre d’ invite de commande RML pour exécuter la ligne de commande ostress.exe. Les paramètres de ligne de commande indiquent à ostress d’effectuer les tâches suivantes :

  • Exécuter 100 connexions simultanément (-n100).
  • Chaque connexion doit exécuter le script T-SQL 50 fois (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

Pour exécuter la ligne de commande ostress.exe précédente :

  1. Réinitialisez le contenu de la base de données en exécutant la commande suivante dans SSMS, pour supprimer toutes les données insérées lors des exécutions précédentes :

    EXECUTE Demo.usp_DemoReset;

  2. Copiez le texte de la ligne de commande ostress.exe qui précède dans le presse-papiers.

  3. Remplacez le <placeholders> des paramètres -S -U -P -d par les valeurs réelles correctes.

  4. Exécutez la ligne de commande que vous avez modifiée dans la fenêtre de commande RML.

Il en résulte une durée

Lorsque ostress.exe est terminé, la durée d’exécution est indiquée sur la dernière ligne de sortie dans la fenêtre de commande RML. Par exemple, une série de tests plus courte a duré environ 1,5 minute :

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Réinitialisez, paramétrez _ondisk, puis procédez à une nouvelle exécution.

Une fois le résultat de l’exécution de _inmem obtenu, effectuez les opérations suivantes pour l’exécution de _ondisk :

  1. Réinitialisez la base de données en exécutant la commande suivante dans SSMS pour supprimer toutes les données insérées lors de l’exécution précédente :

    EXECUTE Demo.usp_DemoReset;

  2. Modifiez la ligne de commande ostress.exe pour remplacer toutes les occurrences de _inmem par _ondisk.

  3. Réexécutez ostress.exe une deuxième fois, puis enregistrez le résultat de durée.

  4. Réinitialisez à nouveau la base de données (pour supprimer une quantité de données de test qui peut s’avérer conséquente).

Résultats de la comparaison attendus

Nos tests en mémoire montrent une multiplication par neuf de l’amélioration des performances pour cette charge de travail simple, avec ostress s’exécutant sur une machine virtuelle Azure dans la même région Azure que la base de données.

3. Installer l'exemple d'analytique en mémoire

Dans cette section, vous comparez les résultats des statistiques et les résultats d’E/S lors de l’utilisation d’un index columnstore par rapport à un index d’arborescence B traditionnel.

Pour l’analyse en temps réel sur une charge de travail OLTP, il est souvent préférable d’utiliser un index columnstore sans cluster. Pour plus d’informations, consultez Index columnstore décrits.

Préparer le test d’analyse columnstore

  1. Utilisez le portail Azure pour créer une base de données AdventureWorksLT à partir de l’exemple.

    • Utilisez ce nom exact.
    • Choisissez un niveau de service Premium.

  2. Copiez sql_in-memory_analytics_sample dans le Presse-papiers.

    • Le script T-SQL crée les objets en mémoire nécessaires dans l’exemple de base de données AdventureWorksLT créé à l’étape 1.
    • Le script crée la table Dimension et deux tables de faits. Les tables de faits comprennent 3,5 millions de lignes chacune.
    • Le script peut prendre 15 minutes pour s’exécuter.

  3. Collez le script T-SQL dans SSMS, puis exécutez-le. Le mot clé COLUMNSTORE est essentiel dans l’instruction CREATE INDEXCREATE INDEXCREATE NONCLUSTERED COLUMNSTORE INDEX ...;, comme dans l’exemple ci-dessous :

  4. Mettre AdventureWorksLT au niveau de compatibilité le plus récent, SQL Server 2022 (160) : ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Tables et index columnstore essentiels

  • dbo.FactResellerSalesXL_CCI est une table contenant un index columnstore en cluster, ce qui permet la compression avancée au niveau des données.

  • dbo.FactResellerSalesXL_PageCompressed est une table qui possède un index cluster régulier équivalent, compressé uniquement au niveau de la page.

4. Requêtes essentielles pour comparer l’index columnstore

Il existe plusieurs types de requête T-SQL que vous pouvez exécuter pour mettre en évidence les améliorations des performances. À l’étape 2 dans le script T-SQL, soyez attentif à ces deux requêtes. Elles diffèrent uniquement d’une ligne :

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

Un index columnstore en cluster se trouve dans la table FactResellerSalesXLFactResellerSalesXL_CCICCI.

Le script T-SQL suivant imprime les statistiques d’E/S logiques et d’heure à l’aide de SET STATISTICS IO et SET STATISTICS TIME pour chaque requête.

/*********************************************************************
Step 2 -- Overview
-- Page Compressed BTree table v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the Fact Table with dimension tables
-- Note this query will run on the Page Compressed table, Note down the time
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Dans une base de données ayant le niveau tarifaire P2, vous pouvez attendre une multiplication par 9 des performances de cette requête avec l’index columnstore en cluster par rapport à l’index traditionnel. Avec P15, vous pouvez vous attendre à une multiplication des performances par 57 à l’aide de l’index columnstore.

Contenu connexe