Поделиться через

Обзор ключевых областей в OLTP на основе памяти

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Эта статья предназначена для разработчиков, которым нужно быстро изучить основы повышения производительности In-Memory OLTP в базе данных Microsoft SQL Server и Azure SQL.

В этой статье рассматриваются следующие аспекты In-Memory OLTP:

  • Краткое описание функций.
  • Основные примеры кода, реализующие функции.

SQL Server и база данных SQL незначительно отличаются друг от друга в плане поддержки технологий выполнения в памяти.

В неофициальной среде некоторые блогеры называют OLTP в оперативной памяти Hekaton.

Преимущества функций в памяти

SQL Server позволяет использовать функции выполнения в памяти, позволяющие значительно повысить производительность систем многих приложений. В этом разделе описываются самые простые соображения.

Функции OLTP (обработка транзакций в реальном времени).

Системы, которые должны обрабатывать большое количество INSERT SQL одновременно, являются отличными кандидатами на функции OLTP.

  • Наши тесты показывают, что за счет применения функций выполнения в памяти скорость обработки можно увеличить в 5–20 раз.

Превосходными кандидатами станут также системы, выполняющие большой объем вычислений в Transact-SQL.

  • Хранимая процедура, предназначенная для больших вычислений, может выполняться до 99 раз быстрее.

Впоследствии вы можете посетить следующие статьи, в которых демонстрируется увеличение производительности в результате применения In-Memory OLTP:

Функции оперативной аналитики

Модуль аналитики в памяти ссылается на операции SQL SELECT, которые объединяют данные транзакций (обычно за счет добавления предложения GROUP BY). Тип индекса columnstore является основным для операционной аналитики.

Вот два основных сценария:

  • Впакетной операционной аналитике используются процессы статистической обработки, которые выполняются либо по окончании рабочего дня, либо на дополнительном оборудовании, где имеются копии данных транзакций.
    • К пакетной операционной аналитике относится такжеAzure Synapse Analytics.
  • В операционной аналитике в реальном времени используются процессы статистической обработки, которые выполняются в рабочее время на том оборудовании, где выполняются рабочие нагрузки транзакции.

В этой статье основное внимание уделяется OLTP, а не аналитике. Сведения о том, как индексы columnstore обеспечивают аналитику в SQL, см. в разделе:

Columnstore

Серия отличных публикаций, доступно и изящно разъясняющих принципы работы с индексами columnstore с различных точек зрения. Большинство постов более подробно описывают концепцию операционной аналитики в реальном времени, которую поддерживают столбцовые хранилища данных. Автор этих записей, опубликованных в блоге в марте 2016 г., — Сунил Агарвал (Sunil Agarwal), руководитель программы в корпорации Майкрософт.

Операционная аналитика в реальном времени

  1. Операционная аналитика в реальном времени на основе технологии в памяти
  2. Операционная аналитика в реальном времени. Обзор некластеризованного индекса columnstore (NCCI)
  3. Операционная аналитика в реальном времени. Простой пример использования некластеризованного индекса columnstore (NCCI) в SQL Server 2016
  4. Операционная аналитика в реальном времени. Операции DML и некластеризованный индекс columnstore (NCCI) в SQL Server 2016
  5. Операционная аналитика в реальном времени. Некластеризованный индекс columnstore (NCCI) с фильтрацией
  6. Оперативная аналитика в режиме реального времени: параметр задержки сжатия для некластеризованного индекса columnstore (NCCI)
  7. Операционная аналитика в реальном времени: параметр "Задержка сжатия" с использованием NCCI и его влияние на производительность
  8. Аналитика в реальном времени: оптимизированные для памяти таблицы и индекс столбцового хранения

Дефрагментация индекса columnstore

  1. Дефрагментация индекса columnstore с помощью команды REORGANIZE
  2. Политика слияния индекса columnstore для команды REORGANIZE

Массовый импорт данных

  1. Кластеризованное хранилище столбцов: Массовая загрузка
  2. Кластеризованный колонковый индекс: оптимизация загрузки данных — минимальное ведение журнала
  3. Кластеризованный индекс columnstore: оптимизация загрузки данных — параллельный массовый импорт

Особенности внутрипамятной OLTP

Рассмотрим основные особенности In-Memory OLTP.

Таблицы, оптимизированные для памяти

Для того чтобы таблица существовала в активной памяти, а не на диске, в инструкции CREATE TABLE указывается определенное ключевое слово T-SQL — MEMORY_OPTIMIZED.

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

  • Копия на диске предназначена только для восстановления после завершения работы и перезапуска сервера или базы данных. Такая двойственность памяти и диска скрыта от вас и вашего кода.

Модули, скомпилированные в собственном коде

Нативно компилируемая хранимая процедура создается с использованием ключевого слова T-SQL NATIVE_COMPILATION в инструкции CREATE PROCEDURE. При первом использовании собственной процедуры T-SQL инструкции компилируются в машинный код, что происходит каждый раз при подключении базы данных online. Инструкции T-SQL больше не выдерживают медленную интерпретацию каждой инструкции.

  • Мы видели, что нативная компиляция приводит к длительности, составляющей 1/100 от длительности интерпретируемой версии.

Модули, скомпилированные в собственном коде, могут обращаться только к таблицам, оптимизированным для памяти. К таблицам на диске они обращаться не могут.

Модули, компилируемые в собственном коде, бывают трех типов:

Доступность базы данных Azure SQL

Выполняющаяся в памяти OLTP и индекс columnstore доступны в Базе данных SQL Azure. Для получения дополнительной информации см. Оптимизация производительности с использованием технологий In-Memory в базе данных SQL.

1. Обеспечение уровня >совместимости = 130

Данный раздел начинается с ряда нумерованных разделов, которые демонстрируют синтаксис Transact-SQL, который можно использовать для реализации функций выполнения OLTP в памяти.

Во-первых, очень важно, чтобы для базы данных был задан уровень совместимости не менее 130. Код T-SQL, с помощью которого можно узнать текущий уровень совместимости, заданный для текущей базы данных.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Код T-SQL, с помощью которого можно изменить уровень при необходимости.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Повышение до моментального снимка

Если в транзакции участвует как дисковая таблица, так и таблица, оптимизированная для памяти, это называется межконтейнерная транзакция. В такой транзакции очень важно обеспечить, чтобы оптимизированная для памяти часть транзакции выполнялась на уровне изоляции транзакции, который называется "моментальный снимок".

Чтобы гарантированно обеспечить этот уровень для оптимизированных для памяти таблиц в межконтейнерной транзакции, измените параметры базы данных, выполнив следующий запрос T-SQL.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Создание оптимизированной файловой группы

В Microsoft SQL Server прежде чем создать таблицу, оптимизированную для памяти, необходимо сначала создать файловую группу, для которой сделано объявление CONTAINS MEMORY_OPTIMIZED_DATA. Файловая группа присвоена базе данных. Дополнительные сведения см. в разделе:

В базе данных SQL Azure не требуется и нет возможности создавать такую файловую группу.

Следующий пример скрипта T-SQL включает базу данных для выполняющейся в памяти OLTP и настраивает все рекомендуемые параметры. Он подходит и для SQL Server, и для Базы данных SQL Azure: enable-in-memory-oltp.sql.

Обратите внимание, что для баз данных с файловой группой MEMORY_OPTIMIZED_DATA поддерживаются не все функции SQL Server. Дополнительные сведения об ограничениях см. в разделе Неподдерживаемые функции SQL Server для выполняющейся в памяти OLTP.

4. Создание таблицы с оптимизацией для памяти

Главным ключевым словом в Transact-SQL является ключевое слово MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Инструкции Transact-SQL INSERT и SELECT для таблицы, оптимизированной для памяти, такие же, как для обычной таблицы.

Инструкция ALTER TABLE для таблиц, оптимизированных для памяти

С помощью инструкций ALTER TABLE...ADD/DROP можно добавлять и удалять столбцы из оптимизированной для памяти таблицы или индекса.

  • Инструкции CREATE INDEX и DROP INDEX не могут выполняться для таблицы, оптимизированной для памяти. Используйте вместо этого ALTER TABLE ... ADD/DROP INDEX.
  • Дополнительные сведения см. в разделе Изменение таблиц с оптимизацией для памяти.

Запланируйте таблицы и индексы, оптимизированные для памяти

5. Создание нативно скомпилированной хранимой процедуры

Критически важное ключевое слово – NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

Ключевое слово SCHEMABINDING означает, что таблицы, на которые ссылается нативная хранимая процедура, нельзя удалить, если сначала не удалить саму процедуру. Дополнительные сведения см. в разделе Создание хранимых процедур, скомпилированных в собственном коде.

Обратите внимание, что для доступа к таблице, оптимизированной для памяти, не требуется создавать скомпилированную в собственном коде хранимую процедуру. На оптимизированные для памяти таблицы также можно ссылаться из традиционных хранимых процедур и нерегламентированных пакетов.

6. Выполните собственную процедуру

Заполните таблицу из двух строк данных.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Затем выполните инструкцию EXECUTE для нативно скомпилированной хранимой процедуры.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Вот фактический результат выполнения команды PRINT:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Руководство по документации и дальнейшие действия

Описанные выше простые примеры создают основу для изучения более сложных функций выполнения OLTP в памяти. В следующих разделах описаны особенности, которые следует знать, и дополнительные сведения, касающиеся каждой из них.

Почему функции выполнения OLTP в памяти работают намного быстрее?

В следующих подразделах кратко описано, как функции In-Memory OLTP работают на внутреннем уровне, чтобы обеспечивать улучшенную производительность.

Почему оптимизированные для памяти таблицы работают быстрее?

Двойное представление. Таблица, оптимизированная для памяти, имеет два представления: одно представление в активной памяти, а другое — на жестком диске. Все транзакции фиксируются в обоих представлениях таблицы. Операции с транзакциями выполняются с гораздо более быстрым представлением в активной памяти. Оптимизированные для памяти таблицы используют преимущество более высокой скорости работы активной памяти по сравнению с диском. Кроме того, более высокое быстродействие активной памяти позволяет реализовать более сложные структуры таблицы, оптимизированные по скорости. Продвинутая структура также не имеет разбиения на страницы, поэтому позволяет избежать издержек и конфликтов при использовании кратковременных блокировок и спин-блокировок.

Без блокировок. Таблица, оптимизированная для памяти, базируется на оптимистичном подходе к решению конкурирующих задач сохранения целостности данных и обеспечения параллелизма и высокой пропускной способности. Во время транзакции таблица не устанавливает блокировок ни на какие версии измененных строк данных. Это может значительно снизить вероятность возникновения конфликтов в некоторых системах высокой нагрузки.

Версии строк. Вместо установки блокировки, оптимизированная для памяти таблица добавляет новую версию измененной строки в саму таблицу, а не в базу данных tempdb. Исходная строка сохраняется до фиксации транзакции. Во время транзакции другие процессы могут читать исходную версию строки.

  • При создании нескольких версий строки для таблицы на диске версии строк временно хранятся в базе данных tempdb.

Меньше затрат на ведения журнала. Предыдущая и последующая версии измененных строк хранятся в таблице, оптимизированной для памяти. В этих двух строках содержится значительная часть информации, которая обычно записывается в файл журнала. Это позволяет системе записывать в журнал меньше информации и делать это реже. И тем не менее целостность транзакций гарантируется.

Почему скомпилированные в собственном коде хранимые процедуры выполняются быстрее?

Преобразование обычных интерпретируемых хранимых процедур в скомпилированные в собственном коде процедуры позволяет значительно уменьшить количество инструкций, выполняемых во время выполнения.

Компромиссы функций работы в памяти

Как это часто бывает в компьютерных технологиях, прирост производительности, получаемый за счет применения функции выполнения в памяти, является компромиссом. Новые функции дают преимущества, выигрыш от которых перевешивает издержки, связанные с реализацией этих функций. Подробные сведения о компромиссах вы найдете в следующих статьях:

В остальной части этого раздела перечислены некоторые важные аспекты планирования и компромиссов.

Преимущества и недостатки таблиц с оптимизацией для памяти

Оценка памяти. Необходимо оценить объем активной памяти, который оптимизированная для памяти таблица будет занимать. Ваш компьютер должен обладать памятью достаточной емкости для размещения таблицы, оптимизированной для памяти. Дополнительные сведения см. в разделе:

Секционирование больших таблиц: Один из способов обеспечить потребность в большом объеме активной памяти — это разделить большую таблицу на части: одна часть в памяти, хранящая горячие недавние строки данных, и другая часть на диске, хранящая холодные устаревшие строки (например, заказы на продажу, которые были полностью отгружены и завершены). Разработка секционирования и его реализация выполняются вручную. См.

Плюсы и минусы нативных процедур

  • Скомпилированная в собственном коде хранимая процедура не может обращаться к дисковой таблице. Нативная процедура может обращаться только к таблицам, оптимизированным для памяти.
  • Когда нативная хранимая процедура запускается впервые после того, как сервер или база данных были возвращены в рабочий режим, процедура должна быть перекомпилирована один раз. Это вызывает задержку перед запуском нативного процесса.

Дополнительные соображения по оптимизированным для памяти таблицам

Индексы для таблиц, оптимизированные для памяти отличаются от индексов в обычных таблицах на диске. Хэш-индексы доступны только в таблицах, оптимизированных для памяти.

Необходимо убедиться, что для планируемой оптимизированной для памяти таблицы и ее индексов будет достаточно места в активной памяти. См.

Объявить таблицу, оптимизированную для памяти, позволяет параметр DURABILITY = SCHEMA_ONLY:

  • Эта инструкция указывает системе, что необходимо уничтожить все данные из таблицы, оптимизированной для памяти, при переводе базы данных в автономный режим. Сохраняется только определение таблицы.
  • Когда база данных переходит в оперативный режим, оптимизированная для памяти таблица загружается обратно в активную память (без данных).
  • Если речь идет о множестве тысяч строк, таблицы SCHEMA_ONLY могут служить альтернативой таблицам #temporary в базе данных tempdb.

Табличные переменные также можно объявлять как оптимизированные для памяти. См.

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

Ниже приведены типы скомпилированных в собственном коде модулей, доступные через Transact-SQL.

Скомпилированная функция, определяемая пользователем, выполняется быстрее, чем интерпретированная. Вот что следует учесть при работе с UDF:

  • В случае, если инструкция T-SQL SELECT использует ППФ (пользовательская определяемая функция - UDF), эта функция всегда вызывается один раз для каждой возвращаемой строки.
    • Определяемые пользователем функции никогда не выполняются как встроенные функции, и вместо этого всегда вызываются.
    • Разграничение менее значимо, чем издержки, связанные с повторяющимися вызовами, присущие всем пользовательским функциям.
    • Затраты на вызовы определяемых пользователем функций часто допустимы на практическом уровне.

См. данные тестов и объяснение производительности встроенных пользовательских функций в следующих статьях:

Руководство по оптимизированным для памяти таблицам

Обратитесь к следующим статьям, посвященным некоторым соображениям, касающимся оптимизированных для памяти таблиц:

Руководство по документации встроенных процедур

В следующей статье и ее подразделах приводятся подробные сведения о хранимых процедурах, скомпилированных в собственном коде.

Следующие статьи содержат примеры кода и демонстрируют повышение производительности за счет применения In-Memory OLTP: