When Adaptive Server caches a statement, it changes the statement from an ad-hoc query to a lightweight stored procedure. For example, if you issue sp_bindefault or sp_bindrule in the same batch as the statement that invokes the default or rule without caching the statement, Adaptive Server issues error message 540. However, if you cache the statement, Adaptive Server binds the default or rule to the column.
Adaptive Server may issue a runtime errors instead of normalization
errors when statements are cached and executed as stored procedures.
For example, this query raises error number 241 if the statement
is not cached, but raises a Truncation error and
aborts the command if the statement is cached.
create table t1(c1 numeric(5,2) go insert t1 values(3.123) go
To process ad hoc SQL statements using the statement cache:
Adaptive Server parses the statement.
If the statement should be cached (see “Caching conditions”), Adaptive Server computes a hash value from the statement. Adaptive Server uses this hash value to search for a matching statement in the statement cache (see “Statement matching criteria”).
If a match is found in the statement cache, Adaptive Server skips to step 4.
If a match is not found, Adaptive Server proceeds to step 2.
Adaptive Server caches the SQL statement text.
Adaptive Server wraps the SQL statement with a lightweight stored procedure and changes any local variables into procedure parameters. The internal representation of the lightweight procedure is not yet compiled into the plan.
Adaptive Server converts the SQL statement into an execute statement for the corresponding lightweight procedure.
If there is no plan in the cache, Adaptive Server compiles the procedure and caches the plan. Adaptive Server compiles the plan using the assigned runtime values for the local variables.
If the plan exists but is invalid, Adaptive Server returns to step 3 using the text of the cached SQL statement.
Adaptive Server then executes the procedure. Substituting the lightweight procedure increments the @@nestlevel global variable.
Adaptive Server matches an ad hoc SQL statement to a cached statement by the SQL text and by login (particularly if both users have sa_role), user ID, database ID, and session state settings. The relevant session state consists of settings for these set command parameters:
forceplan
jtc
parallel_degree
prefetch
quoted_identifier
table count
transaction isolation level
chained (transaction mode)
Settings for these parameters determine the behavior of the plan Adaptive Server produces for a cached statement. See the Reference Manual: Commands.
You must configure set chained on/off in
its own batch if you enable the statement cache.
Adaptive Server caches statements according to these conditions:
Adaptive Server currently caches select, update, delete, and insert select statements with at least one table reference.
Statements are not cached if the abstract plan dump or abstract plan load parameters are enabled. That is, you cannot enable the statement cache and enable the abstract plan load and abstract plan dump at configuration parameters the same time
Adaptive Server does not cache select into statements, cursor statements, dynamic statements, plain insert (not insert select) statements, and statements within stored procedures, views, and triggers. Statements that refer to temporary tables are not cached, nor are statements with language parameters transmitted as binary large object (BLOB) datatypes. Statements that are prohibitively large are not cached. Also, select statements that are part of a conditional if exists or if not exists clause are not cached.
Each cached statement requires approximately 1K memory in the statement cache, depending on the length of the SQL text. Each cached plan requires at least 2K of memory in the procedure cache. To estimate the statement cache memory required, account for the following for each statement to be cached:
The length of the SQL statement, in bytes, rounded up to the nearest multiple of 256.
Approximately 100 bytes overhead.
The size of the plan in the procedure cache. This size is equivalent to the size of a stored procedure plan containing only the cached statement. There may be duplicates of the plan for a single cached statement being used concurrently by two or more users.
