Harvesting SQL Server optimizer stats detail from Query Plan XML: Part I Trace Flag 8666

***** Update 6 November 2018 *****

Here's a link to the more efficient type of query that can be used after SQL Server 2016 SP2.  After SQL Server 2016 SP2, trace flag 8666 is no longer needed to get optimizer plan stats details into plan XML.  They are they by default - and in a nicer structure than previously available with T8666.

Here you go...

Harvesting SQL Server optimizer stats detail from Query Plan XML: Part II SQL Server 2016 SP2++

https://sql-sasquatch.blogspot.com/2018/11/harvesting-sql-server-optimizer-stats.html

***** End update *****


Years ago someone said "Hey - why not drop auto-created stats, since the stats you need will just get created again and you'll end up getting rid of those you no longer need."   That *may* be a reasonable step on some systems.  If the risk of bad plans on first execution of a query needed stats that have been dropped is too high, its a bad deal.  If the potential concurrent cost of auto-creating dropped stats is too high, that's a bad deal.  What about analyzing query plans over some period of time to see which stats are actually used in those plans?  Then auto-stats which aren't used in that set of plans could be dropped.

That type of stats analysis could have other uses, too.  Prioritizing manual stats updates in regular maintenance comes to mind.  Or, determining what stats to create/update on an Always On Availability Group primary based on secondary activity.  And troubleshooting problem queries or identifying suspicious "watchlist" stats based on highly variable queries/plans they are involved with.

So I created the following blog post almost 4 years ago... 

Before I drop all those SQL Server auto-generated column stats....
http://sql-sasquatch.blogspot.com/2014/07/before-i-get-rid-of-all-sql-server-auto.html 

And now I'll plead with you to not use the query in that 2014 blog post... it's awful.  If you want to query trace flag 8666 style stats from plan XML, please start from the query in this very 2018 blog post instead - its much more well behaved 😊

In SQL Server 2016 SP2 and after, the optimizer stats consulted in compiling the plan are included in the plan XML by default without trace flag 8666.

Today, let's talk about the world before that.

If trace flag 8666 is set at the session level, a plan retrieved from plan cache will also include details for optimizer stats relevant to the plan in the XML. (Trace flag 8666 also includes other details.) The format is quite different than in SQL Server 2016 SP2 and later. If trace flag 8666 is set globally, plans in the Query Store will also contain the trace flag 8666 details. On a production server, I recommend targeted use of trace flag 8666 for investigation or troubleshooting purposes - please test very carefully for overhead if trace flag 8666 will be used globally.

So here I create a test table, with an integer column and an XML column.  Today I'll insert a single plan for testing.

DROP TABLE IF EXISTS test_plan_XML 
CREATE TABLE test_plan_XML
(plan_id BIGINT,
 test_plan XML);
INSERT INTO test_plan_XML
SELECT 36, '<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3025.34">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="308.313" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.999363" StatementText="SELECT 1" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="480" CompileTime="142" CompileCPU="126" CompileMemory="5016">
            <InternalInfo LockClassNoHint="0" LockClassIntLockHint="0" LockClassRCIsoHint="0">
              <EnvColl>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="T8666" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="sysclsobjs" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000002_00000040" />
                  </ModTrackingInfo>
                </Recompile>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="T8666" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="sysnsobjs" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000002_0000002C" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000004_0000002C" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                </Recompile>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="T8666" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="sysscalartypes" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc1" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc1" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc2" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc1" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc2" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000004_00000032" />
                  </ModTrackingInfo>
                </Recompile>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="mssqlsystemresource" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="syscolpars" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000002_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000006_00000029_readonly_database_statistics" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_0000000B_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000005_00000029_readonly_database_statistics" />
                  </ModTrackingInfo>
                </Recompile>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="T8666" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="syscolpars" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000002_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000006_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_0000000D_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_0000000B_00000029" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000005_00000029" />
                  </ModTrackingInfo>
                </Recompile>
                <Recompile>
                  <Field FieldName="wszDb" FieldValue="T8666" />
                  <Field FieldName="wszSchema" FieldValue="sys" />
                  <Field FieldName="wszTable" FieldValue="sysschobjs" />
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc1" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="clst" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc1" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc2" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc3" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000005_00000022" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000006_00000022" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="nc3" />
                  </ModTrackingInfo>
                  <ModTrackingInfo>
                    <Field FieldName="wszStatName" FieldValue="_WA_Sys_00000008_00000022" />
                  </ModTrackingInfo>
                </Recompile>
              </EnvColl>
              <RX IsRXPlan="0" />
            </InternalInfo>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>';

So here's the query...

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT DISTINCT plan_id, 
                DbName.DbName_Node.value('@FieldValue','NVarChar(128)')              db,
                SchemaName.SchemaName_Node.value('@FieldValue','NVarChar(128)')      [schema],
                TableName.TableName_Node.value('@FieldValue','NVarChar(128)')        tbl,
                StatsName_Node.value('@FieldValue','NVarChar(128)')                  sts
FROM (SELECT plan_id, test_plan from test_plan_XML) qsp
CROSS APPLY qsp.test_plan.nodes(N'//p:InternalInfo/p:EnvColl/p:Recompile')           AS Recomp(Recomp_node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'p:Field[@FieldName="wszDb"]')                 AS DbName(DbName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'p:Field[@FieldName="wszSchema"]')             AS SchemaName(SchemaName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'p:Field[@FieldName="wszTable"]')              AS TableName(TableName_Node)
CROSS APPLY Recomp.Recomp_Node.nodes(N'p:ModTrackingInfo')                           AS [Table](Table_Node)
CROSS APPLY Table_Node.nodes(N'p:Field[@FieldName="wszStatName"]')                   AS [Stats](StatsName_Node)
OPTION (MAXDOP 1);

And... it works!



Its fairly efficient, too.  The results from "set statistics time on" for this test case are below. If parsing more plans, certainly the CPU time and elapsed time will increase.

I've got an upcoming workshop at TugaIT in July 2018... getting the most bang out of your buck for administrative tasks like stats updates is a topic on the docket!

TugaIT Summer 2018 Edition: Insight-Based Administration
http://sql-sasquatch.blogspot.com/2018/07/tugait-summer-2018-edition-insight.html