Как создать таблицу, оптимизированную для памяти? Технология In-Memory OLTP в Microsoft SQL Server

В Microsoft SQL Server уже достаточно давно появилась технология In-Memory OLTP, которая позволяет хранить данные в памяти, за счет этого можно добиться значительного увеличения производительности. Сегодня в статье я расскажу Вам про эту технологию, а также покажу, как создаются таблицы, оптимизированные для памяти.

In-Memory OLTP – это технология, которая позволяет размещать таблицы с данными в памяти системы, при этом Вы можете обращаться к ним точно так же, как и к таблицам, которые расположены на диске.

Данная технология предназначена для оптимизации производительности, иными словами, благодаря In-Memory OLTP можно в десятки раз увеличить производительность SQL инструкций.

Функционал In-Memory OLTP – это не отдельный компонент, который нужно устанавливать, это часть ядра Microsoft SQL Server, но он доступен только в редакции Enterprise и впервые появился в 2014 версии SQL сервера.

Таким образом, использовать возможности In-Memory OLTP в Microsoft SQL Server 2012 или более ранних версиях не получится. Так же, как и не получится использовать In-Memory OLTP в редакциях Standard или Express.

Когда только появилась технология In-Memory OLTP в Microsoft SQL Server 2014, она функционировала с большими ограничениями, в 2016 версии много ограничений было снято, а в 2017 версии функционал еще больше расширили, в следующих версиях улучшения в этом направлении, я думаю, также будут. Поэтому если Вы планируете активно применять технологию In-Memory OLTP, то приобретайте самую последнюю версию SQL Server.

Заметка! Все, что мы рассмотрим ниже, предполагает наличие у Вас базы знаний (основ) в части работы с языком T-SQL и Microsoft SQL Server в целом. Новичкам рекомендую посмотреть мой видеокурс по T-SQL для начинающих, в нем подробно рассмотрены все базовые конструкции языка T-SQL.

Пошаговое описание создания таблицы, оптимизированной для памяти

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

Примечание! Помните, что In-Memory OLTP доступна в редакции Enterprise, начиная с Microsoft SQL Server 2014.

Исходные данные

Для тестирования технологии In-Memory OLTP давайте создадим отдельную базу данных, в которой мы и будем создавать таблицы, оптимизированные для памяти. Для примера я создам DBInMemory.

   
   --Создание обычной базы данных для тестов
   CREATE DATABASE DBInMemory;
   GO
   --Переходим в контекст новой базы данных
   USE DBInMemory;
   GO

Шаг 1 – Создание файловой группы для таблиц, оптимизированных для памяти

Для таблиц, оптимизированных для памяти, сначала необходимо создать специальную, оптимизированную для памяти файловую группу — MEMORY_OPTIMIZED_DATA. Давайте создадим для нашей тестовой базы данных файловую группу, оптимизированную для памяти, например, с названием FileGroupInMemory.

   
   --Создаём файловую группу, оптимизированную для памяти
   ALTER DATABASE DBInMemory ADD FILEGROUP FileGroupInMemory CONTAINS MEMORY_OPTIMIZED_DATA;   
   GO

Шаг 2 – Добавляем контейнер в файловую группу

После создания специальной файловой группы, нам нужно добавить в нее контейнер для хранения файлов данных. В моем случае я добавляю контейнер FileInMemory, его расположение на диске D в каталоге DataBase.

   
   --Добавляем контейнер в файловую группу
   ALTER DATABASE DBInMemory ADD FILE (name='FileInMemory', 
                                                        filename='D:\DataBase\FileInMemory') 
                                                  TO FILEGROUP FileGroupInMemory;   
  GO

Шаг 3 – Создание таблицы, оптимизированной для памяти

Чтобы создать таблицу, оптимизированную для памяти, необходимо использовать обычную инструкцию CREATE TABLE, но при этом нужно указать параметр MEMORY_OPTIMIZED=ON, иными словами, сказать SQL серверу о том, что мы хотим создать таблицу именно в памяти, а не на диске.

Мы можем создать таблицы, оптимизированные для памяти двух видов, на это влияет параметр DURABILITY:

  • DURABILITY=SCHEMA_AND_DATA – таблица создается в памяти, но при этом дублируется на диске, чтобы в случае перезапуска SQL сервера все данные восстановились. Это поведение по умолчанию, поэтому в таких случаях параметр DURABILITY со значением SCHEMA_AND_DATA указывать необязательно;
  • DURABILITY=SCHEMA_ONLY — таблица создается только в памяти, после перезагрузки SQL Server все данные будут утеряны.

Создание таблицы, оптимизированной для памяти, предполагает обязательное создание индекса, при этом можно использовать два вида индекса (Основы индексов в Microsoft SQL Server):

  • хэш-индекс (появился одновременно с технологией In-Memory OLTP);
  • некластеризованный индекс.

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

  • datetimeoffset;
  • geography;
  • geometry;
  • hierarchyid;
  • rowversion;
  • xml;
  • sql_variant.

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

Заметка! Архитектура хранения данных в Microsoft SQL Server.

Для одного поля мы создадим первичный ключ и некластеризованный индекс, здесь стоит понимать, что нам нужен именно некластеризованный индекс, так как, если помните, при создании обычной таблицы на диске с указанием первичного ключа по умолчанию создаётся кластеризованный индекс, поэтому в случае с таблицами в памяти мы принудительно указываем NONCLUSTERED.

   
   --Создаем таблицу, оптимизированную для памяти
   CREATE TABLE TestTableInMemory (   
    ProductId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
    ProductName NVARCHAR (100) NOT NULL,   
    Price MONEY  
    ) WITH (MEMORY_OPTIMIZED=ON);--Параметр для создания таблицы, оптимизированной для памяти
   GO

Шаг 4 – Работа с таблицей, оптимизированной для памяти

С оптимизированными для памяти таблицами можно работать как с обычными таблицами, т.е. мы также можем писать инструкции SELECT, INSERT, UPDATE и DELETE. При этом стоит, конечно же, учитывать, что существуют определенные ограничения на использование некоторых конструкций языка Transact-SQL, например, нельзя использовать инструкцию TRUNCATE. Более подробно узнать о том, какие конструкции языка Transact-SQL не поддерживаются в In-Memory OLTP, можете посмотреть в официальной документации.

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

   
   --Добавляем данные в таблицу
   INSERT INTO TestTableInMemory
   VALUES ('Тестовая строка', 100);
   GO
   --Проверяем
   SELECT * FROM TestTableInMemory;

Итоговый скрипт создания таблицы, оптимизированной для памяти в Microsoft SQL Server

Сейчас я приведу итоговый скрипт, который выполняет все необходимые действия по созданию таблицы, оптимизированной для памяти, в него я также включил и инструкцию по созданию тестовой базы данных.

   
   --Создание обычной базы данных для тестов
   CREATE DATABASE DBInMemory;
   GO
   --Переходим в контекст новой базы данных
   USE DBInMemory;
   GO
   --Создаём файловую группу, оптимизированную для памяти
   ALTER DATABASE DBInMemory ADD FILEGROUP FileGroupInMemory CONTAINS MEMORY_OPTIMIZED_DATA;   
   GO
   --Добавляем контейнер в файловую группу
   ALTER DATABASE DBInMemory ADD FILE (name='FileInMemory', 
                                                        filename='D:\DataBase\FileInMemory') 
                                                  TO FILEGROUP FileGroupInMemory;   
   GO
   --Создаем таблицу, оптимизированную для памяти
   CREATE TABLE TestTableInMemory (   
    ProductId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
    ProductName NVARCHAR (100) NOT NULL,   
    Price MONEY  
    ) WITH (MEMORY_OPTIMIZED=ON);--Параметр для создания таблицы, оптимизированной для памяти
   GO
   --Добавляем данные в таблицу
   INSERT INTO TestTableInMemory
   VALUES ('Тестовая строка', 100);
   GO
   --Проверяем
   SELECT * FROM TestTableInMemory;

Скриншот 2

Заметка! Блокировки в Microsoft SQL Server – что это такое и для чего нужны.

У меня все, надеюсь, материал был Вам интересен и полезен, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.