Statistics Used Compile Execution Plan
by In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan.
Trace Flags
We will need three undocumented trace flags. The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab.
The second trace flag is 9292. With this enabled, we get a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For potentially useful statistics, just the header is loaded.
The third trace flag is 9204. With this enabled, we see the ‘interesting’ statistics which end up being fully loaded and used to produce cardinality and distribution estimates for some plan alternative or other. Again, this only happens when a plan is compiled or recompiled – not when a plan is retrieved from cache.
You can enable and disable these flags with the usual DBCC TRACEON and TRACEOFF commands, but it is also possible to enable them just for a particular statement using the undocumented QUERYTRACEON query hint (demonstrated below).
Sample Query
| DBCC freeproccacheselect p.NAME, total_quantity = sum(th.quantity) FROM adventureworks.production.product AS p JOIN adventureworks.production.transactionhistory AS th ON th.productid = p.productid WHERE th.actualcost >= $5.00 AND p.color = N'Red' GROUP BY p.NAME ORDER BY p.NAME OPTION ( querytraceon 3604, querytraceon 9292, querytraceon 9204) |
The DBCC FREEPROCCACHE is just there to empty the plan cache so we get a compilation. You can also evict the current plan from cache if you know its handle (SQL Server 2008) or use a RECOMPILE query hint. Using RECOMPILE is often convenient, but you may get a different plan compared to that obtained without the hint. Note that compiling the query is enough – we do not need to execute the query; simply requesting an ‘estimated plan’ will do. It doesn’t hurt to run it either though, just to be clear.
Sample Output
Stats header loaded: |
There’s no sign of an official way to get this very useful information in Denali, despite it being requested many times over the years.Trace flag 9204 works at least as CTP 3.Far back as SQL Server 2005. Both 92xx flags work in 2008, R2, and Denali.
There’s no sign of an official way to get this very useful information in Denali, despite it being requested many times over the years.Trace flag 9204 works at least as CTP 3.Far back as SQL Server 2005. Both 92xx flags work in 2008, R2, and Denali.
Statistics Says It All
16
Years of Experience
3000
Gratified Students
100
Training Batches
9600
Training Hours
Subscribe
Please subscribe our technical blog to get recent updates.