자동 통계 업데이트에 인한 recompile 유무 테스트

http://cafe.naver.com/sqlmvp/3487

 

dbcc freeproccache
go

drop table tblx
go

create table tblx
(idx int
,a int
,b int)
go

create clustered index nc_tblx_01 on tblx (idx)
create nonclustered index nc_tblx_02 on tblx (a)

--dbcc show_statistics (tblx, nc_tblx_01)
--dbcc show_statistics (tblx, nc_tblx_02)

if OBJECT_ID ('usp_cluster') is null
exec ('create proc usp_cluster as select 1 ')
go
if OBJECT_ID ('usp_covered') is null
exec ('create proc usp_covered as select 1 ')
go
if OBJECT_ID ('usp_none_covered') is null
exec ('create proc usp_none_covered as select 1 ')
go

alter proc usp_cluster
@a int
as
select * from tblx where idx = @a
go

alter proc usp_covered
@a int
as
select a from tblx where a = @a
go

alter proc usp_none_covered
@a int
as
select * from tblx where a = @a
go

set nocount on
declare @init int = 0
while (@init < 10000) begin
insert into tblx values (1,@init,1)
set @init += 1
end
go

exec dbo.usp_cluster 1
go 10
exec dbo.usp_covered 1
go 10
exec dbo.usp_none_covered 1
go 10

select refcounts, usecounts, plan_generation_num, text, *
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text (a.sql_handle) b
left outer join sys.dm_exec_cached_plans c
on a.plan_handle = c.plan_handle
where text like '%tblx%'
and text not like '%sys.dm%'
and text not like '%insert%'
go

set nocount on
declare @init int = 0
while (@init < 10000) begin
insert into tblx values (1,@init,1)
set @init += 1
end
go

-- 여기서 부터 프로파일링 잡을것

exec dbo.usp_cluster 1
go
exec dbo.usp_covered 1
go
exec dbo.usp_none_covered 1
go

select refcounts, usecounts, plan_generation_num, text, *
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text (a.sql_handle) b
left outer join sys.dm_exec_cached_plans c
on a.plan_handle = c.plan_handle
where text like '%tblx%'
and text not like '%sys.dm%'
and text not like '%insert%'
go

프로시저가 플랜을 재컴파일이 일어남을 잡는 방법 및 재컴파일시 결과

clustered index 만 재컴파일 일어나도록 했고, 나머지는 돌리지 않은 결과 입니다.