A Warning to Those Using sys.dm_exec_query_stats

re: A Warning to Those Using sys.dm_exec_query_stats

merrillaldrich — Fri, 23 Apr 2010 05:50:18 GMT

Great tip - thank you

re: A Warning to Those Using sys.dm_exec_query_stats

Michael J Swart — Fri, 23 Apr 2010 11:53:43 GMT

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.

re: A Warning to Those Using sys.dm_exec_query_stats

James Luetkehoelter — Fri, 23 Apr 2010 13:05:39 GMT

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

re: A Warning to Those Using sys.dm_exec_query_stats

Ptrick Cahill — Fri, 23 Apr 2010 17:49:36 GMT

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.

re: A Warning to Those Using sys.dm_exec_query_stats

Adam Machanic — Fri, 23 Apr 2010 18:41:25 GMT

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.

re: A Warning to Those Using sys.dm_exec_query_stats

Geyzersky Dmitry — Sat, 24 Apr 2010 06:24:58 GMT

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.

re: A Warning to Those Using sys.dm_exec_query_stats

Michael Zilberstein — Sun, 25 Apr 2010 15:10:40 GMT

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

re: A Warning to Those Using sys.dm_exec_query_stats

PatrickCahill — Mon, 26 Apr 2010 15:11:36 GMT

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

re: A Warning to Those Using sys.dm_exec_query_stats

Ike Ellis — Mon, 13 Dec 2010 18:39:08 GMT

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

re: A Warning to Those Using sys.dm_exec_query_stats

Ike Ellis — Mon, 13 Dec 2010 18:40:01 GMT

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