OLTP en mémoire dans Azure SQL Database

Article
09/26/2024

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.

Deux exemples dans cet article illustrent l'utilisation de In-Memory OLTP ainsi que des index columnstore dans Azure SQL Database.

Pour plus d’informations, consultez l’article suivant :

Présentation de l’OLTP en mémoire et scénarios d’utilisation (avec notamment des références à des études de cas client et des informations de prise en main)
Documentation pour l’OLTP In-Memory
Description des index columnstore
Traitement hybride transactionnel et analytique (HTAP), également appelé analytique opérationnelle en temps réel

Pour une démonstration d’introduction de In-Memory OLTP, consultez :

Version : in-memory-oltp-demo-v1.0
Code source : in-memory-oltp-demo-source-code

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. Ensuite, suivez les étapes de cette section pour ajouter des objets In-Memory OLTP à votre base de données AdventureWorksLT et démontrer les avantages en termes de performances.

Procédure d’installation :

Dans le portail Azure, créez une base de données Premium (DTU) ou Critique pour l'entreprise (vCore) sur un serveur logique. 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.
Vous connecter à la base de données avec SQL Server Management Studio (SSMS).
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.

Collez le script T-SQL dans SSMS, puis exécutez-le. La clause MEMORY_OPTIMIZED = ON des instructions CREATE TABLE 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 In-Memory OLTP n’est pas pris en charge, et 1 signifie qu’il l’est. In-Memory OLTP est disponible 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 affichages 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) AS module_name, 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 pour exécuter les deux procédures stockées. Vous pouvez comparer le temps d’exécution des deux contraintes.

Installer les utilitaires RML et ostress

De préférence, vous devez exécuter ostress.exe sur une machine virtuelle Azure. Vous créez une machine virtuelle Azure dans la même région Azure où votre base de données AdventureWorksLT réside. Vous pouvez également exécuter ostress.exe sur votre ordinateur local si vous pouvez vous connecter à votre base de données Azure SQL. Toutefois, le temps de réponse du réseau entre votre machine et la base de données dans Azure peut réduire les avantages en matière de performances d’OLTP en mémoire.

Sur la machine virtuelle, ou sur l’hôte que vous avez choisi, installez les utilitaires Replay Markup Language (RML). Les utilitaires incluent ostress.exe.

Pour plus d’informations, consultez l’article suivant :

La discussion ostress.exe dans Exemple de base de données pour OLTP en mémoire.
Exemple de base de données pour OLTP en mémoire.

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 RML Cmd pour exécuter 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 :

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;
```
Copiez le texte de la ligne de commande ostress.exequi précède dans le presse-papiers.
Remplacez le <placeholders> des paramètres -S -U -P -d par les valeurs correctes.
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 :

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;
```
Modifiez la ligne de commande ostress.exe pour remplacer toutes les occurrences de _inmem par _ondisk.
Réexécutez ostress.exe une deuxième fois, puis enregistrez le résultat de durée.
De nouveau, réinitialisez la base de données.

Résultats de la comparaison attendus

Nos tests In-Memory OLTP montrent une multiplication par 9 de l’amélioration des performances pour cette charge de travail simple, avec ostress.exe 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

Utilisez le portail Azure pour créer une base de données AdventureWorksLT à partir de l’exemple. Utilisez n’importe quel objectif de service qui prend en charge les index columnstore.
Copiez sql_in-memory_analytics_sample dans le Presse-papiers.
- Le script T-SQL crée les objets nécessaires dans la base de données d'exemple AdventureWorksLT que vous avez créée à 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.
- Sur les objectifs de service plus petits, le script peut prendre 15 minutes ou plus.
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 :
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 FactResellerSalesXL_CCI.

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 vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
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 query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
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 utilisant l'objectif de service P2, vous pouvez vous attendre à un gain de performance neuf fois supérieur pour cette requête en utilisant l'index columnstore en cluster par rapport à l'index rowstore traditionnel. Avec l’objectif de service P15, vous pouvez vous attendre à une multiplication des performances par 57 à l’aide de l’index columnstore.

Partager via

OLTP en mémoire dans Azure SQL Database

1. Installer l’exemple In-Memory OLTP

Procédure d’installation :

Erreur 40536

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

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

Installer les utilitaires RML et ostress

Script pour ostress.exe

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

Il en résulte une durée

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

Résultats de la comparaison attendus

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

Préparer le test d’analyse columnstore

Tables et index columnstore essentiels

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

Commentaires

Ressources supplémentaires

Partager via

OLTP en mémoire dans Azure SQL Database

1. Installer l’exemple In-Memory OLTP

Procédure d’installation :

Erreur 40536

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

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

Installer les utilitaires RML et ostress

Script pour ostress.exe

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

Il en résulte une durée

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

Résultats de la comparaison attendus

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

Préparer le test d’analyse columnstore

Tables et index columnstore essentiels

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

Contenu connexe

Commentaires

Ressources supplémentaires