Démonstration : optimisation des performances de l'OLTP en mémoire
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
l’exemple de code de cette rubrique illustre les performances rapides des tables optimisées en mémoire. L’amélioration des performances est évidente lorsque les données d’une table mémoire optimisée sont accessibles à partir de Transact-SQL traditionnel interprété. 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 de l’article présent est monothread et ne tire pas parti des avantages de la concurrence d’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 : Conditions préalables à l’utilisation de SQL Server
Les étapes décrites dans 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 Azure SQL Database. Procédez comme suit :
Utilisez SQL Server Management Studio (SSMS.exe) pour vous connecter à votre serveur 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 Azure SQL Database
Cette sous-section s’applique uniquement si vous utilisez Azure SQL Database. Procédez comme suit :
Déterminez la base de données de test existante que vous utiliserez 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). Procédez comme suit :
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. Procédez comme suit :
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
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour