SQL SERVER – Identifying Statistics Used by Query

“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 (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – Identifying Statistics Used by Query

  1. 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

    Like

  2. 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.

    Like

  3. 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 :)

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  5. 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.

    http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

    Also Fabiano Amorim has a blog about how to see what statistic objects were used by the Query Plan. Below is the link.

    http://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/

    Regards,
    Nawaz.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s