ASE Procedure Cache and Engine Local Cache (ELC)
When an ASE experience procedure cache spinlock contention, most users implement two methods to reduce the spinlock contention:
trace flag (TF) 753 (disable large allocation) and
scheduled dbcc proc_cache(free_unused) to remove the unused plan.
In this blog, I would like to give you more information on the TF 753 and how this TF help to reduce the contention.
When ASE procedure cache manager requested memory, and there is not enough free procedure cache or not enough contiguous memory chunk. The ASE memory manager flush an unused stored procedure plan out of the procedure cache to free up memory. The memory manager tries again to grab the requested memory. If it is still not available, because either the freed memory was too small or it did not free a large enough chunk of contiguous memory, memory manage find another unused store procedure plan and flush it again. It repeat the calls until it acquired the memory or a 701 error raised. When the Error 701 was raised, all unused procedure plans flushed out. All these operations require spinlocks. In the old days, the procedure cache memory allocation is always in 2K chunk. In ASE 12.5, the max allocation memory was increased to 16K. In ASE 15, it was increased to 64K. The idea of Auto-tune Large Allocation is to reduce the number of replacement of the procedure when a connection requests a large chunk of procedure cache. The feature was implemented by scale up the size of the request based on previous allocation request from the same connection. It starts with requesting 2K, if the requests was successful for a pre-defined number for time, it double the requesting size. ASE follows the same algorithm until all requests are satisfied. If the request failed in between, it reduce the size and scale up again. Under a heavy workload or procedure cache is close to full, a process could take longer to acquire the memory and causes procedure cache spinlock contention.Trace flag 753 is use to disable large allocation auto tune in memory manager.
ASE engine local cache (ELC). is another way to alleviate the problem, ASE ELC does not need spinlock to access it. ELC exists in ASE procedure cache. In ASE 16 documents, it provides a good explanation of ELC and the enhancement.
Procedure Cache Enhancement
High workload scenarios can increase contention on procedure cache, which SAP ASE alleviates by setting aside local memory for each engine. Because this memory is local, accessing it does not cause contention.
In versions earlier than 16.0, SAP ASE set aside 25% of the procedure cache for the engine local cache (ELC) for use by requests of a specific size. Using trace flag 758 increased the size to 50% of the procedure cache, and made the ELC available for all request sizes.
SAP ASE versions 16.0 and later enable ELC by default. 50% of the procedure cache is used for ELC, which services all request sizes. The engine local cache percent configuration parameter determines the size of the ELC. The default value, 50, means that the local cache uses fifty percent of the procedure cache, and each engine receives:
((0.50 * procedure_cache_size) / number_of_online_engines)
Increasing the size of ELC can decrease contention on the procedure cache. For example, to increase ELC from default 50% to 60%, issue:
sp_configure ‘engine local cache percent’, 60
Additionally, SAP ASE versions 16.0 and later include these configuration parameters to control the engine local cache:
- enable large chunk elc (replaces traceflag 758)
- large allocation auto tune (replaces traceflag 753)
ELC is based on free proc cache - so the formula above should read:
((0.50 * procedure_cache_free) / number_of_online_engines)
DBA's also need to be aware that the typical query can use between 150-300KB of proc cache. During query optimization when you are dealing with ~10 or more work plans, you can easily consume 2-5MB of proc cache per query. A single proc can use 5-10MB of proc cache for the final plan. Given that we would like to minimize the amount of fetches from the global pool, and given the typical query processing, we would like to see more than one spid's worth of proc cache free in ELC so that the engine doesn't have to grab more proc cache for every spid context switch and especially multiple times for a single spid. Hence, using a rule of thumb of 50MB of proc cache free per engine yields 25MB of ELC per engine. With 30 engines (as an example), we would need ~1.5GB of proc cache *free* to satisfy decent ELC sizes. Less could result in much higher proc cache spinlock contention due to exhausting ELC more quickly.
I do not know whether this is proper place to ask, I am sorry if not, feel free to erase it but please send me an email where can I ask.
Thank you for this information. I have one question. From what I have seen in manuals ELC is not only for procedures but also for data. If this is correct, what happens when data page in one thread's ELC is needed by another thread ?
Came upon this in 2021, so for the sake of anyone else reading this thread:
ELC is strictly for procedure cache only. It does not apply to any data cache.