SQL Server - recursive CTE to retrieve dependency chain of Resource Governor Classifier Function

SQL Server Resource Governor classifier functions can include references to functions as well as tables in the master database.  And each referenced function can in turn reference additional functions and tables.

In order to get a thorough understanding of how sessions are classified on a given system, one may need some familiarity with each function or table in the dependency tree.

This recursive function will retrieve all object names in the dependency tree (object names will be retrieved but column names will not be retrieved, due to the referenced_minor_id = 0 qualifier.

;WITH DTree AS 
(	SELECT		root_id		= classifier_function_id
	,		referencing_id	= classifier_function_id
	,		referenced_id	= classifier_function_id
	,		NestLevel	= 1
	FROM		sys.dm_resource_governor_configuration
	UNION ALL
	SELECT		root_id         = rgc.classifier_function_id
	,		d1.referencing_id
	,		d1.referenced_id
	,		NestLevel + 1
	FROM		sys.sql_expression_dependencies d1 
	JOIN		DTree r		ON d1.referencing_id =  r.referenced_id	
	CROSS JOIN	sys.dm_resource_governor_configuration rgc
	WHERE		d1.referenced_minor_id = 0)
SELECT		classifier_function	= OBJECT_NAME(t1.root_id)
,		referencing_name	= OBJECT_NAME(t1.referencing_id)
,		referenced_name		= OBJECT_NAME(t1.referenced_id)
,		referenced_type		= so.type_desc
,		t1.NestLevel
FROM		DTree t1
JOIN		sys.objects so		ON so.object_id = t1.referenced_id
ORDER BY	t1.NestLevel, t1.referencing_id;

SQL Server - recursive CTE to retrieve rowstore index IAM chain

 A SQL Server recursive CTE can reach a maximum level of recursion of 32768.

Strting with the first IAM page for a rowstore index, the entire IAM chain can be retrieved, as each IAM page indicates the next page in the chain.

So a recursive CTE could reliably retrieve IAM chains for the rowstore indexes in a filegroup until the filegroup exceeds 128 TB in aggregate file size. (Because each IAM page accounts for database file usage in a ~4GB range of a file, for a single rowstore index.)

This CTE will retrieve an IAM chain. AFAIK, it's performance will beat the brakes off any similar query relying on sys.dm_db_database_page_allocations.

DECLARE @object_id	INT		= OBJECT_ID('NOTES_FROM_BEYOND');
DECLARE @index_id	SMALLINT	= 1; 
DECLARE @type_desc	VARCHAR(20)	= 'IN_ROW_DATA';

					/*IN_ROW_DATA, ROW_OVERFLOW_DATA, LOB_DATA*/

;WITH start_page AS 
(	SELECT				fp.file_id
	,				fp.page_id
	FROM				sys.system_internals_allocation_units siau
	JOIN				sys.partitions sp 
	ON				siau.container_id	= sp.partition_id
	CROSS APPLY			sys.fn_PhysLocCracker(siau.first_iam_page) fp
	WHERE				sp.object_id		= @object_id
	AND				sp.index_id		= @index_id
	AND				siau.type_desc		= @type_desc	)
, rec_cte AS
(	SELECT				next_page_file_id	= CONVERT(BIGINT, sp.file_id)
	,				next_page_page_id	= CONVERT(BIGINT, sp.page_id)
	,				iam_page_ordinal	= 1
	FROM				start_page sp

	UNION ALL
	SELECT				CONVERT(BIGINT, dpi.next_page_file_id)
	,				CONVERT(BIGINT, dpi.next_page_page_id)
	,				iam_page_ordinal = iam_page_ordinal + 1
	FROM				rec_cte
	CROSS APPLY			sys.dm_db_page_info(DB_ID(), next_page_file_id, next_page_page_id, 'DETAILED') dpi
	WHERE				dpi.next_page_file_id <> 0    ) 
SELECT		rc.iam_page_ordinal, file_id = rc.next_page_file_id, page_id = rc.next_page_page_id
FROM		rec_cte rc
WHERE		rc.next_page_file_id IS NOT NULL
ORDER BY	rc.iam_page_ordinal
OPTION		(maxrecursion 32767);

SQL Server snapshot db temporary stats - created or updated

For a great introduction to this topic, please see this post at SQLSkills from Joe Sack.

Temporary Statistics for Database Snapshots in SQL Server 2012

https://www.sqlskills.com/blogs/joe/temporary-statistics-for-database-snapshots-in-sql-server-2012/

Today's test instance is running SQL Server 2019 CU6.  

OK, let's create a test database.

USE [master]

GO

CREATE DATABASE [snap_test_src]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'snap_test_src', SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB 
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\snap_test_src.mdf')
 LOG ON 
( NAME = N'snap_test_src__log', SIZE = 8192KB , MAXSIZE = 2GB , FILEGROWTH = 65536KB 
, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\snap_test_src__log.ldf')

ALTER DATABASE [snap_test_src] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [snap_test_src] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [snap_test_src] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [snap_test_src] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [snap_test_src] SET ANSI_NULLS OFF
ALTER DATABASE [snap_test_src] SET ANSI_PADDING OFF
ALTER DATABASE [snap_test_src] SET ANSI_WARNINGS OFF 
ALTER DATABASE [snap_test_src] SET ARITHABORT OFF
ALTER DATABASE [snap_test_src] SET AUTO_CLOSE OFF 
ALTER DATABASE [snap_test_src] SET AUTO_SHRINK OFF
ALTER DATABASE [snap_test_src] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [snap_test_src] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [snap_test_src] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [snap_test_src] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [snap_test_src] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [snap_test_src] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [snap_test_src] SET DISABLE_BROKER 
ALTER DATABASE [snap_test_src] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [snap_test_src] SET TRUSTWORTHY OFF 
ALTER DATABASE [snap_test_src] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [snap_test_src] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [snap_test_src] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [snap_test_src] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [snap_test_src] SET RECOVERY FULL 
ALTER DATABASE [snap_test_src] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [snap_test_src] SET DB_CHAINING OFF 
ALTER DATABASE [snap_test_src] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
ALTER DATABASE [snap_test_src] SET TARGET_RECOVERY_TIME = 60 SECONDS 
ALTER DATABASE [snap_test_src] SET DELAYED_DURABILITY = DISABLED 
ALTER DATABASE [snap_test_src] SET QUERY_STORE = OFF
ALTER DATABASE [snap_test_src] SET READ_WRITE
ALTER DATABASE [snap_test_src] SET MULTI_USER

Now let's create a very boring table and put 1000 numbers into each of 2 columns.

 

USE snap_test_src;
CREATE TABLE stats_test(col1 INT NOT NULL, col2 INT NOT NULL);

;WITH num AS 
(SELECT TOP (1000) n = ROW_NUMBER()
                       OVER (ORDER BY (SELECT NULL))
 FROM master..spt_values)
INSERT INTO stats_test
SELECT n, 1001 - n
FROM num;

Because auto_create_statistics is on in this database, a little query will result in creating a statistic on col1.

SELECT * FROM stats_test where col1 < 3;

And here is that statistic.

SELECT ss.name, ss.stats_id, ss.auto_created, ss.user_created, ss.is_temporary
     , sp.last_updated
FROM snap_test_src.sys.stats ss
CROSS APPLY snap_test_src.sys.dm_db_stats_properties(ss.object_id, ss.stats_id) sp
WHERE ss.object_id = object_id('stats_test');

Now let's make that statistic stale 😆 A thousand more numbers oughtta do it.

 

USE snap_test_src;

;WITH num AS 
(SELECT TOP (1000) n = ROW_NUMBER()
                       OVER (ORDER BY (SELECT NULL))
 FROM master..spt_values)
INSERT INTO stats_test
SELECT n, 1001 - n
FROM num;

All right.  Got a table with 2000 rows and a single auto-created statistics object that's now stale.

Let's create a snapshot database!!

CREATE DATABASE snap_test ON
    ( NAME = snap_test_src, 
	  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\snap_test_src.ss')
AS SNAPSHOT OF snap_test_src;

OK... now for a query in the snapshot database...

USE snap_test
SELECT * FROM stats_test
WHERE col1 < 3 AND col2 > 998;

Let's check those stats... now there are two stats and both are marked as temporary.

SELECT ss.name, ss.stats_id, ss.auto_created, ss.is_temporary
     , sp.last_updated
FROM snap_test.sys.stats ss
CROSS APPLY snap_test.sys.dm_db_stats_properties(ss.object_id, ss.stats_id) sp
WHERE ss.object_id = object_id('stats_test');

Let's take another look, and compare the stats in the source database and the snapshot database.

(*** update 2020 September 24 ***

this little trick where I reference dm_db_stats_properties in a different database by adding the database name to the reference works in SQL Server 2019 CU6.  But it didn't work on a SQL Server 2016 instance I tried the same trick on - looks like both references to the function resolved to the database context my query was in.)

USE snap_test
SELECT ss.name, source_rows = sp2.rows, source_sample = sp2.rows_sampled
     , snap_rows = sp.rows, snap_sample = sp.rows_sampled
FROM snap_test.sys.stats ss
CROSS APPLY snap_test.sys.dm_db_stats_properties(ss.object_id, ss.stats_id) sp
OUTER APPLY snap_test_src.sys.dm_db_stats_properties(ss.object_id, ss.stats_id) sp2
WHERE ss.object_id = object_id('stats_test');

The source database and the snapshot database both have a stats_test table with 2000 rows, of course.  But the source database has only a statistic on col1, not col2.  And the col1 statistic was last updated when the table had 1000 rows.  The snapshot database has stats on col1, also.  They've been updated - as a temporary statistic - with the row count of 2000 and the sample of all 2000 rows.  That was current as of the time of the snap.

 Consider a snapshot database which is created daily.  The purpose is to provide analytics reporting access while maintenance or data loads take place in the source database.  In the snapshot database, analytics reports have no locking concerns from the activity in the underlying source database.  And the temporary statistics provided by SQL Server, combined with the statistics inherited from the source database, provide a lot of information to the optimizer for query plan selection.

But what if significant fact tables are queried in the snapshot and leave a situation like col2 in stats_test?  A column which generates an auto-created stat in the snapshot, but never gets a statistic created in the source database.  Each day, the cost of creating that statistic and every statistic like it will be paid as part of the workload.  Even if the underlying table is a now-stable dimension. 

If a snapshot database is being used to provide reporting access, it may be valuable to check for temporary statistics that are perpetually being created - or updated - in the snapshot.  In some cases, creating or updating the statistic in the underlying source database may provide a benefit by eliminating redundant work.

q

SQL Server Query Store: Usability Soft Spots -- Part 3

Others in this series...

SQL Server Query Store: Usability Soft Spots -- Part 1

https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft.html

 SQL Server Query Store: Usability Soft Spots -- Part 2

https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft_16.html

Summary of this post: comb through the surprise plans, finding an example that looks like the description of "morally equivalent plans"... and one that kinda doesn't.

~~~~~

A reminder of where this set of tests is currently.

In Part 2, the repro code was tweaked a bit, in order to no longer use a tempdb table in a CTAS statement ("SELECT... INTO", Create Table As SELECT) and to TRUNCATE results table between iterations rather than DROP. After 1600 iterations of the test query with some filler queries also executed with each iteration, 19 plan_id values were displayed in Query Store "Tracked Queries".

Attempting to force each one of those plan_id values from Query Store resulted in 2 successful forces and 17 uses of surprise plans!

A grid of all 34 plan_id values is below.  Two interesting things are highlighted.  In the blue box a single row is highlighted, where plan_id 195 produced plan_id 292 when forced.  That's unusual because it's the only example in the list where one of the original 17 plans produced another one of the 17 originals as the "Surprise Plan". The other box highlights two consecutive rows.  Plan_id values 814 and 899 both produced "Surprise Plan" plan_id 3250.

OK, let's see if any of those "surprise plans" resulted in a last_force_failure_reason_desc.

SELECT plan_id, query_id, engine_version, compatibility_level,  
       is_forced_plan, last_force_failure_reason_desc
FROM sys.query_store_plan 
WHERE query_id = 2
AND plan_id < 3220 
AND plan_id NOT IN (292, 501)
ORDER BY plan_id ASC;

Nope, not a single one is listed as a "force failure", even though the plan forced at the time was not used.

Let's compare plan_id 195 and 292.

That's pretty much what we'd expect from a "morally equivalent plan".  Both plans have the same shape.  All joins and plan operators are in the same location of the two graphical plans.

After seeing that plan_id 195 and plan_id 292 have all the same operators in all the same places, why did new estimates appear for several operators, resulting in plan_id 292 being used when plan_id 195 was forced?

Was it due to a stats update?
Because the new CE is used, optimizer stats info is included in plan XML.  Here's OptimizerStatsUsage for plan_id 195.

<OptimizerStatsUsage>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="193" SamplingPercent="100" Statistics="[plan_persist_plan_cidx]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_text_cidx]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[_WA_Sys_00000002_09DE7BCC]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="193" SamplingPercent="100" Statistics="[plan_persist_plan_idx1]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_idx1]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_cidx]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
</OptimizerStatsUsage>

And here's OptimizerStatsUsage for plan 292.

<OptimizerStatsUsage>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="290" SamplingPercent="100" Statistics="[plan_persist_plan_cidx]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_text_cidx]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[_WA_Sys_00000002_09DE7BCC]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="290" SamplingPercent="100" Statistics="[plan_persist_plan_idx1]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_idx1]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_cidx]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
</OptimizerStatsUsage>

Notice that LastUpdate for each of the six optimizer stats is the same in plan_id 195 and plan_id 292.
Its not a stats update that results in different estimates.  ModificationCount shows a difference, though, with each of the six ModificationCount values higher in plan_id 292 than plan_id 195.

Here's a relevant passage from a blog post by Itzik Ben Gan.


Seek and You Shall Scan Part II: Ascending Keys
https://www.itprotoday.com/sql-server/seek-and-you-shall-scan-part-ii-ascending-keys

• "The new cardinality estimator identifies that the query filter exceeds the maximum value in the histogram. It has access to the column modification counter (call it in short modctr), so it knows how many changes took place since the last refresh. When the column is unique and of an integer type or numeric with a scale of zero, SQL Server assumes it’s ascending. So it interpolates the estimate based on the distribution of the values within the existing histogram and the number of changes that took place since the last refresh."

*This* definitely seems like the description I've heard of a "morally equivalent plan".  As estimates for various operators changed along with modctr, eventually the previous plan no longer "fit" even though the newer plan still had the same plan shape, with the same plan operators in the same positions.

Let's compare plan_id 814 to plan_id 3250.

All operator in the same spots in the two plans, the difference between them being the estimates.  Its the same when comparing plan_id 899 to plan_id 3250.

But when I look at the planXML for plan_id 3250, I see something extraordinary.  The QueryHash value is the same as the QueryPlanHash value.

QueryHash="0xE91EDF2B4F8EA8B4" QueryPlanHash="0xE91EDF2B4F8EA8B4"

Well.  Let's see how widespread that phenomenon is. First we';ll check the original 19 plan_id values.

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
planXML AS
(SELECT plan_id, planXML = CONVERT(XML, query_plan)
 FROM sys.query_store_plan WHERE query_id = 2 AND plan_id < 3220)
SELECT plan_id, xml_query_hash = RelOp.pln.value(N'@QueryHash', N'varchar(50)'), 
       xml_plan_hash = RelOp.pln.value(N'@QueryPlanHash', N'varchar(50)'),
       TerminationReason = RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)')
FROM planXML
CROSS APPLY planXML.planXML.nodes(N'//StmtSimple') RelOp (pln);

Nope, none of them have QueryHash = QueryPlanHash in the plan XML.

What about the 15 surprise plans that only showed up once we started forcing other plans?

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
planXML AS
(SELECT plan_id, planXML = CONVERT(XML, query_plan)
 FROM sys.query_store_plan WHERE query_id = 2 AND plan_id > 3219)
SELECT plan_id, xml_query_hash = RelOp.pln.value(N'@QueryHash', N'varchar(50)'), 
       xml_plan_hash = RelOp.pln.value(N'@QueryPlanHash', N'varchar(50)'),
       TerminationReason = RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)')
FROM planXML
CROSS APPLY planXML.planXML.nodes(N'//StmtSimple') RelOp (pln);

Wow.  15 "surprise plans" first showed up after other plans were forced for query_id 2.  Every single one of them has QueryHash equal to QueryPlanHash in the plan XML.

That is... hm.  So even if the forced query plan doesn't fit the estimates, or there's some other "near-miss" reason for avoiding the forced query plan... the QueryHash value in the plan XML should always be the same as [sys].[query_store_query].[query_hash].  Its not in this case.  And strangely QueryHash = QueryPlanHash.

(Microsoft hasn't yet confirmed this kind of QueryHash behavior is a bug... but I honestly can't imagine a design that anticipates or intends this behavior.)


Q

dbcc MemoryStatus -- IO error??

No answers in this post, just a question I don't want to lose track of.

2019 May 7 I put this question to #sqlhelp:

#sqlhelp [dbcc memorystatus] in #sqlserver 2017 cu13 gives information about IO errors. Is there another source for that error count or more details elsewhere? Nothing in the SQL Server error log, Windows event log.

https://twitter.com/sqL_handLe/status/1125806349978415109



No answer yet.

I set up a task via schtasks to send 'dbcc memorystatus' output to a file every 5 minutes for a memory investigation.  I thought it might shed some light on this information from 'dbcc memorystatus'.  Nope, not yet.
hhttps://twitter.com/sqL_handLe/status/1125806349978415109ttps://twitter.com/sqL_handLe/status/1125806349978415109

Here are a few of the results I scraped.  Not sure if 'IO error' is a count of errors, or indicating an error code for the most recent IO error.  Though there's seemingly no information for errors with some of these numbers.  It may be a count of errors within each checkpoint, or somesuch.  The two busy databases in this instance are both using automatic rather than indirect checkpoints.
 
May 11 time        Dirty     IO error
4:00:01          2382710         7685
4:05:01          1510246         2412
4:10:01          1076007        19739
4:15:01           364968         9050
4:20:00            44028        16855
4:25:00            28251         1653
4:30:00            23242         2674
4:35:00            24668        32750
4:40:00            25199       124190
4:50:01            26107         2079
4:55:00            25661        70204
5:00:00           909808        13664



I'll update this post if I learn anything exciting in the future.

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