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

Пример в памяти в Управляемый экземпляр SQL Azure

  • Статья

Область применения: Управляемый экземпляр SQL Azure

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

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

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

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

1. Восстановление образца базы данных OLTP в памяти

Пример базы данных можно восстановить AdventureWorksLT с помощью нескольких шагов T-SQL в SQL Server Management Studio (SSMS). Дополнительные сведения о восстановлении базы данных в управляемом экземпляре SQL см. в кратком руководстве. Восстановление базы данных в Управляемый экземпляр SQL Azure с помощью SSMS.

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

  1. Откройте SSMS и подключитесь к управляемому экземпляру SQL.

    Примечание.

    Подключения к Управляемый экземпляр SQL Azure из локальной рабочей станции или виртуальной машины Azure можно безопасно выполнять без открытия общедоступного доступа. Рассмотрим краткое руководство. Настройка подключения типа "точка — сеть" для Управляемый экземпляр SQL Azure из локальной среды или краткого руководства. Настройка виртуальной машины Azure для подключения к Управляемый экземпляр SQL Azure.

  2. В обозревателе объектов щелкните правой кнопкой мыши Управляемый экземпляр и выберите Создать запрос, чтобы открыть окно нового запроса.

  3. Выполните следующую инструкцию T-SQL, которая использует общедоступный предварительно настроенный контейнер хранилища и ключ подписанного URL-адреса для создания учетных данных в управляемом экземпляре SQL. При использовании общедоступного хранилища подпись SAS не требуется.

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';

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

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

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

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete
   , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  6. По завершении процесса восстановления просмотрите AdventureWorksLT базу данных в обозреватель объектов. Вы можете убедиться, что AdventureWorksLT база данных восстановлена с помощью представления sys.dm_operation_status .

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

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

  • 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), 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, что и управляемый экземпляр SQL. Но вместо этого можно запускать 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. Восстановите новую AdventureWorksLT базу данных в управляемом экземпляре SQL, перезаписав ранее установленную базу данных.WITH REPLACE

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

  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

В зависимости от конфигурации управляемого экземпляра SQL вы можете ожидать значительного повышения производительности для этого запроса с помощью кластеризованного индекса columnstore по сравнению с традиционным индексом.

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