OLTP en mémoire dans Azure SQL Database
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.
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 instructionsCREATE 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.exe
qui 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.
- Le script T-SQL crée les objets nécessaires dans la base de données d'exemple
Collez le script T-SQL dans SSMS, puis exécutez-le. Le mot clé COLUMNSTORE est essentiel dans l’instruction
CREATE INDEX
CREATE 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.
Contenu connexe
- Démarrage rapide 1 : Technologies OLTP en mémoire pour des performances T-SQL plus rapides
- Utiliser OLTP en mémoire pour améliorer les performances de votre application
- Surveiller le stockage OLTP In-Memory
- Blog : In-Memory OLTP dans Azure SQL Database
- OLTP en mémoire
- Index Columnstore
- Index columnstore pour l’analytique opérationnelle en temps réel
- Article technique : OLTP en mémoire - modèles de charge de travail courants et considérations relatives à la migration dans SQL Server 2014