Démonstration : optimisation des performances de l'OLTP en mémoire

Article
07/12/2024

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance

l’exemple de code de cette rubrique illustre les performances rapides des tables optimisées en mémoire. L’optimisation des performances est évidente quand l’accès aux données dans une table optimisée en mémoire s’effectue à partir d’un traditionnel et interprété Transact-SQL. Cette amélioration des performances est encore supérieure lorsque les données dans les tables optimisées en mémoire sont accédées au moyen d’une procédure stockée compilée en mode natif (NCSProc).

Pour voir une démonstration plus complète des améliorations des performances potentielles d’OLTP en mémoire, voir Démo v1.0 des performances de la fonction OLTP en mémoire SQL Server.

L’exemple de code dans cet article est mono-thread, et il ne tire pas profit des avantages de la concurrence de l’OLTP en mémoire. Une charge de travail utilisant la concurrence aura des gains de performance supérieurs. Cet exemple de code illustre un seul aspect de l’amélioration des performances, l’efficacité de l’accès aux données pour INSERT.

L’amélioration des performances offerte par les tables mémoire optimisées est totale lorsque l’accès aux données s’effectue au moyen d’une NCSProc.

Exemple de code

Les sous-sections suivantes décrivent chaque étape.

Étape 1a : condition préalable requise si vous utilisez SQL Server

Les étapes de cette première sous-section s’appliquent uniquement si vous exécutez dans SQL Server et ne s’appliquent pas si vous exécutez dans la base de données Azure SQL. Effectuez les actions suivantes :

Utilisez SQL Server Management Studio (SSMS.exe) pour vous connecter à votre SQL Server. Sinon, tout autre outil similaire à SSMS.exe convient.
Créez manuellement un répertoire nommé C:\data\. L’exemple de code Transact-SQL exige que le répertoire existe déjà.
Exécutez le T-SQL court pour créer la base de données et son groupe de fichiers mémoire optimisé.

go  
CREATE DATABASE imoltp;    --  Transact-SQL  
go  
  
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
    CONTAINS MEMORY_OPTIMIZED_DATA;  
  
ALTER DATABASE imoltp ADD FILE  
    (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')  
    TO FILEGROUP imoltp_mod;  
go  
  
USE imoltp;  
go

Étape 1b : prérequis si vous utilisez la base de données Azure SQL

Cette sous-section s’applique uniquement si vous utilisez la base de données Azure SQL. Effectuez les actions suivantes :

Décidez quelle base de données test existante vous allez utiliser pour l’exemple de code.
Si vous décidez de créer une nouvelle base de données de test, utilisez le portail Azure pour créer une base de données nommée imoltp.

Pour des instructions concernant l’utilisation du portail Azure, consultez Prise en main de la base de données SQL Azure.

Étape 2 : Créer des tables optimisées en mémoire et une NSCProc

Cette étape permet de créer des tables optimisées en mémoire et une procédure stockée compilée en mode natif (NCSProc). Effectuez les actions suivantes :

Utilisez SSMS.exe pour vous connecter à votre nouvelle base de données.
Exécutez le T-SQL suivant dans votre base de données.

go  
DROP PROCEDURE IF EXISTS ncsp;  
DROP TABLE IF EXISTS sql;  
DROP TABLE IF EXISTS hash_i;  
DROP TABLE IF EXISTS hash_c;  
go  
  
CREATE TABLE [dbo].[sql] (  
  c1 INT NOT NULL PRIMARY KEY,  
  c2 NCHAR(48) NOT NULL  
);  
go  
  
CREATE TABLE [dbo].[hash_i] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE TABLE [dbo].[hash_c] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE PROCEDURE ncsp  
    @rowcount INT,  
    @c NCHAR(48)  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS   
  BEGIN ATOMIC   
  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @i INT = 1;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[hash_c] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
END;  
go

Étape 3 : Exécuter le code

Vous pouvez maintenant exécuter les requêtes pour démontrer les performances des tables optimisées en mémoire. Effectuez les actions suivantes :

Utilisez SSMS.exe pour exécuter le T-SQL suivant dans votre base de données.

Ignorez les données de vitesse ou autres données de performance générées par cette première exécution. La première exécution effectue plusieurs opérations uniques, telles que les allocations initiales de mémoire.
Là encore, utilisez SSMS.exe pour exécuter à nouveau le T-SQL suivant dans votre base de données.

go  
SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
  
-- Inserts, one at a time.  
  
DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timems INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
  
-- Harddrive-based table and interpreted Transact-SQL.  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[sql] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'A: Disk-based table and interpreted Transact-SQL: '  
    + cast(@timems AS VARCHAR(10)) + ' ms';  
  
-- Interop Hash.  
  
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
    BEGIN;  
      INSERT INTO [dbo].[hash_i] VALUES (@i, @c);  
      SET @i += 1;  
    END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '  
    + cast(@timems as VARCHAR(10)) + ' ms';  
  
-- Compiled Hash.  
  
SET @starttime = sysdatetime();  
  
EXECUTE ncsp @rowcount, @c;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'C: memory-optimized table with hash index and native SP:'  
    + cast(@timems as varchar(10)) + ' ms';  
go  
  
DELETE sql;  
DELETE hash_i;  
DELETE hash_c;  
go

Les statistiques de temps de sortie générées par la seconde exécution test sont les suivantes.

10453 ms , A: Disk-based table and interpreted Transact-SQL.  
5626 ms , B: memory-optimized table with hash index and interpreted Transact-SQL.  
3937 ms , C: memory-optimized table with hash index and native SP.

Voir aussi

In-Memory OLTP (optimisation en mémoire)

Partager via