Skip to Content
0

Turning off recompilation of procs

Dec 02, 2016 at 11:53 AM

197

avatar image
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.

10 |10000 characters needed characters left characters exceeded
Former Member

There is the configuration settting

"optimize temp table resolution"

which will reduce compilations.

0
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Bret Halford
Dec 02, 2016 at 04:46 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 02, 2016 at 05:05 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Dec 02, 2016 at 06:41 PM
0

As a possible workaround to eliminate both types of recompilation failures ...

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

... you can try loading a modified copy of the sp_drv_bcpmetadata proc into all databases.

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

Start by grabbing a copy of the source code for sp_drv_bcpmetadata and make the following edits:

!!!!!!!!!!!!!!!!!!!!!!!!!

NOTE : With additional testing it looks like you may be able skip/ignore EDIT #1, ie, allow the proc to make the execute() call.

!!!!!!!!!!!!!!!!!!!!!!!!!

EDIT #1: disable use of execute() construct by disabling use of the table qualifier (ie, always set @use_table_qualifier=0); this will force use of the local copy of the proc (more on this later):

... snip ...
    SELECT @use_table_qualifier = CASE DB_NAME() WHEN @table_qualifier THEN 0 ELSE 1 END

-- always disable use of table qualifier
select @use_table_qualifier=0

    IF @use_table_qualifier = 1
    BEGIN
        DECLARE @command VARCHAR(16384)
        SELECT @command = 'EXEC ' + @table_qualifier + '..sp_drv_bcpmetadata @table_name, @table_owner, @table_qualifier'
        EXECUTE(@command)
    END
... snip ...

EDIT #2: disable call to dbo.sp_drv_getcomment() when there's 0 or 1 entry in syscomments for a column's default; note addition of 'c' alias for the syscolumns referencet:

... snip ...
--              dbo.sp_drv_getcomment(cdefault) AS COLUMN_DEF,

                case    when (select count(*) from syscomments cm where cm.id = c.cdefault) > 1
                        then dbo.sp_drv_getcomment(c.cdefault)
                        else (select text from syscomments cm where cm.id = c.cdefault)
                end as COLUMN_DEF,
... snip ...
FROM
                syscolumns c
... snip ...

Now load your custom version of sp_drv_bcpmetadata into all databases that you need to run sp_drv_bcpmetadata against.

Also load the custom proc into the model database if you'll need access to the proc in your tempdb's (after a dataserver reboot).

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

My basic/limited testing shows all recompiles are eliminated when executing the local (to each database) copy of sp_drv_bcpmetadata.

Obviously if you've got any defaults that take up more than one record in syscomments you may get a recompile when calling the dbo.sp_drv_getcomment() function. [You may want to try loading an unmodified copy of dbo.sp_drv_getcomment() into each local database to see if this eliminates the associated recompile.]

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
avatar image
Former Member Dec 05, 2016 at 10:21 AM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Is monProcessWaits showing anything for these lengthy delays/timeouts, or are the spids cpu bound?

If cpu bound, might be interesting to see what kind, if any, numbers you're seeing in monSpinlockActivity?

There's not much to that proc (or the subordinate function) so wondering why/how it could be timing out on a recompile ... excessive (b)locking or spinlocks?

0
avatar image
Former Member Dec 05, 2016 at 05:27 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded