Home Uncategorized A Warning to Those Using sys.dm_exec_query_stats

    A Warning to Those Using sys.dm_exec_query_stats

    1241
    14

    The sys.dm_exec_query_stats view is one of my favorite DMVs. It has replaced a large chunk of what I used to use SQL Trace for–pulling metrics about what queries are running and how often–and it makes this kind of data collection painless and automatic. What’s not to love? But use cases for the view are a topic for another post. Today I want to quickly point out an inconsistency.

    If you’re using this view heavily, as I am, you should know that in some cases your queries will not get a row. One such case, as it turns out, is any time an ALTER TABLE appears in your batch. “No big deal,” you might be thinking. “I don’t regularly alter tables in stored procedures.” But think again. Do you ever create a temporary table using SELECT INTO? Do you ever create indexes on the resultant table? I know I do–and quite often those indexes are primary keys, or unique constraints.

    The interesting thing is that CREATE INDEX does not suffer from this problem. You can create unique indexes all day long and still get entries in the query stats DMV. And thus the inconsistency: Using ALTER TABLE to create a primary key or unique constraint on a temporary table is, for all intents and purposes exactly the same thing as creating a unique index using CREATE INDEX. But one works with the query_stats DMV and the other doesn’t.

    It’s not difficult to imagine how this inconsistency arose; just rather annoying in practice. I went through a bunch of code today and changed several instances of ALTER TABLE to CREATE INDEX, and my monitoring scripts are already working better as a result. I recommend that you take the time to do the same, if you use this view.

    To conclude this post, a quick repro so that you can see the issue:

    SELECT
        'abc' AS a
    INTO #x
    
    ALTER TABLE #x
    ADD UNIQUE (a)
    
    SELECT *
    FROM sys.dm_exec_query_stats
    WHERE
        plan_handle IN
        (
            SELECT
                plan_handle
            FROM sys.dm_exec_requests
            WHERE
                session_id = @@SPID
        )
    
    DROP TABLE #x
    GO
    
    
    SELECT
        'abc' AS a
    INTO #x
    
    CREATE UNIQUE INDEX whatever
    ON #x (a)
    
    SELECT *
    FROM sys.dm_exec_query_stats
    WHERE
        plan_handle IN
        (
            SELECT
                plan_handle
            FROM sys.dm_exec_requests
            WHERE
                session_id = @@SPID
        )
    
    DROP TABLE #x
    GO
    

    Enjoy, and best of luck in all of your monitoring endeavors.

    Previous articleT-SQL Tuesday #005: On Technical Reporting
    Next articleT-SQL Tuesday #006: LOB Data
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    14 COMMENTS

    1. That explains a lot. This week I’m analyzing a migration script (bringing one version of a schema up to another version) and the dmv wasn’t it’s usual self. There were lots of table-alterin’ going on.
      I got better results in my situation with profiler (actually a server trace) and cleartrace.

    2. Very good to know. I have a stored procedure that is not showing up. So I just did a test and I found that you will not get a new row if you open a symmetric key in the procedure. Thanks for the information.

    3. Ptrick, thanks for sharing.
      If anyone else notices similar areas please leave a note; would be great to have all of these (or at least a large number) documented. I can’t find any information on this in BOL.

    4. It is probably related to the way execution plans are cached in memory. Execution plans for procedures containing cryptographic functions won’t be cached at all (for security reasons) as far as I’m aware. It explains Ptrick’s case with symmetric key. Thanks for a good post.

    5. Same thing if you’re used to monitor running queries with
      sys.dm_exec_requests er
      CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) st
      sys.dm_exec_sql_text doesn’t return ALTER TABLE, so you don’t see your session using CROSS APPLY (I used CROSS APPLY in order to eliminate system sessions and sessions that do nothing). Actually only [command] column of sys.dm_exec_requests returns "ALTER TABLE".

    6. FYI– I can’t reproduce this issue in SQL Server 2012– looks like the issue may have been fixed. (I just reran your scripts, I didn’t do any additional testing. I see what you see on 2008, but not on 2012.)

    7. Is there any workaround for this on SQL Server 2008? I’m creating a monitoring solution and I need to get the execution time of stored procedures what may include ALTER TABLE statements (e.g. I need a solution that will work on any stored procedure, regardless of its code and that I can easily query from a .NET application)…?

    8. I just tried this code in a sql2014 instance, and both had rows show in query _stats.  looks like it got fixed at some point.

    Comments are closed.