“Can I know which statistics were used by my query?”
Recently, someone asked this question in my training class of query optimization and performance tuning. I really liked the question. The answer for me is very simple.
“No.”
Well, if I stop here suggesting only “No,” it will be an incomplete answer. Let us continue a bit more. There is no direct method or DVM or any tool which can tell us which statistics were used by any query. In fact, it looks like there is no way one can know if the any created statistics was ever used or not.
The indirect way of knowing this is if one knows which index was used, then one can think the statistics which was created automatically when the index was created may be is used.
I would like to open this question to all of you and ask if you know any method using which one can identify which statistics was used when the query was executed.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I think one way would to be to read sys.dm_exec_query_stats into temporary table or table valued variable, then run your query and select all the rows from sys.dm_exec_query_stats which has last_execution_time greater than the one found in temp table. Use outer apply to get executed SQL statement and compare it to your query to get correct lines.
I’m not really sure about this but something like this:
declare @t table (plan_handle varbinary(64), last_execution_time datetime, execution_count int)
insert into @t
select qs.plan_handle, qs.last_execution_time, execution_count
from sys.dm_exec_query_stats qs
select *
from sys.dm_exec_query_stats
select *
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.plan_handle) st
join @t t on t.plan_handle = qs.plan_handle
where t.last_execution_time > qs.last_execution_time
Hi Marko,
Thanks for the query, interesting subject Pinal, thanks for the article.
An interesting question. I would say that the optimizer would use any given statistic on a Sargable column to reject or accept a possible plan. Given that the accepted plan may not reference the statistics that were use to reject another plan, in my book that does not make them ‘unused’ by the query.
I would vote for the answer that you gave in this blog post i.e., the statistics of the index that is being used to fetch the data is used. Reason is simple. You have X number of indexes defined on a table. How is SQL Server determining which index to be used? Statistics will play a role there and depending on the number of rows that are being retrieved, respective index scan/seek will be done to retrieve the data.
I go with your Answer Pinal :)
Hi dave,
There are 2 ways we can find out what statistics objects we used by the query. One is by enabling the trace flags 3604, 9292, 9204. Below blog by Paul White wlaks through the process.
Also Fabiano Amorim has a blog about how to see what statistic objects were used by the Query Plan. Below is the link.
Regards,
Nawaz.
Though I don’t believe you can determine exactly what was used for a query, if you dig though the XML for the execution plan it will tell you what statistics were used to create it.