データベースメンテナンスチェックを確認する

7 分

クエリオプティマイザーでは、インデックスの統計情報を利用して、最適な実行プランの構築が試行されます。

Azure SQL 内では、バックアップや整合性チェックなどのメンテナンスタスクが自動的に処理されます。自動更新で統計を最新の状態に保つだけで十分な場合もありますが、それでは不十分な場合もあります。

適切なインデックスと統計を用意することで、特定のプランが最適な効率で実行されるようになります。インデックスのメンテナンスは、データベース内のデータが時間の経過と共に変化するため、定期的に実行する必要があります。データに対する変更の頻度に基づいて、インデックスのメンテナンス方法を変更することができます。

再構築と再構成

インデックスページ内の論理順序が物理的な順序と一致しない場合、インデックスの断片化が発生します。 UPDATE、DELETE、INSERT などの日常的なデータ変更ステートメントの実行中に、ページの順序が変更されることがあります。断片化によってパフォーマンスの問題が発生する可能性があります。これは、インデックスページ内のポインターによって参照されるデータを特定するために必要とされる余分な I/O が原因です。

インデックスのデータが挿入、更新、削除されると、インデックス内の論理的な順序が、インデックスを構成しているページ内およびページ間の物理的な順序と一致しなくなります。また、時間の経過と共に、データの変更により、データベース内でデータが分散または断片化される可能性があります。断片化が発生し、データベースエンジンが必要なデータを見つけるために余分のページを読み取らなくてはならない場合、クエリのパフォーマンスが低下する可能性があります。

インデックスの再構成はオンライン操作であり、インデックス (クラスター化と非クラスター化の両方) のリーフレベルが最適化されます。この最適化プロセスでは、左から右へのノードの論理順序と一致するように、リーフレベルのページの順序が物理的に変更されます。この処理中、構成されている fillfactor 値に基づいて、インデックスページも圧縮されます。

再構築は、実行されたコマンドまたは使用されている SQL Server のエディションに応じて、オンラインまたはオフラインにすることができます。オフラインの再構築プロセスでは、インデックス自体が削除され、再作成されます。オンラインで実行できる場合は、既存のインデックスと並行して新しいインデックスが作成されます。新しいインデックスが作成されると、既存のインデックスが削除され、新しいインデックスの名前が古いインデックス名に合うように変更されます。オンラインバージョンでは、新しいインデックスが既存のインデックスと並行して作成されるため、より多くの領域が必要であることにご注意ください。

インデックスのメンテナンスに関する一般的なガイダンスは次のとおりです。

5% 以上 30% 未満 - インデックスを再構成します
>30% 以上 - インデックスを再構築します

これらの数値は一般的な推奨事項として使用してください。ワークロードやデータによっては、より積極的な関与が必要な場合があります。また、場合によっては、主に実行されるクエリが特定のページのシークであるデータベースのインデックスメンテナンスを保留しても構わないこともあります。

SQL Server および Azure SQL プラットフォームには、オブジェクトの断片化を検出できる DMV が用意されています。この目的に最もよく使用される DMV は、B ツリーインデックスの場合は sys.dm_db_index_physical_stats、列ストアインデックスの場合は sys.dm_db_column_store_row_group_physical_stats です。

もう 1 つ注意すべき点は、インデックスを再構築するとインデックスの統計が更新されるため、パフォーマンスにさらに役立つ可能性があることです。インデックスの再編成では、統計は更新されません。

Microsoft は、SQL Server 2017 で再開可能なインデックスの再構築操作を導入しました。再開可能なインデックスの再構築操作オプションを使用すると、特定のインスタンスに対して再構築操作で必要とされる時間を柔軟に制御できます。 SQL Server 2019 で、関連する並列処理の最大限度を制御する機能が追加され、データベース管理者がよりきめ細かく制御できるようになりました。

統計

Azure SQL でパフォーマンスの調整を行う場合は、統計の重要性を理解することが不可欠です。

統計は、バイナリラージオブジェクト (BLOB) としてユーザーデータベースに格納されます。これらの BLOB には、テーブルまたはインデックス付きビューの 1 つ以上の列におけるデータ値の分布に関する統計情報が含まれています。

統計には、列内のデータ値の分布に関する情報が含まれています。クエリオプティマイザーでは、カーディナリティを判別するために列とインデックスの統計が使用されます。これは、クエリによって返されることが予想される行数です。

カーディナリティ推定は、実行プランを生成するためにクエリオプティマイザーによって使用されます。カーディナリティ推定は、要求されたデータを取得するために使用する操作の種類 (index seek や scan など) をオプティマイザーが決定するのにも役立ちます。

最終更新日のユーザー定義統計の一覧を表示するには、次のクエリを実行します。

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

統計を作成する

AUTO_CREATE_STATISTICS オプションが ON の場合、クエリオプティマイザーはそのインデックス付き列の統計を既定で作成します。また、クエリオプティマイザーは、クエリ述語内の 1 列ずつの統計も作成します。

これらのメソッドにより、ほとんどのクエリに高品質のクエリプランが提供されます。特定のクエリプランを改善するために、CREATE STATISTICS ステートメントを使用してさらに統計を作成することが必要になる場合もあります。

AUTO_CREATE_STATISTICS オプションを有効にしておくことをお勧めします。そのようにすると、クエリオプティマイザーでクエリ述語列の統計を自動的に作成できます。

次の状況に直面した場合はいつでも、統計の作成を検討します。

データベースエンジンチューニングアドバイザーによって統計の作成が提案される
クエリ述語に複数の列が含まれているものの、それらの列はまだ同じインデックスに存在していない
クエリがデータのサブセットから選択する
クエリに統計が不足している

メンテナンスタスクの自動化

Azure SQL には、自動化を実現するためにデータベースメンテナンスタスクを実行するネイティブツールが用意されています。データベースが実行されているプラットフォームに応じて、さまざまなツールを使用できます。

Azure 仮想マシン上の SQL Server

ユーザーは、SQL Agent や Windows タスクスケジューラなどのスケジュールサービスにアクセスできます。これらの自動化ツールは、インデックス内の断片化の量を最小限に抑えるのに役立ちます。大規模なデータベースを使用する場合は、パフォーマンスを最適化するインデックスの再構築と再構成のバランスを見つける必要があります。 SQL エージェントまたはタスクスケジューラによって提供される柔軟性により、カスタムジョブを実行できます。

Azure SQL Database

Azure SQL Database の性質により、ユーザーは SQL Server エージェントにも Windows タスクスケジューラにもアクセスできません。これらのサービスを使用できないため、他の方法を使用してインデックスのメンテナンスを作成する必要があります。 SQL Database のメンテナンス操作の管理には、次の 3 つの方法があります。

Azure Automation Runbook
Azure 仮想マシン内の SQL Server からの SQL Agent ジョブ (リモート呼び出し)
Azure SQL エラスティックプール

Azure SQL Managed Instance

Azure 仮想マシン上の SQL Server と同様に、SQL Server エージェントを使用して、SQL Managed Instance にジョブをスケジュール設定できます。 SQL Server エージェントを使用すると、データベースのインデックス内の断片化を軽減するように設計されたコードを柔軟に実行できます。

続行

データベース メンテナンス チェックを確認する