对使用sys.dm_exec_query_stats动态视图者的警告（A Warning to Those Using sys.dm_exec_query_stats)

原文：

http://sqlblog.com/blogs/adam_machanic/archive/2010/04/22/a-warning-to-those-using-sys-dm-exec-query-stats.aspx

 

Adam Machanic

Adam Machanic, Boston-based independent database consultant, writer, and speaker, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.

A Warning to Those Using sys.dm_exec_query_stats

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.