Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.