Azure SQL Managed Instance のメモリ内サンプル

[アーティクル]
03/18/2024

Azure SQL Managed Instanceのインメモリテクノロジにより、アプリケーションのパフォーマンスを向上させることができ、また、データベースのコストを削減できる可能性があります。 Azure SQL Managed Instanceのインメモリテクノロジを使用すれば、さまざまなワークロードでパフォーマンスの向上を実現できます。

この記事では、インメモリ OLTP と列ストアインデックスを Azure SQL Managed Instanceで使用する方法を示す 2 つのサンプルを確認します。

詳細については、以下を参照してください:

インメモリ OLTP の概要と使用シナリオ (開始するためのお客様の導入事例と情報への参照)
インメモリ OLTP のドキュメント
列ストアインデックスの説明
リアルタイム運用分析とも呼ばれる、ハイブリッドトランザクション/分析処理 (HTAP)

より簡潔でありながら見栄えの良いインメモリ OLTP のパフォーマンスデモについては、次を参照してください。

リリース: in-memory-oltp-demo-v1.0
ソースコード: in-memory-oltp-demo-source-code

1. インメモリ OLTP サンプルデータベースを復元します

SQL Server Management Studio (SSMS)では、いくつかの T-SQL 手順を使用して AdventureWorksLT サンプルデータベースを復元できます。 SQL マネージドインスタンスへのデータベースの復元の詳細については、「クイックスタート: SSMS を使用して Azure SQL Managed Instance にデータベースを復元する」を参照してください。

このセクションの手順では、インメモリ OLTP オブジェクトを使用して AdventureWorksLT データベースを強化する方法について説明し、パフォーマンス上のメリットを示します。

SSMS を開き、SQL Managed Instance に接続します。

Note

オンプレミスのワークステーションまたは Azure VM から Azure SQL Managed Instance への接続は、パブリックアクセスを開かずに安全に行うことができます。「オンプレミスからAzure SQL Managed Instanceへのポイントツーサイト接続を設定するクイックスタート」または「Azure VMを設定してAzure SQL Managed Instanceに接続するクイックスタート」をご覧になることを検討してみてください。
オブジェクトエクスプローラーで、マネージドインスタンスを右クリックし、 [新しいクエリ] を選択して新しいクエリウィンドウを開きます。
次の T-SQL ステートメントを実行します。このステートメントは、一般公開されており事前構成済みのストレージコンテナーおよび Shared Access Signature キーを使用して、マネージドインスタンスに資格情報を作成します。パブリックに使用可能なストレージでは、SAS 署名は必要ありません。
```
CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';
```

次のステートメントを実行して、サンプルの AdventureWorksLT データベースを復元します。

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

次のステートメントを実行して、復元の状態を追跡します。

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');

復元が完了したら、オブジェクトエクスプローラー.で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 ワークロードを実行します

次の 2 つの ストアドプロシージャ の違いは、1 つ目のプロシージャはメモリ最適化バージョンのテーブルを使用し、2 つ目のプロシージャは通常のディスク上のテーブルを使用している点です。

SalesLT.usp_InsertSalesOrder_inmem
SalesLT.usp_InsertSalesOrder_ondisk

このセクションでは、便利な ostress.exe ユーティリティを使用して、負荷が高い状態で 2 つのストアドプロシージャを実行する方法について説明します。 2 つのストレス実行が完了するまでの時間を比較することができます。

RML ユーティリティと ostress をインストールする

Azure 仮想マシン (VM) で ostress.exe を実行する計画を立てるのが理想的です。あなたは、SQLマネージドインスタンスと同じAzureリージョンにAzure VMを作成することになります。ただし、Azure SQL マネージドインスタンスに接続できる限り、代わりにローカルワークステーションでostress.exeを実行できます。

VM または選択した任意のホストに、Replay Markup Language (RML) ユーティリティをインストールします。このユーティリティに ostress.exe が含まれています。

詳細については、次を参照してください。

「インメモリ OLTP のサンプルデータベース」にある ostress.exe の説明。
インメモリ OLTP のサンプルデータベース。
ostress.exe のインストールに関するブログ。

ostress.exe のスクリプト

このセクションでは、ostress.exe コマンドラインに埋め込まれた T-SQL スクリプトを示します。このスクリプトでは、インストールした T-SQL スクリプトで作成されたアイテムを使用します。

ostress.exe を実行する場合、以下の2つの戦略を使用してワークロードをストレステストするように設計されたパラメーター値を渡すことをお勧めします。

多数のコンカレント接続を実行するには、-n100を使用します。
各接続を数百回ループさせるには、-r500を使用します。

一方、すべてが適切に動作するようにするには、-n10、-r50などの小さな値から始めることもできます。

次のスクリプトでは、5 行のアイテムがあるサンプルの販売注文を、次のメモリ最適化 テーブルに挿入します。

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

前述した ostress.exe の T-SQL スクリプトの _ondisk バージョンを作成するには、両方の _inmem サブストリングを _ondisk に置き換えます。これらの置換は、テーブルとストアドプロシージャの名前に影響があります。

最初に _inmem stress ワークロードを実行する

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 コマンドラインを実行するには:

SSMS で次のコマンドを実行してデータベースのデータコンテンツをリセットし、前回の実行で挿入されたすべてのデータを削除します。
```
EXECUTE Demo.usp_DemoReset;
```
上記の ostress.exe コマンドラインのテキストをクリップボードにコピーします。
パラメーター <placeholders> の -S -U -P -d を実際の正しい値に置き換えます。
編集したコマンドラインを 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 実行に次の手順を実行します。

SSMS で次のコマンドを実行してデータベースをリセットし、前回の実行で挿入されたすべてのデータを削除します。
```
EXECUTE Demo.usp_DemoReset;
```
ostress.exe コマンドラインを編集して、すべての _inmem を _ondisk に置き換えます。
ostress.exe を再び実行し、期間の結果を取得します。
(大量のテストデータとなる可能性があるデータを確実に削除するために) もう一度データベースをリセットします。

予想される比較結果

インメモリテストの結果、をデータベースと同じ Azure リージョンにある Azure VM でostress 実行した場合、この単純なワークロードではパフォーマンスが 9 倍向上することがわかりました。

3.インメモリ分析のサンプルをインストールします

このセクションでは、列ストアインデックスと従来の B ツリーインデックスを使用した場合の IO と統計情報の結果を比較します。

OLTP ワークロードのリアルタイム分析では、多くの場合、非クラスター化列ストアインデックスを使用するのが最適です。詳細については、列ストアインデックスの説明に関するページを参照してください。

列ストア分析テストを準備する

新しいAdventureWorksLT データベースを SQL マネージドインスタンスに復元し、WITH REPLACEを使用して、前にインストールした既存のデータベースを上書きします。
```
RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;
```
sql_in-memory_analytics_sample をクリップボードにコピーします。
- この T-SQL スクリプトによって、手順 1.で作成した AdventureWorksLT サンプルデータベース内に、必要なインメモリオブジェクトが作成されます。
- このスクリプトでは、ディメンションテーブルと 2 つのファクトテーブルを作成します。 fact テーブルには、それぞれ 350 万行のデータが設定されています。
- スクリプトが完了するには約 15 分かかります。
T-SQL スクリプトを SSMS に貼り付け、スクリプトを実行します。次のように、CREATE INDEX CREATE INDEXステートメントの COLUMNSTORE キーワードが重要です。CREATE NONCLUSTERED COLUMNSTORE INDEX ...;
最新の互換性レベルを SQL Server 2022 (160) にAdventureWorksLT アップグレードします。ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

重要なテーブルと列ストアインデックス

dbo.FactResellerSalesXL_CCI は、クラスター化列ストアインデックスがあるテーブルで、データ レベルで高度に圧縮されます。
dbo.FactResellerSalesXL_PageCompressed は、同等の標準のクラスター化されたインデックスがあるテーブルで、ページ レベルでのみ圧縮されます。

4.列ストアインデックスを比較する重要なクエリ

パフォーマンスの改善を確認できるいくつかの T-SQL クエリの種類については、こちらを参照してください。 T-SQL スクリプトの手順 2. では、このペアのクエリに注意してください。 2 つのクエリの違いは次の 1 行のみです。

FROM FactResellerSalesXL_PageCompressed AS a
FROM FactResellerSalesXL_CCI AS a

クラスター化列ストアインデックスは FactResellerSalesXL_CCIテーブルにあります。

次のT-SQLスクリプトは、各クエリに対してSET STATISTICS IOとSET STATISTICS TIMEを使用して、論理I/O活動と時間統計を出力します。

/*********************************************************************
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 マネージドインスタンスの構成によっては、従来のインデックスと比較してクラスター化列ストアインデックスを使用することで、このクエリのパフォーマンスが大幅に向上する可能性があります。

Share via

Azure SQL Managed Instance のメモリ内サンプル

1. インメモリ OLTP サンプルデータベースを復元します

作成されるメモリ最適化項目の概要

2．サンプルの OLTP ワークロードを実行します

RML ユーティリティと ostress をインストールする

ostress.exe のスクリプト

最初に _inmem stress ワークロードを実行する

結果は期間

リセット、 _ondisk の編集、再実行

予想される比較結果

3.インメモリ分析のサンプルをインストールします

列ストア分析テストを準備する

重要なテーブルと列ストアインデックス

4.列ストアインデックスを比較する重要なクエリ

フィードバック

フィードバック

その他のリソース

Share via

Azure SQL Managed Instance のメモリ内サンプル

1. インメモリ OLTP サンプル データベースを復元します

作成されるメモリ最適化項目の概要

2．サンプルの OLTP ワークロードを実行します

RML ユーティリティと ostress をインストールする

ostress.exe のスクリプト

最初に _inmem stress ワークロードを実行する

結果は期間

リセット、 _ondisk の編集、再実行

予想される比較結果

3.インメモリ分析のサンプルをインストールします

列ストア分析テストを準備する

重要なテーブルと列ストア インデックス

4.列ストア インデックスを比較する重要なクエリ

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

1. インメモリ OLTP サンプルデータベースを復元します

重要なテーブルと列ストアインデックス

4.列ストアインデックスを比較する重要なクエリ