Пример базы данных для выполняющейся в памяти OLTP

  • Статья

Область применения:SQL ServerAzure SQL Database

Обзор

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

Заметка

Чтобы просмотреть этот раздел для SQL Server 2014 (12.x), ознакомьтесь с расширениями AdventureWorks для демонстрации OLTP в памяти.

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

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

Документация по этому примеру имеет следующую структуру.

Предварительные условия

Установка примера OLTP в памяти на основе AdventureWorks

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

  1. Скачивание и SQLServer2016Samples.zip отAdventureWorks2016_EXT.bak: https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks в локальную папку, напримерC:\Temp.

  2. Восстановите резервную копию базы данных с помощью Transact-SQL или SQL Server Management Studio:

    1. Задайте целевую папку и имя для файла данных, например

      'h:\DATA\AdventureWorks2022_Data.mdf'

    2. Задайте целевую папку и имя для файла журнала, например

      'i:\DATA\AdventureWorks2022_log.ldf'

      1. Файл журнала следует разместить на диске, отличном от того, на котором находится файл данных. В идеале для обеспечения максимальной производительности это должен быть высокоскоростной диск, например хранилище SSD или PCI.

    Пример скрипта T-SQL:

    RESTORE DATABASE [AdventureWorks2022]   
  FROM DISK = N'C:\temp\AdventureWorks2022.bak'   
    WITH FILE = 1,    
  MOVE N'AdventureWorks2022_Data' TO N'h:\DATA\AdventureWorks2022_Data.mdf',    
  MOVE N'AdventureWorks2022_Log' TO N'i:\DATA\AdventureWorks2022_log.ldf',  
  MOVE N'AdventureWorks2022_mod' TO N'h:\data\AdventureWorks2022_mod'  
 GO

  3. Чтобы просмотреть примеры скриптов и рабочей нагрузки, распакуйте файл SQLServer2016Samples.zip в локальную папку. Инструкции по запуску рабочей нагрузки см. в файле In-Memory OLTP\readme.txt.

Описание образцов таблиц и процедур

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

Новые оптимизированные для памяти таблицы имеют суффикс "_inmem". В примере также есть соответствующие таблицы с суффиксом "_ondisk" — эти таблицы можно использовать для сравнения "один к одному" между производительностью оптимизированных для памяти таблиц и таблиц на диске в системе.

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

Целевой рабочей нагрузкой для этого образца является обработка заказа на продажу, в рамках которой также учитывается информация о продукте и скидки. Для этого мы используем таблицы SalesOrderHeader, SalesOrderDetail, Product, SpecialOffer и SpecialOfferProduct.

Для вставки заказов на продажу и обновления сведений о доставке по данному заказу на продажу используются две новые хранимые процедуры, Sales.usp_InsertSalesOrder_inmem и Sales.usp_UpdateSalesOrderShipInfo_inmem.

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

В частности, пример выполняющейся в памяти OLTP добавляет в базу данных AdventureWorks2022 следующие объекты:

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

Новые таблицы

Sales.SalesOrderHeader_inmem

  • Данные заголовка о заказах на продажу. Для каждого заказа на продажу в этой таблице есть отдельная строка.

Sales.SalesOrderDetail_inmem

  • Подробные сведения заказов на продажу. Для каждого элемента заказа на продажу в этой таблице есть отдельная строка.

Sales.SpecialOffer_inmem

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

Sales.SpecialOfferProduct_inmem

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

Production.Product_inmem

  • Сведения о продуктах, включая их цену по прейскуранту.

Demo.DemoSalesOrderDetailSeed

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

Те же таблицы, но находящиеся на диске:

  • Sales.SalesOrderHeader_ondisk

  • Sales.SalesOrderDetail_ondisk

  • Sales.SpecialOffer_ondisk

  • Sales.SpecialOfferProduct_ondisk

  • Production.Product_ondisk

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

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

Sales.SalesOrderHeader_inmem

  • Ограничения по умолчанию поддерживаются для оптимизированных для памяти таблиц, и большинство ограничений по умолчанию переносятся как есть. Однако исходная таблица Sales.SalesOrderHeader содержит два ограничения по умолчанию, которые получают текущую дату, для столбцов OrderDate и ModifiedDate. В рабочей нагрузке по обработке заказов значительного объема, когда множество заказов обрабатываются одновременно, наличие любого глобального ресурса может вызвать конфликт. Системное время является таким глобальным ресурсом, и мы заметили, что при выполнении рабочей нагрузки выполняющейся в памяти OLTP, вставляющей заказы на продажу, это может оказаться узким местом, в частности, если требуется получить системное время для нескольких столбцов из заголовка заказа на продажу, а также для подробных сведений о заказе на продажу. Для устранения этой проблемы в этом образце для каждого вставляемого заказа на продажу получение системного времени производится только один раз, после чего это значение используется для столбцов даты и времени в таблицах SalesOrderHeader_inmem и SalesOrderDetail_inmem в хранимой процедуре Sales.usp_InsertSalesOrder_inmem.

  • Псевдонимы пользовательских типов (UDT). В исходной таблице используется два псевдонима UDT, dbo.OrderNumber и dbo.AccountNumber, для столбцов PurchaseOrderNumber и AccountNumber соответственно. SQL Server 2016 (13.x) не поддерживает псевдоним UDT для оптимизированных для памяти таблиц, поэтому новые таблицы используют системные типы данных nvarchar(25) и nvarchar(15) соответственно.

  • Столбцы, допускающие значение NULL, в ключах индексов. В столбце SalesPersonID исходной таблицы могут содержаться значения NULL, а в столбцах новых таблиц значения NULL недопустимы. Кроме того, они имеют ограничение по умолчанию со значением (-1). Связано это с тем, что индексы в оптимизированных для памяти таблицах не могут содержать в ключе индекса столбцы, допускающие значение NULL; в этом случае значение –1 является заменой значения NULL.

  • Вычисляемые столбцы — вычисляемые столбцы и TotalDue опущены, так как SQL Server 2016 (13.x) не поддерживает вычисляемые столбцы SalesOrderNumber в оптимизированных для памяти таблицах. В новом представлении Sales.vSalesOrderHeader_extended_inmem отображаются столбцы SalesOrderNumber и TotalDue. Поэтому при необходимости в этих столбцах можно использовать это представление.

    • Область применения: SQL Server 2017 (14.x) CTP 1.1.
      Начиная с SQL Server 2017 (14.x) CTP 1.1 вычисляемые столбцы поддерживаются в оптимизированных для памяти таблицах и индексах.

  • Ограничения внешнего ключа поддерживаются для таблиц, оптимизированных для памяти, в SQL Server 2016 (13.x), но только в том случае, если ссылки на них также оптимизированы для памяти. Внешние ключи, ссылающиеся на таблицы, которые также переносятся в оптимизированные для памяти таблицы, сохраняются в перенесенных таблицах, а остальные внешние ключи пропускаются. Кроме того, в примере рабочей нагрузки SalesOrderHeader_inmem является горячей таблицей, а ограничения внешнего ключа требуют дополнительной обработки для всех операций DML, поскольку при этом необходимо выполнять поиск и подстановку во всех остальных таблицах, на которые ссылаются эти ограничения. Поэтому предполагается, что приложение обеспечивает ссылочную целостность для таблицы Sales.SalesOrderHeader_inmem, которая не проверяется при вставке строк.

  • Rowguid . Столбец rowguid опускается. Хотя uniqueidentifier поддерживается для таблиц, оптимизированных для памяти, параметр ROWGUIDCOL не поддерживается в SQL Server 2016 (13.x). Столбцы этого вида обычно используются либо для репликации слиянием, либо для таблиц, в которых есть столбцы filestream. В этом образце нет ни того ни другого.

Sales.SalesOrderDetail

  • Ограничения по умолчанию. Как и в случае с SalesOrderHeader, ограничение по умолчанию, которому требуется системные дата и время, не переносится. Вместо этого хранимая процедура, вставляющая заказы на продажу, задает системные дату и время при первой вставке.

  • Вычисляемые столбцы — вычисляемый столбец LineTotal не был перенесен, так как вычисляемые столбцы не поддерживаются с оптимизированными для памяти таблицами в SQL Server 2016 (13.x). Для доступа к этому столбцу используйте представление Sales.vSalesOrderDetail_extended_inmem.

  • Rowguid. Столбец rowguid опускается. Дополнительные сведения см. в описании таблицы SalesOrderHeader.

/Production.Product

  • Псевдонимы UDT. В исходной таблице используется определяемый пользователем тип данных dbo.Flag, который эквивалентен системному типу данных bit. В перенесенной таблице вместо него используется тип данных bit.

  • Rowguid. Столбец rowguid опускается. Дополнительные сведения см. в описании таблицы SalesOrderHeader.

Sales.SpecialOffer

  • Rowguid. Столбец rowguid опускается. Дополнительные сведения см. в описании таблицы SalesOrderHeader.

Sales.SpecialOfferProduct

  • Rowguid. Столбец rowguid опускается. Дополнительные сведения см. в описании таблицы SalesOrderHeader.

Соображения в отношении индексов в оптимизированных для памяти таблицах

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

Для дальнейшей оптимизации рабочей нагрузки можно использовать индексы HASH. Они оптимизированы для уточняющих запросов и вставки строк. Однако следует учитывать, что они не поддерживают просмотр диапазонов, упорядоченный просмотр или поиск в начальных столбцах ключа индекса. Поэтому при использовании этих индексов требуется соблюдать осторожность. Кроме того, при создании необходимо указать параметр bucket_count. Обычно его значение должно быть в 1–2 раза больше числа значение ключей индекса, однако переоценка редко создает проблему.

Дополнительные сведения см. по ссылке .

Индексы в перенесенных таблицах настроены для выполнения демонстрационной рабочей нагрузки по обработке заказов на продажу. Рабочая нагрузка использует операции вставки и уточняющие запросы, которые выполняются в таблицах Sales.SalesOrderHeader_inmem и Sales.SalesOrderDetail_inmem, а также уточняющие запросы для столбцов первичного ключа в таблице Production.Product_inmem и Sales.SpecialOffer_inmem.

В таблице Sales.SalesOrderHeader_inmem есть три индекса, причем все они являются индексами HASH. Это связано с необходимостью обеспечения высокой производительности, а также с тем, что для этой рабочей нагрузки не требуются упорядоченные проверки или проверки по диапазону.

  • Индекс HASH для (SalesOrderID). Размер bucket_count составляет 10 миллионов (с округлением до 16 миллионов), поскольку ожидаемое количество заказов на продажу составляет 10 миллионов.

  • Индекс HASH для (SalesPersonID). Размер bucket_count равен 1 миллиону. Указанный набор данных не содержит много продавцов. Но большое значение bucket_count допускает будущее увеличение. Кроме того, вы не оплачиваете снижение производительности при поиске по точкам, если размер значения bucket_count слишком велик.

  • Индекс HASH для (CustomerID). Размер bucket_count равен 1 миллиону. В предоставленном наборе данных нет большого числа клиентов, однако это дает место для расширения в будущем.

В таблице Sales.SalesOrderDetail_inmem есть три индекса, причем все они являются индексами HASH. Это связано с необходимостью обеспечения высокой производительности, а также с тем, что для этой рабочей нагрузки не требуются упорядоченные проверки или проверки по диапазону.

  • Индекс HASH для (SalesOrderID, SalesOrderDetailID). Это индекс первичного ключа, и хотя уточняющие запросы для (SalesOrderID, SalesOrderDetailID) будут выполняться часто, использование индекса HASH для ключа позволяет ускорить вставку строк. Параметр bucket_count задан в размере 50 миллионов (с округлением до 67 миллионов): ожидаемое количество заказов на продажу составляет 10 миллионов, а значение параметра выбрано с тем расчетом, что каждый заказ будет содержать пять элементов

  • Индекс HASH для (SalesOrderID). Уточняющие запросы заказов на продажу выполняются часто: необходимо будет находить все линейные элементы, соответствующие одному заказу. bucket_count задан в размере 10 миллионов (с округлением до 16 миллионов), поскольку ожидаемое количество заказов на продажу составляет 10 миллионов.

  • Индекс HASH для (ProductID). Размер bucket_count равен 1 миллиону. В предоставленном наборе данных нет большого числа продуктов, однако это дает место для расширения в будущем.

Production.Product_inmem имеет три индекса.

  • Индекс HASH (ProductID). Уточняющие запросы для ProductID расположены по критическому пути для этой демонстрационной рабочей нагрузки, поэтому здесь применяется Индекс HASH.

  • Индекс NONCLUSTERED для (Name). Этот индекс позволит выполнять упорядоченные проверки названий продуктов.

  • Индекс NONCLUSTERED в (ProductNumber). Этот индекс позволяет выполнять упорядоченные проверки номеров продуктов.

Таблица Sales.SpecialOffer_inmem имеет один индекс HASH для (SpecialOfferID). Уточняющие запросы специальных предложений находятся в самом сложном месте этой демонстрационной рабочей нагрузки. Параметр bucket_count задан в размере 1 миллиона для обеспечения возможности роста в будущем.

На таблицу Sales.SpecialOfferProduct_inmem нет ссылок в демонстрационной рабочей нагрузке, поэтому нет никакой очевидной необходимости использовать хэш-индексы в этой таблице для оптимизации рабочей нагрузки — индексы для (SpecialOfferID, ProductID) и (ProductID) относятся к категории NONCLUSTERED.

Обратите внимание, что некоторые из приведенных выше чисел контейнеров имеют слишком большое значение, кроме чисел контейнеров для индексов в таблицах SalesOrderHeader_inmem и SalesOrderDetail_inmem. Их размер задан равным 10 миллионам заказов на продажу. Сделано это было для того, чтобы обеспечить возможность установки образца в системах с небольшим объемом памяти, хотя в этих случаях демонстрационная рабочая нагрузка будет завершаться ошибкой из-за нехватки памяти. Если все же требуется обрабатывать намного больше, чем 10 миллионов заказов, то можно задать соответствующие значения для числа контейнеров.

Соображения по использованию памяти

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

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

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

  • Sales.usp_InsertSalesOrder_inmem

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

    • Выходной параметр:

      • @SalesOrderID int — SalesOrderID для только что вставленного заказа на продажу.

    • Входные параметры (обязательные):

      • @DueDate datetime2

      • @CustomerID int

      • @BillToAddressID [int]

      • @ShipToAddressID [int]

      • @ShipMethodID [int]

      • @SalesOrderDetailsSales.SalesOrderDetailType_inmem — возвращающий табличное значение параметр (TVP), содержащий элементы строки заказа.

    • Входные параметры (необязательные):

      • @Status [tinyint]

      • @OnlineOrderFlag [bit]

      • @PurchaseOrderNumber [nvarchar] (25)

      • @AccountNumber [nvarchar] (15)

      • @SalesPersonID [int]

      • @TerritoryID [int]

      • @CreditCardID [int]

      • @CreditCardApprovalCode [varchar] (15)

      • @CurrencyRateID [int]

      • @Comment nvarchar(128)

  • Sales.usp_UpdateSalesOrderShipInfo_inmem

    • Обновляет сведения о доставке для данного заказа на продажу. Также обновляются сведения о доставке для всех линейных элементов заказа на продажу.

    • Это процедура-оболочка для скомпилированных в собственном коде хранимых процедур Sales.usp_UpdateSalesOrderShipInfo_native, содержащих логику повтора для обработки (непредвиденных) возможных конфликтов, возникающих при обновлении одного заказа выполняющимися одновременно транзакциями. Дополнительные сведения см. в статье Логика повторных попыток.

  • Sales.usp_UpdateSalesOrderShipInfo_native

    • Это скомпилированная в собственном коде хранимая процедура, которая фактически выполняет обновление сведений о доставке. Она вызывается из хранимой процедуры-оболочки Sales.usp_UpdateSalesOrderShipInfo_inmem. Если клиент может обрабатывать сбои и имеет логику повтора, то эту процедуру можно вызывать напрямую без использования хранимой процедуры-оболочки.

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

  • Demo.usp_DemoReset

    • Выполняет сброс демонстрации путем повторного заполнения таблиц SalesOrderHeader и SalesOrderDetail.

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

  • Production.usp_InsertProduct_inmem

  • Production.usp_DeleteProduct_inmem

  • Sales.usp_InsertSpecialOffer_inmem

  • Sales.usp_DeleteSpecialOffer_inmem

  • Sales.usp_InsertSpecialOfferProduct_inmem

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

  1. dbo.usp_ValidateIntegrity

    • Необязательный параметр: @object_id — идентификатор объекта для проверки целостности.

    • Эта процедура берет правила целостности, соответствие которым необходимо проверить, из таблиц dbo.DomainIntegrity, dbo.ReferentialIntegrity и dbo.UniqueIntegrity — образец заполняет эти таблицы с учетом проверочных ограничений, ограничений внешнего ключа и ограничений уникальности, которые имеются в исходных таблицах из базы данных AdventureWorks2022.

    • Для формирования кода T-SQL, который нужен для выполнения проверок целостности, используются вспомогательные процедуры dbo.usp_GenerateCKCheck, dbo.usp_GenerateFKCheck и dbo.GenerateUQCheck.

Измерение производительности с помощью демонстрационной рабочей нагрузки

Ostress — это средство командной строки, разработанное группой поддержки Майкрософт CSS SQL Server. С его помощью можно одновременно выполнять запросы или хранимые процедуры. Можно задать количество потоков для параллельного выполнения данной инструкции T-SQL, а также можно указать, сколько раз следует выполнить инструкцию в этом потоке. Программа ostress запустит потоки и выполнит инструкцию во всех потоках одновременно. После завершения выполнения для всех потоков программа ostress сообщит время, которое потребовалось на завершение выполнения всеми потоками.

Установка ostress

Программа Ostress устанавливается как часть пакета Report Markup Language (RML) Utilities. Ее нельзя установить отдельно.

Действия по установке

  1. Скачайте и запустите пакет установки x64 для служебных программ RML на следующей странице: скачайте RML для SQL Server.

  2. Если появится диалоговое окно, в котором будет указано, что некоторые файлы используются, нажмите кнопку "Продолжить".

Запуск ostress

Программа ostress запускается из командной строки. Удобнее всего запускать это средство из командной строки «RML Cmd Prompt», которая устанавливается как часть пакета RML Utilities.

Чтобы открыть командную строку RML Cmd Prompt, выполните следующие инструкции:

В Windows откройте меню Пуск, нажав клавишу Windows, и введите rml. Выберите "RML Cmd Prompt", который будет находиться в списке результатов поиска.

Удостоверьтесь в том, что командная строка находится в установочной папке RML Utilities.

Параметры командной строки для программы ostress отображаются при запуске ostress.exe без каких-либо параметров. Основные параметры, которые можно использовать при запуске программы ostress для этого образца:

  • -S — имя экземпляра Microsoft SQL Server для подключения.

  • -E — использование проверки подлинности Windows для подключения (по умолчанию). При использовании проверки подлинности SQL Server задайте параметры -U и -P, чтобы указать имя пользователя и пароль соответственно

  • -d имя базы данных (в этом случае AdventureWorks2022)

  • -Q выполняемая инструкция T-SQL

  • -n количество соединений, обрабатывающих каждый входной файл/запрос

  • -r количество итераций для каждого соединения, выполняющих каждый входной файл/запрос

Демонстрационная рабочая нагрузка

Главная хранимая процедура, используемая в демонстрационной рабочей нагрузке, — Sales.usp_InsertSalesOrder_inmem/ondisk. Приведенный далее скрипт формирует возвращающий табличное значение параметр с образцом данных и вызывает процедуру для вставки заказа на продажу с пятью линейными элементами.

Средство ostress служит для параллельного выполнения вызовов хранимых процедур с целью имитации одновременной вставки заказов на продажу клиентами.

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

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

DECLARE   
      @i int = 0,   
      @od Sales.SalesOrderDetailType_inmem,   
      @SalesOrderID int,   
      @DueDate datetime2 = sysdatetime(),   
      @CustomerID int = rand() * 8000,   
      @BillToAddressID int = rand() * 10000,   
      @ShipToAddressID int = rand() * 10000,   
      @ShipMethodID int = (rand() * 5) + 1;   
  
INSERT INTO @od   
SELECT OrderQty, ProductID, SpecialOfferID   
FROM Demo.DemoSalesOrderDetailSeed   
WHERE OrderID= cast((rand()*106) + 1 as int);   
  
WHILE (@i < 20)   
BEGIN;   
      EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od;   
      SET @i += 1   
END

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

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

Мы воспользуемся средством ostress для выполнения скриптов с использованием нескольких параллельных соединений. Чтобы определить количество соединений, укажем параметр -n, а параметр -r — для определения числа выполнений скрипта в каждом соединении.

Выполнение рабочей нагрузки

Чтобы выполнить масштабное тестирование, вставим 10 миллионов заказов на продажу с использованием 100 соединений. Этот тест работает относительно неплохо на не очень мощном сервере (например, с 8 физическими и 16 логическими ядрами) с базовым хранилищем SSD для журнала. Если производительность теста на вашем оборудовании неудовлетворительна, ознакомьтесь с разделом Устранение неполадок тестов, которые выполняются медленно. Если требуется снизить нагрузку для этого теста, уменьшите количество соединений, изменив параметр "-n". Например, чтобы снизить число соединений до 40, замените параметр -n100 на -n40.

В качестве меры производительности рабочей нагрузки служит затраченное время, сообщаемое программой ostress.exe после выполнения рабочей нагрузки.

В приведенных ниже инструкциях и измерениях используется рабочая нагрузка, которая вставляет 10 миллионов заказов на продажу. Инструкции по запуску более легкой рабочей нагрузки, которая вставляет 1 миллион заказов на продажу, см. в файле In-Memory OLTP\readme.txt в архиве SQLServer2016Samples.zip.

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

Начнем с выполнения рабочей нагрузки в оптимизированных для памяти таблицах. Следующая команда открывает 100 потоков, каждый из которых выполняет 5000 итераций. Каждая итерация вставляет 20 заказов на продажу в отдельных транзакциях. В каждой итерации есть 20 вставок для компенсации использования базы данных, для формирования вставляемых данных. Это дает в общей сложности 20 * 5000 * 100 = 10 000 000 заказов на продажу.

Откройте командную строку RML Cmd Prompt и выполните следующую команду:

Нажмите кнопку Копировать, чтобы скопировать команду, и вставьте ее в командную строку RML Utilities.

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"

Ее выполнение на одном тестовом сервере, общее число ядер в котором составляет 8 физических (16 логических), заняло 2 минуты 5 секунд. Ее выполнение на втором тестовом сервере, общее число ядер в котором составляет 24 физических и 48 логических, заняло 1 минуту 0 секунд.

Следите за использованием ЦП во время выполнения рабочей нагрузки (например, с помощью диспетчера задач). Вы увидите, что использование ЦП близко к 100 %. Если это не так, то имеется узкое место в области ввода-вывода журнала (см. также раздел Устранение неполадок тестов, которые выполняются медленно).

Таблицы на диске

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

Откройте командную строку RML Cmd Prompt и выполните следующую команду:

Нажмите кнопку "Копировать", чтобы скопировать команду, и вставьте ее в командную строку RML Utilities.

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_ondisk, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"

Ее выполнение на одном тестовом сервере, общее число ядер в котором составляет 8 физических и 16 логических, заняло 41 минуту 25 секунд. Ее выполнение на втором тестовом сервере, общее число ядер в котором составляет 24 физических и 48 логических, заняло 52 минуты 16 секунд.

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

Отследить использование ЦП можно во время выполнения рабочей нагрузки (например, с помощью диспетчера задач). Вы увидите, что при использовании таблиц, размещенных на диске, использование ЦП далеко от 100 %. В тестовой конфигурации с 16 логическими процессорами использование находится в районе 24 %.

Кроме того, с помощью системного монитора можно просмотреть количество ожиданий, связанных с кратковременными блокировками (счетчик производительности \SQL Server:Latches\Latch Waits/sec).

Сброс образца

Чтобы сбросить образец, откройте командную строку RML Cmd Prompt и выполните следующую команду:

ostress.exe -S. -E -dAdventureWorks2022 -Q"EXEC Demo.usp_DemoReset"

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

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

Устранение неполадок тестов, которые выполняются медленно

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

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

  • Мало ядер, доступных для SQL Server. Это означает, что в системе будет низкий уровень параллелизма, так как число параллельно исполняемых транзакций будет равно числу ядер, доступных для SQL.

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

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

    • Симптом: если при выполнении рабочей нагрузки в оптимизированных для памяти таблицах использование ЦП не приближается к 100 % или подвержено очень высоким колебаниям, то, возможно, существует узкое место, связанное с вводом-выводом журнала. Проверить это можно, открыв системный монитор и оценив длину очереди для диска журнала.

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

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

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

Общее использование базы данных

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

SELECT type  
   , name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'

Моментальный снимок сразу после создания базы данных:

тип name pages_MB
MEMORYCLERK_XTP По умолчанию. 94
MEMORYCLERK_XTP DB_ID_5 877
MEMORYCLERK_XTP По умолчанию. 0
MEMORYCLERK_XTP По умолчанию. 0

Клерки памяти по умолчанию содержат структуры памяти во всей системе и являются относительно небольшими. Клерк памяти, предназначенный для пользовательской базы данных (в данном случае это база данных с идентификатором 5, но database_id в вашем экземпляре может отличаться), составляет примерно 900 МБ.

Использование памяти на таблицу

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

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';

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

Имя таблицы memory_allocated_for_table_kb memory_allocated_for_indexes_kb
SpecialOfferProduct_inmem 64 3840
DemoSalesOrderHeaderSeed 1984 5504
SalesOrderDetail_inmem 15316 663552
DemoSalesOrderDetailSeed 64 10432
SpecialOffer_inmem 3 8192
SalesOrderHeader_inmem 7168 147456
Product_inmem 124 12352

Как видите, размер таблиц весьма невелик. SalesOrderHeader_inmem занимает примерно 7 МБ, а SalesOrderDetail_inmem — 15 МБ.

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

Использование памяти после выполнения рабочей нагрузки

После вставки 10 миллионов заказов на продажу общий объем использованной памяти будет примерно таким:

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'
тип name pages_MB
MEMORYCLERK_XTP По умолчанию. 146
MEMORYCLERK_XTP DB_ID_5 7374
MEMORYCLERK_XTP По умолчанию. 0
MEMORYCLERK_XTP По умолчанию. 0

Как можно видеть, SQL Server использует чуть меньше 8 ГБ под оптимизированные для памяти таблицы и индексы из образца базы данных.

Подробные данные об использовании памяти для каждой таблицы после одного выполнения:

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'
Имя таблицы memory_allocated_for_table_kb memory_allocated_for_indexes_kb
SalesOrderDetail_inmem 5113761 663552
DemoSalesOrderDetailSeed 64 10368
SpecialOffer_inmem 2 8192
SalesOrderHeader_inmem 1575679 147456
Product_inmem 111 12032
SpecialOfferProduct_inmem 64 3712
DemoSalesOrderHeaderSeed 1984 5504

Как видите, общий объем данных составляет примерно 6,5 ГБ. Обратите внимание, что индексы в таблицах SalesOrderHeader_inmem и SalesOrderDetail_inmem имеют тот же размер, что и индексы до вставки заказов на продажу. Размер индексов не изменился, потому что в обеих таблицах используются индексы HASH, а они являются статическими.

После сброса образца

Для сброса образца используется хранимая процедура Demo.usp_DemoReset. Она удаляет данные из таблиц SalesOrderHeader_inmem и SalesOrderDetail_inmem и вносит в них данные из исходных таблиц SalesOrderHeader и SalesOrderDetail.

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

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%';
тип name pages_MB
MEMORYCLERK_XTP По умолчанию. 2261
MEMORYCLERK_XTP DB_ID_5 7396
MEMORYCLERK_XTP По умолчанию. 0
MEMORYCLERK_XTP По умолчанию. 0

Это ожидаемо: память будет освобождаться при выполнении транзакционной рабочей нагрузки.

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

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%';
тип name pages_MB
MEMORYCLERK_XTP По умолчанию. 1863
MEMORYCLERK_XTP DB_ID_5 7390
MEMORYCLERK_XTP По умолчанию. 0
MEMORYCLERK_XTP По умолчанию. 0

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

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

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';

Начальное состояние

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

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

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';
Размер на диске в МБ
2312

Как видите, существует большая разница между размером файлов контрольных точек на диске, который составляет 2,3 ГБ, и фактическим размером данных, равным почти 30 МБ.

Чтобы лучше разобраться, откуда взялся такой показатель использования дискового пространства, можно выполнить следующий запрос. Размер на диске, возвращаемый этим запросом, является приблизительным для файлов, находящихся в состоянии 5 (REQUIRED FOR BACKUP/HA), 6 (IN TRANSITION TO TOMBSTONE) или 7 (TOMBSTONE).

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;

Для первоначального состояния образца результат будет выглядеть примерно как для сервера с 16 логическими процессорами:

state_desc file_type_desc count Размер на диске в МБ
PRECREATED ДАННЫЕ 16 2048
PRECREATED РАЗНИЦА 16 128
UNDER CONSTRUCTION ДАННЫЕ 1 128
UNDER CONSTRUCTION РАЗНИЦА 1 8

Как видите, большая часть объема используется воссозданными файлами данных и разностными файлами. SQL Server предварительно создает одну пару файлов (файл данных и разностный файл) на логический процессор. Кроме того, файлы данных имеют заранее заданный размер в 128 МБ, а разностные файлы — в 8 МБ, чтобы сделать вставку данных в эти файлы более эффективной.

Сами данные из оптимизированных для памяти таблиц находятся в одном файле данных.

После выполнения рабочей нагрузки

После единичного запуска теста, производящего вставку 10 миллионов заказов на продажу, общий размер на диске выглядит примерно так (для 16-ядерного тестового сервера):

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';
Размер на диске в МБ
8828

Дисковый размер приближается к 9 ГБ, что близко к размеру данных в памяти.

Более тщательное исследование размера файлов контрольных точек в различных состояниях.

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;
state_desc file_type_desc count Размер на диске в МБ
PRECREATED ДАННЫЕ 16 2048
PRECREATED РАЗНИЦА 16 128
UNDER CONSTRUCTION ДАННЫЕ 1 128
UNDER CONSTRUCTION РАЗНИЦА 1 8

У нас по-прежнему имеется 16 пар предварительно созданных файлов, готовых по мере закрытия контрольных точек.

Одна пара находится в разработке и используется до тех пор, пока не будет закрыта текущая контрольная точка. Вместе с активными файлами контрольных точек это дает примерно 6,5 ГБ занятого дискового пространства для 5,5 ГБ данных в памяти. Вспомним, что индексы не сохраняются на диск, поэтому в данном случае общий размер на диске меньше, чем размер в памяти.

После сброса образца

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

В этом примере после сброса образца можно наблюдать нечто такого рода:

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';
Размер на диске в МБ
11839

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

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;
state_desc file_type_desc count Размер на диске в МБ
PRECREATED ДАННЫЕ 16 2048
PRECREATED РАЗНИЦА 16 128
АКТИВНЫЙ ДАННЫЕ 38 5152
АКТИВНЫЙ РАЗНИЦА 38 1331
MERGE TARGET ДАННЫЕ 7 8 %
MERGE TARGET РАЗНИЦА 7 56
MERGED SOURCE ДАННЫЕ 13 1772
MERGED SOURCE РАЗНИЦА 13 455

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

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

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

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;
state_desc file_type_desc count Размер на диске в МБ
PRECREATED ДАННЫЕ 16 2048
PRECREATED РАЗНИЦА 16 128
UNDER CONSTRUCTION ДАННЫЕ 2 268
UNDER CONSTRUCTION РАЗНИЦА 2 16
АКТИВНЫЙ ДАННЫЕ 41 5608
АКТИВНЫЙ РАЗНИЦА 41 328

В этом случае будет две пары файлов контрольных точек в состоянии "в разработке", а это означает, что несколько пар файлов были перемещены в состояние "в разработке", вероятнее всего, из-за высокого уровня параллелизма в рабочей нагрузке. Нескольким параллельным потокам одновременно потребовалась новая пара файлов, из-за чего пара была переведена из состояния "создана заранее" в состояние "в разработке".

Далее