Démonstration : Amélioration des performances de In-Memory OLTP

Cet exemple montre des améliorations des performances lors de l’utilisation de In-Memory OLTP en comparant les différences dans les temps de réponse lors de l’exécution d’une requête Transact-SQL identique sur des tables à mémoire optimisée et basées sur des disques traditionnels. En outre, une procédure stockée compilée en mode natif est également créée (basée sur la même requête), puis exécutée pour démontrer que vous obtenez généralement les meilleurs temps de réponse lors de l’interrogation d’une table optimisée en mémoire avec une procédure stockée compilée en mode natif. Cet exemple montre uniquement un aspect des améliorations des performances lors de l’accès aux données dans des tables mémoire optimisées ; efficacité de l’accès aux données lors de l’exécution d’insertions. Cet exemple est monothread et ne tire pas parti des avantages de la concurrence de In-Memory OLTP. Une charge de travail qui utilise la concurrence verra un gain de performances plus élevé.

Pour terminer cet exemple, vous allez effectuer les opérations suivantes :

1. Créez une base de données nommée imoltp et modifiez ses détails de fichier pour la configurer à l’aide de In-Memory OLTP.

2. Créez les objets de base de données pour notre exemple : trois tables et une procédure stockée compilée en mode natif.

3. Exécutez les différentes requêtes et affichez les temps de réponse pour chaque requête.

Pour configurer la base de données imoltp pour notre exemple, commencez par créer un dossier vide : c :\imoltp_data, puis exécutez le code suivant :

USE master  
GO  
  
-- Create a new database.  
CREATE DATABASE imoltp  
GO  
  
-- Prepare the database for In-Memory OLTP by  
-- adding a memory-optimized filegroup to the database.  
ALTER DATABASE imoltp ADD FILEGROUP imoltp_file_group  
    CONTAINS MEMORY_OPTIMIZED_DATA;  
  
-- Add a file (to hold the memory-optimized data) to the new filegroup.  
ALTER DATABASE imoltp ADD FILE (name='imoltp_file', filename='c:\imoltp_data\imoltp_file')  
    TO FILEGROUP imoltp_file_group;  
GO  
  

Ensuite, exécutez le code suivant pour créer la table sur disque, deux (2) tables mémoire optimisées et la procédure stockée compilée en mode natif qui sera utilisée pour illustrer les différentes méthodes d’accès aux données :

USE imoltp  
GO  
  
-- If the tables or stored procedure already exist, drop them to start clean.  
IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'DiskBasedTable')  
   DROP TABLE [dbo].[DiskBasedTable]  
GO  
  
IF EXISTS (SELECT NAME FROM sys.objects WHERE NAME = 'InMemTable')  
   DROP TABLE [dbo].[InMemTable]  
GO  
  
IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'InMemTable2')  
   DROP TABLE [dbo].[InMemTable2]  
GO  
  
IF EXISTS (SELECT NAME FROM sys.objects  WHERE NAME = 'usp_InsertData')  
   DROP PROCEDURE [dbo].[usp_InsertData]  
GO  
  
-- Create a traditional disk-based table.  
CREATE TABLE [dbo].[DiskBasedTable] (  
  c1 INT NOT NULL PRIMARY KEY,  
  c2 NCHAR(48) NOT NULL  
)  
GO  
  
-- Create a memory-optimized table.  
CREATE TABLE [dbo].[InMemTable] (  
  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 a 2nd memory-optimized table.  
CREATE TABLE [dbo].[InMemTable2] (  
  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 a natively-compiled stored procedure.  
CREATE PROCEDURE [dbo].[usp_InsertData]   
  @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].[inMemTable2](c1,c2) VALUES (@i, @c);  
    SET @i += 1;  
  END  
END  
GO  
  

La configuration est terminée et nous sommes prêts à exécuter les requêtes qui affichent les temps de réponse comparant les performances entre les méthodes d’accès aux données.

Pour terminer l’exemple, exécutez le code suivant plusieurs fois. Ignorez les résultats de la première exécution qui est affectée négativement par l’allocation de mémoire initiale.

SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
  
-- Delete data from all tables to reset the example.  
DELETE FROM [dbo].[DiskBasedTable]   
    WHERE [c1]>0  
GO  
DELETE FROM [dbo].[inMemTable]   
    WHERE [c1]>0  
GO  
DELETE FROM [dbo].[InMemTable2]   
    WHERE [c1]>0  
GO  
  
-- Declare parameters for the test queries.  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
DECLARE @timems INT;  
DECLARE @starttime datetime2 = sysdatetime();  
  
-- Disk-based table queried with interpreted Transact-SQL.  
BEGIN TRAN  
  WHILE @I <= @rowcount  
  BEGIN  
    INSERT INTO [dbo].[DiskBasedTable](c1,c2) VALUES (@i, @c);  
    SET @i += 1;  
  END  
COMMIT  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT CAST(@timems AS VARCHAR(10)) + ' ms (disk-based table with interpreted Transact-SQL).';  
  
-- Memory-optimized table queried with interpreted Transact-SQL.  
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN  
  WHILE @i <= @rowcount  
    BEGIN  
      INSERT INTO [dbo].[InMemTable](c1,c2) VALUES (@i, @c);  
      SET @i += 1;  
    END  
COMMIT  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT CAST(@timems AS VARCHAR(10)) + ' ms (memory-optimized table with interpreted Transact-SQL).';  
  
-- Memory-optimized table queried with a natively-compiled stored procedure.  
SET @starttime = sysdatetime();  
  
EXEC usp_InsertData @rowcount, @c;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT CAST(@timems AS VARCHAR(10)) + ' ms (memory-optimized table with natively-compiled stored procedure).';  
  

Les résultats attendus fournissent des temps de réponse réels montrant comment l’utilisation de tables optimisées en mémoire et de procédures stockées compilées en mode natif fournit généralement des temps de réponse plus rapides que les mêmes charges de travail exécutées sur des tables sur disque traditionnelles.

Voir aussi

Extensions d’AdventureWorks afin de démontrer In-Memory OLTP
In-Memory OLTP (OptimisationIn-Memory)
Memory-Optimized tables
Procédures stockées compilées en mode natif
Configuration requise pour l’utilisation de tables Memory-Optimized
CREATE DATABASE (SQL Server Transact-SQL)
Options de fichier et de groupe de fichiers ALTER DATABASE (Transact-SQL)
CREATE PROCEDURE et les tables Memory-Optimized