Exemple en mémoire dans Azure SQL Managed Instance

  • Article

S’applique à : Azure SQL Managed Instance

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 l’article suivant :

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

1. Restaurer l’exemple de base de données OLTP en mémoire

Vous pouvez restaurer l’exemple AdventureWorksLT de base de données avec quelques étapes T-SQL dans SQL Server Management Studio (SSMS). Pour plus d’informations sur la restauration d’une base de données sur votre instance managée SQL, consultez Démarrage rapide : Restaurer une base de données dans Azure SQL Managed Instance avec SSMS.

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.

  1. Ouvrez SSMS et connectez-vous à votre SQL managed instance.

    Remarque

    Connections à votre instance managée Azure SQL à partir de votre station de travail locale ou d’une machine virtuelle Azure peut être effectuée en toute sécurité, sans ouvrir l’accès public. Consultez Configurer la machine virtuelle Azure pour qu’elle se connecte à Azure SQL Database Managed Instance et Configurer une connexion point à site à Azure SQL Managed Instance à partir d’un emplacement local.

  2. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur votre instance managée et sélectionnez Nouvelle requête pour ouvrir une nouvelle fenêtre de requête.

  3. Exécutez l'instruction T-SQL suivante, qui utilise un conteneur de stockage préconfiguré disponible publiquement et une clé de signature d'accès partagée pour créer un identifiant dans votre SQL Managed Instance. Avec le stockage disponible publiquement, aucune signature SAP n’est requise.

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';

  4. Exécutez l’instruction suivante pour restaurer l'exemple de base de données AdventureWorksLT.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

  5. Exécutez l’instruction suivante pour suivre l’état de votre processus de restauration.

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete
   , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  6. Une fois le processus de restauration terminé, affichez la base de données AdventureWorksLT dans l’explorateur d’objets. Vous pouvez vérifier que la base de données AdventureWorksLT est restaurée à l’aide de l’affichage sys.dm_operation_status.

À 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 devez créer un ordinateur virtuel Azure dans la même région Azure que votre instance Azure SQL Managed Instance. Toutefois, vous pouvez exécuter ostress.exe sur votre station de travail locale à la place, tant que vous pouvez vous connecter à votre instance managée 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. Restaurez une nouvelle AdventureWorksLT base de données sur votre instance managée SQL, en remplaçant la base de données existante que vous avez installée précédemment, à l’aide WITH REPLACEde .

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

  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

Selon la configuration de votre instance managée SQL, vous pouvez vous attendre à des gains de performances significatifs pour cette requête à l’aide de l’index columnstore cluster par rapport à l’index traditionnel.

Contenu connexe