Пример базы данных для OLTP в памяти

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

Обзор

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

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

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

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

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

• Инструкции по установке примера OLTP In-Memory на основе AdventureWorks.

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

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

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

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

• SQL Server 2016 (13.x)

• Для тестирования производительности требуется сервер, имеющий те же характеристики, что и в рабочей среде. Для этого конкретного примера должно быть доступно по меньшей мере 16 ГБ памяти в SQL Server. Общие рекомендации по оборудованию для OLTP в памяти см. в следующей записи блога: рекомендации по оборудованию для In-Memory OLTP в SQL Server

Установка примера 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. В рабочей нагрузке по обработке заказов значительного объема, когда множество заказов обрабатываются одновременно, наличие любого глобального ресурса может вызвать конфликт. Системное время является таким глобальным ресурсом, и мы заметили, что оно может стать узким местом при выполнении рабочей нагрузки OLTP In-Memory, которая вставляет заказы на продажу, в частности, если системное время необходимо получить для нескольких столбцов в заголовке заказа продаж, а также сведения о заказе на продажу. Для решения этой проблемы в данном примере системное время извлекается только один раз для каждого вставляемого заказа на продажу и затем используется для столбцов даты и времени в таблицах 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). Начиная с SQL Server 2017 (14.x), вычисляемые столбцы поддерживаются в оптимизированных для памяти таблицах и индексах.

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

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

• Рекомендации по операциям с индексами в сети
• Выбор правильного значения bucket_count
• Индексы для оптимизированных для памяти таблиц

Индексы таблиц, перенесенных, были настроены для рабочей нагрузки при обработке заказов на продажу в демонстрационном режиме. Рабочая нагрузка зависит от операций вставки и выборочных запросов в таблицах Sales.SalesOrderHeader_inmem и Sales, а также от выборочных запросов по столбцам первичных ключей в таблицах SalesOrderDetail_inmem и Production.Product_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 index на (SalesOrderID, SalesOrderDetailID): это первичный ключевой индекс, и даже если поисковые операции по (SalesOrderID, SalesOrderDetailID) выполняются редко, использование хэш-индекса для ключа ускоряет вставку строк. Параметр bucket_count задан в размере 50 миллионов (с округлением до 67 миллионов): ожидаемое количество заказов на продажу составляет 10 миллионов, а значение параметра выбрано с тем расчетом, что каждый заказ будет содержать пять элементов

• HASH index on (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) некластеризованы.

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

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

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

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

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

• Sales.usp_InsertSalesOrder_inmem

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

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

    • @SalesOrderID int - SalesOrderID для только что добавленного заказа

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

    • @DueDatedatetime2
    • @CustomerIDint
    • int @BillToAddressID
    • @ShipToAddressIDint
    • @ShipMethodIDint
    • @SalesOrderDetailsSales.SalesOrderDetailType_inmem — табличный параметр (TVP), содержащий элементы заказа.

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

    • @Statustinyint
    • @OnlineOrderFlagбит
    • @PurchaseOrderNumbernvarchar(25)
    • @AccountNumbernvarchar(15)
    • @SalesPersonIDint
    • @TerritoryIDint
    • @CreditCardIDint
    • @CreditCardApprovalCodevarchar(15)
    • @CurrencyRateID int
    • @Commentnvarchar(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 — это средство командной строки, разработанное группой поддержки Microsoft CSS SQL Server. С его помощью можно одновременно выполнять запросы или хранимые процедуры. Можно настроить количество потоков для параллельного выполнения данной инструкции T-SQL, а также указать, сколько раз следует выполнять инструкцию в этом потоке; ostress запускает потоки и выполняет инструкцию во всех потоках параллельно. После завершения выполнения для всех потоков ostress сообщает время завершения выполнения всех потоков.

Установите ostress

ostress устанавливается в составе служебных программ языка разметки отчетов (RML); Для ostress не существует автономной установки.

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

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

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

Запуск ostress

Программа ostress запускается из командной строки. Это удобнее всего запустить средство из командной строки RML, которая устанавливается в составе служебных программ RML.

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

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

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

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

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

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

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

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

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

DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS 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
        EXECUTE 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 и выполните следующую команду:

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

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = SYSDATETIME(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS 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 EXECUTE 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 AS INT = 0, @od AS Sales.SalesOrderDetailType_ondisk, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS 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 EXECUTE 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 не может полностью использовать ЦП. Причина состоит в конфликтах по кратковременным блокировкам: выполняющиеся одновременно транзакции пытаются производить запись на одну и ту же страницу данных. Кратковременные блокировки используются для обеспечения того, что только одна транзакция будет осуществлять запись на страницу в определенный момент времени. Движок In-Memory OLTP не использует фиксаторы (latch) и строки данных не организованы в страницах. Таким образом, параллельные транзакции не блокируют вставки друг друга, что позволяет SQL Server полностью использовать ЦП.

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

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

Сбросить демонстрацию

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

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

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

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

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

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

• Количество параллельных транзакций: при выполнении рабочей нагрузки в одном потоке производительность с In-Memory OLTP, скорее всего, меньше 2X. Конкуренция на уровне блокировки является значительной проблемой только в случае высокого уровня параллелизма.

• Низкое количество ядер, доступных для SQL Server: это означает, что в системе существует низкий уровень параллелизма, так как в SQL может быть только столько одновременных выполнения транзакций, сколько ядер доступно для 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%';

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

Регистраторы памяти по умолчанию содержат системные структуры памяти и достаточно компактны. Память, выделенная для пользовательской базы данных, в данном случае база данных с идентификатором 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 AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

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

Как видно, таблицы довольно малы: SalesOrderHeader_inmem около 7 МБ и SalesOrderDetail_inmem составляет около 15 МБ.

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

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

После вставки 10 миллионов заказов на продажу все ресурсы памяти выглядят примерно так, как показано в следующем запросе:

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

Вот результат.

Как можно видеть, 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 AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

Вот результат.

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

После сброса демонстрации

Хранимая процедура 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%';

Вот результат.

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

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

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

Вот результат.

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

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

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS 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 AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Вот результат.

Как видно, существует большое несоответствие между размером файлов контрольных точек на диске, что составляет 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 логическими процессорами:

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

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

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

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

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Вот результат.

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

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

После сброса демонстрации

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

В этом примере после сброса демонстрации вы можете увидеть что-то вроде следующего:

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Вот результат.

При размере почти 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;

Вот результат.

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

После второго запуска демонстрационной рабочей нагрузки, после вставки 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;

Вот результат.

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

Связанный контент

• In-Memory обзор и сценарии использования OLTP
• Файловая группа, оптимизированная для памяти
• Скрипт, позволяющий включить выполняющуюся в памяти OLTP и задать рекомендуемые параметры