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)
