Обзор начальных областей в выполняющейся в памяти 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 с различных точек зрения. Большая часть этой серии более подробно описывает концепцию операционной аналитики в реальном времени, которая поддерживается индексами 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

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

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

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

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

Функции выполняемой в памяти OLTP

Рассмотрим основные функции выполняемой в памяти OLTP.

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

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

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

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

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

Для создания хранимой процедуры, компилируемой в собственном коде, в инструкцию CREATE PROCEDURE добавляется определенное ключевое слово T-SQL — NATIVE_COMPILATION. При первом использовании процедуры, компилируемой в собственном коде с запуском цикла базы данных в сети, инструкции T-SQL компилируются в машинном коде. Инструкции 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 можно добавлять и удалять столбцы из оптимизированной для памяти таблицы или индекса.

  • Инструкции DROP INDEX и CREATE 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 в памяти работают намного быстрее?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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