Procedure cached plans not reused for new connection

[pietercoene]

Hi,

moving to an Azure Cloud VM, our customer complained that the first time executing a (complex) stored procedure, this takes up a long time (~ 15 seconds). From then on, its fast (300 ms). When i look into the master..monCachedProcedures table, i can see the ExecutionCount go up when the same database connection (and other database connections that already executed this proc once) execute this proc. So far so good. But every new database connection does not reuse this cached plan, an new plan is always created (that takes those 15 seconds). After that, all the other existing database connections are using this new cached plan.

I can't seem to find any documentation on this behaviour, does this seem normal?

Greetz
Pieter

[Mark_A_Parsons]

Some assumptions:

• a new connection/spid always sees the first invocation of the proc taking 15 seconds; all subsequent calls (by the same spid) always run fast (~300 ms)
• after the 15-second initial proc call, a query of monCachedProcedures shows the PlanID and CompileDate change and the ExecutionCount is reset to 0; (if there are multiple rows in monCachedProcedures for this proc then I would expect only one of these rows to see said changes after a 15-second proc call)
• once you get past the 15-second initial proc call (ie, all follow-on proc calls are fast @ ~300 ms), all repeated queries of monCachedProcedures show that PlanID and CompileDate columns are not changing while the ExecutionCount column shows an ever increasing/incrementing value
• you are using the same ASE version on the old (non-azure) instance and the new (azure) instance; or if using different ASE versions the differences are in the minor/subversions (ie, we're not comparing ASE 12.5.4 vs ASE 15, or ASE 15 vs ASE 16)

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

re: the lengthy 15-second run time for the 'initial' proc call

The initial lengthy proc call is likely related to an excessive (re)compile time, with subsequent calls being (relatively) fast due to not having to recompile the proc.

But why aren't you seeing this issue on the old (non-azure) instance?

Off the top of my head I'm guessing a difference in optimizer related configuration settings could be at play here with the primary culprit being optimization goal; consider:

• allrows_oltp - enables the fewest optimizer features which in turn can mean the (re)compile phase is (relatively) fast
• allrows_mix [default config] - enables a few more optimizer features (eg, merge joins) which in turn can mean the (re)compile phase takes longer (than allrows_oltp); with complicated, multi-table queries it's not uncommon to see the (re)compile phase take many seconds, or even minutes, to complete
• allrows_dss - enables a few more optimizer features (eg, merge and hash joins) which in turn can mean the (re)compile phase may (still) take longer (than allrows_oltp) to complete; again, total (re)compile time will be based on the complexity of the query(s) in the proc

My best guess on what's happening:

• the old (non-azure) instance is configured with optimization goal == allrows_oltp
• the new (azure) instance is configured with optimization goal == allrows_mix (or allrows_dss)
• the (re)compile process is occurring in both (non-azure, azure) instances but the faster allrows_oltp based (re)compilation is so (relatively) fast that users don't see any noticeable differences in proc speeds

While this could explain the proc calls that take 15 seconds it doesn't explain why all spids are are incurring a 15 second run time the first time each spid calls the proc; spids that find an available entry in procedure cache should not incur the 15 second delay.

FWIW:

• To this day I still recommend clients configure their ASE instances to use optimization goal == allrows_oltp if simply because the extra compilation overhead of allrows_mix/allrows_dss is still quite excessive; for queries where merge and/or hash joins would be of benefit I recommend clients enable/disable connection level settings as needed or use abstract query plans.
• It hasn't been mentioned (so far) in this Q&A so I'll throw out the (obvious) recommendation ... conduct a detailed comparison of the configuration settings (sp_configure) for both (non-azure, azure) instances to insure you're comparing apples to apples.

The next guess would be a change in the application's connection process and/or a change in an ASE login trigger/proc that's changing some compiler related configs at the connection level.  Short of doing some code reviews you could try running sp_options show, null, null, <spid> against processes running on both (non-azure, azure) instances to see if there are any differences in compiler settings at the connection levels.

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

A couple scenarios come to mind where #temp tables can wreak havoc on performance by forcing excessive (re)compile operations

• (sp_configure) optimize temp table resolution is set to the default setting of 0/off (default): when set to 0/off the proc (re)compiles are based on the #temp table object id so multiple spids, creating the same #temp table (but with difference object ids) and then calling the same proc (that references the #temp table), will see recompiles due to the differences in #temp table object ids; when set to 1/on the proc recompiles are typically reduced, if not eliminated, because the differences in object ids are ignored; this should also apply for #temp tables created across multiple tempdbs
• regardless of how optimize temp table resolution is configured you can still see proc recompiles if the different spids have #temp tables with the same name but a difference in the structure of the table, eg, different sets of columns, different order of columns (in the create table call), different datatypes, different indexes

One (obvious) problem with this idea (#temp table related recompilations) is it would not be limited to just the initial proc call (for a given spid); in other words, issues with #temp table related recompilations should be occurring quite frequently throughout the life of a connection/spid where multiple spids are (re)using the same entry from procedure cache

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

An additional idea that builds on the idea of proc recompilations based on #temp table structure changes and which could explain why the only recompilation occurs with a spid's initial proc call:

• spid creates a #temp table with no indexes
• spid calls proc (first time)
• proc checks to see if the #temp table has a specific index; if the index does not exist the proc creates the index
• the creation of the index is considered a change in the #temp table's structure so the proc is recompiled
• proc completes and control is returned to the parent spid
• at this point the #temp table has a new index
• spid calls proc again
• proc checks to see if the #temp table has a specific index; this index exists so no new index needs to be created which in turn means the #temp table's structure does not change which means the proc does not have to be recompiled
• all other spids reusing this proc (from procedure cache) also already have the (new) index so no need to perform the create-index/change-table-structure/recompile-proc phase

A variation on this idea would see the conditional create index replaced with a conditional update statistics call.

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

You've mentioned in a comment the proc uses a global temporary table. I would expect a global temporary table to be treated more like a permanent table (as opposed to a #temp table) if simply because we're dealing with a single object id and a fixed structure; all I've got at this point are questions

• are you using global temporary tables in the old (non-azure) instance
• are you by any chance creating multiple global temporary tables (same name but in different tempdb databases)
• is one instance using a single tempdb while the other instance is using multiple tempdbs

Assuming this isn't an issue with the optimization goal configuration, and there are no performance issues with global temporary tables in the old (non-azure) instance, but there are differences (code changes, introduction of multi-tempdb databases) in the new (azure) instance then I'd probably opt for opening a tech support case to see if there any known issues with your 'new' environment.

[pietercoene]

Hi Mark,

Thx for the info. I have checked the config on the On-Premise and in the Azure and they are exactly the same. I may have mislead you by mentioning Azure, we have the recompile-behaviour on the On-Premise server as well, but that was a very fast server so it was less noticable (i didn't measure it, but i could be just half the time like 6-7 seconds).

Moving to the (expensive) Azure we could not specify the same specs (CPU- and Disk-wise) for this new server, leading to somewhat slower performance.

I did not know the "optimize temp table resolution" option, i will definitely look into that one. I did knew the #temp-table with index meant recompile 😉

As for the global temporary table, i can easily reproduce this issue just by creating a sample procedure with one line (a select to a global temporary table) and the recompile-issue i described occurs. When the stored procedure is not big, a user would not notice this, but sadly, we have a couple of big stored procedures (~ 10.000 lines of SQL code) that all use global temporary tables, so a recompile takes time.

Greetz

[Mark_A_Parsons]

Ahhhh, so this performance issue is occurring in the current environment but is now being magnified by the migration (to azure).

The easy/fast recommendation would be to open a tech support case to find out if there are any known performance issues (and workarounds) related to global temporary tables.

The harder/slower recommendation would be a detailed P&T analysis of the stored proc(s) in question. Without knowing details about your env I'll just dump everything that comes to mind ...

• if (per feedback from tech support) there is a hard-coded performance hit for using global temporary tables then revisit the reason(s) for using global temporary tables
• tweak optimizer settings to see if you can reduce the performance hit for using global temporary tables (eg, is the test proc's (re)compile phase faster under allrows_oltp)
• track down the individual queries that are taking the most time to (re)compile and look at possible steps to speed up the (re)compilation phase (eg, query rewrite, addition of optimizer hints via set and/or abstract query plans)
• if the (re)compilation overhead is related to UNION queries then consider breaking into separate queries that populate one or more #temp tables (then UNION the #temp tables); primary objective being to reduce optimization complexity and overhead by giving the optimizer smaller, easier  queries to process
• break a large proc into a series of smaller procs with the idea being to limit the (re)compilation phase to just those (smaller) procs that contain the problematic queries

Some additional comments re: abstract query plans (AQPs):

• for a query that has a large (re)compilation overhead keep in mind you can capture the AQP (that the optimizer finally generates) and then cut-n-paste said AQP into the query via the plan 'AQP' clause
• a complete AQP can be quite large but can often be reduced to an equivalent partial (and smaller) AQP but will likely require a bit of trial and error to get correct (especially if you don't do a lot of work with AQPs)
• see P&T Series: Query Processing and Abstract Plans for details on using AQPs

[pietercoene]

Hi Mark,

we used to be an longtime OEM Partner with Sybase, then SAP of course but sadly, it was not possible to maintain that OEM Partner-status due to new restrictions of SAP.

We have however found a workaround for our 'global temporary table'-recompile issue. It is not perfect, but we do not notice any performance loss using our work-around.

Workaround:

• create a real table in stead of a global temporary table (we use a separate DB for this), but add a spid column that has a default value @@spid
• create a function _get_spid that select the current spid (select @@spid)
• create a view that selects every column from this new table but adds 'where spid = dbo._get_spid' (we can't use @@spid in a function, so we cheated a bit and use a function for that)
• Now we can use this table as it was a global temporary table, meaning we can add data into the table without defining our own @@spid every time (we are developers, we are lazy)

When using this real table, the stored procedure does not recompile any more on every new connection! We realize that there is function call extra needed, but the data added to this tables is never that much, so performance wise we don't see any issues.

Greetz
Pieter

[DilipVoora]

Hi Pieter,

Re: "Every new database connection does not reuse this cached plan, an new plan is always created. After that, all the other existing database connections are using this new cached plan".

If you think the problem is due to the query plan, check if the new database connection is using sp_recompile before executing the sp. If yes a new query plan will be generated and then the existing connections(trying to execute the sp as part of the code not having sp_recompile)will make use of the existing plan.

If you think the problem is execution time but the existing plan being used, check your cache (default data/user defined cache) is configured properly or not and the other one to concentrate is that any temporary tables (#) that are needed by the new database connection to execute the sp are present or not.

Regards,
Dilip Voora

[pietercoene]

Hi Dilip,

Thx for the response. I have investigated further. It only happens with stored procedure that are using global temporary tables. This behaviour does even occur on a very simple procedure with a few lines of code, if it uses a global temporary table.

I suppose this is internal behaviour of the SAP ASE (we are working on version 16.0 SP04 PL01) and i'm afraid we can not much do about it.

Greetz
Pieter