Демонстрация повышения производительности выполняющейся в памяти OLTP

Статья
05/30/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

Пример кода в этом разделе демонстрирует повышение производительности оптимизированных для памяти таблиц. Улучшение производительности видно, когда данные в оптимизированной для памяти таблице получают доступ к традиционным интерпретируемым Transact-SQL. Улучшение производительности будет даже заметнее, если доступ к данным в оптимизированной для памяти таблице выполняется из хранимой процедуры, скомпилированной в собственном коде (NCSProc).

Чтобы ознакомиться с более подробной демонстрацией преимуществ выполняющейся в памяти OLTP в отношении производительности, см. демонстрацию производительность выполняющейся в памяти OLTP версии 1.0.

Пример кода в данной статье является однопоточным, и он не использует преимущества параллелизма в памяти OLTP. Рабочая нагрузка, которая использует параллелизм, получит более заметное повышение производительности. В этом примере кода показан только один аспект улучшения производительности — эффективность доступа к данным для вставки.

Улучшение производительности, обеспечиваемое оптимизированными для памяти таблицами, полностью реализуется, когда доступ к данным в этих таблицах осуществляется из NCSProc.

Пример кода

В следующих подразделах описывается каждый шаг.

Шаг 1a. Предварительные требования при использовании SQL Server

Действия, описанные в этом первом подразделе, применяются только в том случае, если вы работаете в SQL Server и не применяется, если вы работаете в базе данных SQL Azure. Выполните следующие действия.

Используйте SQL Server Management Studio (SSMS.exe) для подключения к SQL Server. Можно также использовать любое другое аналогичное средство.
Вручную создайте каталог C:\data\. Пример кода Transact SQL предполагает, что каталог уже существует.
Запустите короткий код T-SQL, чтобы создать базу данных и соответствующую оптимизированную для памяти файловую группу.

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

Шаг 1b. Предварительные требования при использовании базы данных SQL Azure

Этот подраздел применяется только в том случае, если вы используете базу данных SQL Azure. Выполните следующие действия.

Определите, какую существующую тестовую базу данных вы будете использовать для примера кода.
При необходимости создайте тестовую базу данных на портале Azure с именем imoltp.

Инструкции по созданию базы данных на портале Azure см. в руководстве по началу работы с базами данных SQL Azure.

Шаг 2. Создание оптимизированных для памяти таблиц и NCSProc

На этом шаге создаются оптимизированные для памяти таблицы и хранимая процедура, скомпилированная в собственном коде (NCSProc). Выполните следующие действия.

Подключитесь к новой базе данных с помощью SSMS.exe.
Запустите следующий код T-SQL в базе данных.

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

Шаг 3. Запуск кода

Затем можно выполнить запросы, которые продемонстрируют производительность оптимизированных для памяти таблиц. Выполните следующие действия.

Запустите приведенный ниже код T-SQL в базе данных с помощью SSMS.exe.

Игнорируйте данные о скорости или другие данные производительности, которые формируются при первом запуске. Во время первого запуска выполняется несколько одноразовых операций, например начальное выделение памяти.
Повторно запустите приведенный ниже код T-SQL в базе данных с помощью SSMS.exe.

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

Ниже приведены статистические данные о времени вывода, сформированные при выполнении второго тестового запуска.

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.

См. также

In-Memory OLTP (оптимизация в памяти)