Пример в памяти в База данных 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. Скопируйте в буфер обмена скрипт Transact-SQL для In-Memory OLTP . Скрипт T-SQL создает необходимые объекты в памяти в примере базы данных, созданной AdventureWorksLT на шаге 1.

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

    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 является то, что OLTP в памяти не поддерживается и 1 означает, что он поддерживается. OLTP в памяти доступен на уровнях База данных SQL Azure Premium (DTU) и критически важный для бизнеса (vCore).

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

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

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

Вы можете отфильтровать только оптимизированные для памяти таблицы в обозреватель объектов в SSMS. Щелкнув правой кнопкой мыши таблицы, перейдите к разделу >">Параметры фильтра" оптимизированы> для памяти. Значение равно 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) AS module_name, 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 в том же регионе Azure, где находится ваша AdventureWorksLT база данных. Вы также можете запустить ostress.exe на локальном компьютере, если вы можете подключиться к базе данных SQL Azure. Однако задержка в сети между компьютером и базой данных в Azure может снизить производительность OLTP в памяти.

На виртуальной машине или на любом выбранном узле установите служебные программы языка разметки воспроизведения (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

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

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

Для выполнения ostress.exeможно использовать окно командной строки RML. Параметры командной строки указывают программе 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. Снова сбросьте базу данных.

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

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

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

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

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

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

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

  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 vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
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 query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
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 можно ожидать около девяти раз, чем производительность этого запроса, используя кластеризованный индекс columnstore по сравнению с традиционным индексом rowstore. С помощью целевой цели службы P15 можно ожидать около 57 раз повышения производительности с помощью индекса columnstore.

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