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
The given information was excellent and useful. This is one of the excellent blog, I have come across. Do share more.
ReplyDeleteAzure Training in Chennai
Azure Training center in Chennai
Cloud Computing Courses in Chennai
Cloud Computing Training in Velachery
VMware course in Chennai
AWS course in Chennai
DevOps Certification in Chennai
Cloud computing Training in Chennai
Very well written post. Thanks for sharing this, I really appreciate you taking the time to share with everyone. PMP Certification Online
ReplyDelete