Azure SQL データベースのインメモリ サンプル

  • [アーティクル]

適用対象:Azure SQL Database

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

この記事では、インメモリ OLTP と列ストア インデックスを Azure SQL データベースで使用する方法を示す 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 に貼り付け、スクリプトを実行します。 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 の場合はサポートされています。 インメモリ テクノロジは、Azure SQL データベース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), 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 を作成します。 ただし、Azure SQL データベースに接続できる限り、ローカル ワークステーションでostress.exeを実行できます。

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. パラメーター <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. (大量のテスト データとなる可能性があるデータを確実に削除するために) もう一度データベースをリセットします。

予想される比較結果

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

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

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

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

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

  1. Azure Portal を使用して、サンプルから最新の AdventureWorksLT データベースを作成します。

    • 正確な名前を使用します。
    • 任意の Premium サービス階層を選択します。

  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 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 倍のパフォーマンス向上が期待できます。

関連するコンテンツ