What Statistics Are Being Used
Published: 27th November 2013
You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?

This is where these trace flags come into their own… they will give you just that information.

For these to have any effect though we first need to utilise another Trace Flag… 3604. This is undocumented but well used. It’s function is to simply redirect the trace output to the Messages tab. Therefore without this trace flag we wouldn’t actually see any results which somewhat defeats the point.

These two trace flags are as follows:

9292 - This will output the statistics objects that the optimizer thinks are interesting. It will only load the header of statistics that it considers as potentially useful but doesn’t use.

9204 - This flag shows the statistics that are fully loaded and therefore actually used in order to obtain cardinality figures.

Let’s show this in a simple example:

use AdventureWorks2012

dbcc freeproccache

select *
from Person.Person
where lastName like 'D%'
querytraceon 3604,
querytraceon 9292,
querytraceon 9204

The output is as follows:

Stats header loaded: DbName: AdventureWorks2012, ObjName: Person.Person, IndexId: 2, ColumnName: LastName, EmptyTable: FALSE

Stats loaded: DbName: AdventureWorks2012, ObjName: Person.Person, IndexId: 2, ColumnName: LastName, EmptyTable: FALSE

(556 row(s) affected)

Looking at this output it’s easy to see exactly which statistics were used by the optimizer. It is also clear that the optimizer only considered 1 statistic which it then ended up loading fully and using for the cardinality estimates.

It’s clear that this would be a little confusing if you were running this in a very large query, but when breaking your query into pieces makes this a very valuable approach to see what statistics are being used and therefore what estimates appear in your execution plans.

The last point to note is that these trace flags do not work when a plan is brought out of cache, hence my use of “dbcc freeproccache” in order to clear the plan cache prior to utilising these flags.
NB: Comments will only appear once they have been moderated.