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

Пример базы данных In-Memory OLTP

  • Статья

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

Обзор

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

Примечание.

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

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

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

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

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

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

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

  1. Скачайте AdventureWorks2016_EXT.bak и SQLServer2016Samples.zip с 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 содержит вспомогательные таблицы и хранимые процедуры для выполнения демонстрационной рабочей нагрузки.

В частности, пример In-Memory 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. В рабочей нагрузке по обработке заказов значительного объема, когда множество заказов обрабатываются одновременно, наличие любого глобального ресурса может вызвать конфликт. Системное время является глобальным ресурсом, и мы заметили, что при выполнении рабочей нагрузки In-Memory 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.

  • Вычисляемые столбцы — вычисляемые столбцы и SalesOrderNumber опущены, так как SQL Server 2016 (13.x) не поддерживает вычисляемые столбцы TotalDue в оптимизированных для памяти таблицах. В новом представлении 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. В этом образце нет ни того ни другого.

Продажи.ДеталиЗаказа

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

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

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

/Production.Product

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

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

Продажи.Специальное предложение

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

Продажи.ПродуктСпециальногоПредложения

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

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

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

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

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

Индексы перенесенных таблиц настроены для демонстрационной рабочей нагрузки по обработке заказов на продажу. Рабочая нагрузка зависит от операций вставки и выборочных запросов в таблицах Sales.SalesOrderHeader_inmem и Sales, а также от выборочных запросов по столбцам первичных ключей в таблицах 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 индексами, что обусловлено необходимостью обеспечения высокой производительности и отсутствием необходимости в упорядоченных или диапазонных сканированиях для данной нагрузки.

  • Индекс ХЭШ для (SalesOrderID, SalesOrderDetailID): это индекс первичного ключа, и хотя уточняющие запросы для (SalesOrderID, SalesOrderDetailID) будут выполняться редко, использование хэш-индекса для ключа позволяет ускорить вставку строк. Параметр 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]

      • @SalesOrderDetails Sales.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. Приведенный далее скрипт формирует табличный параметр (TVP) с образцом данных и вызывает процедуру вставки заказа на продажу с пятью строками.

Инструмент 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 %. Если это не так, то у вас имеется узкое место в области log-IO (см. также Устранение неполадок тестов, которые выполняются медленно).

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

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

Откройте командную строку 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 и выполните следующую команду:

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

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

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

Устранение неполадок медленно работающих тестов

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

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

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

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

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

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

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

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

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

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

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

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

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

тип имя страницы_МБ
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) память_выделенная_для_индексов_КБ
SpecialOfferProduct_inmem 64 3840
ДемоновыйЗаголовокЗаказаПродавцаШаблон 1984 5504
SalesOrderDetail_inmem 15316 663552
DemoSalesOrderDetailSeed 64 10432
Специальное предложение_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%'
тип имя страницы_МБ
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'
Имя таблицы память_выделенная_для_таблицы_кб память_выделенная_для_индексов_кб
"ДеталиЗаказаПродаж_Inmem" 5113761 663552
DemoSalesOrderDetailSeed 64 10368
SpecialOffer_inmem 2 8192
ЗаголовокЗаказа_inmem 1575679 147456
Product_inmem 111 12032
SpecialOfferProduct_впамяти 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%';
тип имя 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%';
тип имя страницы_МБ
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 логическими процессорами:

описание состояния описание типа файла подсчет Размер на диске в МБ
ПРЕДСОЗДАННЫЙ ДАННЫЕ 16 2048
предварительно создан ДЕЛЬТА 16 128
В РАЗРАБОТКЕ ДАННЫЕ 1 128
В СТАДИИ СТРОИТЕЛЬСТВА ДЕЛЬТА 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;
описание состояния описание типа файла подсчет Размер на диске в МБ
заранее созданный ДАННЫЕ 16 2048
предварительно созданный ДЕЛЬТА 16 128
В РАЗРАБОТКЕ ДАННЫЕ 1 128
ИДЁТ СТРОИТЕЛЬСТВО ДЕЛЬТА 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;
Описание состояния описание типа файла count Размер на диске в МБ
предварительно созданный ДАННЫЕ 16 2048
предварительно созданный ДЕЛЬТА 16 128
АКТИВНЫЙ ДАННЫЕ 38 5152
АКТИВНЫЙ ДЕЛЬТА 38 1331
ЦЕЛЬ СЛИЯНИЯ ДАННЫЕ 7 896
ЦЕЛЬ ОБЪЕДИНЕНИЯ ДЕЛЬТА 7 56
ОБЪЕДИНЕННЫЙ ИСТОЧНИК ДАННЫЕ 13 1772
СЛИТЫЙ ИСТОЧНИК ДЕЛЬТА 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 описание типа файла количество Размер на диске в МБ
предварительно созданный ДАННЫЕ 16 2048
предварительно созданный ДЕЛЬТА 16 128
В СТАДИИ СТРОИТЕЛЬСТВА ДАННЫЕ 2 268
В РАЗРАБОТКЕ ДЕЛЬТА 2 16
АКТИВНЫЙ ДАННЫЕ 41 5608
АКТИВНЫЕ ДЕЛЬТА 41 328

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

Следующие шаги