Avoiding compilation

Using small stored procs or sp_executesql dramatically reduces the number of
recompiles and increases the reuse of execution plans. This is evident from
both the usecount in syscacheobjects, perfmon, and profiler. However I'm at
a loss to determine what causes a compilation. Under rare circumstances the
usecount for Compiled Plan does not increase as statements are run. Seems
to correspond to when there is no execution plan. It would seem to me that
compilation is a resource intensive task that if possible (data and schema
are not changing) should be held to a minimum.

How does one encourage the reuse of compile plans?
Is this the same as minimizing compilation?

Looks like some of this behavior is changing in SQL 2005...

Thanks,
DannyI am not privy to all the internals, there are better guys out there for
that .. but my $.02

When enough has changed in the data and table statistics where SQL Server
thinks it could get a better query plan it will do a recompile.

I know you can force a recompile by adding a parm to the proc create
statement, but don't know how to have it NOT recompile, or if you'd really
want to (I'd like to continue to use this crappy query plan please)..

Some thing that almost guarantees a recompile is the creation of #Temp
Tables in the proc.
Substitute a table variable to get around that.

"Danny" <istdrs@.flash.net> wrote in message
news:59Rrd.1559$nE7.982@.newssvr17.news.prodigy.com ...
> Using small stored procs or sp_executesql dramatically reduces the number
> of recompiles and increases the reuse of execution plans. This is evident
> from both the usecount in syscacheobjects, perfmon, and profiler. However
> I'm at a loss to determine what causes a compilation. Under rare
> circumstances the usecount for Compiled Plan does not increase as
> statements are run. Seems to correspond to when there is no execution
> plan. It would seem to me that compilation is a resource intensive task
> that if possible (data and schema are not changing) should be held to a
> minimum.
> How does one encourage the reuse of compile plans?
> Is this the same as minimizing compilation?
> Looks like some of this behavior is changing in SQL 2005...
> Thanks,
> Danny|||David,

In general on a DSS, recompiles I can avoid. Although I've always heard
that using table variables instead or temp tables in procs reduces the
chance of recompile. But in simple testing of temp tables in procs (see
sample code below), I'm not seeing any indication of a recompile either in
syscacheobjects, perfmon, or profiler.

Any know if this is true and why?

Danny

use northwind
go

Create proc TestRecompile (@.X int)
As
set nocount on

-- create temp table
create table #testtable (col1 int not null)
insert into #testtable values (@.X)

--do something else
select * from northwind.dbo.[order details] o
join #testtable t on o.orderid = t.col1

go

dbcc FREEPROCCACHE

exec TestRecompile 10248

select bucketid, cacheobjtype, objid, usecounts from
master..syscacheobjects where objtype = 'Proc' and sql = 'TestRecompile'
-- recompile on first run

exec TestRecompile 10255
-- Perfmon shows compilation
-- usecounts increases to 2 for Compiled Plan no corresponding Executable
plan

select bucketid, cacheobjtype, objid, usecounts from
master..syscacheobjects where objtype = 'Proc' and sql = 'TestRecompile'

--drop proc TestRecompile

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:xsRrd.1020236$Gx4.172215@.bgtnsc04-news.ops.worldnet.att.net...
>I am not privy to all the internals, there are better guys out there for
>that .. but my $.02
> When enough has changed in the data and table statistics where SQL Server
> thinks it could get a better query plan it will do a recompile.
> I know you can force a recompile by adding a parm to the proc create
> statement, but don't know how to have it NOT recompile, or if you'd really
> want to (I'd like to continue to use this crappy query plan please)..
> Some thing that almost guarantees a recompile is the creation of #Temp
> Tables in the proc.
> Substitute a table variable to get around that.
> "Danny" <istdrs@.flash.net> wrote in message
> news:59Rrd.1559$nE7.982@.newssvr17.news.prodigy.com ...
>> Using small stored procs or sp_executesql dramatically reduces the number
>> of recompiles and increases the reuse of execution plans. This is
>> evident from both the usecount in syscacheobjects, perfmon, and profiler.
>> However I'm at a loss to determine what causes a compilation. Under rare
>> circumstances the usecount for Compiled Plan does not increase as
>> statements are run. Seems to correspond to when there is no execution
>> plan. It would seem to me that compilation is a resource intensive task
>> that if possible (data and schema are not changing) should be held to a
>> minimum.
>>
>> How does one encourage the reuse of compile plans?
>> Is this the same as minimizing compilation?
>>
>> Looks like some of this behavior is changing in SQL 2005...
>>
>> Thanks,
>> Danny
>>|||Danny (istdrs@.flash.net) writes:
> In general on a DSS, recompiles I can avoid. Although I've always heard
> that using table variables instead or temp tables in procs reduces the
> chance of recompile. But in simple testing of temp tables in procs (see
> sample code below), I'm not seeing any indication of a recompile either in
> syscacheobjects, perfmon, or profiler.
> Any know if this is true and why?

It's not that simple that if you have a temp table you get a recompile.
But if you create a temp table, fill it with quite some data, you are
likely to see a recompile in the next operation. Also, if you create a
temp table in the middle of a stored procedure, the bets for a reompile
are good.

Note that sometimes recompilations are bad, and sometimes they are heaven-
sent, all depending on the nature of the stored procedure.

Rather than discussing the topic in detail myself, I refer you to this white
paper: http://www.microsoft.com/technet/pr...005/recomp.mspx.
While it is written for SQL 2005, it gives plenty of details that applies
to SQL 2000 as well. The biggest difference between the two, is that
SQL2005 adds statement recompilation which is not in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp