SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 実践 No.1 インメモリ OLTP 機能の実践的な利用方法

SQL Server 2014 実践シリーズ（HTML 版）
「No.1 インメモリ OLTP 機能の実践的な利用方法」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.1 インメモリ OLTP 機能の実践的な利用方法」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

2.3　シングル実行時の性能（ネイティブコンパイル SP で 1.74倍）

前の章では、シングル実行（多重実行ではなく、シングルスレッドでの単体実行）でも、性能が向上することを説明しました。

00084

この値は、1スレッドでアプリケーションを実行して、過去のシステム利用状況をシミュレートして、1,000回の処理を実行したときの「平均値」を比較したものです（SQL Server Profiler ツールを利用して処理をトレース／キャプチャして、その結果から平均値を計算しています）。

全体として 1.74倍の性能向上（1.3ミリ秒から 750マイクロ秒へ向上）することを確認できました。また、INSERT ステートメント 3個では 2.5倍、SELECT ステートメント 6個では 2.1倍、UPDATE ステートメント 3個では 1.2倍の性能向上になりました。

このシングル実行での性能向上は、ネイティブコンパイル SP を作成することで実現しています。

◆ ネイティブコンパイル SP ではアプリケーションの修正が必要になる

ネイティブコンパイル SP 化するにあたっては、ネイティブコンパイル SP の作成と、それを利用するようにアプリケーションを修正する必要も出てきます。とは言っても、アプリケーションが .NET（VB + ADO.NET）で作られているのであれば、修正は簡単です。例えば、次のようなコードで SQL Server へアクセスしているとします。

Using cn As New SqlConnection(cnstr)
    Using cmd As New SqlCommand()
        cn.Open()
        cmd.Connection = cn
        Dim sqlStr As String
        sqlStr = "SELECT * FROM table1 WHERE col1 = @p1"
        Dim p1 As SqlParameter = cmd.Parameters.Add("@p1", SqlDbType.Int)
        p1.Value = 123
        cmd.CommandText = sqlStr
    End Using
End Using

このようにアプリケーションから実行している SELECT ステートメントを、次のようにネイティブコンパイル SP 化したとします。

CREATE PROC p_table1_検索
@p1 int
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'japanese' )
SELECT col1, col2, col3 FROM dbo.table1 WHERE col1 = @p1
END

このネイティブコンパイル SP を利用するには、次のように SQL 文字列の部分（sqlStr 変数）を、ネイティブコンパイル SP 名に変更して、SqlCommand オブジェクトのCommandType を「CommandType.StoredProcedure」へ変更するだけです。

cn.Open()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure     ' この行を追加
Dim sqlStr As String
sqlStr = "p_table1_検索"      ' この行をネイティブコンパイル SP 名に変更
Dim p1 As SqlParameter = cmd.Parameters.Add("@p1", SqlDbType.Int)
     ：

このようにアプリケーションの修正は、簡単に行えますが、簡単とは言っても、修正後にきちんと動作するのかのテストなどが必要になるので、まずは、ネイティブコンパイル SP を作成しないで、できる限りアプリケーションを修正することなく、インメモリ化するだけで、性能向上を実現できないか？と考え、テーブルだけを、メモリ最適化テーブルへ変更することを試みてみました。

◆ ネイティブコンパイル SP を作成しないと効果がない？？

ネイティブコンパイル SP を作成しないで、各テーブルをメモリ最適化テーブルへ変更しただけの場合に、シングル実行したときの性能は、次のようになりました。

00085

この方法は、アプリケーションを修正しなくても済むのが非常に大きなメリットで、各テーブルのインデックスを（メモリ最適化テーブルを利用するために）ハッシュインデックスへ変更したり、ハッシュインデックスを作成するために照合順序を Japanese_BIN2 へ変更したりするなどを行っていますが、今回対象となるテーブルが 6個のみであったこともあり、これらの変更は半日もあれば完了しました（具体的な作業方法については後述します）。

結果は、ディスクベースのものと 14マイクロ秒しか差がでないものとなってしまいました（数値は、前項と同様、1スレッドで 1,000回の処理を実行したときの「平均値」を計算したもの）。

しかし、この結果は、あくまでもシングル実行（シングルスレッドでの単体実行）での結果です。本番を想定した 100多重での負荷テスト（1時間負荷をかけ続けるテスト）を実施すると、次のように 1.83倍の性能向上を確認することができました。

00086

00087

ディスクベースでは、1秒あたり 1,743個の処理量であったところを、テーブルをメモリ最適化テーブルへ変更するだけで、1秒あたり 3,189個の処理ができるようになりました（1.83倍の性能向上）。

なんといっても、アプリケーションを修正することなく、テーブルを変更しただけで、1.83倍もの性能向上を実現できたことに驚きでした。従来ながらのディスクベースのテーブルでは、多重度が上がったときに、ラッチ待ちやロック待ち、チェックポイント時のデータファイルへのフラッシュ、ログ書き込みの負荷、断片化の発生など、多くのオーバーヘッドがあるので、このような性能差を確認することができました。

◆ シングル実行も実は速くなるのでは？

上記のように多重実行で速くなることは確認できましたが、シングル実行でも、何かを変えれば速くなるのかもしれない、という期待があり、改めて、以下の表を見て、気が付くことがありました。

00088

INSERT と UPDATE は若干速くなっていて、足を引っ張っているのは SELECT ステートメントなのではないかという疑問が沸いてきたので、SELECT ステートメントについて詳しく調べてみることにしました。

◆ 各 SELECT ステートメントの実行時間は？

シングル実行での足を引っ張っているのが SELECT ステートメントなのではないか、という疑問から、各ステートメントの実行時間を確認してみました。

00089

すると、「顧客マスター」の SELECT ステートメントのみが倍近く遅い結果（58マイクロ秒が 93.8マイクロ秒）になっていて、そのほかは性能向上していることが分かりました。

◆ 遅かった SELECT ステートメント（IN 演算子を利用）

遅かった SELECT ステートメントは、次のような形をとっていました。

SELECT * FROM 顧客マスター
WHERE カードID = '12345'
AND カード種別 IN ('A1', 'A2', 'A3')
ORDER BY カード種別

この顧客マスターは、次のようなテーブル構成です（列名を伏せるために col3 や col4 など、実際の名前とは異なる名前へ変更しています）。

CREATE TABLE 顧客マスター
( カードID nchar(16) COLLATE Japanese_BIN2 NOT NULL,
  カード種別 nvarchar(2) COLLATE Japanese_BIN2 NOT NULL,
  col3 datetime NULL,
  col4 int NOT NULL,
  col5 datetime NOT NULL,
  col6 datetime NULL,
  col7 nchar(1) NOT NULL DEFAULT ('0')
,CONSTRAINT PK_顧客マスター PRIMARY KEY NONCLUSTERED
         HASH (カードID, カード種別) WITH (BUCKET_COUNT = 20000000)
) WITH ( MEMORY_OPTIMIZED = ON
       , DURABILITY = SCHEMA_AND_DATA )

(カードID, カード種別) を複合主キーとして PRIMARY KEY 制約に設定して、ハッシュインデックスを作成しています。また、ハッシュインデックスを作成するには、BIN2 の照合順序が必須になるので、Japanese_BIN2 を指定しています（メモリ最適化テーブルを作成するための要件については、第3章で説明します）。

遅かった SELECT ステートメントの「実行プラン」を確認してみると、次のようになりました。

00090

Index Seek（インデックスを利用したピンポイント検索）が選択されているので、実行プランを見る限りは問題がなさそうに見えます。黄色い警告が 1つ表示されていますが、これは、カードID 列に統計が作成されていないということで表示されています。

ディスクベースのテーブルでは、複合インデックスの場合は、一番左に指定した列に対して、自動的に統計が作成されますが、メモリ最適化テーブルでは作成されないようです（2つ目の列のカード種別に対しては、クエリ実行後に自動作成されました）。

00091

◆ 統計の作成～CREATE STATISTICS～

カードID 単体には、統計が作成されていないようだったので、CREATE STATISTICS ステートメントを利用して、次のように統計を作成してみました。

-- カードID へ統計の作成
CREATE STATISTICS カードID_stats
ON 顧客マスター(カードID)
WITH FULLSCAN, NORECOMPUTE

このように統計を作成することによって、次のように実行プランに警告は表示されなくなりましたが、実行時間に変化はありませんでした（やはりディスクベースの b-tree インデックスよりも遅い結果となりました）。

00092

◆ クエリを分割して考えてみる～ハッシュインデックスの考え方～

統計を作成しても効果がなかったので、この問題を解決するために、クエリを分割して考えてみました。まず、「カードID」列のみで検索をしてみます。

SELECT * FROM 顧客マスター
WHERE カードID = '12345'

実行プランを確認してみると、次のように「Table Scan」になります。

00093

私たちは、これを見たときに一瞬とまどってしまったのですが、b-tree インデックス的な発想だと、一番左の列に指定している列なら Index Seek になるわけです。しかし、Table Scan（全スキャン）になってしまっています。

これは、次のようにハッシュインデックスをイメージすると分かりやすいと思います。

00094

もし、「カードID」列にのみハッシュインデックスを作成しているのであれば、左図のように Index Seek で検索できますが、(カードID, カード種別) のハッシュインデックスでは、カードID とカード種別がペアになってハッシュ値を計算しているため、カードID の指定だけでは、インデックスを Seek（ピンポイント検索）できないのです（右図）。

これをヒントに、先ほどの遅かったクエリを改めてみてみます。

SELECT * FROM 顧客マスター
WHERE カードID = '12345'
AND カード種別 IN ('A1', 'A2', 'A3')
ORDER BY カード種別

カードID とカード種別を指定していて、実行プランも Index Seek でプラン的にも問題ない、統計を作成しても変わらない、そこで思い浮かんだのが、次の 2点です。

IN 演算子が遅い？
(カードID, カード種別) のペアがうまく認識されていない？

そこで、以下のように IN 演算子を利用しないで、カード種別を１つだけ指定した検索を行ってみました。

SELECT * FROM 顧客マスター
WHERE カードID = '12345'
AND カード種別 = 'A1'
ORDER BY カード種別

この検索は、ディスクベースの検索と遜色ないスピード（むしろディスクベースよりも少し速い？）という感触が得られました。ようやく希望が見えてきたので、次は、IN演算子を UNION ALL を使って分割してみました。

◆ IN 演算子を UNION ALL への変更

IN 演算子は、UNION で置換することができるので、次のクエリは、同じ結果を取得することができます。

-- IN 演算子を使う場合
SELECT * FROM table1
WHERE col1 IN ('A', 'B')

-- UNION を使う場合
SELECT * FROM table1
WHERE col1 = 'A'
UNION
SELECT * FROM table1
WHERE col1 = 'B'

UNION は、重複データを除去する効果があるので、IN 演算子（OR）と等価になります。しかし、重複データを除去する部分は、余計なオーバーヘッドになるので、もし事前に重複データが発生することがあり得ないことが分かっているのであれば、UNION の代わりに UNION ALL を利用して、性能向上を計ることができます。今回のカードID とカード種別の関係は、まさにこの状況なので、UNION ALL が使える状況でした。

そこで、さきほどの SELECT ステートメントの IN演算子を、次のように UNION ALL を使って、分割してみました。

SELECT * FROM (
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A1'
  UNION ALL
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A2'
  UNION ALL
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A3' ) t1
ORDER BY カード種別

この SELECT ステートメントは、同じカードID でカード種別が異なるもの（A1、A2、A3）を取得する、という検索なので、重複値が発生することはないので、UNION ALL を利用することができます。これを利用することで、次のようにディスクベースよりも 28.9％ の性能向上を実現することができました。

00095

ここにたどり着くまでに丸々１日ぐらい費やしてしまいましたが、ハッシュインデックスへの理解が深まったことは大きな収穫になりました。

この結果を、全体の結果へ当てはめてみると、次のように 5.1％ の性能向上になりました。

00096

UNION ALL を利用することで、アプリケーションの修正が必要になりましたが、インメモリ化しただけで、シングル実行でも性能向上を達成できたことは大きな驚きでした。

また、本番を想定した 100多重での負荷テストでは、次のように 1.93倍もの性能向上を確認することができました。

00097

多重実行では、ラッチ待ちやロック待ち、チェックポイントの負荷などがかかるので、より大きな差になることを確認できました。なによりも、アプリケーションをほとんど修正することなく、1.93倍（2倍近い）の性能向上を実現できたことが驚きでした。

なお、ネイティブコンパイル SP を作成すると、UNION ALL を利用するよりも、さらに速い実行時間で処理できていますが、ネイティブコンパイル SP では IN 演算子や UNION ALL が利用できない、という制限があるところを、「メモリ最適化テーブル変数」を利用する、というテクニックを使っていますので、これについては後述します。

Note： IN を UNION へ変換する場合の注意点
今回のシステムでは、IN 演算子で指定するカード種別が 3つと固定だったので、UNION ALL へ変更できましたが、もし IN で指定する値が可変の場合は、簡単に UNION へ変更することができません。このような場合は、後述する「bw-tree インデックス」を利用することで、今回のような問題へ対処することが可能です。

目次へ | 前のページへ | 次のページへ