THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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.

Published Thursday, April 22, 2010 11:15 PM by Adam Machanic
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

merrillaldrich said:

Great tip - thank you

April 23, 2010 1:50 AM
 

Michael J Swart said:

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.

April 23, 2010 7:53 AM
 

James Luetkehoelter said:

Excellent tip! I thought there were things getting dropped..

April 23, 2010 9:05 AM
 

Ptrick Cahill said:

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.

April 23, 2010 1:49 PM
 

Adam Machanic said:

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.

April 23, 2010 2:41 PM
 

Geyzersky Dmitry said:

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.

April 24, 2010 2:24 AM
 

Michael Zilberstein said:

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

April 25, 2010 11:10 AM
 

PatrickCahill said:

I am also seeing the same issue when using the view sys.dm_exec_procedure_stats.

April 26, 2010 11:11 AM
 

Ike Ellis said:

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

December 13, 2010 1:39 PM
 

Ike Ellis said:

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

December 13, 2010 1:40 PM
 

Kendra Little said:

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

September 17, 2013 6:38 PM
 

Saso Koren said:

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)...?

August 21, 2014 9:25 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement