データベース ストレージを最適化する

データベースのストレージを最適化するには、プロポーショナル フィルと tempdb の構成を考慮する必要があります。

I/O パフォーマンスについて

I/O パフォーマンスは、データベース アプリケーションにとってきわめて重要な場合があります。 Azure SQL により、物理的なファイルの配置はなくなりますが、必要な I/O パフォーマンスを確実に取得する方法があります。

1 秒あたりの入出力操作数 (IOPS) は、アプリケーションにとって重要な場合があります。 必ず IOPS のニーズに適したサービス レベルと仮想コアを選んでください。 Azure に移行する場合に、オンプレミスのクエリの IOPS を測定する方法について説明します。 IOPS に制限がある場合、I/O 待機時間が長くなる可能性があります。 仮想コア購入モデルで十分な IOPS がない場合は、仮想コアをスケールアップすることや、Business Critical または Hyperscale に移行することができます。 運用環境ワークロードで DTU を使う場合は、Premium レベルに移行することをお勧めします。

I/O 待機時間は、I/O パフォーマンスにとってもう 1 つの重要なコンポーネントです。 Azure SQL Database の I/O 待機時間を短縮するには、Business Critical または Hyperscale を検討してください。 SQL Managed Instance の I/O 待機時間を短縮するには、Business Critical に移動するか、データベースのファイル サイズまたはファイル数を増加します。 トランザクション ログの待機時間を短縮するには、必要に応じて複数ステートメントのトランザクションを使います。

ファイルとファイル グループ

SQL Server の専門家は多くの場合、物理ファイルの配置によって I/O パフォーマンスを向上させるためにファイルとファイル グループを使用します。 Azure SQL では、ユーザーがファイルを特定のディスク システムに配置することはできません。 ただし、Azure SQL には、レート、IOPS、待機時間に関する I/O パフォーマンスに関するリソース コミットメントがあります。 このため、物理ファイルの配置からユーザーを抽象化すると、利点が得られる場合があります。

Azure SQL Database にはデータベース ファイルが 1 つしか存在せず (通常、Hyperscale には複数あります)、最大サイズは Azure インターフェイス経由で構成されます。 他のファイルを作成する機能はありません。

Azure SQL Managed Instance では、データベース ファイルの追加やサイズの構成はサポートされていますが、ファイルの物理的な配置はサポートされていません。 SQL Managed Instance のファイルの数およびファイル サイズを使用して、I/O パフォーマンスを向上することができます。 さらに、SQL Managed Instance では、管理の容易性のためにユーザー定義のファイル グループがサポートされています。

比例塗りつぶしについて説明する

2 つのデータ ファイルを含む SQL Server データベースに 1 ギガバイトのデータを挿入する場合、各ファイルが約 512 MB 増加することが予想される場合があります。 ただし、これは常に当てはまるとは限りません。 SQL Server は、各ファイルのサイズに基づいてデータを分散します。 たとえば、両方のデータ ファイルが 2 ギガバイトの場合、データは均等に分散されます。 ただし、1 つのファイルが 10 ギガバイトで、もう一方が 1 ギガバイトの場合、約 900 MB は大きなファイルに、100 MB は小さいファイルに入ります。 この動作はどのデータベースでも一般的ですが、書き込み集中型の tempdb では、書き込みパターンが不均等であるため、より多くの書き込みを処理するため、最大のファイルにボトルネックが発生する可能性があります。

SQL Server での Tempdb の構成

SQL Server は、セットアップ中に使用可能な CPU の数を検出し、適切な数のファイル (最大 8 個) を構成し、サイズ変更も行います。 さらに、トレース フラグ 1117 と 1118 の動作はデータベース エンジンに統合されますが、 tempdbに対してのみ統合されます。 tempdb の負荷の高いワークロードの場合、tempdb ファイルの数を 8 を超えて増やすと、コンピューター上の CPU の数と一致する場合があります。

tempdb の使用方法は、SQL Server と Azure SQL のどちらの場合も同じです。 ただし、ファイルの配置、ファイルの数とサイズ、tempdb の構成オプションなど、tempdb を構成する機能は異なることに注意してください。

SQL Server では、ユーザー定義の一時テーブルを格納するだけでなく、さまざまなタスクに tempdb を使用します。 これは、中間クエリ結果、並べ替え操作、行のバージョン管理用のバージョン ストアなどを格納する作業テーブルに使用されます。 この広範な使用率のため、tempdb を使用可能な最も短い待機時間のストレージに配置し、そのデータ ファイルを適切に構成することが重要です。

tempdb のデータベース ファイルは常にローカル SSD ドライブに自動的に格納されるため、I/O パフォーマンスが問題になることはありません。

SQL Server の専門家は、多くの場合、tempdb テーブルの割り当てを分割するために複数のデータベース ファイルを使用します。 Azure SQL Database の場合、ファイルの数は仮想コアの数 (たとえば、2 つの仮想コアが 4 つのファイルと等しい) でスケーリングされ、最大 16 個になります。 ファイルの数は tempdb に対する T-SQL では構成できませんが、デプロイ オプションを変更することによって構成できます。 tempdb の最大サイズは、仮想コアの数によってスケーリングされます。 SQL Managed Instance では、仮想コアには関係なく 12 個のファイルが得られます。

データベース オプション MIXED_PAGE_ALLOCATION は OFF に設定され、 AUTOGROW_ALL_FILES は ON に設定されます。 これを構成することはできませんが、SQL Server と同様に、これらが推奨される既定値です。

SQL Server 2019 で導入された tempdb メタデータ最適化機能は、多発するラッチの競合を軽減できますが、現在、Azure SQL Database または Azure SQL Managed Instance では使用できません。

データベース構成

一般に、T-SQL ALTER DATABASE および ALTER DATABASE SCOPED CONFIGURATION ステートメントを使用してデータベースを構成します。 Azure SQL では、パフォーマンスの構成オプションの多くを使用できます。 SQL Server、Azure SQL Database、Azure SQL Managed Instance の違いについては、 ALTER DATABASE と ALTER DATABASE SCOPED CONFIGURATION T-SQL リファレンスを参照してください。

Azure SQL Database では、既定の復旧モデルは完全復旧です。これにより、データベースが Azure サービス レベル アグリーメント (SLA) を満たすことができます。 つまり、最小ログ記録が許可される tempdbを除き、一括操作の最小ログ記録はサポートされません。

MAXDOP の構成

並列処理の最大限度 (MAXDOP) は、個々のクエリのパフォーマンスに影響を与える可能性があります。 SQL Server と Azure SQL は、同じ方法で MAXDOP を処理します。 MAXDOPを高い値に設定すると、クエリごとにより多くの並列スレッドが使用され、クエリの実行速度が速くなる可能性があります。 ただし、並列処理の増加には追加のメモリ リソースが必要です。これにより、メモリ不足が発生し、ストレージのパフォーマンスに影響を与える可能性があります。 たとえば、行グループを列ストアに圧縮する場合、並列処理に必要なメモリが増え、メモリ不足や行グループのトリミングが発生する可能性があります。

逆に、MAXDOP を小さい値に設定すると、メモリの負荷が軽減され、ストレージ システムのパフォーマンスが高まります。 これは、メモリ リソースが限られている環境や、ストレージの需要が高い環境で重要です。 MAXDOP を慎重に構成することで、クエリパフォーマンスとストレージ効率のバランスを取ることができ、CPU リソースとストレージ リソースの両方を最適に使用できます。

Azure SQL では、SQL Server と同様に、次の手法を使用して MAXDOP を構成できます。

• ALTER DATABASE SCOPED CONFIGURATION 構成する MAXDOP は、Azure SQL でサポートされています。
• "max degree of parallelism" のストアド プロシージャ sp_configure は、SQL Managed Instance でサポートされています。
• MAXDOP クエリ ヒントは完全にサポートされています。
• リソース ガバナーを使用した MAXDOP の構成は、SQL Managed Instance でサポートされています。