In-Memory のサンプルIn-Memory sample

Azure SQL Database のインメモリ テクノロジにより、アプリケーションのパフォーマンスを向上させることができ、また、データベースのコストを削減できる可能性があります。In-Memory technologies in Azure SQL Database enable you to improve performance of your application, and potentially reduce cost of your database. Azure SQL Database のインメモリ テクノロジを使用すれば、さまざまなワークロードでパフォーマンスの向上を実現できます。By using In-Memory technologies in Azure SQL Database, you can achieve performance improvements with various workloads.

この記事では、インメモリ OLTP と列ストア インデックスを Azure SQL Database で使用する方法を示す 2 つのサンプルを確認します。In this article you'll see two samples that illustrate the use of In-Memory OLTP, as well as columnstore indexes in Azure SQL Database.

詳細については、次を参照してください。For more information, see:

 

1.インメモリ OLTP のサンプルをインストールする1. Install the In-Memory OLTP sample

Azure Portal で数回クリックするだけで、AdventureWorksLT のサンプル データベースを作成できます。You can create the AdventureWorksLT sample database with a few clicks in the Azure portal. このセクションの手順では、インメモリ OLTP オブジェクトを使用して AdventureWorksLT データベースを強化する方法について説明し、パフォーマンス上のメリットを示します。Then, the steps in this section explain how you can enrich your AdventureWorksLT database with In-Memory OLTP objects and demonstrate performance benefits.

より簡潔でありながら見栄えの良いインメモリ OLTP のパフォーマンス デモについては、次を参照してください。For a more simplistic, but more visually appealing performance demo for In-Memory OLTP, see:

インストール手順Installation steps

  1. Azure Portal で、サーバー上に Premium または Business Critical データベースを作成します。In the Azure portal, create a Premium or Business Critical database on a server. ソース を AdventureWorksLT サンプル データベースに設定します。Set the Source to the AdventureWorksLT sample database. 詳細な手順については、最初の Azure SQL データベースの作成に関する記事を参照してください。For detailed instructions, see Create your first Azure SQL database.

  2. SQL Server Management Studio (SSMS.exe)を使用して、データベースに接続します。Connect to the database with SQL Server Management Studio (SSMS.exe).

  3. インメモリ OLTP Transact-SQL スクリプト をクリップボードにコピーします。Copy the In-Memory OLTP Transact-SQL script to your clipboard. この T-SQL スクリプトによって、手順 1. で作成した AdventureWorksLT サンプル データベース内に、必要なインメモリ オブジェクトが作成されます。The T-SQL script creates the necessary In-Memory objects in the AdventureWorksLT sample database that you created in step 1.

  4. T-SQL スクリプトを SSMS に貼り付け、スクリプトを実行します。Paste the T-SQL script into SSMS, and then execute the script. MEMORY_OPTIMIZED = ON 句の CREATE TABLE ステートメントが重要です。The MEMORY_OPTIMIZED = ON clause CREATE TABLE statements are crucial. 例:For example:

CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

エラー 40536Error 40536

T-SQL スクリプトを実行するときにエラー 40536 が発生する場合は、次の T-SQL スクリプトを実行し、データベースがインメモリをサポートしているかどうかを確認します。If you get error 40536 when you run the T-SQL script, run the following T-SQL script to verify whether the database supports In-Memory:

SELECT DatabasePropertyEx(DB_Name(), 'IsXTPSupported');

結果が 0 の場合、インメモリがサポートされていないことを示します。1 の場合はサポートされています。A result of 0 means that In-Memory isn't supported, and 1 means that it is supported. 問題を診断するには、データベースを Premium サービス レベルにします。To diagnose the problem, ensure that the database is at the Premium service tier.

作成されるメモリ最適化項目の概要About the created memory-optimized items

テーブル:このサンプルには、次のメモリ最適化テーブルが含まれています。Tables: The sample contains the following memory-optimized tables:

  • SalesLT.Product_inmemSalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmemSalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmemSalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeedDemo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeedDemo.DemoSalesOrderDetailSeed

SSMS でオブジェクト エクスプローラーを使用してメモリ最適化テーブルを確認できます。You can inspect memory-optimized tables through the Object Explorer in SSMS. [テーブル] を右クリックし、 > [フィルター] > [フィルターの設定] > [Is Memory Optimized (メモリ最適化済み)] の順に選択します。Right-click Tables > Filter > Filter Settings > Is Memory Optimized. 値は 1 です。The value equals 1.

または、次のようにカタログ ビューにクエリを実行できます。Or you can query the catalog views, such as:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

ネイティブ コンパイル ストアド プロシージャ:SalesLT.usp_InsertSalesOrder_inmem は、カタログ ビューのクエリを使用して確認できます。Natively compiled stored procedure: You can inspect SalesLT.usp_InsertSalesOrder_inmem through a catalog view query:

SELECT uses_native_compilation, OBJECT_NAME(object_id), definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

 

サンプルの OLTP ワークロードを実行するRun the sample OLTP workload

次の 2 つの ストアド プロシージャ の違いは、1 つ目のプロシージャはメモリ最適化バージョンのテーブルを使用し、2 つ目のプロシージャは通常のディスク上のテーブルを使用している点です。The only difference between the following two stored procedures is that the first procedure uses memory-optimized versions of the tables, while the second procedure uses the regular on-disk tables:

  • SalesLT . usp_InsertSalesOrder _inmemSalesLT . usp_InsertSalesOrder _inmem
  • SalesLT . usp_InsertSalesOrder _ondiskSalesLT . usp_InsertSalesOrder _ondisk

このセクションでは、便利な ostress.exe ユーティリティを使用して、負荷が高い状態で 2 つのストアド プロシージャを実行する方法について説明します。In this section, you see how to use the handy ostress.exe utility to execute the two stored procedures at stressful levels. 2 つのストレス実行が完了するまでの時間を比較することができます。You can compare how long it takes for the two stress runs to finish.

ostress.exe を実行する場合、次の両方について指定したパラメーター値を渡すことをお勧めします。When you run ostress.exe, we recommend that you pass parameter values designed for both of the following:

  • 多数のコンカレント接続を実行するには、-n100 を使用します。Run a large number of concurrent connections, by using -n100.
  • 各接続を数百回ループさせるには、-r500 を使用します。Have each connection loop hundreds of times, by using -r500.

一方、すべてが適切に動作するようにするには、-n10、-r50 などの小さな値から始めることもできます。However, you might want to start with much smaller values like -n10 and -r50 to ensure that everything is working.

ostress.exe のスクリプトScript for ostress.exe

このセクションでは、ostress.exe コマンド ラインに埋め込まれた T-SQL スクリプトを示します。This section displays the T-SQL script that is embedded in our ostress.exe command line. このスクリプトでは、インストールした T-SQL スクリプトで作成されたアイテムを使用します。The script uses items that were created by the T-SQL script that you installed earlier.

次のスクリプトでは、5 行のアイテムがあるサンプルの販売注文を、次のメモリ最適化 テーブルに挿入します。The following script inserts a sample sales order with five line items into the following memory-optimized tables:

  • SalesLT.SalesOrderHeader_inmemSalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmemSalesLT.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 に置き換えます。To make the _ondisk version of the preceding T-SQL script for ostress.exe, you would replace both occurrences of the _inmem substring with _ondisk. これらの置換は、テーブルとストアド プロシージャの名前に影響があります。These replacements affect the names of tables and stored procedures.

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

Azure 仮想マシン (VM) で ostress.exe を実行する計画を立てるのが理想的です。Ideally, you would plan to run ostress.exe on an Azure virtual machine (VM). AdventureWorksLT データベースがある Azure リージョンと同じリージョンに Azure VM を作成します。You would create an Azure VM in the same Azure geographic region where your AdventureWorksLT database resides. 代わりにノートパソコンで ostress.exe を実行することもできます。But you can run ostress.exe on your laptop instead.

VM または選択した任意のホストに、Replay Markup Language (RML) ユーティリティをインストールします。On the VM, or on whatever host you choose, install the Replay Markup Language (RML) utilities. このユーティリティに ostress.exe が含まれています。The utilities include ostress.exe.

詳細については、次を参照してください。For more information, see:

最初に _inmem stress ワークロードを実行するRun the _inmem stress workload first

RML コマンド プロンプト ウィンドウを使用して、ostress.exe コマンド ラインを実行できます。You can use an RML Cmd Prompt window to run our ostress.exe command line. コマンドライン パラメーターは ostress に次のことを行うように求めます。The command-line parameters direct ostress to:

  • 100 個の接続を同時に実行する (-n100)。Run 100 connections concurrently (-n100).
  • 各接続に T-SQL スクリプトを 50 回実行させる (-r50)。Have each connection run the T-SQL script 50 times (-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 コマンド ラインを実行するには:To run the preceding ostress.exe command line:

  1. SSMS で次のコマンドを実行してデータベースのデータ コンテンツをリセットし、前回の実行で挿入されたすべてのデータを削除します。Reset the database data content by running the following command in SSMS, to delete all the data that was inserted by any previous runs:

    EXECUTE Demo.usp_DemoReset;
    
  2. 上記の ostress.exe コマンドラインのテキストをクリップボードにコピーします。Copy the text of the preceding ostress.exe command line to your clipboard.

  3. パラメーター -S -U -P -d の <placeholders> を実際の正しい値に置き換えます。Replace the <placeholders> for the parameters -S -U -P -d with the correct real values.

  4. 編集したコマンドラインを RML コマンド ウィンドウで実行します。Run your edited command line in an RML Cmd window.

結果は期間Result is a duration

ostress.exe が完了すると、RML コマンド ウィンドウに表示される出力の最終行に実行時間が出力されます。When ostress.exe finishes, it writes the run duration as its final line of output in the RML Cmd window. たとえば、短いテストの場合、約 1.5 分かかります。For example, a shorter test run lasted about 1.5 minutes:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

リセット、 _ondisk の編集、再実行Reset, edit for _ondisk, then rerun

_inmem 実行の結果を取得したら、 _ondisk 実行に次の手順を実行します。After you have the result from the _inmem run, perform the following steps for the _ondisk run:

  1. SSMS で次のコマンドを実行してデータベースをリセットし、前回の実行で挿入されたすべてのデータを削除します。Reset the database by running the following command in SSMS to delete all the data that was inserted by the previous run:

    EXECUTE Demo.usp_DemoReset;
    
  2. ostress.exe コマンド ラインを編集して、すべての _inmem_ondisk に置き換えます。Edit the ostress.exe command line to replace all _inmem with _ondisk.

  3. ostress.exe を再び実行し、期間の結果を取得します。Rerun ostress.exe for the second time, and capture the duration result.

  4. (大量のテスト データとなる可能性があるデータを確実に削除するために) もう一度データベースをリセットします。Again, reset the database (for responsibly deleting what can be a large amount of test data).

予想される比較結果Expected comparison results

インメモリ テストの結果、ostress をデータベースと同じ Azure リージョンにある Azure VM で実行した場合、この単純なワークロードではパフォーマンスが 9 倍向上することがわかりました。Our In-Memory tests have shown that performance improved by nine times for this simplistic workload, with ostress running on an Azure VM in the same Azure region as the database.

 

2.インメモリ分析のサンプルをインストールする2. Install the In-Memory Analytics sample

このセクションでは、列ストア インデックスと従来の B ツリー インデックスを使用した場合の IO と統計情報の結果を比較します。In this section, you compare the IO and statistics results when you're using a columnstore index versus a traditional b-tree index.

OLTP ワークロードのリアルタイム分析では、多くの場合、非クラスター化列ストア インデックスを使用するのが最適です。For real-time analytics on an OLTP workload, it's often best to use a nonclustered columnstore index. 詳細については、列ストア インデックスの説明に関するページを参照してください。For details, see Columnstore Indexes Described.

列ストア分析テストを準備するPrepare the columnstore analytics test

  1. Azure Portal を使用して、サンプルから最新の AdventureWorksLT データベースを作成します。Use the Azure portal to create a fresh AdventureWorksLT database from the sample.

    • 正確な名前を使用します。Use that exact name.
    • 任意の Premium サービス階層を選択します。Choose any Premium service tier.
  2. sql_in-memory_analytics_sample をクリップボードにコピーします。Copy the sql_in-memory_analytics_sample to your clipboard.

    • この T-SQL スクリプトによって、手順 1. で作成した AdventureWorksLT サンプル データベース内に、必要なインメモリ オブジェクトが作成されます。The T-SQL script creates the necessary In-Memory objects in the AdventureWorksLT sample database that you created in step 1.
    • このスクリプトでは、Dimension テーブルと 2 つのファクト テーブルを作成します。The script creates the Dimension table and two fact tables. fact テーブルには、それぞれ 350 万行のデータが設定されています。The fact tables are populated with 3.5 million rows each.
    • スクリプトが完了するには約 15 分かかります。The script might take 15 minutes to complete.
  3. T-SQL スクリプトを SSMS に貼り付け、スクリプトを実行します。Paste the T-SQL script into SSMS, and then execute the script. 次のように、CREATE INDEX ステートメントの COLUMNSTORE キーワードが重要です。The COLUMNSTORE keyword in the CREATE INDEX statement is crucial, as in:
    CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. AdventureWorksLT を互換性レベル 130 に設定します。Set AdventureWorksLT to compatibility level 130:
    ALTER DATABASE AdventureworksLT SET compatibility_level = 130;

    レベル 130 はインメモリ機能に直接、関係ありません。Level 130 is not directly related to In-Memory features. しかし、レベル 130 は一般に、120 の場合よりも高いクエリ パフォーマンスを提供します。But level 130 generally provides faster query performance than 120.

重要なテーブルと列ストア インデックスKey tables and columnstore indexes

  • dbo.FactResellerSalesXL_CCI は、クラスター化列ストア インデックスがあるテーブルで、"データ" レベルで高度に圧縮されます。dbo.FactResellerSalesXL_CCI is a table that has a clustered columnstore index, which has advanced compression at the data level.

  • dbo.FactResellerSalesXL_PageCompressed は、同等の標準のクラスター化されたインデックスがあるテーブルで、ページ レベルでのみ圧縮されます。dbo.FactResellerSalesXL_PageCompressed is a table that has an equivalent regular clustered index, which is compressed only at the page level.

列ストア インデックスを比較する重要なクエリKey queries to compare the columnstore index

パフォーマンスの改善を確認できるいくつかの T-SQL クエリの種類については、こちらを参照してください。There are several T-SQL query types that you can run to see performance improvements. T-SQL スクリプトの手順 2. では、このペアのクエリに注意してください。In step 2 in the T-SQL script, pay attention to this pair of queries. 2 つのクエリの違いは次の 1 行のみです。They differ only on one line:

  • FROM FactResellerSalesXL_PageCompressed a
  • FROM FactResellerSalesXL_CCI a

クラスター化列ストア インデックスは FactResellerSalesXL_CCI テーブルにあります。A clustered columnstore index is in the FactResellerSalesXL_CCI table.

次の T-SQL スクリプトの抜粋では、各テーブルのクエリの IO と TIME の統計情報を出力します。The following T-SQL script excerpt prints statistics for IO and TIME for the query of each table.

/*********************************************************************
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 = 130
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 a
INNER JOIN DimProduct b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate 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 a
INNER JOIN DimProduct b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer d ON d.CustomerKey = a.CustomerKey
Inner JOIN DimProductSubCategory e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate 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 倍のパフォーマンス向上が期待できます。In a database with the P2 pricing tier, you can expect about nine times the performance gain for this query by using the clustered columnstore index compared with the traditional index. P15 では、列ストア インデックスを使用することで約 57 倍のパフォーマンス向上が期待できます。With P15, you can expect about 57 times the performance gain by using the columnstore index.

次の手順Next steps

その他のリソースAdditional resources

詳細情報Deeper information

アプリケーションの設計Application design

ツールTools