Something I have been making use of recently is the change in the way statistics that have been used to compile a plan are presented to us as SQL Server users. On a number of occasions I have needed to understand, or at least get some sense of, which statistics SQL Server has used to come up with some of its estimates, usually when troubleshooting a poor estimate. This information has been available to us for a while, but up until fairly recent (OK – define recent, I guess!) versions of SQL Server, it was a bit hidden.
The Old Days
Prior to SQL Server 2017, we needed to use a number of trace flags to surface this information.
For those queries using the legacy Cardinality Optimizer, we needed to enable trace flags, all of which are undocumented by Microsoft and so should be used with caution:
9204 – Output Statistics used by Query Optimizer
Best described, in my opinion, by Paul White as:
Shows the ‘interesting’ statistics that end up being fully loaded, and used to produce cardinality and distribution estimates for some plan alternative or other. Only happens when a plan is compiled or recompiled—not when a plan is retrieved from cache.
9292 – Output Statistics considered to be used by Query Optimizer
Again, described by Paul White as:
Reports statistics objects considered ‘interesting’ by the query optimizer when compiling, or recompiling the query. For potentially useful statistics, just the stats header is loaded.
When using the default (new) Cardinality optimizer, the above don’t work and we have to use the following, which incidentally is also available on the old estimator.
2363 – TF Selectivity
Both of the above methods require 3604, which redirects the output of the above trace flags to the SSMS messages tab.
A comprehensive list of trace flags can be found at SQLServerCentral.com.
The example below is on a SQL Server 2014 instance using the AdventureWorks2014 database.
First, we’ll ensure our queries use the legacy optimizer by changing our compatibility level to 110:
USE [master];
ALTER DATABASE [AdventureWorks2014] SET COMPATIBILITY_LEVEL = 110;
Here is our query; it just sums the value of orders by salespeople called John. I have not spent a great deal of time studying the AdventureWorks schema, so this may not be entirely accurate however, for this post, this query will demonstrate what is required to understand the feature.
SELECT p.FirstName,
p.LastName,
SUM(soh.TotalDue) AS TotalSales
FROM sales.SalesOrderHeader soh
JOIN Person.Person p
ON soh.CustomerId = p.BusinessEntityId
WHERE p.FirstName = 'John'
GROUP BY p.BusinessEntityID,
p.FirstName,
p.LastName;
If we want to see which stats were loaded, as we are on SQL Server 2014 and using the legacy estimator, we need to enable trace flags 9204, 9292 and 3604. The trace flags can be enabled using DBCC TRACEON or query-level hints:
DBCC TRACEON:
DBCC TRACEON (9204,9292,3604);
SELECT p.FirstName,
p.LastName,
SUM(soh.TotalDue) AS TotalSales
FROM sales.SalesOrderHeader soh
JOIN Person.Person p
ON soh.CustomerId = p.BusinessEntityId
WHERE p.FirstName = 'John'
GROUP BY p.BusinessEntityID,
p.FirstName,
p.LastName;
Query-level hint:
SELECT p.FirstName,
p.LastName,
SUM(soh.TotalDue) AS TotalSales
FROM sales.SalesOrderHeader soh
JOIN Person.Person p
ON soh.CustomerId = p.BusinessEntityId
WHERE p.FirstName = 'John'
GROUP BY p.BusinessEntityID,
p.FirstName,
p.LastName
OPTION (QUERYTRACEON 9204,QUERYTRACEON 9292,QUERYTRACEON 3604);
Either approach returns the same result in the messages tab, assuming the execution plan is being compiled for the first time and not retrieved from the plan cache:
Stats header loaded: DbName: AdventureWorks2014, ObjName: sales.SalesOrderHeader, IndexId: 4, ColumnName: CustomerID, EmptyTable: FALSE
Stats loaded: DbName: AdventureWorks2014, ObjName: sales.SalesOrderHeader, IndexId: 4, ColumnName: CustomerID, EmptyTable: FALSE
Stats header loaded: DbName: AdventureWorks2014, ObjName: Person.Person, IndexId: 1, ColumnName: BusinessEntityID, EmptyTable: FALSE
Stats loaded: DbName: AdventureWorks2014, ObjName: Person.Person, IndexId: 1, ColumnName: BusinessEntityID, EmptyTable: FALSE
Stats header loaded: DbName: AdventureWorks2014, ObjName: Person.Person, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
Stats loaded: DbName: AdventureWorks2014, ObjName: Person.Person, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
Now let’s move the database over to the “new” cardinality estimator by changing the compatibility level to 120:
USE [master];
ALTER DATABASE [AdventureWorks2014] SET COMPATIBILITY_LEVEL = 120;
Now we can run our query again. As before, we can use DBCC TRACEON or the query hints; for this example, I’ll just use the query hint:
SELECT p.FirstName,
p.LastName,
SUM(soh.TotalDue) AS TotalSales
FROM sales.SalesOrderHeader soh
JOIN Person.Person p
ON soh.CustomerId = p.BusinessEntityId
WHERE p.FirstName = 'John'
GROUP BY p.BusinessEntityID,
p.FirstName,
p.LastName
OPTION (QUERYTRACEON 2363,QUERYTRACEON 3604);
The output on this one is quite verbose (1094 lines on my system!), so the output below is truncated somewhat for readability:
...
Begin selectivity computation
Input tree:
LogOp_Join
CStCollBaseTable(ID=1, CARD=31465 TBL: sales.SalesOrderHeader AS TBL: soh)
CStCollFilter(ID=3, CARD=58.7138)
CStCollBaseTable(ID=2, CARD=19972 TBL: Person.Person AS TBL: p)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [p].BusinessEntityID
ScaOp_Identifier QCOL: [soh].CustomerID
Plan for computation:
CSelCalcExpressionComparedToExpression( QCOL: [soh].CustomerID x_cmpEq QCOL: [p].BusinessEntityID )
Loaded histogram for column QCOL: [soh].CustomerID from stats with id 4
Loaded histogram for column QCOL: [p].BusinessEntityID from stats with id 1
Scaling join selectivity up by 1.04462 to compensate for more distinct values in dimension than in fact
Selectivity: 2.90282e-005
Stats collection generated:
CStCollJoin(ID=4, CARD=53.6274 x_jtInner)
CStCollBaseTable(ID=1, CARD=31465 TBL: sales.SalesOrderHeader AS TBL: soh)
CStCollFilter(ID=3, CARD=58.7138)
CStCollBaseTable(ID=2, CARD=19972 TBL: Person.Person AS TBL: p)
...
The two lines of relevance to this example are those that start with “Loaded histogram for column”
The “New” Days
The “new” behaviour, which is now almost ten years old at the time of writing, is baked right into the execution plan on SQL Server versions from 2017 as long as the query is compiled using the “new” cardinality estimator. The stats usage is available in the execution plan under the OptimizerStatsUsage node.
Let’s run the query above against a newer instance. Here I am using the same AdventureWorks2014 database for consistency, but now running against a SQL Server 2022 instance using compatibility level 140. This time, no hints or trace flags are required, just the execution plan, estimated or actual:
SELECT p.FirstName,
p.LastName,
SUM(soh.TotalDue) AS TotalSales
FROM sales.SalesOrderHeader soh
JOIN Person.Person p
ON soh.CustomerId = p.BusinessEntityId
WHERE p.FirstName = 'John'
GROUP BY p.BusinessEntityID,
p.FirstName,
p.LastName;
If we look at the actual execution plan, we can see the information presented in the execution plan itself under the Select node (or whatever the root node is), we see a property for each statistic loaded and, in addition, some extra information about the statistic such as the modification count and the sample rate:
Just to prove I’ve nothing funky up my sleeve, I’ll run the same query again in the same Management Studio instance against the 2014 instance:
We can see in the properties on the right that the property OptimizerStatsUsage is not there.
This is a lot easier than before, as it is on by default and doesn’t require any extra permissions. Being included in the execution plan means that we can more easily parse out this information if we want to do anything with it.
References / Further Reading
Konstantin Taranov – SQL Server Trace Flags – Complete list
Microsoft – DBCC TRACEON (Transact-SQL)
Paul White – Cardinality Estimation for Multiple Predicates
Paul White – Finding the Statistics Used to Compile an Execution Plan