テストデータ作成.sql

DROP TABLE IF EXISTS T_EmpGO
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 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
UPDATE STATISTICS T_Emp IX_FlagGO
DBCC SHOW_STATISTICS ('T_Emp', 'IX_Flag') WITH STATS_STREAM  GO
SET NOCOUNT ONGODECLARE @i int = 11DECLARE @name varchar(10) BEGIN TRANWHILE @i <= 1000000 BEGIN  SET @i += 1  SET @name = 'Name' + RIGHT('000000'+ CONVERT(VARCHAR,@i),6)  INSERT INTO T_Emp VALUES( @i,@name,2,1,Getdate() )  SET @i += 1END COMMIT TRANGO
UPDATE STATISTICS T_Emp IX_Flag WITH FULLSCANGO
DBCC SHOW_STATISTICS ('T_Emp', 'IX_Flag') WITH STATS_STREAM  GO
-- 10 件UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000CB2064B200000000D0020000000000007802000000000000300300003000000001000300000000000000000000000000380300003800000004000A000000000000000000000000000700000060B3BE0060A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190000000000000000000000000000001C0000000000000004010000000000000C0100000000000008000000000000001000110000002041000000000000803F0004000003000000C923BE0060A7000000000000506AF84097AE0A000000000002000000000000000000E03F00000000000000000000000000000000000000000000244000000000506AF8402FD0974B2EFFEF3F8292BD0060A700000000000000002440FC2709000000000001000000000000000000F03F00000000B069F840000000000000000000000000B069F84000000000000000008A87E31D0AA0F63F4B40BD0060A700000000000000002440E82709000000000001000000000000000000F03F000000000000000000000000000000000000000000002440000000000000244000000000000000000A00000000000000
-- 20 万件UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000D0F1626200000000EC020000000000009402000000000000300300003000000001000300000000000000000000000000380300003800000004000A000000000000000000000000000700000033ECBE0060A70000A586010000000000A586010000000000000000000000003F87C3273700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A0408052C347000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000020010000000000002801000000000000100000000000000024000000000000001000110000002041000000000000803F0004000010001100804DC347000000000000803F010400000300000060B3BE0060A70000000000000000244046350C000000000001000000000000000000F03F00000000B069F840000000000000000000000000B069F84000000000000000008A87E31D0AA0F63FC923BE0060A7000000000000506AF84097AE0A000000000002000000000000000000E03F00000000000000000000000000000000000000000000244000000000506AF8402FD0974B2EFFEF3F8292BD0060A700000000000000002440FC2709000000000001000000000000000000F03F00000000B069F840000000000000000000000000B069F84000000000000000008A87E31D0AA0F63FA586010000000000
-- 50 万件UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x0100000002000000000000000000000045923AE800000000EC020000000000009402000000000000300300003000000001000300000000000000000000000000380300003800000004000A0000000000000000000000000007000000AEA9C20060A7000095D003000000000095D0030000000000000000000000003F0D37863600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A04040257448000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000020010000000000002801000000000000100000000000000024000000000000001000110000002041000000000000803F0004000010001100C0227448000000000000803F01040000030000004BA5C20060A7000000000000A8840E412F1313000000000002000000000000000000E03F000000000000000000000000000000000000000000000000000000000000000000000000000000009693C20060A700000000000000002440A4420F000000000001000000000000000000F03F0000000058840E4100000000000000000000000058840E410000000000000000DB56CC1563A0F63F33ECBE0060A7000000000000506AF840E1BB0D000000000002000000000000000000E03F00000000000000000000000000000000000000000000344000000000F06AF8402FD0974B2EFFEF3F95D0030000000000
-- 100 万件UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000E0848CB900000000EC020000000000009402000000000000300300003000000001000300000000000000000000000000380300003800000004000A0000000000000000000000000007000000E052C30060A7000025A107000000000025A1070000000000000000000000003F6537063600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A040A024F448000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000020010000000000002801000000000000100000000000000024000000000000001000110000002041000000000000803F00040000100011006023F448000000000000803F01040000030000000C39C30060A700000000000000002440CEE316000000000001000000000000000000F03F000000006C841E410000000000000000000000006C841E410000000000000000A75912BE80A0F63F60B3BE0060A7000000000000000024402F1313000000000001000000000000000000F03F0000000000000000000000000000000000000000F884FE4000000000F884FE400000000000000000C923BE0060A7000000000000506AF84097AE0A000000000002000000000000000000E03F00000000000000000000000000000000000000000000244000000000506AF8402FD0974B2EFFEF3F25A1070000000000
/*DROP INDEX IF EXISTS IX_Flag2 ON T_EmpCREATE INDEX IX_Flag2 on T_Emp(Flag)  INCLUDE(Name, Status,UpdDate)*/DBCC DROPCLEANBUFFERS

テスト実施.sql

-- 本クエリを3回ほど実施
-- 10 件
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x01000000020000000000000000000000CB2064B200000000D0020000000000007802000000000000300300003000000001000300000000000000000000000000380300003800000004000A000000000000000000000000000700000060B3BE0060A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190000000000000000000000000000001C0000000000000004010000000000000C0100000000000008000000000000001000110000002041000000000000803F0004000003000000C923BE0060A7000000000000506AF84097AE0A000000000002000000000000000000E03F00000000000000000000000000000000000000000000244000000000506AF8402FD0974B2EFFEF3F8292BD0060A700000000000000002440FC2709000000000001000000000000000000F03F00000000B069F840000000000000000000000000B069F84000000000000000008A87E31D0AA0F63F4B40BD0060A700000000000000002440E82709000000000001000000000000000000F03F000000000000000000000000000000000000000000002440000000000000244000000000000000000A00000000000000
GO
DBCC FREEPROCCACHE
DECLARE @key int = 0
EXEC sp_executesql N'SELECT * FROM T_Emp WHERE Flag = @p1', N'@p1 int', @key
WAITFOR DELAY '00:00:05'
GO 8

-- 50 万件
UPDATE STATISTICS T_Emp(IX_Flag) WITH STATS_STREAM = 0x0100000002000000000000000000000045923AE800000000EC020000000000009402000000000000300300003000000001000300000000000000000000000000380300003800000004000A0000000000000000000000000007000000AEA9C20060A7000095D003000000000095D0030000000000000000000000003F0D37863600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000020000000200000002000000110000000000A04040257448000000000000803F000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000019000000000000000000000000000000380000000000000020010000000000002801000000000000100000000000000024000000000000001000110000002041000000000000803F0004000010001100C0227448000000000000803F01040000030000004BA5C20060A7000000000000A8840E412F1313000000000002000000000000000000E03F000000000000000000000000000000000000000000000000000000000000000000000000000000009693C20060A700000000000000002440A4420F000000000001000000000000000000F03F0000000058840E4100000000000000000000000058840E410000000000000000DB56CC1563A0F63F33ECBE0060A7000000000000506AF840E1BB0D000000000002000000000000000000E03F00000000000000000000000000000000000000000000344000000000F06AF8402FD0974B2EFFEF3F95D0030000000000
GO

DBCC FREEPROCCACHE
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 = 0x01000000020000000000000000000000CB2064B200000000D0020000000000007802000000000000300300003000000001000300000000000000000000000000380300003800000004000A000000000000000000000000000700000060B3BE0060A700000A000000000000000A00000000000000000000000000803FCDCCCC3D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000100000002000000110000000000A04000002041000000000000803F0000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190000000000000000000000000000001C0000000000000004010000000000000C0100000000000008000000000000001000110000002041000000000000803F0004000003000000C923BE0060A7000000000000506AF84097AE0A000000000002000000000000000000E03F00000000000000000000000000000000000000000000244000000000506AF8402FD0974B2EFFEF3F8292BD0060A700000000000000002440FC2709000000000001000000000000000000F03F00000000B069F840000000000000000000000000B069F84000000000000000008A87E31D0AA0F63F4B40BD0060A700000000000000002440E82709000000000001000000000000000000F03F000000000000000000000000000000000000000000002440000000000000244000000000000000000A00000000000000
GO
DBCC FREEPROCCACHE
DECLARE @key int = 0
EXEC sp_executesql N'SELECT * FROM T_Emp WHERE Flag = @p1', N'@p1 int', @key
WAITFOR DELAY '00:00:05'
GO 5

レコメンドの確認.sql

-- ALTER DATABASE TESTDB1 SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); 
SELECT name, desired_state_desc, actual_state_desc, reason_desc FROM sys.database_automatic_tuning_options;
select * from sys.database_plan_correction_recommendationsselect * 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