こんにちは。最近は、仕事でSQL Serverを扱う機会が増えており、いまさらながらSQL Serverの勉強をはじめております。というわけで、基本的な内容の記事が続きますが、ご容赦ください。
今日は、データベースチューニングアドバイザを少し触ってみたので、調べて分かったことなどをまとめておきたいと思います。
Contents
データベースチューニングアドバイザとは
- データベースを分析し、クエリ パフォーマンスを最適化する手助けをするSQL Serverの機能
- この機能により、データベースの構造や SQL Server の内部構造に関する専門的な知識がなくても、インデックス、インデックス付きビュー、テーブル パーティション分割の最適な組み合わせを選択して作成することができる。
- SQL Serverすべてのバージョンで利用可能。
- DTAを使うシナリオ例：
- 問題のある特定のクエリのパフォーマンスをトラブルシューティングする。
- 1 つまたは複数のデータベースに対する多数のクエリをチューニングする。
- 想定される物理的な設計変更に対し、調査目的の what-if 分析を実行する。
- ストレージ領域を管理する。
- DTAが推奨してくれることの例：
- データベースに最適な行ストアと列ストアインデックスの組み合わせの推奨（SQL Server 2016(13.x)以降の機能）
- 固定パーティション分割または非固定パーティション分割の推奨
- インデックス付きビューの推奨
- インデックスの使用、テーブル間でのクエリの分布、ワークロード内のクエリ パフォーマンスなど、提案された変更を行うとどんな効果が得られるかの分析を提示
- 少数の問題クエリについてデータベースを調整する方法を推奨
- 推奨された構成を実装して得られる効果を要約したレポートを提供
- Azure SQL Database / Azure SQL Managed Instanceではサポートされていない。
- UI操作だけでなく、コマンドプロンプトからの実行も可能（dtaコマンド）
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/performance-improvements-using-dta-recommendations?view=sql-server-ver16
Azure SQL Database / SQL Managed Instanceでは別の機能を利用するようにと。
The Database Engine Tuning Advisor is not supported for Azure SQL Database or Azure SQL Managed Instance. Instead, consider the strategies recommended in Monitoring and performance tuning in Azure SQL Database and Azure SQL Managed Instance. For Azure SQL Database, see also the Database Advisor performance recommendations for Azure SQL Database.
https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver16
データベースチューニングアドバイザ利用の注意点
なるほど、パフォーマンスとのトレードオフはあるみたいですね。以下リンクにトレードオフを回避する方法の詳細説明があります。
データベース エンジン チューニング アドバイザーは、ワークロードを分析し、チューニングの推奨事項を作成するためにクエリ オプティマイザーに依存します。 実稼働サーバー上でこの分析を実行すると、サーバーの負荷が増し、チューニング セッション中のサーバーのパフォーマンスが低下することがあります。 実稼働サーバーに加えてテスト サーバーを使用することで、チューニング セッション中のサーバーの負荷への影響を小さくすることができます。
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/reduce-the-production-server-tuning-load?view=sql-server-ver16
その他制約事項：
データベースチューニングアドバイザの使い方
DTAを利用するにあたって指定する必要のあるものは以下です。
- ワークロード
- チューニング対象データベースとテーブル
指定したワークロードを指定したデータベースまたはテーブルに対して実行した場合の改善推奨事項を提案してくれる、ということのようですね。
ワークロード
ワークロードは、チューニングするデータベースSQLに対して実行される Transact-SQL ステートメントのセットです。 データベース エンジン チューニング アドバイザーでは、サーバーのクエリ パフォーマンスを向上させるインデックスやパーティション分割ストラテジを推奨するために、これらのワークロードが分析されます。
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver16#Create
ワークロードは以下の方法で作成できます。
- クエリストア（SQL Server 2016 (13.x)以降で利用可能）
- プランキャッシュ（SQL Server 2012 (12.x)以降で利用可能）
- トレース テーブル・ファイル
クエリストア
プランキャッシュと比較してより長期間のクエリ情報を持っているので、クエリストアを有効化しているのであれば、このオプションを使うでよさそう。
SQL Server のクエリ ストア機能では、クエリ履歴、計画、および実行時統計を自動的にキャプチャし、これらの情報をデータベース内に保持します。 データベース エンジン チューニング アドバイザー (DTA) は、クエリ ストアを使用してチューニングの適切なワークロードを自動的に選択するという新しいオプションをサポートしています。 大半のユーザーは、この機能を使用すれば、チューニングのワークロードを明示的に収集する必要がなくなります。
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/tuning-database-using-workload-from-query-store?view=sql-server-ver16
クエリ ストアとプラン キャッシュの違いは、前者には、データベースに対して実行されたクエリがより長い期間にわたって含まれているということです。クエリはサーバー再起動後も保持されています。 一方、プラン キャッシュには、プランがメモリにキャッシュされている最近実行されたクエリのサブセットのみが含まれます。 サーバーが再起動すると、プラン キャッシュ内のエントリは破棄されます。
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/tuning-database-using-workload-from-query-store?view=sql-server-ver16#difference-between-using-workload-from-query-store-and-plan-cache
プランキャッシュ
今となってはクエリストアを有効化していない場合の選択肢になるのかな？
データベース エンジン チューニング アドバイザーによって、分析に使用される上位 1,000 件のイベントがプラン キャッシュから選択されます。
https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor?view=sql-server-ver16#PlanCache
チューニングする必要のあるデータベースを選択し、必要に応じて、 [選択したテーブル] から、各データベースのテーブル (複数可) を選択します。 すべてのデータベースのキャッシュ エントリを含めるには、 [チューニング オプション] の [詳細設定オプション] を選択し、 [すべてのデータベースのプラン キャッシュ イベントを含める] をオンにします。
テーブル・ファイル
SQL Serverのトレース結果を蓄積したテーブル、またはファイルを指定してワークロードを作成するオプションの模様。
以上、簡単ですがDTAの概要についてわかったことのまとめでした。実際に使ってみた結果もまたUpdateしたいと思います。
おしまい
コメントを残す