В 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.
- Пошаговое описание создания таблицы, оптимизированной для памяти
- Исходные данные
- Шаг 1 – Создание файловой группы для таблиц, оптимизированных для памяти
- Шаг 2 – Добавляем контейнер в файловую группу
- Шаг 3 – Создание таблицы, оптимизированной для памяти
- Шаг 4 – Работа с таблицей, оптимизированной для памяти
- Итоговый скрипт создания таблицы, оптимизированной для памяти в Microsoft SQL Server
Пошаговое описание создания таблицы, оптимизированной для памяти
Сейчас мы пошагово рассмотрим все действия, которые необходимо выполнить, для того чтобы создать оптимизированную для памяти таблицу.
Примечание! Помните, что 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;
Заметка! Блокировки в Microsoft SQL Server – что это такое и для чего нужны.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
