Skip to Content
avatar image
Former Member

Turning off recompilation of procs

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.

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    There is the configuration settting

    "optimize temp table resolution"

    which will reduce compilations.

  • Get RSS Feed

5 Answers

  • Dec 02, 2016 at 04:46 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 02, 2016 at 05:05 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 02, 2016 at 06:41 PM

    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.]

    Add comment
    10|10000 characters needed 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.

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

    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.

    Add comment
    10|10000 characters needed 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?

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

    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.

    Add comment
    10|10000 characters needed characters exceeded