dynamic SQL plan pinning

dynamic SQL plan pinning improves performance by reducing the time spent by server connections waiting for access to the query plan manager.

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  

When a program sends a dynamic prepared SQL statement to SAP ASE, a stored procedure containing the prepared SQL statement is created. 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 for re-use. When the same statement is executed again, the query plan manager is called to see if a query plan is available, and if so, the query plan manager returns the query plan to the server connection to execute. At the end of execution, the query plan is returned to the query plan manager.

All SAP ASE 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 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 server connections running dynamic prepared SQL statements at the same time), performance may be degraded because each connection must wait its turn to access the query plan manager when retrieving or storing a query plan. dynamic SQL plan pinning can improve performance by reducing the time spent by server connections waiting for access to the query plan manager. When query plan pinning is enabled, each 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.  

Because each 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 server connections executing dynamic SQL prepared statements: In extreme environments with small procedure cache sizes, 2-to-3 times larger may be required.