Fun with SQL Server Plan Cache, Trace Flag 8666, and Stats_stream

More fun.  This time with trace flag 8666, so that it can be used prior to SQL Server 2016 SP2 or with Legacy Cardinality estimator.  If you want to transfer stats between two systems with the same schema for query plan-related testing and research.

Note: this version of the stored procedure takes a plan_handle as a parameter rather than a plan_id for the query store.

CREATE OR ALTER PROCEDURE sasquatch__xfer_one_plan_stats_T8666 @plan_handle VARBINARY(64)
AS
/* stored procedure based on work explained in the following blog post
https://sql-sasquatch.blogspot.com/2018/06/harvesting-sql-server-trace-flag-8666.html

supply a plan_handle and if trace flag 8666 was enabled at system level or session when plan was compiled
UPDATE STATISTICS commands for each of the stats used to compile the plan will be generated
*/

DECLARE @startdb NVARCHAR(256),
        @db      NVARCHAR(256),
        @schema  NVARCHAR(256),
        @table   NVARCHAR(256),
        @stats   NVARCHAR(256),
        @cmd     NVARCHAR(MAX);
        
SET @startdb = DB_NAME();
DROP TABLE IF EXISTS #plan;
CREATE TABLE #plan(planXML XML);

INSERT INTO #plan
SELECT CONVERT(XML, query_plan) planXML
FROM sys.dm_exec_query_plan(@plan_handle);

;WITH XMLNAMESPACES(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DbName.DbName_Node.value('@FieldValue','NVarChar(128)')              dbName,
       SchemaName.SchemaName_Node.value('@FieldValue','NVarChar(128)')      schemaName,
       TableName.TableName_Node.value('@FieldValue','NVarChar(128)')        tableName,
       StatsName_Node.value('@FieldValue','NVarChar(128)')                  statName,
       CONVERT(VARBINARY(MAX), NULL) [stats_stream],
       CONVERT(BIGINT, NULL) [rowcount], CONVERT(BIGINT, NULL) [pagecount]
INTO #QO_stats
FROM #plan qsp
CROSS APPLY qsp.planXML.nodes(N'//InternalInfo/EnvColl/Recompile')       AS Recomp(Recomp_node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'Field[@FieldName="wszDb"]')       AS DbName(DbName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'Field[@FieldName="wszSchema"]')   AS SchemaName(SchemaName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'Field[@FieldName="wszTable"]')    AS TableName(TableName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'ModTrackingInfo')                 AS [Table](Table_Node)
CROSS APPLY Table_Node.nodes(N'Field[@FieldName="wszStatName"]')         AS [Stats](StatsName_Node)
OPTION (MAXDOP 1);

CREATE TABLE #one_stat ([stats_stream] VARBINARY(MAX), [rowcount] BIGINT, [pagecount] BIGINT);

WHILE EXISTS (SELECT TOP 1 1 FROM #QO_stats WHERE [stats_stream] IS NULL)
BEGIN
     SELECT TOP 1 @db = dbName, @schema = schemaName, @table = tableName, @stats = statName
     FROM #QO_stats
     WHERE [stats_stream] IS NULL
     ORDER BY dbName, schemaName, tableName, statName;

     SET @cmd = N'USE ' + @db + N' DBCC SHOW_STATISTICS(''' + @schema + N'.' + @table + N''', ' + @stats 
              + N') WITH STATS_STREAM, NO_INFOMSGS'

     INSERT INTO #one_stat
     EXEC (@cmd);
     UPDATE #QO_stats
     SET [stats_stream] = os.[stats_stream], [rowcount] = os.[rowcount], [pagecount] = os.[pagecount] 
     FROM #one_stat os
     WHERE @db =   #QO_stats.dbName
     AND @schema = #QO_stats.schemaName
     AND @table =  #QO_stats.tableName 
     AND @stats =  #QO_stats.statName;

     TRUNCATE TABLE #one_stat;

END

SELECT N'USE ' + qo.dbName + N' UPDATE STATISTICS ' + qo.schemaName + N'.' + qo.tableName + N'(' + qo.statName + N') WITH ' +
CASE WHEN qo.[pagecount] IS NULL THEN '' ELSE N'PAGECOUNT = ' + CONVERT(NVARCHAR(20), qo.[pagecount]) + N', ' END +
CASE WHEN qo.[rowcount] IS NULL THEN '' ELSE N'ROWCOUNT = ' + CONVERT(NVARCHAR(20), qo.[rowcount]) + N', ' END + 
N'STATS_STREAM = ' + CONVERT(NVARCHAR(MAX), qo.[stats_stream], 1) 
FROM #QO_stats qo;

EXEC (N'USE ' + @startDB);

/* 20181206 */