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

Ok, last one for a while.  This time, pulling the stats relevant to a plan from the plan XML in the plan cache (thanks to trace flag 8666), then getting a little bit of stats update history via trace flag 2388.

I'm not particularly happy with performance; it took between 10 and 12 seconds to return a 12 row resultset 😥

But its good enough for the immediate troubleshooting needs.

Some sample output is below; note that the output is sorted by database, schema, table, and stats name.  But the updates of any one stat are NOT sorted based on the date in [Updated].  I don't have the brainpower right now to figure out how to convert the [Updated] values to DATETIME to get them to sort properly.  Maybe sometime soon 😏

CREATE OR ALTER PROCEDURE sasquatch__hist_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 flags 8666 was enabled at system level or session when plan was compiled
and trace flag 2388 is enabled at session level when stored procedure is executed, recent history of stats used to compile the plan will dsiplayed
*/

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(INT, NULL) done
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);

DROP TABLE IF EXISTS #one_stat_hist;
CREATE TABLE #one_stat_hist
(Updated NVARCHAR(50), [Table Cardinality] BIGINT, [Snapshot Ctr] BIGINT, 
 Steps INT, Density FLOAT, [Rows Above] BIGINT, [Rows Below] BIGINT, 
 [Squared Variance Error] NUMERIC(16,16), [Inserts Since Last Update] BIGINT, 
 [Deletes Since Last Update] BIGINT, [Leading Column Type] NVARCHAR(50));

DROP TABLE IF EXISTS #stats_hist;

 CREATE TABLE #stats_hist
(dbName NVARCHAR(50), schemaName NVARCHAR(50), tableName NVARCHAR(50), statName NVARCHAR(50),
 Updated NVARCHAR(50), [Table Cardinality] BIGINT, [Snapshot Ctr] BIGINT, 
 Steps INT, Density FLOAT, [Rows Above] BIGINT, [Rows Below] BIGINT, 
 [Squared Variance Error] NUMERIC(16,16), [Inserts Since Last Update] BIGINT, 
 [Deletes Since Last Update] BIGINT, [Leading Column Type] NVARCHAR(50));

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

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

     INSERT INTO #one_stat_hist
     EXEC (@cmd);

  INSERT INTO #stats_hist
  SELECT @db, @schema, @table, @stats, *
  FROM #one_stat_hist;

     UPDATE #QO_stats
     SET done = 1 
     WHERE @db =   #QO_stats.dbName
     AND @schema = #QO_stats.schemaName
     AND @table =  #QO_stats.tableName 
     AND @stats =  #QO_stats.statName;

     TRUNCATE TABLE #one_stat_hist;

END

SELECT * FROM #stats_hist ORDER BY dbName, schemaName, tableName, statName

EXEC (N'USE ' + @startDB);

/* 20181206 */