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:
[sql]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
[/sql]
Enjoy, and best of luck in all of your monitoring endeavors.
Great tip – thank you
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.
Excellent tip! I thought there were things getting dropped..
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.
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.
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.
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".
I am also seeing the same issue when using the view sys.dm_exec_procedure_stats.
Hi Adam,
I used this article as the source to prove the same issue exists with SQL Azure. Let me know if that’s OK with you (or not). I gave you full credit:
<a href="http://ellisteam.blogspot.com/2010/12/sql-azure-warning-to-those-using.html">ellisteam.blogspot.com</a>
Ike
Comment generator didn’t pick up the URL properly. Here it is:
http://ellisteam.blogspot.com/2010/12/sql-azure-warning-to-those-using.html
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.)
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)…?
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.
Hi,
I checked the same in sql server 2014, this issue still persists.