Turning off recompilation of procs

[Former Member]

Sybase 15.7 and 16 do a lot more compilations of procedures. Generally, I suspect this is a good things as it increases the likelihood of it getting better query plans (at least it seems that way to me).

However, there are instances where we'd prefer not to recompile proc. We have the option to add "with recompile" to a proc but is there a way to stop a proc recompiling ? ie "with no recompile".

Under heavy loads, we're seeing ADO bulk copy timeout on initialisation due to sp_drv_bcpmetadata timing out. Its timing out on recompiling of the proc (and the SDK hasn't changed the default timeout from 30s).

We could try setting "deferred compilation" to 0 but we don't want to do it across the system.

[Mark_A_Parsons]

OK, did some more testing and it looks like an easier workaround is to just load an original copy of the following into each database (plus model if tempdb needs to be loaded at dataserver startup):

sp_drv_bcpmetadata (procedure)

sp_drv_getcomment (function)

So, no need to modify any code (at least not according to my tests).

------------

My test script:

set showplan on
go
use master
go
exec test1..sp_drv_bcpmetadata sysobjects
go
exec test2..sp_drv_bcpmetadata sysobjects
go
exec test3..sp_drv_bcpmetadata sysobjects
go
exec test4..sp_drv_bcpmetadata sysobjects
go

When executed with just the sybsystemprocs version of the proc/function I get the following recompilation messages:

QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
QUERY PLAN IS RECOMPILED DUE TO DEFERRED_COMPILE_FAIL, TABMISSING.
QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
QUERY PLAN IS RECOMPILED DUE TO DEFERRED_COMPILE_FAIL, TABMISSING.
QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
QUERY PLAN IS RECOMPILED DUE TO DEFERRED_COMPILE_FAIL, TABMISSING.
QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.

When executed with copies of the proc/function in each database I get *no* recompilation messages.

[Former Member]

There is the configuration settting

"optimize temp table resolution"

which will reduce compilations.

[Former Member]

The blocking is simply on sysprocedures as its re-compiling the procedure for different users in different db's. We only spotted it because it timed out after 30s with an error.

The timeout only happens under heavy load - where we're hitting 100% cpu usage. When we don't ahve not 100% CPU we get blocking but it doesn't timeout.

I guess I could reproduce it will many calls to the proc all in different databases.

[Former Member]

Thanks - thats the conclusion we came to as well 😉

From my experience, Sybase 16 does appear to have less contention - but obviously as you remove one bottleneck you hit another one.

We're now getting huge contention of BCPing using the SDK due to recompiles of this proc.As the SDK just timesout after 30s (and no way to stop this) its a huge issue.

[Former Member]

Not sure we can do that in this case - the recompilaton is due to database changes.

It's also a proc supplied by SAP/Sybase - which they could change.

[former_member188958]

You could minimize the impact of recompilations by writing abstract plans into the procedure's statements.