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 */
