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

Avoidance of sp_executesql()

All,
No flames please, just a question that I haven't been able to figure out yet
.
Is there an efficient way to do something like this:
Select A, B From luTable Where A IN (@.AList)
WITH OUT having to resort to the nefarious level of setting role permissions
on luTable (and using sp_executesql and it's merry band)? We use
SQLAuthentication and roles and don't want to have sp_executesql, etc. use a
specific account or role (don't ask...).
I've tried various boondoggles like:
Declare @.AList as varchara(20)
Set @.AList = '1,2,3'
Select A, B From luTable Where A IN (Select @.AList)
and other even more unpleasant concoctions, but, woe to behold, no luck.
Reason I'm asking is I'm trying to wean programmers from using sp_executesql
et. al. and avoid putting select permissions directly on user tables, but
they always throw the "we need to select multiple values using an IN clause
and the users can select multiple values from the select list..." in my
face... and, as of yet, there is no joy in Mudville.
Thankshttp://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Check out:
http://www.sommarskog.se/arrays-in-sql.html
HTH
Jerry
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:1B3FC4E2-9B80-42AE-86DF-C9047E855940@.microsoft.com...
> All,
> No flames please, just a question that I haven't been able to figure out
> yet.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role
> permissions
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
> a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using
> sp_executesql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN
> clause
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||yes you can
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
create table #temp (varcharField varchar(50))
--String manipulation with a pivot table
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='abc,fgt,ddd,ghj'
insert into #temp
SELECT SUBSTRING(',' + @.chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @.chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @.chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @.chvGroupNumbers + ',', NumberID, 1) = ','
Select A, B From luTable l join #temp t on l.A =t.varcharField
more here http://sqlservercode.blogspot.com
"B@.DJJ" wrote:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||Thanks! I had a feeling I was looking under the wrong tree...!
"B@.DJJ" wrote:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
>|||Am Tue, 11 Oct 2005 11:40:05 -0700 schrieb B@.DJJ:

> All,
> No flames please, just a question that I haven't been able to figure out y
et.
> Is there an efficient way to do something like this:
> Select A, B From luTable Where A IN (@.AList)
> WITH OUT having to resort to the nefarious level of setting role permissio
ns
> on luTable (and using sp_executesql and it's merry band)? We use
> SQLAuthentication and roles and don't want to have sp_executesql, etc. use
a
> specific account or role (don't ask...).
> I've tried various boondoggles like:
> Declare @.AList as varchara(20)
> Set @.AList = '1,2,3'
> Select A, B From luTable Where A IN (Select @.AList)
> and other even more unpleasant concoctions, but, woe to behold, no luck.
> Reason I'm asking is I'm trying to wean programmers from using sp_executes
ql
> et. al. and avoid putting select permissions directly on user tables, but
> they always throw the "we need to select multiple values using an IN claus
e
> and the users can select multiple values from the select list..." in my
> face... and, as of yet, there is no joy in Mudville.
> Thanks
maybe something in this way:
declare @.testtable table (a varchar(10), b varchar(10))
insert into @.testtable values('1','aaa')
insert into @.testtable values('11','ccc')
insert into @.testtable values('12','ddd')
insert into @.testtable values('23','eee')
insert into @.testtable values('3','fff')
Declare @.AList as varchar(20)
Set @.AList = ',1,2,3,'
Select A, B From @.testTable Where charindex(','+A+',',@.AList) > 0
For this purpose "we need to select multiple values using an IN clause and
the users can select multiple values from the select list..." this should
work if you have separators not included in the normal values [for example
char(255)?]
bye,
Helmut