Автор: Вячеслав
Начиная с 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, далее новые индексы создаются только через пересоздание таблицы.
Пример вполне можно создать данную таблицу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 нужно немного поменять определение
|
1 |
set @val2= N'text'+convert(nchar(8),@i) COLLATE Cyrillic_General_BIN2 |
иначе будет ошибка:
|
1 2 3 |
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
|
1 2 3 4 5 6 7 |
USE [master] GO ALTER DATABASE [INMemDB] ADD FILEGROUP [InMemory_filegroup] CONTAINSMEMORY_OPTIMIZED_DATA GO |
Добавляем новый файл группы в нашу файловую группу для таблиц In-Memory
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [master] GO ALTER DATABASE [INMemDB] ADD FILE ( NAME = N'InMemoryFile', FILENAME = N'C:\Data\InMemory2014\InMemoryFile' ) TO FILEGROUP [InMemory_filegroup] GO |
В этот момент в указанном каталоге создается каталог InMemoryFile с содержимым аналогично каталогу FileStream:
Далее создаем нашу таблицу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 создаем таблицу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 раза более уникальных значений нашего индекса. Если выбрано неоптимальное значение то, может привести к деградации производительности при обращении к данной таблице.
Далее сделаем тест на загрузку данных.
Я сделал несколько простых тестов на вставку:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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 |
Использовал таблицы, созданные выше в пример создания таблицы,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
Результаты тестирования:
| Parameters of Test | MS SQL Server 2014 | average val, sec | MS SQL Server 2016 | average val, sec |
| Table with DURABILITY = SCHEMA_AND_DATA | 585/584/584/588 | 585,25 | 626/637/610/616 | 622,25 |
| Table with DURABILITY = SCHEMA_AND_DATA with no UNICODE column, BIN | 610/604/585/606/ | 601,25 | ||
| Table with DURABILITY = SCHEMA_AND_DATA , UNICODE, не BINполе | 588/604/614/617 | 605,75 | ||
| Table with DURABILITY = SCHEMA_ONLY | 38/37/39 | 38 | 47/55/52 | 51,3 |
| Table with DURABILITY = SCHEMA_ONLY with no UNICODE column, BIN | 44/46/49 | 46,3 | ||
| Table with DURABILITY = SCHEMA_ONLY , UNICODE, не BIN поле | 53/50/52 | 51,7 | ||
| Native procedure with DURABILITY = SCHEMA_AND_DATA | 560/553/559 | 557,3 | 564/584/581 | 576,3 |
| Native procedure with DURABILITY= SCHEMA_ONLY | 28/26/30/ | 28 | 38/38/37 | 37,7 |
| Disk table | 614/605/596 | 605 | 633/637/634 | 634,67 |
По результатам тестирования:
Наиболее интересные результаты выделил желтым цветом. В целом вставка в 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 на вашем сервере:
Получение общей информации, сколько таблицы занимают в памяти:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
--получение общей информации, объем таблиц в памяти 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, есть запрос:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 снято много ограничений.
