SE の雑記

以前、クエリストアを使用したデータベースチューニングアドバイザーの実行 という投稿を書いたのですが、SSMS 16.3 で、

SQL Server クエリ ストアからのワークロードの自動読み取りをサポートするためのデータベース エンジン チューニング アドバイザー (DTA) を機能強化。

という強化が行われました。

2017/1/25 時点の RTM の最新 (16.5.2) の GUI のデータベースチューニングアドバイザー (DTASHELL) には、クエリストアの選択肢はなかったのですが、CUI の DTA には、「-iq」という、クエリストアをワークロードとしたチューニングアドバイスが追加されていたようです。
# 16.3 のチューニングアドバイザーから、CUI については追加されていた可能性があります。

dta Utility

-iq
Specifies that the Query Store be used as the workload. The top 1,000 events from the Query Store for explicitly selected databases are analyzed. This value can be changed using the ?n option. See Query Store and Tuning Database Using Workload from Query Store for more information.

このパラメーターを使用することで、16.x 系の DTASHELL には、表示されていないクエリストアをワークロードとした分析を実施することができます。

検証用の環境だと、チューニングアドバイザーの解析が途中で止まってしまうケースが結構あって、取得されたレポートの画像がつけられないのですが…。

コマンドとしては、以下のようなコマンドでクエリストアをワークロードとした分析が可能です。

dta -S localhost -E -D tpch_clone -A 5 -fi -fa IDX_IV -fc -iq -e -s "Tuning Session #1"
dta -S localhost -E -D tpch_clone -A 5 -fi -fp FULL -iq -e -s "Tuning Session #2"
dta -S localhost -E -D tpch_clone -A 5 -fk ALIGNED -fp ALIGNED -iq -e -s "Tuning Session #3"
dta -S localhost -E -D tpch_clone -A 5 -fx -iq -e -s "Tuning Session #4"

チューニングレポートを一度クリアしたい場合は、以下のクエリで削除することも可能です。

DECLARE @DTA TABLE(SessionID int, SessionName nvarchar(255), InteractiveStatus int, CreatonTIme datetime, SchedulerStartTime datetime, StopTime datetime, GlobalSessionID uniqueidentifier)
DECLARE @SessionID int
INSERT INTO @DTA EXEC msdb..sp_DTA_help_session
DECLARE DTA CURSOR FORWARD_ONLY FOR SELECT SessionID FROM @DTA
OPEN DTA
FETCH NEXT FROM DTA INTO @SessionID
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC msdb..sp_DTA_delete_session @SessionID
	FETCH NEXT FROM DTA INTO @SessionID
END
CLOSE DTA
DEALLOCATE DTA

クエリストアをワークロードとした分析ですが、クローンデータベースに対しても実行することができます。

-- データベースのクローンを作成
DBCC CLONEDATABASE('tpch', 'tpch_clone')

クローンデータベースですが、作成直後の状態は「読み取り専用」となっており、この状態ではワークロードの分析をすることができません。

データベースのプロパティから「読み取り専用」を解除することができますので、書き込みが可能な状態のデータベースにすることで、ワークロードの分析を実施することができます。

# 分析用の一時インデックスを作成している関係等があるのかと思いますが。

SQL Database のインデックスのアドバイスの分析も、データベースに直接実施するのではなく、クローンを作ったデータベースに対して実行していたりするかもしれないですね。