Azure SQL Database のインメモリ サンプル

  • [アーティクル]

適用対象: Azure SQL Database

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

この記事では、Azure SQL Database でのインメモリ OLTP と列ストア インデックスの使用方法を示す 2 つのサンプルを確認してください。

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

インメモリ OLTP の入門デモについては、次を参照してください。

1.インメモリ OLTP のサンプルをインストールする

Azure Portal で数回クリックするだけで、Azure portal の AdventureWorksLT サンプル データベースを作成できます。 このセクションの手順では、インメモリ OLTP オブジェクトを AdventureWorksLT データベースに追加し、パフォーマンス上のメリットを示します。

インストール手順

  1. Azure Portal で、論理サーバー上に Premium（DTU） または Business Critical（仮想コア） データベースを作成します。 ソース を AdventureWorksLT サンプル データベースに設定します。 詳細な手順については、Azure SQL Database での最初のデータベースの作成に関するページを参照してください。

  2. SQL Server Management Studio (SSMS) を使用した、データベースへの接続

  3. インメモリ OLTP Transact-SQL スクリプト をクリップボードにコピーします。 この T-SQL スクリプトによって、手順 1.で作成した AdventureWorksLT サンプル データベース内に、必要なインメモリ オブジェクトが作成されます。

  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 は、Azure SQL Database の Premium (DTU) と Business Critical (仮想コア) レベルで使用できます。

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

[テーブル] : このサンプルには、次のメモリ最適化テーブルが含まれています。

  • 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 ワークロードを実行します

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

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

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

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

Azure 仮想マシン (VM) で ostress.exe を実行することをお勧めします。 AdventureWorksLT データベースが存在するのと同じ Azure リージョンに Azure VM を作成します。 AAzure SQL Database に接続できる場合は、代わりにローカル コンピューターで ostress.exe を実行することもできます。 ただし、マシンと Azure のデータベースの間のネットワーク待ち時間により、インメモリ OLTP のパフォーマンス上の利点が低下する場合があります。

VM または選択した任意のホストに、Replay Markup Language (RML) ユーティリティをインストールします。 ユーティリティには、 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 コマンド ラインを実行するには:

  1. SSMS で次のコマンドを実行してデータベースのデータ コンテンツをリセットし、前回の実行で挿入されたすべてのデータを削除します。

    EXECUTE Demo.usp_DemoReset;

  2. 上記の ostress.exe コマンドラインのテキストをクリップボードにコピーします。

  3. パラメーター -S -U -P -d の <placeholders> を正しい値に置き換えます。

  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 VM で実行した場合、この単純なワークロードではパフォーマンスが 9 倍向上することがわかりました。

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

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

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

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

  1. Azure Portal を使用して、サンプルから最新の AdventureWorksLT データベースを作成します。 列ストア インデックスをサポートするサービス目標を使用します。

  2. sql_in-memory_analytics_sample をクリップボードにコピーします。

    • この T-SQL スクリプトによって、手順 1.で作成した AdventureWorksLT サンプル データベース内に、必要なオブジェクトが作成されます。
    • このスクリプトでは、ディメンション テーブルと 2 つのファクト テーブルを作成します。 fact テーブルには、それぞれ 350 万行のデータが設定されています。
    • サービス目標が小さい場合、スクリプトの完了には 15 分以上かかる場合があります。

  3. T-SQL スクリプトを SSMS に貼り付け、スクリプトを実行します。 次のように、CREATE INDEX CREATE INDEXステートメントの COLUMNSTORE キーワードが重要です。CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. 最新の互換性レベルを 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 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 サービス目標を使用するデータベースでは、クラスター化列ストア インデックスを使用すると、従来の行ストア インデックスと比較して、このクエリのパフォーマンスが約 9 倍向上することが期待できます。 P15 サービス目標では、列ストア インデックスを使用することで約 57 倍のパフォーマンス向上が期待できます。

関連するコンテンツ