What Statistics Are Being Used
Published: Mar 20, 2015
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
go

dbcc freeproccache
go

select *
from Person.Person
where lastName like 'D%'
option
(
    
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron