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

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, далее новые индексы создаются только через пересоздание таблицы.

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

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

Кластерные по полю 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:

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 нужно немного поменять определение

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

Ограничение при работе с таблицами 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

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

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

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

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

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

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

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

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

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

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

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

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

структура во всех тестах была одинакова, за исключением менял параметр 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 на вашем сервере:

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

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

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

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 снято много ограничений.

Запись опубликована в рубрике Оптимизация с метками . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: