Microsoft SQL Server. Таблицы в памяти (in-memory tables)

Автор: Вячеслав

Начиная с MS SQL Server 2014 Microsoft предоставила к использованию технологию таблиц In-Memory, в 2016 данная технология получила продолжения и улучшения. Технология подразумевает, что определяется таблица, которая оптимизирована для нахождения в памяти сервера, что позволяет повысить производительность обработки данных в данной таблице, за счет быстроты работы данных в памяти и исключения задержек, связанные с вводом\выводом (хотя здесь есть свои нюансы). Постараюсь описать все нюансы и возможности в одной статье, чтобы не искать по разным страницам msdn, немного много, но зато все в одном.

Итак, требования

Чтобы вы могли в MS SQL Server использовать In-Memory таблицы, то должны проверить следующие требования:

—  64 – разрядный MS SQL Server 2014и выше редакции Enterprise, Developer или Evaluation

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

— Необходимо включить быструю инициализацию файлов, т.е предоставить учетной записи MSSQL Server право на «Perform volume maintenance tasks» в локальных политиках сервера. Это требования желательное, в противном случае может сыграть отрицательно на производительность.

Немного теории

Основным хранилищем для таблиц In-Memory является основная память, т.е вся память находится в памяти. Строки записываются и считываются только из памяти. Для отказоустойчивости данный таблиц дублируются на диск, но можно настроить, чтобы таблица была только в памяти, это не создает дополнительной нагрузки на диск, но и все данные в таблицах хранятся до перезагрузки сервера. Все операции с таблицами транзакционны и соответствуют классификации ACID(atomic, consistent, isolated, durable) . Транзакционность выполняется за счет версионность строк, т.е. каждая изменённая строка создает новую версию строки, к которой будет дальнейшее обращение. Это позволяет практически сократить блокировки в таблицах.

Одновременно с появлением In-Memory таблиц, появился новый тип индексов –HASH индексы. Создание HASH-индекса осуществляется с помощью внутренней hash функции, которая является единственной для всего ms sql server и является детерминированной, из этого следует, что несколько значений ключей индекса могут быть связаны с одним сопоставление хеш индекса, появляется конфликт хеша. Большое число конфликтов может отрицательно связаться на операции чтения. Использование hash индексов нужно быть аккуратным, они используются только когда в предикате условия указаны все поля hash индекса. К примеру: создали hash индекс на Имя, Фамилию, а в запросе используется только Фамилия, то наш Hash индекс работать не будет, нужно указать в Запросе и имя и Фамилию. Так же в HASH индексах запрещен поиск по диапазону.

На таблицах In-Memory могут быть определены индексы как кластерные, не кластерные, так и новые HASH индексы одновременно, возможно несколько HASH индексов. Единственное замечание: все индексы создаются при создании таблицы инструкцией CREATE TABLE, далее новые индексы создаются только через пересоздание таблицы.

Пример вполне можно создать данную таблицу:

CREATE TABLE [dbo].[TblInMem_Index]

(

       [id] [int] NOT NULL,

       [val1] [nchar](36) COLLATE Cyrillic_General_BIN2 not NULL,

       [val2] [nchar](36) COLLATE Cyrillic_General_BIN2 NOT NULL,

INDEX [Hass_ind] NONCLUSTERED HASH

(

       [val2]

)WITH ( BUCKET_COUNT = 1048576),

INDEX [idx2] NONCLUSTERED

(

       [val1] ASC,

       [val2] ASC

),

 PRIMARY KEY NONCLUSTERED

(

       [id] ASC

)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

В которой мы определили три индекса:

Кластерные по полю Id

Не кластерный [idx2]

Hash индекс [Hass_ind].

Так же существуют два вида таблиц: таблицы оптимизированные с параметром DURABILITY=SCHEMA_AND_DATA – это таблицы, которые размещены в памяти, но и существуют на диске, второй тип таблиц это таблицы с параметром DURABILITY = SCHEMA_ONLY, это значит , что данные находятся в памяти и доступны только до перезагрузки сервера, так же эти данные не будут доступны и при создание резервной копии, с параметром DURABILITY =SCHEMA_AND_DATA данные в таблице In-Memory будут доступны после восстановления из резервной копии.

Параметр WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) определяется для всей таблицы: вне зависимо есть ли в ней новые HASH индексы или кластерные, при значении параметра DURABILITY= SCHEMA_ONLY- данные хранятся до перезагрузки ms sql server.

Обращение к таблицам In-Memory происходит с помощью стандартных инструкций T-SQL с явным указанием уровня изоляции SNAPSHOT, REPETABLEREAD или SERIALIZABLE или помощью так называемых процедур скомпилированные в собственном коде (Native Compiled Stored Procedures). В обращение к таблицам In-Memory есть много ограничений, следует это учитывать.

Процедуры, скомпилированные в собственном коде (Native Compiled Stored Procedures) –это наиболее быстрый доступ к таблицам In-Memory, но и имеющий много особенностей. На «физическом» уровне после создания Native Compiled процедуры мы имеем dll библиотеку, которая компилируется один раз при создании или при рестарте сервера.

Особенности Native Compiled процедур:

— код процедуры определяется разово, далее ее можно изменить только через пересоздание

— внутри процедуры транзакция определяется как BEGIN ATOMIC , что определяет свои требования

— объекты, на которые ссылается процедура, не могут быть изменены при наличие данных процедур

— нельзя просмотреть актуальный план данных процедур

— нельзя получить статистику выполнения данных процедур

— для соединения таблиц внутри хранимой процедуры используется только NETED LOOP

— не используется параллелизм

— план выполнения Native Compiled процедуры определяется в момент ее создания, в MS SQL Server2016 для перекомпиляции процедуры можно использовать процедуру sp_recompile

Пример создания Native Compiled процедурs:

create procedure [dbo].[InsertIntoMemoryTable](@i int)

with native_compilation,schemabinding,execute as owner

as

begin atomic with (transaction isolation level = snapshot,language = N'English' )



       declare @id int =convert(int,RAND()*1000000000)

       declare @val1 nchar(36)

       set @val1=convert(nvarchar(36),newid())COLLATE Cyrillic_General_BIN2 

       declare  @val2 nchar(36)

       set @val2= 'text'+convert(nchar(8),@i) COLLATE Cyrillic_General_BIN2



       insert into [dbo].[TblInMem1]

             values (@id,@val1,@val2)

end

with native_compilation,schemabinding,execute as owner- При определение данной процедуры обязательно

begin atomic with (transaction isolation level = snapshot,language = N’English’ ) – так же обязательные параметры, требования ATOMIC

После создания данной процедуры в каталоге баз данных будет создана папка xtp далее папка номер базы данных, внутри которой будут файлы нашей процедуры:

xtp_t_9_1973582069_183184666479020.xml

xtp_t_9_2037582297_183184668414697.c

xtp_t_9_2037582297_183184668414697.dll- сама dll библиотека

xtp_t_9_2037582297_183184668414697.obj

xtp_t_9_2037582297_183184668414697.out

xtp_t_9_2037582297_183184668414697.pdb

Содержимое которых вы можете посмотреть, оно связано с определением процедуры на коде C. Файлы вы можете изменить\удалить, но ms sql server придется их заново создать, что потребует время при вызове процедуры.

В имени файла выше 9 это номер базы данных, 2037582297 – номер объекта из sysobjects.

Кстати, выше процедура будет работать только в MS SQL Server 2016, т.к в MS SQL  Server 2014 текстовые поля все должны быть в UNICOD формате. В MS SQL Server 2014 нужно немного поменять определение

set @val2= N'text'+convert(nchar(8),@i) COLLATE Cyrillic_General_BIN2

иначе будет ошибка:

Msg 12329, Level 16, State 103, Procedure InsertIntoMemoryTable1, Line 21

The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with natively compiled stored procedures.

Ограничение при работе с таблицами In-Memory:

Ниже описаны наиболее явные ограничения в MS SQL Server на таблицы In-Memory, которые чаще всего мы привыкли использовать при обычных disk таблицах. Приведена только часть ограничений, полные ограничения можно изучить в msdn.

Общие ограничения для MS SQL 2014 и MS SQL 2016:

Для баз данных с таблицами In-Memory запрещены свойство AUTO_CLOSE

Запрещена операция CREATE DATABASE  с параметром ATTACHE_REBUILD_LOG

Запрещена операция создания DATABASE SNAPSHOT

Операции проверки целостности DBCC CHECKDB, CHECKTABLE пропускают таблицы In-Memory

Не поддерживаются межбазовые запросы и транзакции, а также обращения со связанными серверами

Не поддерживаются вычисляемые столбцы в таблицах In-Memory

Не поддерживается репликация для таблиц In-Memory

Не поддерживаются столбцы SPARSE

Не поддерживаются операции TRUNCATE

Не поддерживается сжатие, секционирование таблиц

Не поддерживается репликация, зеркалирование

В Native Compiled процедурах Функции MIN и MAX не поддерживаются для типов nvarchar, char, varchar, varchar, varbinary и binary

В Native Compiled процедурах DISTINCT не поддерживается в предложении ORDER BY

В Native Compiled процедурах не поддерживаются WITH TIES и PERCENT в предложении TOP

В Native Compiled процедурах не поддерживается многостроковая вставка через INSERT.

В Native Compiled процедурах не поддерживается SELECT INTO

В Native Compiled процедурах не поддерживается инструкция CASE

Таблицы In-Memory с SCHEMA_ONLY в базах данных в группе доступности AlwaysOn будут пустыми.

Не поддерживаются типы данных: datetimeoffset, geography, geometry, hierarchyid, rowversion,xml, sql_variant, определяемые пользователем типы

Операция MERGE только в качестве назначения

Доступ из модулей CLR запрещен к таблицам In-Memory

Табличные подсказки

Фильтруемые индексы не поддерживаются

Не поддерживаются курсоры в Native Compiled процедурах

Ограничения MS SQL 2014

все ограничения выше +

Использование только UNICOD типов данных

Использование Collation _Bin для символьных полей индексов

Ограничение общего объем всех таблиц в памяти не должен превышать 250 Гб

Не авто обновляется статистика для таблиц In-Memory, необходимо вручную обновлять

Не поддерживаются LOB объекты

Пример создания таблиц

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

USE [master]

GO

ALTER DATABASE [INMemDB] ADD FILEGROUP [InMemory_filegroup] CONTAINSMEMORY_OPTIMIZED_DATA

GO

Добавляем новый файл группы в нашу файловую группу для таблиц In-Memory

USE [master]

GO

ALTER DATABASE [INMemDB] ADD FILE

( NAME = N'InMemoryFile', FILENAME = N'C:\Data\InMemory2014\InMemoryFile' )

TO FILEGROUP [InMemory_filegroup]

GO

В этот момент в указанном каталоге создается каталог InMemoryFile с содержимым аналогично каталогу FileStream:

Далее создаем нашу таблицу:

CREATE TABLE [dbo].TblInMem

(

       [id] [int] NOT NULL,

       [val1] [char](20) NULL,

       [val2] [char](20) NOT NULL,

        PRIMARY KEY NONCLUSTERED HASH

(

       [id],

       [val2]

)WITH (BUCKET_COUNT=1000000)

)

WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

Создали, ОК, далее. СТОП далее, нужно уточнить, что выше создалась таблица в MS SQL Server2016, в 2014 она не создается, т.к в 2014 в таблицах In-Memory возможно использовать только UNICODE типы данных

В 2014 создаем таблицу:

CREATE TABLE [dbo].[TblInMem1]

(

       [id] [int] NOT NULL,

       [val1] [nchar](36) COLLATE Cyrillic_General_CI_AS NULL,

       [val2] [nchar](36)COLLATE Cyrillic_General_BIN2  NOT NULL,

 PRIMARY KEY NONCLUSTERED HASH

(

       [id],[val2]

)WITH ( BUCKET_COUNT = 1048576)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Таблица определена с одним hash индексом.

Немного об синтаксисе создания таблицы:

PRIMARY KEY NONCLUSTERED HASH  – создается не кластерный HASH индекс, HASH индекс поддерживается только для In-Memory таблиц, без него мы не сможем создать нашу таблицу в памяти, обязательный параметр.

WITH (BUCKET_COUNT=1000000) – так же обязательный параметр при создание HASH индексов, указывается так называемые количество контейнеров для hash индексов, которое желательно должно быть в 2 раза более уникальных значений нашего индекса. Если выбрано неоптимальное значение то, может привести к деградации производительности при обращении к данной таблице.

Далее сделаем тест на загрузку данных.

Я сделал несколько простых тестов на вставку:

set nocount on

go

declare @start datetime2(7)=SYSDATETIME()

declare @stop datetime2(7)

select @start

declare @i int=0

while @i<1000000

begin

 begin try

       insert into [dbo].[TblInMem]

       values(convert(int,RAND()*1000000000),convert(varchar(36),newid()),'text'+convert(nchar(8),@i))

       set @i=@i+1

 end try

 begin catch

   print @i

 end catch

end

set @stop = SYSDATETIME()

select @stop

select DATEDIFF (ss,@start,@stop)

go

set nocount off

go

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

CREATE TABLE [dbo].[TblInMem1]

(

       [id] [int] NOT NULL,

       [val1] [nchar](36) COLLATE Cyrillic_General_CI_AS NULL,

       [val2] [nchar](36)COLLATE Cyrillic_General_BIN2  NOT NULL,

 PRIMARY KEY NONCLUSTERED HASH

(

       [id],[val2]

)WITH ( BUCKET_COUNT = 1048576)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

структура во всех тестах была одинакова, за исключением менял параметр DURABILITY, а так же изменял поля в MS SQL Server2016.

Результаты тестирования:

По результатам тестирования:

Наиболее интересные результаты выделил желтым цветом. В целом вставка в In-Memory таблиц смотрится хорошо, можно заметить, что в MS SQL Server 2014 она даже быстрее чем в 2016, видно из-за того, что в 2016 было снято множество ограничений, что немного повлияло на скорость. По таблице заметен выигрыш Native Compiled процедур.

Тесты «Table with DURABILITY = SCHEMA_AND_DATA, UNICODE, не BIN поле» -это тестирование в MS SQL Server 2016 с полями таблицы не UNICODE и не BIN collation видно, что они не сильно влияют на скорость, но заметно что не BIN и не UNICODE полей и при DURABILITY = SCHEMA_AND_DATA данные чуть ниже, возможно из-за меньших типов данных при хранении.

По результатам Table with DURABILITY = SCHEMA_AND_DATA и Disk table результаты не сильно отличаются в пользу In-memory таблиц. У меня disk table таблицы и файлы файловой группы In-Memory расположены на одних дисках, так что все упирается в них.  На практике, для данных таблиц In-Memory желательно выделять отдельный диск, а лучше SSD диск, тогда производительность таблиц In-Memory будет заметна. К примеру, у вас есть база данных 1 тб, вы покупаете отдельный диски 120 Гб , строите Raid массив, и располагаете на них вашу файловую группу In-Memory, то в данном случае мы получим довольно хороший выигрыш.

Тесты запускались больше количество раз, чем указано выше в таблице, все результаты были в этих границах.

Наблюдение таблиц In-Memory

Ниже несколько запрос по получению информации по таблицам In-Memory на вашем сервере:

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

--получение общей информации, объем таблиц в памяти

SELECT type 

, name 

, pages_kb/1024 AS pages_MB  

FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%' 

В общем, объекты, связанные с таблицами In-Memory, выделяются префиксов xtp.

По таблицам

---распределение в памяти по таблицах

SELECT object_name(t.object_id) AS [Table Name] 

     , memory_allocated_for_table_kb 

 , memory_allocated_for_indexes_kb 

FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t  

ON dms.object_id=t.object_id 

WHERE t.type='U' 

Размер файлов таблиц In-Memory на диске:

--размер файлов на диске, размер папки InMemoryFile на диске

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB] 

FROM sys.filegroups f JOIN sys.database_files df  

   ON f.data_space_id=df.data_space_id 

WHERE f.type=N'FX' 

По типам файлов

SELECT state_desc 

 , file_type_desc 

 , COUNT(*) AS [count] 

 , SUM(CASE 

   WHEN state = 5 AND file_type=0 THEN 128*1024*1024 

   WHEN state = 5 AND file_type=1 THEN 8*1024*1024 

   WHEN state IN (6,7) THEN 68*1024*1024 

   ELSE file_size_in_bytes 

    END) / 1024 / 1024 AS [on-disk size MB]  

FROM sys.dm_db_xtp_checkpoint_files 

GROUP BY state, state_desc, file_type, file_type_desc 

ORDER BY state, file_type

Для диагностики таблиц In-Memory в плане достаточности параметра BUCKET_COUNT, есть запрос:

SELECT object_name(hs.object_id) AS 'object name',

       i.name as 'index name',

       hs.total_bucket_count,

       hs.empty_bucket_count,

       floor((cast(empty_bucket_count as float)/total_bucket_count) * 100)AS'empty_bucket_percent',

       hs.avg_chain_length,

       hs.max_chain_length

FROM sys.dm_db_xtp_hash_index_stats AS hs

JOIN sys.indexes AS i ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Необходимо обратить на значения:

empty_bucket_count – указывает число пустых контейнеров в  hash индексе.

Меньше 10, то число значение BUCKET_COUNT слишком малое, идеально значение более 33 и более.

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

Заключение

Технология In-Memory довольно полезное дополнение, которое позволяет получить огромную производительность в базах данных на MS SQL Server. Но, как и везде, для этого нужно правильно организовать и построить вашу базу данных, просто взять и включить опцию In-Memory на ваших таблицах недостаточно, в некоторых случаях мы можем вообще получить деградацию.

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