OLTP のワークロードの特徴は、類似した小規模なトランザクションが大量にあるという点です。

この特徴を念頭に置いて、データベース デザイン、リソース使用状況、およびシステム パフォーマンスの重要性について調べる必要があります。OLTP アプリケーションのパフォーマンスに関して上位に挙げられるボトルネックや問題について以下で説明します。

データベース デザインに関する問題の場合

• 頻繁に使用されるクエリのテーブル結合が多すぎる。OLTP アプリケーションで結合を使用しすぎると、
  クエリの実行時間が長くなり、システム リソースを浪費することになります。一般的には、データベースを
  再設計して、頻繁に実行される操作で 5 以上のテーブル結合が行われないようにする必要があります。
• 頻繁に更新される (挿入、更新、および削除を含む) テーブルのインデックスが多すぎるため、インデックス
  メンテナンスのオーバーヘッドが余分に発生する。一般的に、OLTP データベースのデザインでは、インデックス数を機能に支障がない範囲で最小限に抑える必要があります。これは、類似したトランザクションが大量にあり、インデックス メンテナンスのコストが発生するためです。
• インデックスの欠落によるテーブル スキャンや範囲スキャンなどの大きな I/O。定義上、OLTP トランザクションは大きな I/O を必要としないので、調査する必要があります。
• 未使用のインデックスによって、ユーザーに利点のない、挿入、更新、および削除のためのインデックス メンテナンスのコストが発生している。未使用のインデックスは除去する必要があります。使用されたインデックス
  (選択、更新、または削除で使用) は、sys.dm_db_index_usage_stats に表示されます。したがって、この DMV に含まれていない定義済みインデックスは、SQL Server を最後に再起動してから使用されていません。

CPU ボトルネックの場合

• シグナル待機が合計待機時間の 25% を超える。シグナル待機と合計待機時間については、sys.dm_os_wait_stats を参照してください。シグナル待機では、実行可能なキューの CPU 待ち
  時間が計測されます。シグナル待機の数値が高い場合は、CPU ボトルネックを示しています。
• プランの再使用が 90% 未満。クエリ プランは、クエリを実行するために使用されます。(類似または同一のトランザクションに対して) 同じプランを再作成すると CPU リソースが浪費されるので、OLTP のワークロードではプランを再使用するようにします。SQL Server SQL Statistics の Batch Requests/sec と SQL Compilations/sec を比較します。プランの再使用の計算式は、“プランの再使用 = (バッチ要求数 - SQL コンパイル数)/バッチ要求数” です。プランの再使用に関するルールの特別な例外は、SQL Server 2005 SP2 ではゼロ コスト プランはキャッシュされない (再使用されない) 点です。ゼロ コスト
  プランを使用するアプリケーションは、プランの再使用を低下させますが、パフォーマンスの問題にはなりません。
• 並列待機の種類の cxpacket が合計待機時間の 10% を超える。並列処理は、実行速度のために CPU リソースを浪費します。大規模な OLTP である場合、並列クエリは、通常、OLTP のスループットを低下させるので、使用しないでください。待機統計については、sys.dm_os_wait_stats を参照してください。

メモリ ボトルネックの場合

• ページの平均有効期間が常に短い。Perfmon オブジェクトの SQL Server Buffer Manager にある Average Page Life Expectancy Counter を参照してください (このカウンタは、ページがキャッシュに保持される平均秒数を表します)。OLTP では、ページの平均有効期間は 300 (5 分) です。これを
  下回る場合、メモリ不足、インデックスの欠落、またはキャッシュのフラッシュを示している可能性があります。
• ページの有効期間の急激な短縮。OLTP アプリケーション (小規模なトランザクションなど) では、通常、ページの有効期間は安定 (または緩やかに延長) しています。Perfmon オブジェクトの SQL Server Buffer Manager を参照してください。
• 保留中のメモリ許可。Perfmon オブジェクトの SQL Server Memory Manager で、Memory Grants Pending カウンタを参照してください。小規模な OLTP トランザクションでは、通常、大きなメモリ許可は必要としません。
• SQL キャッシュ ヒット率の急激な低下またはこの値が常に低い。OLTP アプリケーション (小規模なトラン
  ザクションなど) では、通常、キャッシュ ヒット率は高くなります。OLTP トランザクションは小さいので、通常、
  (1) SQL キャッシュ ヒット率の急激な低下、(2) 常に低いキャッシュ ヒット率 (90% 未満) のいずれも発生しません。キャッシュ ヒット率の低下や値の低さは、メモリ不足やインデックスの欠落を示している可能性があります。

I/O ボトルネックの場合

• ディスクの読み取りあたりの平均秒数が長い。I/O サブシステムがキューに登録されると、ディスクの読み取りあたりの秒数が長くなります。Perfmon の論理ディスクまたは物理ディスク (PhysicalDisk またはLogicalDisk オブジェクトの Avg. Disk sec/Read カウンタ) を参照してください。通常、I/O 負荷がない場合、1 回の読み取りを完了するのに 4 ～ 8 ミリ秒かかります。多数の I/O 要求により I/O サブシステムに負荷がかかっている場合は、1 回の読み取りの完了に要する平均時間が長くなります。これは、ディスク キューの影響を示しています。(Avg.) Disk sec/Read の値が定期的に高くなることは、アプリケーションの多くで許容できます。高パフォーマンスの OLTP アプリケーションでは、高度な SAN サブシステムによって、I/O 使用率の急激な上昇に対処するうえで優れた I/O のスケーラビリティや回復性が提供されます。(Avg.) Disk sec/Read の値が高いままである場合は (15 ミリ秒超)、ディスク ボトルネックを示しています。
• ディスクの書き込みあたりの平均秒数が長い。パフォーマンス モニタの論理ディスクまたは物理ディスクを参照してください。大規模な OLTP アプリケーションのスループットは、高速なシーケンシャル トランザクション ログ書き込みに依存します。トランザクション ログ書き込みは、高パフォーマンスの SAN 環境では 1 ミリ秒以下になります。アプリケーションの多くでは、高度な SAN サブシステムの高いコストを考慮すると、Avg. Disk sec/Write の定期的な急上昇は許容できます。ただし、Avg. Disk sec/Write の値が高いままである場合は、ディスク ボトルネックの可能性があります。
• インデックスの欠落によるテーブル スキャンや範囲スキャンなどの大きな I/O。

sys.dm_os_wait_stats の待機統計の上位項目は、ASYNCH_IO_COMPLETION、IO_COMPLETION、LOGMGR、WRITELOG、PAGEIOLATCH_x などの I/O に関連しています。

ブロッキング ボトルネックの場合

• インデックス競合。sys.dm_db_index_operational_stats でロック待機およびラッチ待機について
  調べます。ロック要求およびラッチ要求と比較します。
• 行ロックまたはラッチの平均待機時間が長い。行ロックまたはラッチの平均待機時間は、ロックおよびラッチの待機時間 (ミリ秒) をロックおよびラッチの待機数で除算することによって算出されます。sys.dm_db_index_operational_stats から算出されたロックの平均待機時間 (ミリ秒) は、
  各ブロックの平均時間を表します。
• ブロック プロセス レポートに長時間のブロックが表示される。sp_configure の “blocked process threshold” の値と、Profiler の Errors and Warnings イベントにある “Blocked Process Report” の値を参照してください。
• 待機統計の上位項目が LCK_x である。sys.dm_os_wait_stats を参照してください。
• デッドロックの数が非常に多い。Locks イベントで Profiler の “Graphical Deadlock” を参照して、デッドロックに関係するステートメントを特定します。

ネットワーク ボトルネックの場合

• アプリケーションでデータベースへのラウンド トリップが多数発生し、ネットワークの待機時間が長い。
• ネットワーク帯域幅が限界まで使用されている。パフォーマンス モニタの Netwrok Interface オブジェクトの Packets/sec と Current Bandwidth カウンタを参照してください。TCP/IP フレームの場合、実際の帯域幅の計算式は、“パケット/秒 * 1500 * 8/1000000 Mbps” になります。