Оптимизированнные для памяти таблицы в SQL Server
Пересказ статьи Monica Rathbun. Memory Optimized Tables in SQL Server
Иногда, когда я пытаюсь узнать о новой концепции, мой мозг отключается. Разговоры обо всем, что использует концепцию «In Memory», из той же серии. Важно отметить, что «In Memory» является маркетинговым термином для ряда особенностей в SQL Server, которые имеют общее поведение, но по сути не связанных между собой.
Итак, в нескольких следующих блогах я попытаюсь объяснить некоторые понятия "In Memory", связанные с SQL Server, начиная с таблиц, оптимизированных для памяти. Я уже писала о поколоночном хранении, которое существенно отличается от случая использования In Memory OLTP. Поколоночное хранение представляет собой идеальный пример концепции In Memory, который перевернул мой представления в свое время.
Оптимизированная для памяти таблица, появившаяся в SQL Server 2014, это просто таблица, которая имеет две копии - одна в оперативной памяти, а другая постоянно хранящаяся на диске и, либо содержащая данные, либо только схему, что я объясню позже. Поскольку память очищается при перезапуске SQL Server, он хранит физическую копию таблицы, которая является восстанавливаемой. Хотя имеется две копии таблицы, копия в памяти является совершенно прозрачной и спрятана от вас.
Я всегда это спрашиваю, когда смотрю на опции или возможности SQL Server. Для таблиц в памяти - это способ, которым SQL Server обрабатывает ожидания и блокировки. Согласно Microsoft, движок использует для них оптимистический подход, т.е. блокировки и ожидания не накладываются ни на какую версию обновляемых строк данных, что сильно отличается от обычных таблиц. Именно этот механизм снижает конкуренцию и обеспечивает транзакциям экспоненциальный рост скорости. Вместо блокировок технология In-Memory использует версии строк (Row Versions), сохраняя оригинальную строку до тех пор, пока транзакция не будет зафиксирована. Во многом подобно изоляции Read Committed Snapshot (RCSI), это позволяет другим транзакциям читать исходную строку при её обновлении новой версией. По структуре версия In-Memory является "бесстраничной" и оптимизирована по скорости для работы в оперативной памяти, что дает существенный прирост производительности в зависимости от рабочей нагрузки.
SQL Server изменил также журнализацию для этих таблиц. Вместо полной журнализации, эта двойственность обеих - дисковой и в памяти - версий (версий строк) таблицы позволяет меньше записывать в журнал. SQL Server может использовать версии до и после, чтобы получить информацию, которую он обычно запрашивал бы из файла журнала. В SQL Server 2019 та же концепция применяется к новому подходу ускоренного восстановления данных (ADP) в журнализации и восстановлении.
Наконец, еще одним новым преимуществом является опция DURABILITY, рассматриваемая на примере ниже. Использование SCHEMA_ONLY может стать отличным способом обойти использование временных таблиц #TEMP и более эффективно использовать обработку временных данных, особенно для больших таблиц.
Теперь можно было бы ожидать, что каждый подобную функцию во все свои таблицы. Однако, как и все опции SQL Server, это годится не для всех окружений. Есть вопросы, которые вам следует рассмотреть, прежде чем внедрять таблицы в памяти. Первое и основное - это количество памяти и её конфигурация. Вы ДОЛЖНЫ правильно конфигурировать SQL Server, а также быть готовым к росту использования памяти, что может означать предварительное добавление памяти на ваш сервер. Во-вторых, знайте, что, подобно поколоночным индексам, эти таблицы не применимы ко всему на свете. Они оптимизированы на интенсивные операции ЗАПИСИ, и не годятся для хранилищ данных, которые используются, главным образом, для чтений.
Отличительной особенностью таблицы "In-Memory" является использование ключевого слова “MEMORY-OPTIMIZED” в операторе create при создании такой таблицы. Заметим, что нельзя преобразовать (ALTER) существующую таблицу в таблицу, оптимизированную для памяти. Вам потребуется пересоздать существующую таблицу и загрузить в нее данные, чтобы получить пользу от использования этого варианта. Есть еще пара настроек, которые вам потребуется конфигурировать, чтобы это заработало. Вот они.
Сначала убедитесь, что установленный уровень совместимости базы >=130
Если это не так, измените его.
Затем вы должны изменить вашу базу данных, чтобы получить выгоду от использования In-Memory, включив следующий параметр:
Наконец, в вашу базу данных необходимо добавить файловую группу, оптимизированную для памяти:
Нижеследующий код создает файл в новой файловой группе.
Давайте теперь создадим таблицу.
Вставка и извлечение данных из этой таблицы синтаксически не отличается от любой другой обычной таблицы, однако под капотом различия существенны. Работа со структурой таблицы, кроме её создания, в основном та же самая, включая добавление или удаление столбца. Но я хочу предостеречь вас о том, что для этих таблиц вы не можете создавать и удалять индекс тем же способом (CREATE/DROP). Вы должны для этого использовать ADD/DROP Index, поверьте мне, я пробовала.
Помните, я упомянула опцию DURABILITY? Это важно. Приведенный выше пример использует её установку в значение SCHEMA_AND_DATA. Это означает, что при отключении базы данных схема и данные сохраняются на диске. Если выбрать SCHEMA_ONLY, то будет сохраняться только структура, а данные будут удалены. Это важно, т.к. может привести к потере данных.
Как вы могли заметить, таблицы In-Memory не так сложны, как хотел их представить мой мозг. Это относительно простое понятие, которое просто объединяет версионность строк и две копии таблицы. Как только вы разобьете концепцию на составные части, её становится легче понять.
Что такое оптимизированные для памяти таблицы?
Оптимизированная для памяти таблица, появившаяся в SQL Server 2014, это просто таблица, которая имеет две копии - одна в оперативной памяти, а другая постоянно хранящаяся на диске и, либо содержащая данные, либо только схему, что я объясню позже. Поскольку память очищается при перезапуске SQL Server, он хранит физическую копию таблицы, которая является восстанавливаемой. Хотя имеется две копии таблицы, копия в памяти является совершенно прозрачной и спрятана от вас.
В чем состоят дополнительные преимущества от использования таблиц в памяти?
Я всегда это спрашиваю, когда смотрю на опции или возможности SQL Server. Для таблиц в памяти - это способ, которым SQL Server обрабатывает ожидания и блокировки. Согласно Microsoft, движок использует для них оптимистический подход, т.е. блокировки и ожидания не накладываются ни на какую версию обновляемых строк данных, что сильно отличается от обычных таблиц. Именно этот механизм снижает конкуренцию и обеспечивает транзакциям экспоненциальный рост скорости. Вместо блокировок технология In-Memory использует версии строк (Row Versions), сохраняя оригинальную строку до тех пор, пока транзакция не будет зафиксирована. Во многом подобно изоляции Read Committed Snapshot (RCSI), это позволяет другим транзакциям читать исходную строку при её обновлении новой версией. По структуре версия In-Memory является "бесстраничной" и оптимизирована по скорости для работы в оперативной памяти, что дает существенный прирост производительности в зависимости от рабочей нагрузки.
SQL Server изменил также журнализацию для этих таблиц. Вместо полной журнализации, эта двойственность обеих - дисковой и в памяти - версий (версий строк) таблицы позволяет меньше записывать в журнал. SQL Server может использовать версии до и после, чтобы получить информацию, которую он обычно запрашивал бы из файла журнала. В SQL Server 2019 та же концепция применяется к новому подходу ускоренного восстановления данных (ADP) в журнализации и восстановлении.
Наконец, еще одним новым преимуществом является опция DURABILITY, рассматриваемая на примере ниже. Использование SCHEMA_ONLY может стать отличным способом обойти использование временных таблиц #TEMP и более эффективно использовать обработку временных данных, особенно для больших таблиц.
Что нужно учитывать
Теперь можно было бы ожидать, что каждый подобную функцию во все свои таблицы. Однако, как и все опции SQL Server, это годится не для всех окружений. Есть вопросы, которые вам следует рассмотреть, прежде чем внедрять таблицы в памяти. Первое и основное - это количество памяти и её конфигурация. Вы ДОЛЖНЫ правильно конфигурировать SQL Server, а также быть готовым к росту использования памяти, что может означать предварительное добавление памяти на ваш сервер. Во-вторых, знайте, что, подобно поколоночным индексам, эти таблицы не применимы ко всему на свете. Они оптимизированы на интенсивные операции ЗАПИСИ, и не годятся для хранилищ данных, которые используются, главным образом, для чтений.
Давайте попробуем создать такую таблицу
Отличительной особенностью таблицы "In-Memory" является использование ключевого слова “MEMORY-OPTIMIZED” в операторе create при создании такой таблицы. Заметим, что нельзя преобразовать (ALTER) существующую таблицу в таблицу, оптимизированную для памяти. Вам потребуется пересоздать существующую таблицу и загрузить в нее данные, чтобы получить пользу от использования этого варианта. Есть еще пара настроек, которые вам потребуется конфигурировать, чтобы это заработало. Вот они.
Сначала убедитесь, что установленный уровень совместимости базы >=130
SELECT d.compatibility_level
FROM sys.databases as d
WHERE d.name = Db_Name();Если это не так, измените его.
ALTER DATABASE AdventureWorks2016CTP3
SET COMPATIBILITY_LEVEL = 130;Затем вы должны изменить вашу базу данных, чтобы получить выгоду от использования In-Memory, включив следующий параметр:
ALTER DATABASE AdventureWorks2016CTP3
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;Наконец, в вашу базу данных необходимо добавить файловую группу, оптимизированную для памяти:
ALTER DATABASE AdventureWorks2016CTP3
ADD FILEGROUP AdventureWorks2016CTP3_mod CONTAINS MEMORY_OPTIMIZED_DATA;Нижеследующий код создает файл в новой файловой группе.
ALTER DATABASE AdventureWorks2016CTP3
ADD FILE (name='AdventureWorks2016CTP3_mod1',
filename='c:\dataAdventureWorks2016CTP3')
TO FILEGROUP AdventureWorks2016CTP3_modДавайте теперь создадим таблицу.
USE AdventureWorks2016CTP3
CREATE TABLE dbo.InMemoryExample
(
OrderID INTEGER NOT NULL IDENTITY
PRIMARY KEY NONCLUSTERED,
ItemNumber INTEGER NOT NULL,
OrderDate DATETIME NOT NULL
)
WITH
(MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA);Вставка и извлечение данных из этой таблицы синтаксически не отличается от любой другой обычной таблицы, однако под капотом различия существенны. Работа со структурой таблицы, кроме её создания, в основном та же самая, включая добавление или удаление столбца. Но я хочу предостеречь вас о том, что для этих таблиц вы не можете создавать и удалять индекс тем же способом (CREATE/DROP). Вы должны для этого использовать ADD/DROP Index, поверьте мне, я пробовала.
Помните, я упомянула опцию DURABILITY? Это важно. Приведенный выше пример использует её установку в значение SCHEMA_AND_DATA. Это означает, что при отключении базы данных схема и данные сохраняются на диске. Если выбрать SCHEMA_ONLY, то будет сохраняться только структура, а данные будут удалены. Это важно, т.к. может привести к потере данных.
Заключение
Как вы могли заметить, таблицы In-Memory не так сложны, как хотел их представить мой мозг. Это относительно простое понятие, которое просто объединяет версионность строк и две копии таблицы. Как только вы разобьете концепцию на составные части, её становится легче понять.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой