-- Detecting Recompiles

use AdventureWorks          -- On SQL Server 2000, say "use pubs" 
go 
drop procedure DemoProc1 
drop table #t1
go 
create procedure DemoProc1 as 
create table #t1 (a int, b int) 
select * from #t1 
go 
exec DemoProc1 
go 
exec DemoProc1 
go

-- To see all of the possible recompilation reasons reported for SP:Recompile event, 
-- issue the following query on SQL Server 2005:
select v.subclass_name, v.subclass_value 
from sys.trace_events e inner join sys.trace_subclass_values v  
on e.trace_event_id = v.trace_event_id 
where e.name = 'SP:Recompile'

DBCC FREEPROCCACHE

-- Mixing Data Definition Language (DDL) and Data Manipulation Language (DML) 
-- statements within a batch or stored procedure is a bad idea because it can cause 
-- unnecessary recompilations. The following example illustrates this using a stored procedure. 
drop table tab1
drop table tab2
drop procedure MixDDLDML 
go 
create procedure MixDDLDML as 
create table tab1 (a int)            -- DDL 
select * from tab1                   -- DML 
create index nc_tab1idx1 on tab1(a)  -- DDL 
select * from tab1                   -- DML 
create table tab2 (a int)            -- DDL 
select * from tab2                   -- DML 
go 
exec MixDDLDML 
go

-- In conclusion, three recompilations happen in both SQL Server 2000 and SQL Server 2005 
-- for this example. The SQL Server 2005 recompilations, however, are cheaper than 
-- SQL Server 2000 ones because they are statement-level rather than stored procedure-level.
-- If the stored procedure is written as follows, an interesting phenomenon is observed.
create procedure DDLBeforeDML as 
create table tab1 (a int)            -- DDL 
create index nc_tab1idx1 on tab1(a)  -- DDL 
create table tab2 (a int)            -- DDL 
select * from tab1                   -- DML 
select * from tab1                   -- DML 
select * from tab2                   -- DML 
go 
exec DDLBeforeDML 
go

/*
In SQL Server 2005, because of statement-level recompilations, three recompilations still 
happen. When compared with the MixDDLDML stored procedure, the number of recompilations has 
not reduced. If the same example is tried on SQL Server 2000, the number of recompilations is 
reduced from 3 to 1. In SQL Server 2000, recompilations are stored procedure-level, and 
therefore, the three "select" statements could be compiled in one shot. In conclusion, 
in SQL Server 2005, the recompilation effort has not increased but the number of 
recompilations has increased when compared to SQL Server 2000.
*/

-- Next, consider the following T-SQL code snippet:
-- dbo.someTable will be used to populate a temp table 
-- subsequently. 
create table dbo.someTable (a int not null, b int not null) 
go 
declare @i int 
set @i = 1 
while (@i <= 2000) 
begin 
    insert into dbo.someTable values (@i, @i+5) 
    set @i = @i + 1 
end 
go 
  
-- This is the stored procedure of main interest. 
create procedure dbo.AlwaysRecompile 
as  
set nocount on 
  
-- create a temp table 
create table #temp1(c int not null, d int not null) 
  
select count(*) from #temp1 
  
-- now populate #temp1 with 2000 rows 
insert into #temp1 
select * from dbo.someTable 
  
-- create a clustered index on #temp1 
create clustered index cl_idx_temp1 on #temp1(c) 
  
select count(*) from #temp1 
go

exec dbo.AlwaysRecompile

/*
In SQL Server 2000, when this stored procedure is executed the first time, the first 
SP:Recompile event is generated for the first "select" statement. This is a deferred compile, 
and not a true recompilation. The second SP:Recompile event is for the second "select". 
When the first recompilation happened, the second "select" was also compiled because 
compilations are batch-level in SQL Server 2000. Then, during execution, schema of #temp1 
changed because of the newly created clustered index. Therefore, reason for the second 
SP:Recompile is schema change.
*/


-- Recompilations due to number of row modifications
-- Consider the following stored procedure and its execution.
use AdventureWorks   -- or say "use pubs" on SQL Server 2000 
go 
create procedure RowCountDemo 
as 
begin 
    create table #t1 (a int, b int) 
 
    declare @i int 
    set @i = 0    while (@i < 20) 
    begin 
       insert into #t1 values (@i, 2*@i - 50) 
 
       select a 
       from #t1  
       where a < 10 or ((b > 20 or a >=100) and (a < 10000)) 
       group by a 
  
       set @i = @i + 1 
    end 
end 
go 
exec RowCountDemo 
go
/*
Recall that the recompilation threshold for a temporary table is 6 when the table is empty 
when the threshold is calculated. When RowCountDemo is executed, a "statistics changed"-related
recompilation can be observed after #t1 contains exactly 6 rows. By changing the upper bound 
of the "while" loop, more recompilations can be observed.
*/


-- Recompilations due to SET option changes
-- Consider the following stored procedure.
use AdventureWorks 
go 
create procedure SetOptionsDemo as 
begin 
    set ansi_nulls off 
    select p.Size, sum(p.ListPrice) 
    from Production.Product p  
         inner join Production.ProductCategory pc 
          on p.ProductSubcategoryID = pc.ProductCategoryID 
    where p.Color = 'Black' 
    group by p.Size 
end 
go 
exec SetOptionsDemo    -- causes a recompilation 
go 
exec SetOptionsDemo    -- does not cause a recompilation 
go

/*
When SetOptionsDemo is executed, the "select" query is compiled with "ansi_nulls" ON. When SetOptionsDemo begins execution, the value of that SET option changes because of "set ansi_nulls off", and therefore the compiled query plan is no longer "valid." It is therefore recompiled with "ansi_nulls" OFF. The second execution does not cause a recompilation because the cached plan is compiled with "ansi_nulls" OFF
*/

drop table #t1

-- Another example of more recompilations in SQL Server 2005 than in SQL Server 2000
-- Consider the following stored procedure.
use AdventureWorks     -- say "use pubs" on SQL Server 2000 
go 
create procedure CreateThenReference as 
begin 
   -- create two temp tables 
   create table #t1(a int, b int) 
   create table #t2(c int, d int) 
 
   -- populate them with some data 
   insert into #t1 values (1, 1) 
   insert into #t1 values (2, 2) 
   insert into #t2 values (3, 2) 
   insert into #t2 values (4, 3) 
 
         -- issue two queries on them 
   select x.a, x.b, sum(y.c) 
   from #t1 x inner join #t2 y on x.b = y.d 
   group by x.b, x.a 
   order by x.b 
  
   select *  
   from #t1 z cross join #t2 w 
   where w.c != 5 or w.c != 2 
end 
go 
exec CreateThenReference 
go

/*
In SQL Server 2005, CreateThenReference's first execution causes six statement-level recompilations: four for the "insert" statements and two for the "select" queries. When the stored procedure starts executing, the initial query plan does not contain plans for either "insert" or "select" statements because the objects that they reference — temporary tables #t1 and #t2 — do not yet exist. After #t1 and #t2 are created, query plans for "insert" and "select" are compiled, and these compilations count as recompilations. In SQL Server 2000, because the entire stored procedure is recompiled at once, only one (stored procedure level) recompilation — the one that is caused when the first "insert" begins execution — happens. At that time, the entire stored procedure is recompiled, and because #t1 and #t2 already exist, the subsequent "insert"s and "select"s can all be compiled in one attempt. Obviously, the number of statement-level recompilations in SQL Server 2005 can be increased without bound by adding more statements that reference such objects as #t1 and #t2.
*/