SQL AZURE - A Warning to Those Using sys.dm_exec_query_stats

Today, I read Dinakar Nethi's blog post on Troubleshooting and Optimizing Queries with SQL Azure. In that article, he discusses how we can use SQL Azure DMVs to troubleshoot poor performing queries. He relies heavily on sys.dm_exec_query_stats DMV, which I use all the time with on-premise SQL Servers.

While reading it, I remembered reading a blog post from Adam Mechanic about how if there is an alter statement in a batch, sys.dm_exec_query_stats will not pick up any of the statements in the batch. This might be because some DDL statements do not generate query plans, but I'm not sure.

I wanted to see if SQL Azure had the same issue. I was hopeful it wouldn't because they change the implementation of many of their views. Nope...the issue is still there in SQL Azure. I re-wrote Adam's script, because SQL Azure does not support SELECT..INTO. Also, tables in SQL Azure require a primary key.

Here's the script:


--BATCH #1
--In this batch, the select does not appear in sys.dm_exec_query_stats

create table #x
(a int primary key
, b varchar(100))

--This is the alter statement that causes the issue.
alter table #x
add unique (a)

insert into #x
values
  (1, 'getstuff')
, (2, 'getMoreStuff')
, (3, 'getEvenMoreStuff')

select *
from #x

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

--BATCH #2
--In this batch, the select does appear in sys.dm_exec_query_stats,
--because I change the alter table to a create index statement

create table #x
(a int primary key
, b varchar(100))

--I change the alter statement to a create unique index statement.
CREATE UNIQUE INDEX whatever
ON #x (b)

insert into #x
values
  (1, 'getstuff')
, (2, 'getMoreStuff')
, (3, 'getEvenMoreStuff')

select *
from #x

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