松本美穂のコラム

こんにちは。松本美穂です。
お盆の時期ですね。みなさんはどうお過ごしですか？ 夏休みの方も多いのではないでしょうか。
私のアパートには、夏は、夏バテ？した蝉やカブトムシがベランダなどに落ちてきたりするので、それらを救済（ひっくり返ってるのを拾って日陰に置いたり、果物や野菜を与えたり）するのが習慣になっています。ここ数日は、シオカラトンボやひぐらしが飛んでくるようになって、夏も後半に入ったのだなと実感しています。

■ SQL Server 2014 実践シリーズ №1 「インメモリ OLTP 機能の実践的な利用方法」 ドキュメントのご案内

5月から夏の前半まで、私たちが集中して検証・調査・執筆制作に取り組んでいた SQL Server 2014 実践シリーズの №1 「インメモリ OLTP 機能の実践的な利用方法」のドキュメントがついに公開されましたのでご紹介します。

SQL Server 2014 実践シリーズ
https://www.microsoft.com/ja-jp/sqlserver/2014/technology/self-learning.aspx#practical_contents

このシリーズでは、実践的なパフォーマンス検証の結果や、具体的な実装方法を交えた、上級者向けのドキュメントとして、「自習書シリーズ」ではなく 「実践シリーズ」 として別個のカテゴリを設けて公開していただいております。

インメモリ OLTP 機能自体が上級者向けの機能のため、十分なパフォーマンス効果を得るためには、かなりのデーターベース熟練者としての経験とコツが必要になります。そういった経験とコツを弊社のお客様のシステム例を挙げて具体的な手順を盛り込んで、ノウハウを満載にしておりますので、導入を検討中のみなさんにとっては必読のドキュメントになっていると思います。心を込めて作りましたので、ぜひご覧いただければと思います。

目次は次のとおりです。

STEP 1. インメモリ OLTP 機能の 実際の利用例 6
1.1 インメモリ OLTP 機能の概要 7
- SQL Server と完全に統合されている 7
- 追加コスト不要 8
1.2 インメモリ OLTP 機能による実際の性能向上（早期導入事例） 9
- bwin 社では 16.7倍の性能向上 ～45万 Batch Reqeust/secも～ 9
- SBIリクイディティ・マーケット株式会社では 2.5倍 ～国内事例～ 10
- Edgenet 社では 8～11倍の性能向上 ～DWH 環境での利用～ 11
- TPP 社では 7倍の性能向上 ～数十万トランザクション/sec～ 12
- 弊社のお客様 A社のポイントカード システム ～2.8倍の性能向上～ 12
1.3 インメモリ OLTP の性能効果を期待できるシステム 13
- インメモリ OLTP の性能効果を期待できるシステム 14
1.4 大量のユーザーによる同時更新が発生するシステムに最適 15
- ロック フリーな楽観的同時実行制御（tempdb 不使用のスナップショット分離） 15
- ラッチ待ちとは？ ～スループットの低下～ 15
- ラッチ待ちが発生しているかどうかを調べる ～Latch Waits/sec～ 17
1.5 インメモリ OLTP はシングル実行でも性能向上を期待可能 18
- どの部分で性能が向上するのか？ ～接続、実行、ログ書き込み～ 19
- ネイティブ コンパイル ストアド プロシージャによる性能向上 20
- ネイティブ コンパイル ストアド プロシージャの作成概要 21
1.6 RDB での主な性能低下をインメモリ OLTP で解決 22
1.7 INSERT 中心のシステムでも効果を発揮 24
- ラッチ待ちの様子 ～インデックスの最終ページがホットスポット～ 27
1.8 bwin 社における利用例 30
- Web サイトの利用状況 ～15万/日、100万新規ユーザー/年～ 31
- ASP.NET のセッション状態データベースで負荷集中 ～ラッチ待ちの多発～ 31
- ラッチ フリー、SCHEMA_ONLY の利用 31
- クライアント コードは未修正、互換性を保つ ～BLOB への対応～ 32
- Write-Write 競合への対応 ～エラー 41302～ 33
- 1秒あたり 450,000 バッチ要求も達成 ～約30倍の性能向上～ 33
- ASP.NET Session State provider for SQL Sever In-Memory 34
1.9 SBIリクイディティ・マーケット株式会社での利用例 35
- システム構成 ～売買集計システムでインメモリ OLTP を採用～ 37
- 更新系処理でのロック／ラッチ待ちを解消するためにインメモリ OLTP を採用 37
1.10 Edgenet 社での利用例 39
- サービス概要 ～最適化された商品データの提供～ 40
- Delayed Durability の採用、更新にブロックされない読み取り 40
1.11 TPP 社での利用例 41
1.12 弊社のお客様 A社の「ポイントカード システム」での検証結果 42
- 検証の背景 ～将来のアクセス増への対応～ 43
- 処理の内容 43
- テーブル構成 43
- シングル実行でも性能向上（1.74倍の性能向上） 44
- 多重実行時のオーバーヘッド（ラッチ待ち、ロック待ち、チェックポイントなど） 44
- パフォーマンス カウンターの様子（ラッチ待ちとバッチ要求数） 45
- ラッチ待ちによるスループットの低下 46
- ラッチ待ちの割合を調べる ～Latch Waits/sec～ 46
- ラッチ待ちの詳細を調べるには ～Wait Stats、Index Stats～ 47
- Index ごとのラッチ待ちを確認可能 ～dm_db_index_operational_stats～ 48
- Processor Time（CPU 利用率）と Disk Queue（ディスク待ち） 50
- チェックポイント処理の負荷 ～復旧間隔、Checkpoint pages/sec～ 51
- インメモリ OLTP ではチェックポイントの負荷が軽い 53
- ガベージ コレクションの影響 54
- ディスク ベースでは断片化も発生する 54
- ログ書き込み量の削減 ～インデックスに関してはログ記録しない～ 55
- まとめ、今後の展望 56
- インメモリ OLTP 実装のポイント（次の章で説明） 56
1.13 この章のまとめ 57
- ディスク ベースのテーブルでの性能低下の主な原因 58
- インメモリ OLTP の性能効果を期待できるシステム 59

STEP 2. ポイントカード システムにおける
インメモリ OLTP 機能の実装のポイント 61
2.1 ポイントカード システムの概要 62
- ポイントカード システムの主な処理内容（SQL ステートメント） 62
2.2 テーブル構成 63
2.3 シングル実行時の性能（ネイティブ コンパイル SP で 1.74倍） 64
- ネイティブ コンパイル SP ではアプリケーションの修正が必要になる 64
- ネイティブ コンパイル SP を作成しないと効果がない？？ 65
- シングル実行も実は速くなるのでは？ 66
- 各 SELECT ステートメントの実行時間は？ 67
- 遅かった SELECT ステートメント（IN 演算子を利用） 67
- 統計の作成 ～CREATE STATISTICS～ 69
- クエリを分割して考えてみる ～ハッシュ インデックスの考え方～ 69
- IN 演算子を UNION ALL への変更 70
2.4 bw-tree インデックスの利用 73
2.5 SELECT ステートメントの具体的な性能向上例 75
SELECT ステートメントは 1.6～1.9倍の性能向上 ～PKを利用した参照～ 75
- カード マスターの検索例 75
- メッセージ マスターの検索例 76
- カード種別マスターの検索例 77
- 顧客マスターの検索例（IN／UNION ALL をメモリ最適化テーブル変数へ） 77
2.6 メモリ最適化テーブル変数の利用（IN、OR、UNION ALL の代替） 78
- ネイティブ コンパイル SP 内では IN、OR、UNION が利用できない 78
- メモリ最適化テーブル変数の利用 78
2.7 UPDATE ステートメントの具体的な性能向上例 81
- UPDATE ステートメントは 1.2～1.3倍の性能向上 ～PKを利用した更新～ 81
- 顧客マスターの更新 81
- カード マスターの更新 82
2.8 INSERT ステートメントの具体的な性能向上例 84
- INSERT ステートメントは 1.4～4.6倍の性能向上 84
- トランザクション テーブルへの INSERT 84
- SCOPE_IDENTITY での ID の取得をネイティブ コンパイル SP へ含める 85
- 顧客利用履歴テーブルへの INSERT ～Delayed Durability～ 85
2.9 各ステートメントの性能に関するまとめ 87
- ネイティブ コンパイル SP による性能向上 87
- ネイティブ コンパイル SP の作成は、約２日 88
2.10 ネイティブ コンパイル SP を簡単に作成する方法 89
- 実際の手順 89
- SQL Server Profiler ツールで SQL をキャプチャする 90
- ネイティブ コンパイル SP の作成 92
- オブジェクト名にスキーマ名を付与する 92
- アプリケーションの修正（ADO.NET の場合） 93
- パラメーターに char／varchar が利用できない ～ n付きへ変更する～ 95
- プロシージャ内の単一引用符には Nプレフィックスが必要 95
- SELECT * を利用できない ～列名を列挙するように変更する～ 96
2.11 ネイティブ コンパイル SP の実行方法の違いによる性能差 99
- 実行方法の違い 99
- 性能比較 101
2.12 char／varchar へ変更した場合の注意点 102
- データベースの照合順序に Japanese_CI_AS を利用している場合 103
- 暗黙の型変換による大幅な性能低下 ～ char に対して nchar を利用するとき～ 104
- データベースの照合順序に 1252 コード ページを利用する場合（Latin_～） 105
- 暗黙の型変換のまとめ ～ char のときに nchar だと遅くなる～ 109
2.13 ADO.NET の AddWithValue、JDBC の場合の注意点 110
- JDBC の setString メソッドは nvarchar に変換されることに注意 110
2.14 char／varchar 型を利用する場合のメモリ使用量 113
- メモリ使用量の確認 ～ dm_db_xtp_table_memory_stats ～ 113
- メモリ使用量の差 ～英数字データなら半分になる～ 114
2.15 ネイティブ コンパイル SP での更新競合への対応 117
- 更新競合への対応 118
2.16 ネイティブ コンパイル SP での SELECT 時の BIN2 照合順序 121
- 文字列の比較や並べ替えは、BIN2 照合順序が必要 121
- Japanese_BIN2 への変更が必要な列 ～ PK／インデックスにも必須 ～ 121
- Japanese_BIN2 へ変更した場合の注意点 ～大文字・小文字の区別など～ 122
- データの格納時に大文字・小文字を統一してしまう 123
2.17 ネイティブ コンパイル SP にするとできなくなること 125
- 実際の実行プランが確認できなくなる 125
- 別バッチから @@ROWCOUNT を取得できなくなる 126
- ExecuteNonQuery で結果件数を取得できなくなる 126
2.18 ネイティブ コンパイル SP を利用するときのポイント 128

STEP 3. ポイントカード システムにおける インメモリ OLTP 機能への移行手順 130
3.1 実際に移行時に行った作業 131
3.2 バックアップとリストアによる DB 移行 132
- 既存の環境（SQL Server 2005）でバックアップを取得 132
- バックアップを新環境（SQL Server 2014）でリストア 132
3.3 データベース移行後のデータベース設定の変更 134
- 互換性レベルを SQL Server 2014（120）へ上げる（オプション） 134
- データベース所有者の変更 ～ ALTER AUTHORIZATION ～ 135
- 瞬時初期化の有効化 ～「ボリュームの保守タスクを実行」権利を付与～ 136
- メモリ最適化テーブル用のファイル グループの作成／追加 137
- Delayed Durability の有効化 138
- ELEVATE_TO_SNAPSHOT を有効化（オプション） 139
3.4 メモリ最適化テーブルへ移行する手順 140
- 既存のテーブルの名前を変更（sp_rename を利用） 141
- 既存のテーブルをスクリプト化（CREATE TABLE を自動生成） 141
- スクリプト（CREATE TABLE ステートメント）の修正 144
- NOT NULL を付与 ～インデックスのキー列は NOT NULL が必須～ 145
- char／varchar を nchar／nvarchar データ型へ変換（n付きへ変更） 145
- PRIMARY KEY 制約の設定（ハッシュまたは bw-tree インデックスの設定） 146
- インデックスを作成する列が char系の場合は BIN2 照合順序へ変更 147
- DEFAULT 制約を設定、名前の変更 148
- 追加のインデックスのスクリプト化 148
- インデックスをハッシュまたは bw-tree へ変更 149
- 利用できないデータ型 150
- 利用できない制約を削除する 150
- データの永続化の決定（SCHEMA_AND_DATA／SCHEMA_ONLY） 150
- CREATE TABLE ステートメントの実行して、メモリ最適化テーブルを作成する 151
3.5 既存テーブルからデータのコピー 152
3.6 ネイティブ コンパイル SP の作成（オプション） 153
- ネイティブ コンパイル SP の基本構文 153

STEP 4. インメモリ OLTP の その他の役立つ利用方法 155
4.1 ELEVATE_TO_SNAPSHOT で自動的にスナップショット分離へ 156
- インメモリ OLTP でサポートされている分離レベル 156
- WITH (SNAPSHOT) でスナップショット分離レベルを指定 157
- ELEVATE_TO_SNAPSHOT を有効化 158
4.2 スナップショット分離レベルでの考え方 159
- トランザクション開始時点での正しいデータを返す ～読み取り一貫性～ 159
- INSERT の場合の動作 ～スナップショット分離レベル～ 160
4.3 更新競合が多発する場合の考え方 ～ナンバリング処理など～ 162
- 更新競合が多発するケース ～DELAY、再試行回数の調整～ 163
- ナンバリング処理 ～手動で連番生成をしている場合～ 163
- ostress ツールで多重実行 ～更新競合のシミュレート～ 165
- 再試行回数を調べてみる 168
- ナンバリング処理はインメモリ化すべきか？？？ 170
4.4 メモリ使用量を制限したい場合 ～リソース プールの作成～ 171
- データベースをリソース プールへバインドする ～～ 172
- メモリ使用量を使い切った場合のエラー 172
4.5 SELECT の取得データ件数が異なる場合の性能差 174
- 検証の詳細 175
- col3 列の検索の場合（約 100件の結果が返る） 178
- col4 列の検索の場合（約 1万件の結果が返る） 179
- 検証のまとめ ～SELECT の取得データ件数の違い～ 180
- ハッシュ インデックスのチェーンの長さ 181
4.6 JOIN がある場合の性能差 183
- 検証結果 184
4.7 bw-tree インデックスの選択基準、範囲スキャン 186
- テストで使用したテーブルの構成 186
- 範囲スキャン ～日付の検索～ 187
- 検証結果 188
- 更新への影響 ～bw-tree は更新が遅くなる～ 189
4.8 フル スキャンを避ける（インメモリ OLTP の苦手な処理） 191
- インメモリ OLTP のフル スキャンのスピード 191
- ネイティブ コンパイル SP を作成するとフル スキャンにも効果がある 193
- インメモリ OLTP は全データを対象とした処理が苦手 ～集計処理など～ 194
- col3 列（インデックスを付与していない列）で GROUP BY を行った場合 196
- col4 列（インデックスを付与していない列）で GROUP BY を行った場合 197
- フル スキャンにはクラスター化列ストア インデックス（CCSI）を利用 198
4.9 BUCKET_COUNT の違いによる性能差 200
- BUCKET_COUNT の違いによる INSERT .. SELECT の性能差 200
- BUCKET_COUNT の違いによる SELECT の性能差 201
- BUCKET_COUNT の設定基準 202
- 空きバケット数、ハッシュ インデックスのチェーンの長さ 204
- バケット数の違いによるメモリ使用量の差 205
4.10 性能検証をする上での注意点 207
- 性能検証の注意点 207
- マイクロ秒の測定ができないツールやコマンドに注意 209
- .NET アプリはデバッグ実行ではなく Release ビルドにすること 210
- Transact-SQL の WHILE ループでの注意点 211
- SQL の直接実行と sp_executesql では性能差が異なることに注意する 212
- ostress ツールによる負荷テスト実行時の注意点 212
4.11 メモリ最適化テーブルを作成するときの制限事項 217
4.12 メモリ最適化アドバイザーによる移行チェック／変換 219
4.13 メモリ最適化テーブルの機能的な制限事項 224
- 制限事項の回避策 225
4.14 ネイティブ コンパイル SP の制限事項 226
- ネイティブ コンパイル SP 内でサポートされている関数 227
4.15 ネイティブ コンパイル アドバイザー 228
4.16 AMR ツール（データ コレクションによる分析／提案機能） 230
4.17 ASP.NET Session State provider for SQL Sever In-Memory 232
利用手順 232
- ASP.NET Session State provider for SQL Sever In-Memory の動作 235
4.18 1秒間に 100万件のデータ挿入も可能 ～Codeplexサンプル～ 237
- 負荷テストの結果 238
- サンプルの概要 238

以上のように、実践的なノウハウが盛りだくさんの内容になっております。
今回の検証作業で注意した点なども載せておりますので、ぜひ、インメモリ OLTP 機能を使いこなすための参考にしていただければと思います。

久しぶりのエンジンの目玉の新機能ということで、私たちも非常に興奮しつつ、しかし、新機能だけに慎重に、1つ 1つ自分達の手で確め、調査と思考を重ねながら、時間をかけて繰り返し検証して取り組みました。新しい発見がある度に、手探りの苦しみが喜びに変わっていき、まさに大汗をかきながらの作業でした。

本ドキュメントを読むことで、インメモリ OLTP 機能を適用して皆さんのシステムに効果が期待できるかの判断の一助になれば幸いです。

■ おわりに
先日ベランダのパセリにいたキアゲハの幼虫2匹がいなくなってしまいました。
無事にどこかで孵っているといいのですが...。一般的には害虫あつかいなので、ちょっと気になっています（存在感あったし、鳥もいますし）。さなぎが蝶に孵るというのは、やっぱり自然の神秘ですよね。

ベランダの睡蓮