データベース構成の推奨
クエリを調整したり、コンカレンシーのトラブルシューティングを行ったりするには、その下に適切なインフラストラクチャが必要です。 Azure SQL Database では、2 つのリソース モデルと複数のサービス レベルが提供されます。 選択した組み合わせによって、ワークロードのコンピューティング、メモリ、I/O、ストレージの上限が設定されます。 選択が少なすぎると、パフォーマンスが低下します。 あまりにも多くを選択し、予算を無駄にする。
リソース モデルの比較
Azure SQL Database では、 仮想コア と DTU の 2 つのリソース モデルがサポートされています。 リソースの測定と課金は異なります。そのため、区別を理解することは、最初から適切な選択を行うのに役立ちます。
仮想コア モデルを使用すると、仮想コア、メモリ、ストレージを直接制御できます。 ハードウェアの世代、サービス レベル、コンピューティング レベルは個別に選択します。 オンプレミスの SQL Server から移行する場合、このモデルは物理的な CPU とメモリにクリーンにマップされるため、容量計画がより簡単になります。 また、コスト削減のための予約インスタンスの価格と Azure ハイブリッド特典もサポートされています。
DTU モデルは、CPU、メモリ、および I/O を、データベース トランザクション ユニット (DTU) と呼ばれる 1 つのユニットにバンドルします。 DTU ベースのレベル (Basic、Standard、Premium) では、構成済みのリソース パッケージが提供されます。 このモデルは、個々のリソース ディメンションをきめ細かく制御する必要がない場合に機能します。
ほとんどの新しいデプロイでは、仮想コア モデルをお勧めします。 リソース制限がより高くなり、スケーリングの粒度がより細かく、価格設定の柔軟性が向上します。
仮想コア モデルのサービス レベルについて
仮想コア モデルには、General Purpose、Business Critical、Hyperscale の 3 つのサービス レベルがあります。 各層は異なるアーキテクチャを使用します。これは、ストレージの種類、I/O のパフォーマンス、可用性に影響します。
General Purpose は 、コンピューティングとストレージを分離します。 データ ファイルが Azure Blob Storage にある間、データベース エンジンはコンピューティング ノードで実行されます。 ストレージの待機時間は、通常、5 ミリ秒から 10 ミリ秒の間です。 このアーキテクチャは、予算に優しい価格を提供し、ほとんどのビジネス ワークロードに適しています。 コンピューティング ノードが失敗した場合、Azure Service Fabric はプロセスをスペア ノードに移動し、リモート ストレージ ファイルを再アタッチします。
導入から e コマース アプリケーションを検討してください。 通常の営業時間中は、General Purposeが問題なく注文量を処理します。 ただし、休日のフラッシュ販売中、5 ミリ秒から 10 ミリ秒の I/O 待機時間は、チェックアウト フローに対して十分な速さではない可能性があります。
Business Critical は 、各ノードのコンピューティングとストレージを統合します。 データベース エンジンとデータ ファイルはどちらも、3 つのセカンダリ レプリカを持つ Always On 可用性グループ内のローカルにアタッチされた SSD を使用します。 この設計では、I/O 待機時間が最も短く (平均で 1 ミリ秒から 2 ミリ秒)、最大 IOPS (1 秒あたりの入力/出力操作)、およびレポート クエリに使用できる無料の読み取り専用レプリカが提供されます。 トレードオフのコストは、同じ仮想コア数の General Purpose の約 2.7 倍です。 eコマース チームの場合、チェックアウト トランザクションに一貫した 2 ミリ秒未満の待機時間が必要な場合は、Business Critical が理にかなっています。
Hyperscale では、独立したページ サーバーと多層キャッシュを備えた分離ストレージ アーキテクチャが使用されます。 最大 128 TB のデータベースをサポートし、0 ~ 4 個の高可用性レプリカを可能にし、データをコピーせずにコンピューティングをスケールアップまたはスケールダウンします。 割り当てられたストレージに対してのみ課金され、最大ストレージには課金されません。 Hyperscale は、他の層の実際のストレージとスケーリングの制限を取り除き、さまざまなワークロードに適しています。
次の表に主要な相違点を示します。
| 特徴 | General Purpose | Business Critical | Hyperscale |
|---|---|---|---|
| ストレージの種類 | リモートストレージ (Azure Blob Storage) | ローカル SSD | ローカル SSD キャッシュを使用して切り離す |
| 最大ストレージ数 | 4テラバイト (4 TB) | 4テラバイト (4 TB) | 128 TB |
| 仮想コアあたりの最大 IOPS | 320 | 4,000 | 5,500 (ローカル SSD) |
| レプリカの可用性 | 1 (読み取りレプリカなし) | 3 + 1 読み取りレプリカ | 0 ~ 4 (構成可能) |
| 最適な用途 | 予算重視のワークロード | 低遅延、高 I/O | 大規模なデータベース、柔軟なスケーリング |
コンピューティング レベルを選択する
仮想コア モデル内では、 プロビジョニング済 みと サーバーレスの 2 つのコンピューティング レベルから選択することもできます。
プロビジョニングされたコンピューティング では、ワークロードアクティビティに関係なく使用可能な仮想コアの固定数が割り当てられます。 一定の時間単位の料金を支払います。 このレベルは、一日を通して注文を処理する e コマース アプリケーションなど、一貫性のある、または予測可能なリソース消費量のワークロードに適合します。
サーバーレス コンピューティング では、使用されるコンピューティングの需要と 1 秒あたりの課金に基づいて仮想コアが自動的にスケーリングされます。 データベースがアイドル状態の場合、自動一時停止を行い、コンピューティング コストを完全に削減できますが、現在、自動一時停止は General Purpose でのみサポートされています。 サーバーレス コンピューティング自体は、General Purpose レベルと Hyperscale レベルの両方で使用できます。 これは、断続的なトラフィックを含む開発環境、内部ツール、またはアプリケーションに適しています。
構成をワークロードと一致させる
オプションを理解したら、どのように決定すればよいでしょうか。 次の要因に対してワークロードを評価します。
- 待機時間の要件: アプリケーションで一貫して 2 ミリ秒未満の I/O 待機時間が必要な場合は、[Business Critical] を選択します。 中程度の待機時間の許容度を得る場合は、General Purpose で十分です。
- ストレージ サイズ: データベースが 4 TB を超える場合、または急速な増加が予想される場合、Hyperscale は最大 128 TB に対応する唯一のオプションです。
- 読み取り負荷の高いワークロード: Business Critical には、無料の読み取り専用レプリカが含まれています。 Hyperscale では、柔軟な読み取りスケールアウトのための名前付きレプリカがサポートされています。
- コストの機密性: プロビジョニングされたコンピューティングを使用する General Purpose では、予測可能な価格が提供されます。 General Purpose または Hyperscale のサーバーレス コンピューティングにより、断続的なワークロードのコストが削減されます。
- 可用性の要件: Business Critical は、3 つの同期レプリカと最速のフェールオーバーで最高の回復性を提供します。 Hyperscale では、回復性とコストのバランスを取るためにレプリカの数を構成できます。
ヒント
オンプレミスの SQL Server から移行する場合は、仮想コア モデルを使用します。仮想コア モデルは物理 CPU とメモリに直接マップされるためです。 DTU モデルでは個々のリソース ディメンションが公開されないため、移行のキャパシティ プランニングが困難になります。
データベース レベルの設定を構成する
レベルとコンピューティング モデルを選択しました。 次に、データベース自体を調べます。 いくつかの設定は、Azure SQL Database による並列処理、クエリの最適化、および復旧の処理方法に影響します。 これらの設定は、サービス レベルを変更せずに調整します。
MAXDOP を使用して並列処理を制御する
並列処理の最大次数 (MAXDOP) は、エンジンが 1 つのクエリに割り当てるプロセッサ スレッドの数を制御します。 Azure SQL Database の既定値は 8 で、さまざまなワークロードに適しています。 2020 年 9 月より前の新しいデータベースの既定値は 0 で、並列処理は無制限で、問題が発生しました。 1 つの分析クエリで使用可能なすべてのスレッドが消費され、CPU のチェックアウト フローが不足する可能性があります。
MAXDOP は、 ALTER DATABASE SCOPED CONFIGURATION SET MAXDOPを使用してデータベース レベルで設定します。 読み取り/書き込みワークロードと読み取り専用ワークロードのコンカレンシーニーズが異なる場合は、セカンダリ レプリカに別の値を設定することもできます。 特定のクエリの場合は、 OPTION (MAXDOP) ヒントを使用します。 1 つのルール: 運用環境では MAXDOP 0 を使用しないでください。 並列処理が無制限になると、リソースの枯渇、クエリのタイムアウト、アプリケーションの停止が発生します。
自動チューニングで回帰をキャッチする
クエリ オプティマイザーは、常に最適なプランを選択するとは限りません。 統計が古くなり、データ分布が変化し、昨日高速だったプランが今日遅くなります。 自動チューニング では、クエリのパフォーマンスが監視され、通知を待たずに修正が適用されます。
Azure SQL Database では、次の 3 つのオプションがサポートされています。
- FORCE_LAST_GOOD_PLAN は、プランの回帰を検出し、以前の高速プランを強制的に実行します。 既定で有効になっています。
- CREATE_INDEX 不足しているインデックスを識別し、作成して、改善を検証します。 既定で無効になっています。
- DROP_INDEX は、未使用のインデックスと重複するインデックスを削除します。 既定で無効になっています。 主キーと一意の制約をサポートするインデックスを含む一意のインデックスは削除されません。
すべての変更は、クエリの頻度に応じて 30 分から 72 時間の検証ウィンドウを通過します。 パフォーマンスが低下した場合、変更は自動的に元に戻されます。
ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON,
CREATE_INDEX = ON,
DROP_INDEX = OFF);
休日の販売中に e コマース アプリケーションについて考えてみましょう。 さまざまな製品ページの人気が急上昇すると、クエリ パターンが変化します。 FORCE_LAST_GOOD_PLANは、これらの回帰を自動的にキャッチするため、午前 2 時の不適切なプランの変更は、月曜日の朝に誰かが気付くまでチェックアウトを遅くしません。 提案内容がレビューされるまで、CREATE_INDEXを残してDROP_INDEXをオフにしたい場合があります。
互換性レベルでオプティマイザー機能のロックを解除する
すべてのデータベースには、使用できるクエリ オプティマイザーの動作を決定する 互換性レベル があります。 Azure SQL Database の新しいデータベースは、既定でレベル 170 または使用可能な最高レベルです。 各レベルでは、一連の インテリジェント クエリ処理 (IQP) 機能のロックが解除されます。
- レベル 150: 行ストアでのバッチ モード、テーブル変数遅延コンパイル、スカラー ユーザー定義関数 (UDF) インライン化。
- レベル 160: パラメーター依存プラン最適化 (PSP)、カーディナリティ推定フィードバック。
- レベル 170: オプションパラメーター設計の最適化。
Microsoft はこの設定を自動的にアップグレードしないため、既存のデータベースは互換性レベルが低い場合に実行できます。 低い既定値が有効だったときに作成されたデータベースは、元のレベルを保持します。 たとえば、2024 年に Azure SQL Database を作成した場合、レベルが手動で更新されない場合、データベースはレベル 160 のままです。 同様に、BACPAC ファイルを使用してデータベースをインポートした場合、インポートされたデータベースの互換性レベルは、ソース データベースの互換性レベルに基づいています。 上に移動するには:
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;
運用環境では、この設定を盲目的に変更しないでください。 クエリ ストアを使用して、現在のレベルでのパフォーマンス ベースラインのキャプチャ、テスト環境でのアップグレード、比較を行います。 クエリが後退した場合は、調査中に古いプランを強制できます。
OPTIMIZE_FOR_AD_HOC_WORKLOADSを使用してプラン キャッシュの肥大化を減らす
eコマース アプリケーションは、多数のフィルターの組み合わせで製品検索クエリを生成します。 一意の各クエリ テキストは、そのクエリが再び実行されない場合でも、キャッシュ内で独自のコンパイル済みプランを取得します。 時間の経過と共に、プラン キャッシュには何千もの単一使用プランが格納され、実際に重要な頻繁に実行されるプランがプッシュされます。
OPTIMIZE_FOR_AD_HOC_WORKLOADS はこの問題を解決します。 有効にすると、エンジンは、完全なプランではなく、最初の実行時に小さな コンパイル済みプラン スタブ を格納します。 同じクエリが 2 回目に実行された場合にのみ、エンジンは完全なプランをコンパイルしてキャッシュします。
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
この設定により、キャッシュが無駄を取り除き、最も重要なクエリのプランがメモリ内に常駐し続けられるようにします。
加速化されたデータベース復旧について
高速データベース復旧 は、Azure SQL Database で常に有効になっています。 オフにすることはできません。また、オフにする必要はありません。 高速データベース復旧 (ADR) では、障害発生時に実行されていたアクティブなトランザクションの数に関係なく復旧時間が一定になるように、復旧プロセスが再設計されます。 また、トランザクションの瞬時ロールバックと積極的なログの切り捨ても提供します。
ADR は、ではなく、データベース内のtempdb (PVS) に行バージョンを格納します。 変更する行のサイズに応じて、バージョンはデータ ページの行内に格納されるか、別の内部テーブルに行外に格納されます。 書き込み負荷の高いワークロードでは、すべての行バージョンがログに記録されるため、ページ分割の増加とログ生成の増加を確認できます。 そのオーバーヘッドを最小限に抑えるには、トランザクションを短くし、不要な中止されたトランザクションを減らします。
PVS はデータベースの割り当てられたストレージを共有するため、増加する PVS によってデータに使用できる領域が削減されます。 行外 PVS のオーバーヘッドを監視するには、 sys.dm_tran_persistent_version_store_stats クエリを実行し、 persistent_version_store_size_kb 列を確認します。この列では、行外バージョンのサイズのみが報告され、データ ページに格納されている行内バージョンは含まれません。 一般的なワークロード中にベースラインを確立するには、その値をデータベースの合計サイズと比較します。 PVS がそのベースラインを大幅に超えた場合、長時間実行されているトランザクションやバージョンのクリーンアップを遅らせる高い中止率がないか確認してください。
重要なポイント
Azure SQL Database では、2 つのリソース モデル、3 つのサービス レベル、および 2 つのコンピューティング レベルが提供されます。 General Purpose を使用した仮想コア モデルは、ほとんどのワークロードを対象とします。 Business Critical では、2 ミリ秒未満の待機時間が追加されます。 Hyperscale では、ストレージとスケーリングの制限が削除されます。 データベース内では、MAXDOP 8 が安全な既定値であり、自動チューニングによってプランの回帰がキャッチされ、互換性レベルをアップグレードすると、最新のインテリジェント クエリ処理 (IQP) 機能のロックが解除されます。 OPTIMIZE_FOR_AD_HOC_WORKLOADSを有効にしてプラン キャッシュをクリーンに保ち、特に書き込み負荷の高いシナリオで、 sys.dm_tran_persistent_version_store_statsを使用して ADR からの PVS ストレージの使用状況を監視します。 次に、分離レベルとコンカレンシー制御がこのインフラストラクチャ内で実行されているクエリに与える影響について説明します。