OLTPブループリント‐OLTPアプリケーションのパフォーマンス分析
マイクロソフトの植田です。
私はSQL Server 開発部門でSQL Serverのテストを担当しております。現在は主にSQL Server ベスト・プラクティスと呼ばれるプロジェクトに参加しています。このプロジェクトで得られた結果は、米国のSQL Server Development Customer Advisory Team（CAT）によって以下のWebサイトから情報提供されています。
CAT Blog
https://blogs.msdn.com/sqlcat/
Microsoft TechNet
https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx
上記ブログは内容がコンパクトにまとまっていて読みやすいものの、残念ながら現在は英語のみでの提供となっておりますので、この場を使って少しでも多くの情報を日本語でお伝えできればと考えております。
第一弾として、OLTPにおけるパフォーマンス分析を取り上げたいと思います。
https://blogs.msdn.com/sqlcat/archive/2006/06/23/Tom-Davidson-SQLCAT-Best-Practices.aspx
注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。
本ドキュメントは以下の方を対象としております。
· 開発者、テストエンジニア、データベース・アドミニストレータ
· Microsoft SQL Server プラットフォーム上でのアプリケーションのパフォーマンス・チューニングに携わっている、または、経験をお持ちの方
· 上記パフォーマンス分析/チューニングについて基本的な知識をお持ちの方
OLTP ブループリント－OLTPアプリケーションのパフォーマンス分析
パフォーマンス、および、チューニングのブループリント
様々なタイプのアプリケーションについて、どのようにリソースを利用しているか、そして、どのようにパフォーマンス・チューニングを行っていくべきかを考えてみましょう。OLTPのパフォーマンス分析は、リレーショナル・データ・ウェアハウスや、レポーティング・アプリケーションのパフォーマンス分析とは全く異なっています。これらの違いを理解し、より高いパフォーマンスを得るための方針を知っておくことはパフォーマンス・チューニングにおいてとても役に立ちます。
OLTP ブループリント
例えばOLTPアプリケーションの特徴は、個々の比較的小さな単位のトランザクション処理を大量に行うことです。それらはSELECT, INSERT, UPDATE, DELETEなどの処理を含んでいます。これらの処理の実装と、データベース・デザイン、リソース使用率、そしてシステムパフォーマンスはとても密接に関わり合っています。
OLTPパフォーマンス分析（ブループリント）の指針：
以下の条件に一致するとき、パフォーマンス劣化の問題が生じます。
注意）値カラムに使われている数値に関してどの値が適切かは、実環境において議論する必要があります。
|
リソース |
ルール |
説明 |
値 |
ソース |
問題点 |
|
データベースデザイン |
ルール1 |
X個のテーブルを使ったクエリを頻繁に行う |
X>4 |
Sys.dm_exec_sql_text, Sys.dm_exec_cached_plans |
多くのテーブル結合を使ったクエリを頻繁に行うことは、広範囲のデータでキャッシュが平準化（⇔局所化）されるためOLTPの拡張性を高められない（スケールを大きくすることがリニアにスループット向上につながらない） |
|
ルール2 |
X個のインデックスを持ったテーブルの更新を頻繁に行う |
X >3 |
Sys.indexes, Sys.dm_db_operational_index_stats |
OLTP処理に対して過度のインデックス再構築が発生している | |
|
ルール3 |
大量のIO テーブル・スキャン レンジ・スキャン |
X>1 |
パフォーマンス・オブジェクト： SQL Server Access Methods Sys.dm_exec_query_stats |
インデックス非利用、キャッシュのフラッシュ | |
|
ルール4 |
使用していないインデックス |
Index not in* |
* Sys.dm_db_index_usage_stats |
使用していないインデックスのメンテナンス | |
|
CPU |
ルール1 |
シグナル 待ち |
>25% |
Sys.dm_os_wait_stats |
実行可能状態のクエリのCPU空き時間待ちが多発している |
|
ルール2 |
Planの再利用 |
<90% |
パフォーマンス・オブジェクト： SQL Server Statistics |
個々のOLTPトランザクションが95%以上のプラン再利用率を持つことが理想的 | |
|
ルール3 |
並列化：Cxpacket待ち |
>5% |
Sys.dm_os_wait_stats |
並列化によるCPU間の同期処理がOLTPのスループットを低下させる | |
|
メモリー |
ルール1 |
Page life expectancyの平均値 |
<300 (秒) |
パフォーマンス・オブジェクト： SQL Server Buffer Manager SQL Server Buffer Nodes |
キャッシュのフラッシュ 大量のReadによるインデックス非利用の可能性 |
|
ルール2 |
Page life expectancyの平均値 |
50%低下 |
パフォーマンス・オブジェクト： SQL Server Buffer Manager |
キャッシュのフラッシュ 大量のReadによるインデックス非利用の可能性 | |
|
ルール3 |
Memory Grants Pendingの値 |
>1 |
パフォーマンス・オブジェクト： SQL Server Memory Manager |
ワークスペースメモリの使用許可を待っている現在のプロセス数 | |
|
IO |
ルール1 |
Avg Disk seconds / read の値 |
>20ms |
パフォーマンス・オブジェクト： Physical Disk |
IO負荷がない状況でReadにかかる時間は4-8ms程度 |
|
ルール2 |
Avg Disk seconds / write の値 |
>20ms |
パフォーマンス・オブジェクト： Physical Disk |
トランザクションログの連続書き込みは1msで行える | |
|
ルール3 |
大量のIO テーブル・スキャン |
>1 |
パフォーマンス・オブジェクト： SQL Server Access Methods |
インデックスの非利用 キャッシュがフラッシュされている | |
|
ルール4 |
Wait statsの上位2つに以下の項目が入っている： 1.ASYNCH_IO_COMPLETION 2.IO_COMPLETION 3.LOGMGR 4.WRITELOG 5.PAGEIOLATCH_ｘ |
Top 2 |
Sys.dm_os_wait_stats |
Wait_statsの上位2つの中にIOに関するものが含まれていれば、IOボトルネックが発生している | |
|
ブロッキング |
ルール1 |
ブロックの割合 |
>2% |
Sys.dm_db_index_operational_stats |
処理のブロックが頻繁に発生している |
|
ルール2 |
ブロックプロセスレポート |
30秒 |
Sp_configure, profiler |
ステートメントのレポート | |
|
ルール3 |
行ロック待ちの平均 |
>100ms |
Sys.dm_db_index_operaional_stats |
ブロックの時間 | |
|
ルール4 |
Wait statsの上位2つに以下の項目が入っている： 1. LCK_ｘ |
Top 2 |
Sys.dm_os_wait_stats |
Wait_statsの上位2つの中にロックに関するものが含まれていれば、ブロックボトルネックが発生している |
結論として、OLTPは個々の比較的小さなトランザクション処理が大量に集まって構成されています。そして、それらはレポートタイプの処理やデータウェアハウスの処理とはリソースの使い方が全く異なっています。上記のブループリントは、リソースの使用状況を調べることを、典型的なOLTPアプリケーションの「パフォーマンス分析」を行う手法として考えています。
例えば、個々のOLTPトランザクション処理が大量に行われるということは、プランを再利用することが理想的であることを意味します。つまりCPUの使用時間はプランの再コンパイルや結合を減らすことによって削減することができます。効果的なインデックス設定、テーブル結合の軽減、および、page life expectancyを高く保つことによってIOパフォーマンスを改善できます。またインデックスの利用により、ソートを制限することができます。そしてブロッキングはインデックス・デザインとトランザクションを短くすることで発生を減らすことができます。
OLTP独特のパフォーマンス分析は、上記のブループリントで述べられているリソース使用率の観点において、パフォーマンス・チューニングの指針を提供します。具体的な値については議論があるかもしれませんが、リソースの使用率やパフォーマンスの特性に関するOLTPアプリケーションの一般的な概念は有効です。
なお、パフォーマンスに関するトラブルシュートとしてはMicrosoft TechNet （Japan）にて参考情報を公開させていただいております。上記で取り上げましたカウンタに関しても説明がありますので、興味がございましたらこちらもご参照ください。
Microsoft SQL Server TechCenter
https://www.microsoft.com/japan/technet/prodtechnol/sql/default.mspx
「SQL Server 2005 でのパフォーマンス問題のトラブルシューティング」
https://www.microsoft.com/japan/technet/prodtechnol/sql/2005/tsprfprb.mspx
コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。