SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2017 CTP 2.0 の自動チューニングについて

leave a comment

SQL Server 2017 では、Adaptive Query Processing (適応型クエリ処理) のほかに、Automatic Tuning という機能が追加されています。

機能名を見た時、SQL Database で実装されている「SQL Database Advisor」のような、推奨インデックスの自動設定を行う仕組みなのかと思ったのですが、それとは違う機能となるようですね。

ある程度は機能確認ができるサンプルスクリプトが作れたので軽くまとめておきたいと思います。

SQL Server 2017 CTP 2.0 で実装された、自動チューニングの機能ですが、

  • クエリストアの情報を使用して、実行プランが退行 (効率が悪くなった) を検出し、自動的に実行プランを変更する

機能となるようです。

この機能向けに SQL Server 2017 では、以下のシステムビューが追加されています。

sys.dm_db_tuning_recommendations (Transact-SQL)
sys.database_automatic_tuning_options (Transact-SQL)

この情報から、クエリストアの情報を使用して、実行プランの変化により実行効率が低下したプランのプラン強制の推奨情報が取得できるようになっています。

推奨情報はクエリストアではなく、DMV として保持しているため、SQL Server の再起動を行うと、クリアされてしまうようです。

 

一部の列の情報については JSON となっているため、以下のようなクエリで JSON をパースして情報を表示する必要があります。

SELECT 
	r.reason, 
	score,
    JSON_VALUE(details, '$.implementationDetails.script') script,
    planState.*,
	planForceDetails.*
FROM 
	sys.dm_db_tuning_recommendations r
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH (
	[query_id] int '$.queryId',
	[new plan_id] int '$.regressedPlanId',
	[recommended plan_id] int '$.forcedPlanId'
	) as planForceDetails
CROSS APPLY OPENJSON (state)
WITH
	(
	[currentValue] nvarchar(100) '$.currentValue',
	[reason] nvarchar(100) '$.reason'
    ) as planState

 

推奨情報が存在している場合は、以下のような情報が取得できます。

image

デフォルトの動作は推奨を取得するだけで、自動的に効率が低下したプランを強制するというような動作は行われません。

推奨情報としてプランの実行効率の低下が検知され、現状使用されている実行プランより、過去に実行されたプランの方が効率が良い場合には、最後によかったプランを使用するように強制を行うようにするためには、以下のクエリを実行してデータベース単位で設定を有効にします。

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )

 

設定としては以下のようになっているようですので、Enterprise Edition 限定の機能となるようですね。

image

 

自動チューニングが動作すると、以下のように、効率の良いプランを使用した実行プランの強制の設定が自動的に行われるようになります。

image

推奨情報の検出ですが以下のようなクエリを実行することで検証ができるかと。

最初にテスト用のデータを作成します。

DROP TABLE IF EXISTS T_Emp
GO
CREATE TABLE T_Emp(
	ID int NOT NULL, 
	Name Varchar(20), 
	Status Varchar(1), 
	Flag tinyint NOT NULL, 
	UpdDate Datetime, 
	CONSTRAINT PK_ID PRIMARY KEY CLUSTERED(ID)) 
GO

CREATE INDEX IX_Name on T_Emp(Name)
CREATE INDEX IX_Status on T_Emp(Status)
CREATE INDEX IX_Flag on T_Emp(Flag)
CREATE INDEX IX_UpdDate on T_Emp(UpdDate)
GO

TRUNCATE TABLE T_Emp
GO

-- 10 件データを登録
SET NOCOUNT ON
GO

DECLARE @i int = 1
DECLARE @name varchar(10)

WHILE @i <= 10
	BEGIN SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6)
	INSERT INTO T_Emp VALUES( @i,@name,1,0,Getdate() )
	SET @i += 1
END
GO

-- 10 件の状態の統計情報を取得
UPDATE STATISTICS T_Emp IX_Flag WITH FULLSCAN
DBCC SHOW_STATISTICS ('T_Emp', 'IX_Flag') WITH STATS_STREAM
GO

--  100 万件までデータを増幅
SET NOCOUNT ON
GO

DECLARE @i int = 11
DECLARE @name varchar(10)
BEGIN TRAN
WHILE @i <= 1000000
BEGIN
	SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6)
	INSERT INTO T_Emp VALUES( @i,@name,2,1,Getdate() )
	SET @i += 1
END
COMMIT TRAN
GO

-- 100 万件の状態の統計情報の STATS STREAM を取得
UPDATE STATISTICS T_Emp IX_Flag WITH FULLSCAN
DBCC SHOW_STATISTICS ('T_Emp', 'IX_Flag') WITH STATS_STREAM 
GO

/*
-- 10 件の統計情報
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x010000000200000000000000000000002B1CE23900000000E00100000000000088010000000000003003000030000000010003000000000000000000DE0100003803FFFF3800000004000A000000000000000000020000000700000083CFCD0061A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000000000000000000000000000001C00000000000000240000000000000008000000000000001000110000002041000000000000803F000400000A00000000000000

-- 100 万件の統計情報
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000FE816841000000005402000000000000FC010000000000003003C4C630000000010003000000000000000000DE0100003803C4C63800000004000A0000000000000000000000000007000000FFFBCD0061A7000040420F000000000040420F0000000000000000000000003FBD37863500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A04000247449000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000088000000000000009000000000000000100000000000000024000000000000001000110000002041000000000000803F000400001000110060237449000000000000803F010400000100000083CFCD0061A7000000000000000024400A0000000000000001000000000000000000F03F000000006C842E410000000000000000000000006C842E410000000000000000D7123F928FA0F63F40420F0000000000
*/

/*
-- 実行プランを変化させるためのインデックス
DROP INDEX IF EXISTS IX_Flag2 ON T_Emp
CREATE INDEX IX_Flag2 on T_Emp(Flag)  INCLUDE(Name, Status,UpdDate)
*/

 

次に以下のクエリを 2, 3 回程度実行します。

このクエリでは、統計情報を変更することで、同一のクエリを実行しても実行プランが変わるようにしています。

image

image

/*
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR
*/

-- 10 件の統計情報の状態でクエリを実行
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x010000000200000000000000000000002B1CE23900000000E00100000000000088010000000000003003000030000000010003000000000000000000DE0100003803FFFF3800000004000A000000000000000000020000000700000083CFCD0061A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000000000000000000000000000001C00000000000000240000000000000008000000000000001000110000002041000000000000803F000400000A00000000000000
GO
DBCC FREEPROCCACHE
GO

DECLARE @key int = 0
EXEC sp_executesql N'SELECT * FROM T_Emp WHERE Flag = @p1', N'@p1 int', @key
WAITFOR DELAY '00:00:01'
GO 8

-- 100 万件の統計情報の状態でクエリを実行
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000FE816841000000005402000000000000FC010000000000003003C4C630000000010003000000000000000000DE0100003803C4C63800000004000A0000000000000000000000000007000000FFFBCD0061A7000040420F000000000040420F0000000000000000000000003FBD37863500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A04000247449000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000088000000000000009000000000000000100000000000000024000000000000001000110000002041000000000000803F000400001000110060237449000000000000803F010400000100000083CFCD0061A7000000000000000024400A0000000000000001000000000000000000F03F000000006C842E410000000000000000000000006C842E410000000000000000D7123F928FA0F63F40420F0000000000
GO
DBCC FREEPROCCACHE
GO

DECLARE @key int = 0
EXEC sp_executesql N'SELECT * FROM T_Emp WHERE Flag = @p1', N'@p1 int', @key
WAITFOR DELAY '00:00:01'
GO 12

-- 10 件の統計情報の状態でクエリを実行
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x010000000200000000000000000000002B1CE23900000000E00100000000000088010000000000003003000030000000010003000000000000000000DE0100003803FFFF3800000004000A000000000000000000020000000700000083CFCD0061A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000000000000000000000000000001C00000000000000240000000000000008000000000000001000110000002041000000000000803F000400000A00000000000000
GO
DBCC FREEPROCCACHE
GO

DECLARE @key int = 0
EXEC sp_executesql N'SELECT * FROM T_Emp WHERE Flag = @p1', N'@p1 int', @key
WAITFOR DELAY '00:00:01'
GO 5

数回実行すると、以下のクエリで推奨情報と適用状態を確認することができるかと。

自動チューニングを有効にしていても、「AutomaticTuningOptionNotEnabled」になって、強制されないケースが結構あるのが気になっているのですが。

また、うまく推奨情報が出ない場合、一度 SQL Server のサービスを再起動してみてください。

/*
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF )
*/

SELECT name, desired_state_desc, actual_state_desc, reason_desc FROM sys.database_automatic_tuning_options;


SELECT * FROM sys.database_plan_correction_recommendations
SELECT * FROM sys.dm_db_tuning_recommendations

SELECT 
	r.reason, 
	score,
    JSON_VALUE(details, '$.implementationDetails.script') script,
    planState.*,
	planForceDetails.*
FROM 
	sys.dm_db_tuning_recommendations r
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH (
	[query_id] int '$.queryId',
	[new plan_id] int '$.regressedPlanId',
	[recommended plan_id] int '$.forcedPlanId'
	) as planForceDetails
CROSS APPLY OPENJSON (state)
WITH
	(
	[currentValue] nvarchar(100) '$.currentValue',
	[reason] nvarchar(100) '$.reason'
    ) as planState

 

実行プランの変化を意識する機能は検証するのにひと手間かかりますね。

Written by masayuki.ozawa

4月 26th, 2017 at 3:06 pm

Leave a Reply

*