The configuration option dynamic SQL plan pinning has been introduced in Adaptive Server 15.7 ESD #4 to improve Adaptive Server performance by reducing the time spent by server connections waiting for access to the query plan manager.
When a program sends a dynamic prepared SQL statement to Adaptive Server, Adaptive Server internally creates a stored procedure containing the prepared SQL statement. This stored procedure is similar to a user-created stored procedure, except that it has no system catalog entries, that is, it exists in memory only. The first time that the prepared statement is executed, a query plan is compiled and executed. At the end of execution, the query plan is released to the query plan manager in Adaptive Server for re-use. When the same statement is executed again, Adaptive Server calls to the query plan manager to see if a query plan is available, and if so, the query plan manager returns the query plan to the Adaptive Server connection to execute. At the end of execution, the query plan is returned to the query plan manager.
All Adaptive Server connections can access the query plan manager to ask for available query plans as well as to store new query plans that they have compiled and finished executing. However, only one Adaptive Server connection can access the query plan manager at a time, to avoid multiple connections getting the same query plan at the same time (only a single connection can execute a given query plan at a time). Each connection will access the query plan manager twice for each dynamic prepared SQL statement that it executes: Once to acquire the query plan and once to release it for re-use.
In a highly concurrent environment (many Adaptive Server connections running dynamic prepared SQL statements at the same time), Adaptive Server performance may be degraded because each connection must wait its turn to access the query plan manager when retrieving or storing a query plan. The dynamic SQL plan pinning feature was introduced to improve Adaptive Server performance by reducing the time spent by server connections waiting for access to the query plan manager. When query plan pinning is enabled, each Adaptive Server connection compiles a query plan for each dynamic prepared statement that it executes and does not release it to the query plan manager for re-use. Each connection keeps all query plans that it compiles for its own exclusive re-use, thus, avoiding the need to access to the query plan manager on the second and subsequent executions of the same dynamic prepared SQL statement.
To enable dynamic SQL plan pinning, use:
sp_configure 'dynamic SQL plan pinning', 1
Once enabled, dynamic SQL plan pinning will only take effect if one of the following options is also enabled:
sp_configure 'streamlined dynamic SQL', 1
sp_configure 'enable functionality group', 1
Because each Adaptive Server connection keeps its own copy of each query plan and query plans are created from the procedure cache memory pool, this pool may need to be configured to a larger size when dynamic SQL plan pinning is enabled. Exactly how much larger the procedure cache needs to be depends upon the number of concurrent Adaptive Server connections executing dynamic SQL prepared statements: In extreme environments with small procedure cache sizes, 2-to-3 times larger may be required.