SQL Server / SQL Database のパフォーマンスベースライン作成と問題解決のための最適化の基礎知識

本投稿について

先日、投稿を行った 「SQL Server / SQL Database パフォーマンスチューニングのための基礎知識」 と近い内容となりますが、Microsoft Learn で 「SQL Server での運用リソースの監視と最適化」 というラーニングパスが公開されていました。

このラーニングパスでは、前回の投稿と似たようなコンセプトとなっており、SQL Server ベースの環境の運用リソースの監視や最適化について触れられているものとなっています。

この内容はパフォーマンスチューニングを実施する際のベースライン策定にとても役に立つ内容となっていましたので、前回と同様にまとめてみました。

パフォーマンスチューニングを実施する際には「どの状態と比較を行うか」がとても重要になってきます。
比較対象の状態がないと、どのリソースに負荷がかかるようになったのか、チューニングよって効果が得られたのかの比較を行うことができません。
比較対象となるベースラインの取得はチューニングの効果や通常時の状態を把握するためにとても重要となります。

Microsoft Learn のコンテンツですので Azure 寄りになっている個所もいくつかはありますが、それ以外の環境で SQL Server を実行している場合も利用することができる考え方ですので、汎用性のある内容になっている個所が多々あるかと思いますので、Azure 以外で SQL Server を動作させる場合にも役に立つのではないでしょうか。

モニタリングの仕組みなどは、実行場所によって異なり、取得されている情報も差が出てきますが、どのような情報を取得すればよいか / どのような可視化を行えば効果的かについては、実行場所に依存しない共通の考えですので。

細かな内容については、原文も合わせて参照いただければと思います。

1. パフォーマンスモニタリング (Describe performance monitoring より

##パフォーマンスモニタリングツール

• Azure で実行している環境の場合のベースライン作成で利用可能なツールの例
  • Market Place のイメージから仮想マシンを作成した場合、仮想マシンにエージェントがインストールされているため、Azure Portal で OS の基本的なメトリックを確認することができる
    • Azure Monitor にメトリックが取得され、取得されている情報を Log Analytics で確認することもできる
    • Azure Monitor では、Isgiths の機能により、追加で取得できる項目もある
    • 仮想マシンのエージェントについては、SQL Server 特有のものではなく、OS の基本的な情報についてのメトリックの取得が行われている
      • SQL Server 特有の情報については、仮想マシン内の情報から取得を行う必要がある
      • SQL Server インストール済み仮想マシンを Market Place から展開した場合、SQL Server リソースプロバイダー の機能が利用でき、データベースファイルの使用状況等をポータルから確認することができるようになる。
      • SQL Server リソースプロバイダーは手動でインストールすることも可能

SQL Server on Azure 仮想マシンのパフォーマンス監視

• Windows OS では、パフォーマンス モニター (perfmon) が標準ツールとして含まれており、容易に定期的なパフォーマンス メトリックを取得することができる。
  • perfmon は、OS の基本的な情報だけでなく、インストールされているプログラムがパフォーマンスモニターに情報を出力していれば、プログラム固有のメトリックを取得することができる
  • SQL Server をインストールしている場合は、SQL Server 用のカウンター が登録され、各種情報を取得することができる

重要なパフォーマンスメトリック

Azure メトリックで取得されている情報

• Azure Monitor のサービスには、リソースの様々なメトリックを追跡する機能が含まれている
• メトリックは一定の間隔で取得されているため、アラートを発生されるためのデータソースとして使用することができる
  • Azure Monitor のメトリックは、パフォーマンスデータの分析 / 可視化だけでなく、アラートや Azure Automation の Runbook を起動するトリガーとしても利用できる
  • アクティブなデータについては 93 日分の保存となるため、それ以上のデータについてはAzure Storage にアーカイブを検討する

古いパフォーマンスデータの再確認

• Azure Monitor のメトリックは 93 日間保存されており、時間のウィンドウを指定することができるため、特定のタイミングに遡って情報を確認することができる
• 過去の情報を確認することができ、問題の診断ができるようになっていることは重要である

重要な SQL Server のメトリック

• SQL Server をインストールした環境では、データベースエンジンのパフォーマンス関連の問題を改善するために活用できるメトリックが、パフォーマンスモニター内に多数ある
• 問題の分析に活用できるメトリックは、パフォーマンスモニターから取得可能なものもあれば、DMVからもアクセスできるものもある
  • DMV からしか取得できないメトリックもあり、例としては、sys.dm_io_virtual_file_stats のデータファイル / ログファイルのレイテンシ
  • SQL Server を介しては取得できない情報もあり、ディスクのボリュームごとのレイテンシは OS からしか取得できない
  • 片方からのみ情報を取得するのではなく、DMV / OS から取得できる情報を組み合わせることで、パフォーマンスの問題が、データベースの構造 / 物理ストレージ (物理リソース) のボトルネックに関連しているのかを、よりよく分析することができる

ベースライン メトリックの確立

• ベースラインとは、アプリケーションやサーバーのパフォーマンスの「通常時の定常的な状態」を理解するために役立つ、測定データの集まり
  • CPU 使用率のチャート / 特定のアプリケーション呼び出しからの粒度の高いレベルのパフォーマンスデータ / 複数のメトリックの複雑な集合等がある
  • ベースラインの粒度は、データベース / アプリケーションのパフォーマンスの重要度によって異なる
    • すべてを同じ粒度で取得するのではなく、重要度に応じて粒度を変更し、解析しやすいようにするという考え方もある
    • 重要度の低いものであれば、汎用的に誰でも分析ができる粒度で情報を取得しておけば、問題がないケースもある
• 時間をかけて収集したデータを保持することで、通常の状態からの変化を特定することができる
• ベースラインを確立することは重要であり、ベースラインが存在していないと、発生している問題が、通常と同じ状態化で発生しているのか、特定の閾値を超えた場合に発生するのかを判断できない
  • ベースラインが存在していないと、発生している問題が通常時と比較して問題のある状態なのかを判断することができない
  • 極論をいうとベースラインが存在しない状態では問題ではなく、通常の稼働状況として見れてしまう

SQL Server と OS のパフォーマンスの相関関係

• SQL Server のパフォーマンスと OS のパフォーマンスを相関させることが重要
  • OS の情報と SQL Server の待ち時間の統計のような SQL Server 固有のデータを併用して情報の分析を行うことで、ハードウェアやコードのボトルネックを特定することにつながる
  • SQL Server on Linux の場合は、How the SQLCAT Customer Lab is Monitoring SQL on Linux の仕組みを活用することで、OS の情報を取得することもできる
  • ベースラインを策定する際に確認を行う、パフォーマンスモニターの項目の一例としては次のようなものがある
    • Processor(_Total)% Processor Time
      • サーバー上の、全 CPU の使用率を測定したもの
      • パフォーマンス問題の調査を行う際には、基本となる情報であり、他のカウンターと組み合わせて活用を行うことで問題の特定につながることがある
    • Paging File(_Total)% Usage :
      • SQL Server が適切に構成 (max server memory / メモリ内のページのロック 等) されている場合、メモリがディスク上のページングファイルにページングされることはない
      • SQL Server 以外の他のサービスが実行されており、(SQL Server にアクセスする他のアプリケーション等が) メモリをディスクにページングしてしまっている可能性があるかを確認できる
    • PhysicalDisk(_Total)\Avg. Disk sec/Read and Avg. Disk sec/Write
      • ストレージサブシステムがどのように動作しているかの指標
      • たいていの環境では、レイテンシーは 20ms を超えてはいけない
      • Azure の Premium Storage の場合は、10ms 未満の値で性能を発揮することができる
    • System\Processor Queue Length
      • プロセッサ上で待機が発生しているスレッド数を示す
      • 0 よりも大きい値の場合は、CPU に負荷が発生しており、より多くの CPU コア数の環境にすることで、ワークロードを最適化できる可能性がある
    • SQLServer:Buffer Manager\Page life expectancy
      • SQL Server がメモリ内でどの程度の時間、ページを保持できているかを示す値
      • どの程度の値を示していれば適切という指標はない
        • 古いドキュメントでは 300 秒が適切とされていたが、これは 32 ビット時代の搭載メモリが少ない時代の古い情報である
      • 長期的な機関で情報の取得を行い、値が低下するタイミングがある場合は、非効率なクエリの実行や、外部からのメモリの圧迫 (SQL Server 以外のプロセスでのメモリ利用や、大量のデータ取得) が発生している可能性がある
        • 恒常的にメモリに収まらない大量のデータにアクセスしている / 大規模なデータベースの一貫性チェックを実行している可能性などもある
    • SQLServer:SQL Statistics\Batch Requests/sec
      • SQL Server に対してのクエリ要求の値となる
      • SQL Server がどの程度ビジーな状態 (新しい要求を効率的に受け付けられていない) となっているかの評価に利用することもできる
      • CPU の使用状況と組みあわせて利用することで、作業負荷やベースラインをより理解することができる
    • SQLServer:SQL Statistics\SQL Compilations/sec and SQL Re-Compilations/sec
      • クエリの実行計画をコンパイル / リコンパイルしなければならない場合に値が更新される
        • リコンパイルについては明示的に RECOMPILE ヒントを使用している可能性も考えられる
        • 大量のアドホッククエリによるメモリ圧迫が発生し、コンパイル回数が高くなっている可能性もある
  • 上記の項目は一例であり、特定のパフォーマンス問題の解析には、より多くの情報を調べる必要がある

待機事象 (Wait Statistics)

• クエリの実行により、スレッドが実行されており、利用できないリソースによる待機が発生した場合、SQL Server では、これらのメトリックを sys.dm_os_wait_stats から追跡することができる
• このデータはベースラインとなる性能を理解するうえで重要であり、クエリの実行とハードウェアのリソース制限の両方で発生している性能問題を特定するのに役に立つ
• 適切な待機タイプトそれに対する解決策を特定することは重要である

Azure Intelligent Insights

• Azure SQL Database を使用している場合は、組み込みのベースラインのパフォーマンス収集機能として、Intelligent Insights を使用することができる
  • クエリのパフォーマンスを分析することができる Azure SQL Database のコンポーネント
• クエリストア で取得されているデータを基にして構築が行われている
  • 実行時統計 / 実行計画の履歴など、クエリのパフォーマンスに関連するメトリックを自動的に収集し、長期間にわたって保存することができる
  • Azure ポータルに統合されており、過去にさかのぼってクエリの情報を確認することもできる
  • 保存オプションがあり、Azure BLOB ストレージに対して XEL ファイル (拡張イベント形式のファイル) を長期間保存 / イベントハブに連携 / Log Analytics に保存することができる

Azure Log Analytics の SQL Insights

• Log Analytics にデータを保存することで、Azure SQL Analytics のダッシュボードを使用することができる
• SQL Analytics により、Log Analytics に格納されている情報を容易に可視化することができる
• 複数のサブスクリプションにまたがる、SQL Database の環境の監視で活用することができる

問題のあるクエリの特定

• どのクエリが最もリソースを消費しているかを特定することはデータベースパフォーマンスチューニングの第一歩となる
• 古いバージョンの SQL Server では、この情報を取得するためには、大規模なトレースと複雑な SQL スクリプトが必要であったが、SQL Server 2016 以降ではクエリストア、Azure SQL Database では、Query Performance Insight を使用することで、容易の情報を取得することができるようになった
• クエリ調査の観点には次のようなものがある
  • 長時間実行されているクエリ
  • リソース (CPU / ログ I/O / データ I/O / メモリ) の消費量が多いクエリ

2. パフォーマンスの問題の原因を探る (Explore causes of performance issues より)

クエリストア

• クエリストア は、SQL Server に対して実行されているクエリについてのフライトデータレコーダーとして利用することができる
  • クエリのパフォーマンス情報を収集し、恒久的に保存、集計を行っている
• SQL Server 2016 より前は、クエリの情報は一過性のものであるか、収集が行われていなかったが、クエリストアが使用できる場合は、特定のユーザーデータベーで実行されたクエリの実行時間の情報 (実行時間 / 論理 I/O / CPU 使用率など) を自動的にキャプチャすることができる
• 各実行の推定実行計画についてもキャプチャされるため、パフォーマンスが低下した際の実行プランを迅速に確認することができる
• キャプチャされるデータには次の 2 種類がある
  • クエリ自体にしてのデータ (実行回数 / 使用された実行計画 / クエリテキスト)
  • クエリの各実行に対してのランタイム統計

クエリストアのレポート

• SSMS ではクエリストアの情報のレポート機能があり、収集されている情報を素早く解析することができる
• レポートは次のメトリックでフィルタすることができる
  • CPU 使用時間 (ms) / 実行時間 / 論理読み取り / 論理書き込み / 物理読み取り / CLR 時間 / 並列度 (DOP) / メモリ消費量 / 行数 / 使用ログメモリ / Tempdb のメモリ使用量 / 待機時間
  • 平均値 / 最大 / 最小 / 標準偏差 / 合計
  • 取得時間
• クエリストアの情報は過去に遡って鵜人することができ、SSMS には次のようなレポートが内蔵されている
  • 後退したクエリ
    • 対象期間で実行時のメトリックが低下したクエリが表示される
    • サーバー構成やデータベースのスキーマの軽微な変更を実行した場合の、変更の影響を評価する際に活用できる
  • 全体のリソース消費量
    • データベース内で最も、影響を与えているクエリを観測することができる
    • リソースを消費するクエリのレポートにリンクされており、実行計画の詳細を確認することができる
  • リソースを消費するクエリの上位
    • データベース内で最も影響のあるクエリのテキストと実行計画が表示される
    • クエリに複数の実行プランがあるか、複数の実行プランが存在する場合、各プランのパフォーマンスにばらつきがあるかを確認することができる
  • 強制されたプランを持つクエリ
    • プランの強制とプランの強制失敗 (強制実行のプランが実行されなかった状況) を確認することができる
  • 高バリエーションのクエリ
    • 実行時間の変動が大きいクエリを参照することができ、メトリックの情報でソートすることができる
  • クエリ待機統計
    • 待機時間の統計を集計して表示し、待機時間の多かったクエリをドリルスルーして詳細情報を確認することができる
    • 待機事象は集計されており、sys.dm_os_wait_stats で確認できる詳細な情報より粒度は大きくなっている
  • 追跡したクエリ
    • query_id / クエリテキストでフィルタリングすることができ、特定のクエリのパフォーマンスと実行計画を確認できる

クエリストアのパフォーマンスへのオーバーヘッド

• どのようなシステムでもデータの収集を行うと CPU / メモリ / ディスク使用率にデータ収集のコストが発生する
  • Observer Overhead と呼ばれる
• クエリストアでもオーバーヘッドは発生するが、影響を最小限に抑えるように設計 されている
  • 新しいクエリを実行 / 既存のクエリを実行した際にメモリ上にデータを書き込む
  • DATA_FLUSH_INTERVAL の設定の間隔 (デフォルト 15 分 / データベース単位に設定を変更可能) で、ディスクにフラッシュする頻度を調整することで、ディスク負荷を軽減させている
  • データはデータベース内に保存することで永続化を行うが、最大サイズ (デフォルト 100MB) を指定することができる
    • ユニークなアドホックなクエリが多い環境では、データ量が多くなるため、サイズについては適宜調整を検討する
    • 最大サイズを超えると、サイズの変更を手動で行うか、クリーンアップが行われるまでは読み取り専用モードとなる
  • クリーンアップはサイズ / 時間ベースで実行することができ、デフォルトでは 30 日が設定されている
  • クエリキャプチャモードも性能影響への要因となる
    • SQL Server 2019 / SQL Database では、「Auto」がデフォルトの値となっており、実行頻度の低いクエリについては、取得対象とならない
    • それ以外のバージョンでは ALL が設定されており、すべてのクエリが取得対象となる
      SQL Server 2019 では取得のフィルタを設定することができ実行時間等のメトリックを使用して、クエリのキャプチャ対象を調整することができるため、アドホッククエリが多い環境下では活用を検討する

クエリストアによるプランの強制

• クエリストアには情報の収集のほかに、実行プランを強制することができる機能がある
  • SQL Server 2017 で導入された児童チューニング機能でも、クエリストアのプラン矯正が活用されている
• パフォーマンスの交代が発生した際に、クエリの最後に実行されたパフォーマンスの良いクエリを強制的に実行するように補正が行われる
• プランの強制は、パフォーマンスの問題を迅速に緩和させることができるが、実行計画が変動した原因については調査を行い、インデックスの追加やクエリの一部最適化の検討は行う

SQL Server でのブロッキングとロック

• RDBMS の特徴の一つにロックがあり、ACID モデルの原子性 / 一貫性 / 分離性の特性を維持するためには、ロックは不可欠であり、RDBMS は一貫性と分離性に違反するアクションをブロックする
• データベースエンジンはクエリの影響を受けるテーブルの論理的一貫性を保護するためにロックの機構を提供する
• SQL Server では、あるプロセスが特定のリソース (行 / ページ / テーブル / データベース) に対して、ロックを保持し、2 番目のプロセスが同じリソースに対して 互換性のないロックタイプのロック を取得しようとした場合にブロッキングが発生する
  • 通常、ロックは非常に短い期間のみ保持され、ロックを保持しているプロセスがロックを解放すると、ブロックされていたプロセスがロックを取得してトランザクションを完了させることができる
• SQL Server はトランザクションを正常に完了させるため、必要な最小のデータに対してロックを取得し、最大の同時実行性を可能としている
  • SQL Server が一つの行をロックしている場合、テーブル内に他の行は他のプロセスで使用できるため、多くの同時実行の作業を行うことができる
  • ロックにはメモリリソースが必要となり、一つのプロセスが一つのテーブルに何千ものロックを個別に取得することは費用対効果が高くなく、SQL Server は同時実行性とコストのバランスを取ろうとする
  • 使用されている手法の一つに、ロックエスカレーションがあり、SQL Server は一つのステートメントで一つのオブジェクトに対して 5,000 行のロックを行う必要がある場合、複数の行ロックを一つのテーブルロックにエスカレーションする
    • 他のプログラムで取得されているロックが起因して、ロックエスカレーションができない場合は、エスカレーションをあきらめ、再度ロックを取得したタイミングでエスカレーションを試行するという動作となるため、大量のロックを取得したからと言って、必ずロックエスカレーションが成功するとは限らない
    • ロックエスカレーションを使用しないように設定 を行うこともできる (ALTER TABLE の LOCK_ESCALATION でも制御できる)
• 通常、ブロッキングは発生しても瞬間的なものであるが、すぐに解決しないような場合には問題となる
• ブロッキングにより引き起こされるパフォーマンス問題には、次の 2 種類がある
  • プロセスがあるリソースのロックを解放する前に、長時間該当のリソースのロックが保持され、他のプロセスがブロッキングにより、クエリパフォーマンスと同時実行性を低下させる
  • 取得されたリソースが自動的には解放されず、問題を解決するためには、管理者が介入する必要がある
• 他のブロッキングのシナリオとしてはデッドロックがある
  • トランザクションがリソースに対してのロックを保持
  • 別のトランザクションが他のリソースに対してロックを保持
  • その後、それぞれのトランザクションが他のトランザクションで保持されているロックを取得しようとした場合に、どちらのトランザクションも完了できないため、無限の待ち時間が発生し、デッドロックとなる
  • SQL Server ではでddロックの検出メカニズムがあり、デッドロックを緩和させるために、上記のような状態になった場合は、ロールバックする必要のある作業量の最も少ないトランザクションを KILL する
    • KILL が行われたトランザクションは、デッドロックの被害者 (VICTIM) として報告される
  • デッドロックはデフォルトで有効になっている system_health の拡張イベント で記録されており、発生後にログから内容を確認できる
• SQL Server / Azure SQL Database は、デフォルトでは、オートコミットモードで実行されており、ステートメントによって行われた変更は、完了時に自動的にデータベースのトランザクションログに書き込まれる
  • コミットされたデータは、非同期的にデータファイルに反映される
  • 開発者が明示的にトランザクションを開始することもでき、トランザクションを開始した際には取得されるロックと保持期間については留意する必要がある
• データベースの同時実行を助ける仕組みの一つとして、行のバージョニング による分離レベルがある
  • 使用した場合、変更された各行を tempdb に保持し、データの変更が発生している最中に、他のクエリからの読み取りのブロックを防ぐことができる (同時実行性が向上する)

分離レベル

• SQL Server では、いくつかの分離レベルが提供されており、データの一貫性と正確性を保証するため、必要な分離レベルを使用することができる
  • 分離レベルはロックが保持される時間の長さに影響を与える
  • READ UNCOMMITTED
    • 最も低い分離レベル
    • ダーティーリードが許可されるため、他のトランザクションでコミットされていないデータが参照される可能性がある
  • RED COMMITTED
    • SQL Server の既定の分離レベル
    • 他のトランザクションによって変更されていないデータを読み取ることができる
    • SELECT が実行されると、すぐに読み取りロックも解放される
  • REPEATABLE READ
    • 選択されたデータに対して取得された読み取り / 書き込みのロックをトランザクションが終了するまで保持する
  • SERIALIZABLE
    • トランザクションが完全に分離される最も高い分離レベル
    • 読み取りと書き込みは選択したデータで取得されトランザクションが終了するまで解放されない
  • READ COMMITTED SNAPSHOT
    • SQL Database の既定の分離レベル
    • 読み取り操作には、行やページのロックは取られず、各操作ごとにクエリの開始時に存在していたデータをトランザクションとして一貫したスナップショットとして取得を行う
    • ユーザーが OLTP データベースに対して頻繁にレポートクエリを実行している場合に、整合性のあるデータをブロッキングを発生させることなく取得する場合に効果的に活用できる
  • SNAPSHOT
    • トランザクションレベルで読み取り一貫性を提供できる
    • 更新競合に対して脆弱であり、このレベルで実行されているトランザクションが別のトランザクションによって変更されたデータを読み取ると、スナップショットのトランザクションによる更新が終了してロールバックされる
      • READ COMMITTED SNAPSHOT ではこの問題は発生しない
• SQL Server で設定可能な分離レベルはセッションレベルであり、グローバルにデフォルトの分離レベルを変更することはできない

ブロッキングによる問題のモニタリング

• sys.dm_tran_locks と sys.dm_exec_requests を組み合わせることでロックに関しての情報を確認することができる
• ブロッキングの問題を監視する良い方法は 拡張イベント を使用して継続的にモニタリングを行うことである
• ブロッキングの問題は、一般的には次の 2 つに分類される
  • トランザクションの設計が悪い
    • 単一のトランザクションでより多くの作業を実行する / リンクサーバー接続を使用する分散トランザクションにより、予測不可能なパフォーマンスにつながっている
  • スキーマの設計が原因で発生
    • インデックスが欠落している列の更新のような不十分な設計の更新系クエリが実行されている

データファイルの断片化

• ストレージ上の断片化は、関連するデータの集合がディスク上に連続しておらず、分割して保存されているときに発生する
  • 連続していないデータはハードディスクを使用したシステムで問題となるケースが多かった
  • SSD では、OS レベルの断片化の影響は軽減しているが、SQL Server のデータファイル観点では、まだ影響を与える可能性がある
    - ページの連続性の断片化の影響は軽減しているが、ページ内の空き領域の発生による断片化の影響は　SSD でも受ける
• 断片化は、クラスター化 / 非クラスター化インデックスのインデックスキーの値に基づくインデックスの論理的な順序が、ページ内の物理的な順序と一致しないページがある場合に発生する
• 挿入や更新操作を行う際に新しい値を追加するためのスペースがなくなるとページが分割され、データを取得するための追加の I/O が必要となる
  • ページ分割が発生すると、特にスキャン操作のパフォーマンスが低下する可能性がある
  • 断片化は FILLFACTOR を設定することにより、新規投入データの空き領域を調整することで軽減することができる

3. 最適なパフォーマンスのために SQL Server リソースを設定 (Configure SQL Server resources for optimal performance より)

SQL Server 仮想マシンのために Azure ストレージを最適化

• ストレージのパフォーマンスは、データベースエンジンのような I/O Heavy のアプリケーションにとっては重要な要因となる
• Azure では様々なストレージがあり、各ストレージは次のような利用ができる
  • BLOB Storage
    • SQL Srever の URL へのバックアップ機能を使用し、データベースのバックアップを保存する場所として使用できる
    • ユーザーデータベースのファイルの保存場所 としても利用できる
  • File Storage
    • FCI (Failover Cluster Instance) のストレージとして使用することができる
  • Disk Storage
    • SQL Server のデータベースのファイルを保存する際に最もよく使用される

Azure Managed Disk

• ディスクに応じて性能の上限があるため、オンプレミス環境から移行をなう場合、既存環境でパフォーマンスモニターから、ディスクの性能を取得し、どの程度のディスク性能が必要となるかを確認することは重要である
  • SQL Server:Resource Pool Stats からピーク時の DB のディスクアクセス状況を取得することも重要であり、この情報からピーク時の性能を読み取ることができる
• 仮想マシンのサイズに応じて、IOPS の制限もあるため、ディスクだけでなく、仮想マシンの性能上限についても注意する
• Managed Disk には次の 4 種類がある
  • Ultra Disk
    • ミッションクリティカルなデータベースの高 I/O のワークロードを低レイテンシーでサポートする
  • Premium SSD
    • クラウドで実行されているほとんどのデータベースワークロードに有効であり、ハイスループットと低レイテンシーを実現できる
    • 通常1 桁ミリ秒の応答時間となるが、Ultra Disk と比較してコストが低く、設計の柔軟性が高い
    • リードキャッシュをサポートしているためディスクへのアクセス回数を減らすことで、読み取りの多いデータベースワークロードでメリットを得ることができる
  • Standard SSD
    • 少量の I/O の実行で、予測可能なレイテンシーを必要とする、使用頻度の低い開発/テストワークロードや Web サーバーに適している
  • Standard HDD
    • アクセス頻度の低いバックアップやファイルストレージに適している
• 一般的な本番環境のワークロードで利用する SQL Server では、Ultra Disk または Premium SSD を使用する

スループットを最大限に発揮するためのディスクのストライピング

• Azure でディスクから多くのパフォーマンスとボリュームを得る方法の一つとして複数のディスク間でデータをストライプ化する方法がある
  • Ultra Disk の場合、IOPS / スループット / 最大サイズを単一のディスク上でスケーリングできるのでこの方法は適用されない
  • Premium SSD でスループットを向上させるために有効な方法である
• VM に必要なディスクを接続し、記憶域スペース の機能を使用してプールを作成してディスクを利用する
  • プールを複数のディスクで構成することで、複数ディスクの IOPS を束ねて使用することができる

SQL Server ストレージ構成のベストプラクティス

• データとトランザクションログのファイルを別のボリュームに作成する
• データファイルのボリュームの読み取りキャッシングを有効にする
• ログファイルのボリュームのキャッシュは無効にする
• ワークロードのピーク時の対応を考慮し、VM のストレージの構成で必要となるスループットを 20% 追加した形で計画をする
• TempDB のファイルには、D ドライブ (ローカル接続の SSD) を使用する
• データファイルの瞬時初期化 を有効にして、ファイル拡張が発生した場合の影響を軽減させる
• デフォルトトレースとエラーログのディレクトリをデータディスクに移動
• 1 ミリ秒以下のストレージのレイテンシーが必要となるワークロードでは Premium SSD ではなく、Ulta Disk の利用を検討

Azure 仮想マシンリソースプロバイダー

• Azure の Marketplace で異教されている SQL Server インストール済み仮想マシンをデプロイする際には、デプロイ時にストレージ構成を柔軟に構成することができる
• リソースプロバイダーでは、ローカル SSD を使用した TempDB の作成もサポートしており、必要となる設定がタスクスケジューラーで自動的に行われる

仮想マシンのリサイズ

• Azure の仮想マシンには様々なサイズがあり、SQL Server のワークロードでは、利用可能なメモリサイズと IOPS が重要な要素となる
  • コア数は必要ないが、メモリサイズが必要となる場合には、制限付き vCPU 対応の VM を使用することでコア数を抑えることができる
  • SQL Server はコアライセンスのため、制限付き vCPU 対応の仮想マシンでライセンス費を抑えて、大きいサイズのメモリを使用することもできる
• SQL Server ワークロードでは、汎用 / メモリ最適化の VM が使用される機会が多い
  • 大規模なワークロードを実行する場合はメモリ最適化
  • 一般的なワークロードでは汎用
• 仮想マシンがサポートする IOPS も重要な要素であるため、ディスク単体の性能だけでなく、仮想マシンが発生させられる IO についても考慮する
• 仮想マシンは、構築後のサイズ変更をサポートしているが、再起動が発生することを考慮する
  • 仮想マシンの種類によっては、割り当てを解除してからリサイズする動作になることもあるため、再起動に数分かかる可能性がある

データベースストレージの最適化 (TempDB)

プロポーショナルフィルの考慮

• データファイルのサイズが不均等になっている (File #1 : 10GB / File #2 : 100GB) 場合、SQL Server は空き領域の多いデータファイルに頻繁に I/O を発生させるため、使用されるデータファイルに偏りがある場合は、I/O が不均等になる

TempDB 最適化のための設定

• SQL Server 2016 では、セットアップ時に CPU のコア数を検出し、最大で 8 個まで均等なサイズのデータファイルを自動的に作成する
  • SQL Server 2016 より前の環境を使用している場合は、このデータファイルの分割数を参考に適切なデータファイルの設定を行う
  • tempdb に負荷の高い、ワークロードによっては、データファイルの数を増やすことも検討
• tempdb は一時テーブルだけでなく、クエリの中間結果や行のバージョン管理等でも使用されるため、可能な限り低レイテンシーなディスク (仮想マシンの場合は D ドライブ) に配置を行う
• SQL Server 2016 より前のバージョンでは、トレースフラグ 1118 / 1117 を設定し、同時実行性の向上を行う

SQL Server のリソースのコントロール

• 一つのアプリケーションのデータベースのみをホストするケースもあるが、多くのサーバーでは異なるパフォーマンス要件と異なるピークワークロードのサイクルを持つ複数のアプリケーションのでーてベースをホストしている
• 異なるワークロードで使用されるリソースのバランスを取る方法の一つとして、SQL Server 2008 で導入された リソースガバナー を使用することができる
  • リソースガバナーは SQL Server と Azure SQL Managed Instance で使用することができる
  • アプリケーションからの要求で使用できる CPU / 物理 I/O / メモリリソースを制御することができる
  • セッションのワークロードを細分化する設定によって、どのワークロードグループを使用するかの制御を行う

リソースプール

• サーバー上で利用可能な物理リソースを示す
  • リソースガバナーが有効になっていない環境でも、default / internal という二つのプールを持っている
    • internal は SQL Server の重要な機能で使用されるため、制限をすることはできない
    • default は明示的に指定しているプール以外で利用されるリソースを制御することができる
  • internal 以外のプールでは次のような制御が可能
    • Min / Max CPU 使用率
    • CPU 使用率のキャップ
    • Min / Max メモリ使用率
    • NUMA ノードのアフィニティ
    • ボリュームあたりの Min / Max IPS
    • Min / Max CPU 使用率以外のリソース制御は、ハードリミットとなっており、これを超えて利用することはできない
    • CPU 使用率については、利用可能な CPU のサイクルがある場合は、上限を超えて利用し、他のワークロードが実行されている場合に制限が行われる
• プールへの変更は既存のセッションには影響を与えない
  • 長時間実行されているプロセスでリソースを過度に使用しているための対応として、設定の変更を行っても、既に実行されているセッションには影響を与えない

ワークロードグループ

• 分類に基づいたセッション要求のためのコンテナ
• リソースプールと同様に default と internal の 2 種類の組み込みグループがあり、各ワークロードグループは一つのリソースプールに所属することになる
  • リソースグループは複数のワークロードグループを設定することができる
• 分類によって特定のワークロードグループに割り当てられない場合は default が割り当てられ、default のワークロードグループは default のリソースプールに割り当てられているリソースを使用する

分類関数

• SQL Server に接続された時点で分類関数が実行され、各接続を指定されたワークロードグループに分類する
  • 分類できない / 存在しないグループに割り当てを行った場合は、default のワークロードグループに割り当てられる
• 分類関数は接続ごとに実行されるため、関数自体の実行効率についても考慮する必要がある
  • 複雑な関数はユーザーのログインパフォーマンスに影響を与える可能性がある

リソースガバナーの使用例

• 複数のデータベースが単一の SQL Server インスタンスを共有しているマルチテナントのシナリオで使用され、すべてのユーザーのパフォーマンスを安定させる
  整合性チェックやインデックスの再構築などのメンテナンスで使用するリソースを制限し、メンテナンス時にユーザーのクエリに十分なリソースが割り当てられることを保証する

4. パフォーマンスのためのデータベースの設定 (Configure databases for performance より)

データベーススコープの構成オプション

• SQL Server では、以前からデータベースレベルの構成オプションが存在していたが、複雑な機能がデータベースに導入されるにつれて、より多くのオプションが追加されてきた
  • データベースレベルの構成オプションには 2 種類がある
    • ALTER DATABASE SCOPED CONFIGURATION
      • MAXDOP / レガシー基数推定 / 最後の実際の実行プラン / アドホックワークロードの最適化
    • ALTER DATABASE
      • データベースの復旧モデル / 自動チューニングオプション / 統計情報の自動作成, 自動更新, 非同期更新 / クエリストアのオプション / スナップショット分離

データベースの互換性レベル

• 各データベースには個別に互換性レベルを設定することができ、データベースのクエリオプティマイザの動作を制御することができる
• SQL Server をアップグレードする際に互換性レベルの設定を管理することで、クエリが旧バージョンと同等の実行計画で実行されるように制御できる
  • Microsoft は古い互換性レベルでの実行を長期間サポートしている
• Intelligent Query Processing の新しいパフォーマンス向上の機能の多くは、互換性レベル 140 または 150 でしか利用できないため、これらの機能を活用するためには、新しい互換性レベルへの移行を検討する必要がある

Intelligent Query Processing

• 互換性レベル 140 と 150 では多くの新機能が導入され、以前のアンチパターンであったものが最適かされている
  • 適応型クエリ処理
    • 互換性レベル 150 で自動的に有効になる
    • クエリの実行コンテキストに基づいて、クエリ処理を動的に最適化する多くのオプションが含まれている
      • 適応型結合
        • ハッシュとネステッドループの結合の選択を、結合に入力される行数に基づくように延期して決定を行う
        • 現状はバッチモードでのみ実行できる
      • インターリーブ実行
        • マルチステートメントテーブル値関数 (MSTVF) をサポートする
        • SQL Server 2017 以前では、MSTVFは SQL Server のバージョンに応じて、1 または 1,000 行を固定の推定値として使用しており、関数が多くの値を返す場合、この推定値が最適でない実行計画につながる可能性があった
        • インターリーブ実行では、プランの残りの部分がコンパイルされる前に、MSTVF から実際の行数が生成される
      • メモリ許可フィードバック
        • SQL Server では統計情報から推定された行数に基づいてクエリの初期計画のメモリ許可を生成する
        • データの偏りが激しい場合、行の推定値が過大または過少になる可能性があり、許可されたメモリが処理行数に適していない (過少 / 過大) 可能性があり、パフォーマンスの低下 (メモリの不足により tempdb が利用される)/ 同時実行性の低下 (過大なメモリが確保され、メモリの許可待ち) につながっていた
        • メモリ許可フィードバックを使用すると、クエリに付与されるメモリの量の増減をコンパイルが発生しなくても調整されるようになる
  • テーブル変数の遅延コンパイル
    • MSTVF と同様にテーブル変数も実行計画では 1 行の固定行数の推定値が利用されており、これにより最適な実行計画が生成されない可能性があった
    • SQL Server 2019 ではクエリの最初のコンパイル時にテーブル変数が分析され、実際の行数を持つようになっ
  • 行ストアのバッチモード
    • バッチモードは SQL Server 2012 の列ストアインデックスと合わせて導入され、当初は列ストアインデックスでのみ利用可能だった
    • SQL Server 2019 では行ストアでもバッチモードで処理ができるようになり、計算や集計に多大な CPU コストが発生するクエリを最適化できるようになった
  • スカラー UDF インライン
    • 古いバージョンの SQL Server では、スカラー値関数は反復的に実行され、行単位の処理となっており、並列化もできないためパフォーマンスを低下させる要因となった
    • インライン化により実行計画はユーザー定義関数の代わりに、スカラー副問い合わせに変換され、この変換によりパフォーマンスが大きく向上する可能性がある
  • 近似件数の取得
    • 大きなテーブルに対しては、正確な件数を集計することはコストが高い処理であった
    • 近似件数の取得は、行をグループ化することで、個別に件数を取得するよりも高速に動作する
    • 近似の件数のため、97% 程度の正確性で 2% のエラー率となることを保証している

5. SQL Server のパフォーマンスに関連するメンテナンスタスク (Describe performance-related maintenance tasks in SQL Server より)

インデックスのメンテナンス

• 特定のテーブルやインデックスをスキャンする区営rは、適切なインデックス作成に加え、インデックスのメンテナンスの実施がパフォーマンスの最適に保つ重要なポイントとなる
• クエリオプティマイザはインデックスの統計情報を利用し、最適な実行計画を構築しようとする
  • 通常、実行計画は十分に適しているものであるが、最適なインデックスと統計情報尾w持っていれば、任意の実行計画が最適な効率で実行できるようになる
• データベース内のデータは時間の経過とともに変化するため、インデックスのメンテナンスを定期的に行う
  • データの更新頻度に応じてインデックスのメンテナンス戦略を変更する

再構成と再構築

• インデックスの断片化は、インデックスページ内の論理的な順序が物理的な順序と一致しない場合に発生する
  • ページの順序は、UPDATE / DELETE / INSERT などの処理でデータ修正が発生することでくるってしまう可能性がある
  • 断片化が発生すると、インデックスページ内のポインタで参照されているデータを見つけるために、追加の I/O が発生しパフォーマンスの問題となる可能性がある
• インデックスの再構成は、インデックスのリーフレベルをデフラグするオンライン操作となる
  • リーフページの物理的な並び替えと FILLFACOTOR の値に基づいてインデックスページの圧縮を行う
• インデックスの再構築は、コマンドのオプションやエディションに応じて、オンラインまたは、オフラインの操作となる
  • オフラインの再構築処理では、インデックス自体を削除して再作成を行う
  • オンラインのインデックス操作では、既存のインデックスと並行して新しいインデックスが構築され、新しいインデックスが構築されると、既存のインデックスは削除され、新しいインデックスは古いインデックス名にリネームされる
    • 新しいインデックスが既存のインデックスと並行で構築されるため、オンラインでは追加のディスク領域が必要となる
• インデックスのメンテナンスの一般的なガイダンスは次のようになる
  • 5%～30% : インデックスの再構成
  • 30% 以上 : インデックスの再構築
  • この数値は一般的な推奨事項であり、ワークロードやデータによって、実行のタイミングは異なる
• SQL Server 2017 から、再構築操作を再開できるようになり、再構築操作が与える影響をより柔軟に調整することができるようになった
  • SQL Server 2019 からは DOP を制御する機能も追加されデータベース管理者がより細やかな制御が可能となった

SQL Server on Azure VM

• Azure 仮想マシン上で、SQL Server がインストールされている場合、仮想マシン内の SQL Server エージェントや OS のタスクスケジューラーなどのスケジューリング機能を利用することができる
  • これらのツールを使用することで定期的にインデックスの断片化を解消する処理をスケジューリングで実行するためのカスタムのジョブを起動させることができる

Azure SQL Database

• SQL Database では、SQL Server エージェントやタスクスケジューラーの機能は使用できないため、定期的な断片化の解消には、次にような機能を用いる必要がある
  • Azure Automation のランブック
  • Azure 仮想マシンの SQL Serve エージェント / タスクスケジューラーから SQL Database にクエリを実行
  • Azure SQL Elastic Job

Azure SQL Managed Instance

• SQL Server エージェントを使用することができるため、SQL Server のようにクエリの定期実行を標準機能で実現することができる

統計情報のメンテナンス

• SQL Server のパフォーマンスチューニングを実施するためには、統計情報の重要性の理解が必要
• 統計情報はデータベース内に BLOB として格納され、テーブルまたはインデックス付きビューの一つ以上の列内のデータ値の分布に関する統計情報を含む
• 統計情報には列内のデータの分布に関する情報が含まれており、クエリオプティマイザは、列とインデックスの統計情報を使用してクエリが返すと想定される行数であるカーディナリティを決定する
  • カーディナリティの推定値はクエリオプティマイザが実行計画を生成する際に使用される
  • カーディナリティの推定値はオプティマイザが要求されたデータをどのように取得するのかを決定するのにも利用される

自動チューニング

• 実行計画はスキーマの変更 / インデックスの変更 / 統計情報の更新につながるデータの変更により時間の経過とともに変化していく
• 実行計画が与えられたクエリに最適でない場合パフォーマンスの低下が発生する
• クエリストアには、指定されたjっ港プランを強制するようにプランの補正を行う機能がある

自動チューニング機能

• SQL Server 2017 で自動チューニングの機能が導入され、パフォーマンスメトリックに対して、実行プランの自動修正を可能とした
  • SQL Database では上記の自動チューニングのほかに、インデックスチューニング機能を利用することもできる

プランの自動修正

• クエリストアを使用すると、データベースエンジンはクエリの実行プランのパフォーマンスが後退した場合に、それを識別し、最後に実行された良好なプランを適用するように補正することができる
  • クエリストアにより、プランの自動的な修正 (強制) が行われているかについては SSMSから容易に確認することができる
  • 以前に実行されたプランのエラー数が推奨されるプランよりも多い場合 / 推定 CPU 時間の変化が 10 秒よりも多い場合 / 前の実行プランよりも効率の良いプランの実行が継続している場合は、推奨されているクエリの実行プランを自動的に強制する
  • クエリ 15 回実行すると、最後に実行されていた、良好なプランに戻る
• プランの強制が自動的に発生した場合、データベースエンジンは最後に実行された良好なプランを適用し、パフォーマンスの状態を監視する
  • 強制されたプランが前のプランよりもパフォーマンスが低い場合、強制は解除され、新しいプランがコンパイルされるように動作する
  • 強制されたプランが、以前のプランよりも優れたパフォーマンスを維持しいている場合は、再コンパイルが行われるまで強制された状態となる
• sys.dm_db_tuning_recommendations から推奨状況を確認することができ、自動チューニングの設定は sys.database_automatic_tuning_options から確認できる

自動インデックス管理

• SQLDatabase では、インデックスの自動チューニングを実行することができ、時間の経過とともに、既存のワークロードを学習し、適切なインデックスを追加 / 削除についての推奨事項を提供する
• 自動チューニングオプションを有効にすることで機能を利用することができる
  • インデックスの自動作成については、パフォーマンスの問題が発生しないように、新しいインデックスを作成するタイミングが監視され、既存のワークロードに影響を与える可能性がある場合は、インデックスの作成を遅らせる
  • 新しく作成したインデックスが性能の改善につながらない場合には、インデックスは削除されパフォーマンスの低下が発生しないように考慮された動作となる
  • インデックスの削除でも同様の考慮が行われ、インデックスが削除されクエリのパフォーマンスが著しく低下した場合は、削除されたインデックスの再作成が行われる