Пример в памяти в База данных SQL Azure

  • Статья

Применимо к:База данных SQL Azure

Технологии в памяти в База данных SQL Azure позволяют повысить производительность приложения и снизить затраты на базу данных. Используя технологии в памяти в База данных SQL Azure, можно повысить производительность с помощью различных рабочих нагрузок.

В этой статье вы увидите два примера, иллюстрирующие использование OLTP в памяти, а также индексы columnstore в База данных SQL Azure.

Дополнительные сведения см. в разделе:

Более упрощенное, но более визуально привлекательное демонстрация производительности для OLTP в памяти см. в следующем разделе:

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

Пример базы данных можно создать AdventureWorksLT с помощью нескольких шагов в портал Azure. Затем в этом разделе описано, как расширить AdventureWorksLT базу данных с помощью объектов OLTP в памяти и продемонстрировать преимущества производительности.

Этапы установки

  1. В портал Azure создайте базу данных уровня Premium (DTU) или базу данных критически важный для бизнеса (vCore) на сервере. Задайте для источникаAdventureWorksLT образец базы данных. Подробные инструкции см. в статье Краткое руководство. Создание отдельной базы данных в Базе данных SQL Azure.

  2. Подключение в базу данных с помощью SQL Server Management Studio (SSMS).

  3. Скопируйте скрипт OLTP Transact-SQL в буфер обмена. Скрипт T-SQL создает необходимые объекты в памяти в примере базы данных, созданной AdventureWorksLT на шаге 1.

  4. Вставьте сценарий T-SQL в SSMS, а затем выполните его. Предложение MEMORY_OPTIMIZED = ON в CREATE TABLE инструкциях имеет решающее значение. Например:

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Ошибка 40536

Если при запуске скрипта T-SQL возникает ошибка 40536, выполните следующий скрипт T-SQL, чтобы проверить, поддерживает ли база данных объекты в памяти:

SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');

Результатом 0 этого является то, что в памяти не поддерживается и 1 означает, что она поддерживается. Технологии в памяти доступны на уровнях База данных SQL Azure Premium (DTU) и критически важный для бизнеса (vCore).

Сведения о созданных элементах, оптимизированных для памяти

Таблицы— пример содержит следующие оптимизированные для памяти таблицы:

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

Вы можете отфильтровать только оптимизированные для памяти таблицы в обозреватель объектов в SSMS. Щелкнув правой кнопкой мыши таблицы, перейдите к фильтру>> Параметры> Is Memory Optimized. Значение равно 1.

Можно также отправить запрос представлений каталога:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Скомпилированная в собственном коде хранимая процедура: вы можете проверить SalesLT.usp_InsertSalesOrder_inmem запрос представления каталога:

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Запуск примера рабочей нагрузки OLTP

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

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

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

Установка служебных программ RML и ostress

В идеале вам следует запланировать запуск ostress.exe на виртуальной машине Azure. Вы создадите виртуальную машину Azure в том же регионе AdventureWorksLT базы данных Azure. Но вместо этого можно запускать ostress.exe на локальной рабочей станции, если вы можете подключиться к базе данных SQL Azure.

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

Дополнительные сведения см. в разделе:

Скрипт для ostress.exe

В этом разделе приведен скрипт T-SQL, внедренный в командную строку ostress.exe. Этот скрипт использует элементы, созданные ранее с помощью установленного скрипта T-SQL.

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

  • Выполнение большого количества одновременных подключений с помощью -n100.
  • Каждое соединение повторяется сотни раз с помощью -r500.

Тем не менее, вы можете начать с гораздо меньших значений, как -n10 и -r50 обеспечить работу всего.

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

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Чтобы создать версию представленного выше скрипта T-SQL для ostress.exe для таблиц на диске, измените оба вхождения подстроки _inmem на _ondisk. Эти замены влияют на имена таблиц и хранимых процедур.

Запуск тестовой рабочей нагрузки _inmem

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

  • параллельно выполнять 100 подключений (-n100);
  • заставлять каждое подключение запускать сценарий T-SQL 50 раз (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

Выполнить предыдущую команду ostress.exe можно так.

  1. Чтобы удалить все данные, вставленные в ходе предыдущих запусков, сбросьте содержимое базы данных, выполнив следующую команду в SSMS.

    EXECUTE Demo.usp_DemoReset;

  2. Скопируйте текст предыдущей командной строки ostress.exe в буфер обмена.

  3. Замените <placeholders> параметры -S -U -P -d правильными реальными значениями.

  4. В окне командной строки RML запустите измененную командную строку.

Результат — это длительность выполнения теста

При завершении программа ostress.exe записывает значение длительности выполнения в последней строке выходных данных в окне командной строки RML. Например, более короткий тестовый запуск длится около 1,5 минут:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Сброс базы данных, изменение значения _ondisk и повторный запуск

Получив результат выполнения _inmem, выполните следующие действия для запуска _ondisk:

  1. Выполните сброс базы данных, запустив следующую команду в SSMS. Она удалит все данные, вставленные в ходе предыдущего запуска.

    EXECUTE Demo.usp_DemoReset;

  2. Измените командную строку ostress.exe, заменив все вхождения _inmem на _ondisk.

  3. Перезапустите ostress.exe еще раз и запишите результат (длительность выполнения).

  4. Еще раз выполните сброс базы данных, чтобы корректно удалить большой объем тестовых данных.

Ожидаемые результаты сравнения

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

3. Установка примера аналитики в памяти

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

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

Подготовка тестирования аналитики с помощью columnstore

  1. Используйте портал Azure для создания новой AdventureWorksLT базы данных из примера.

    • Используйте такое же имя.
    • Выберите любой уровень служб категории «Премиум».

  2. Скопируйте sql_inmemory_analytics_sample в буфер обмена.

    • Скрипт T-SQL создает необходимые объекты в памяти в примере базы данных, созданной AdventureWorksLT на шаге 1.
    • Скрипт создает таблицы измерений и две таблицы фактов. Таблицы фактов заполняются 3,5 млн строк.
    • Выполнение скрипта может занять до 15 минут.

  3. Вставьте сценарий T-SQL в SSMS, а затем выполните его. Ключевое слово COLUMNSTORE в инструкции CREATE INDEX имеет решающее значение:CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. Установите AdventureWorksLT для последнего уровня совместимости SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Ключевые таблицы и индексы columnstore

  • dbo.FactResellerSalesXL_CCI — это таблица с кластеризованным индексом columnstore, которая имеет расширенное сжатие на уровне данных .

  • dbo.FactResellerSalesXL_PageCompressed — это таблица, которая имеет эквивалентный обычный кластеризованный индекс, который сжимается только на уровне страницы .

4. Ключевые запросы для сравнения индекса columnstore

Существует несколько типов запросов T-SQL, которые можно выполнить для анализа повышения производительности. На втором шаге скрипта T-SQL обратите внимание на следующую пару запросов. Они отличаются только одной строкой.

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

Кластеризованный индекс columnstore находится в FactResellerSalesXL_CCI таблице.

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

/*********************************************************************
Step 2 -- Overview
-- Page Compressed BTree table v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the Fact Table with dimension tables
-- Note this query will run on the Page Compressed table, Note down the time
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,count(SalesOrderNumber) AS NumSales
    ,sum(SalesAmount) AS TotalSalesAmt
    ,Avg(SalesAmount) AS AvgSalesAmt
    ,count(DISTINCT SalesOrderNumber) AS NumOrders
    ,count(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

При использовании кластеризованного индекса рост производительности для этого запроса в базе данных ценовой категории P2 может увеличиться примерно в 9 раз по сравнению с традиционным индексом. При использовании кластеризованного индекса в базе данных ценовой категории P15 можно ожидать рост производительности примерно в 57 раз.

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