8.0 Factors that Affect Batch Cache-ability

During batch compilation we make a determination regarding the cache-ability of the batch. Each statement in the batch is evaluated either as not-cacheable, cacheable, cache even though zero cost, set cost on re-use or not-cacheable due to sensitive information. A batch is marked cacheable if at least one statement in the batch is cacheable.

If a batch contains one statement that contains sensitive information, then the statement is marked not cacheable due to sensitive information. The batch however may have other statements that are evaluated as cacheable, but the presence of even one statement that is marked not cacheable due to sensitive information deems the entire batch not cacheable. Examples of statements that contain sensitive information include: Create/Alter App Role, Create/Alter Login, Create/Alter Credential, Create/Alter/Dump Certificate, Create/Alter/Open Symmetric Key, Create/Alter Asymmetric Key, Create/Alter/Backup/Restore/Open Master Key, Alter/Backup/Restore Service Master Key, Add/Drop Signature and Alter Database. This is important factor to keep in mind when writing application code, because batches that contain such statements cannot benefit from plan re-use.

DDL statements are marked not cacheable, because we expect these statements to be infrequent and to recompile. Therefore if a batch was composed entirely of DDL statements, then the batch is marked not cache-able. There are exceptions however to account for temporary tables DDL operations that are frequent and would benefit from DDL statement caching. Another exception includes drop DDL statements. These statements are marked cacheable. The full exception list includes: Create/Drop Table, Create/Drop Index, Create/Drop Stats, Drop Procedure, Drop Function, Drop View, Drop Rule, Drop Default, Drop Trigger, Drop Aggregate, Drop Synonym and Update Stats.

Sometimes a significant benefit can come from caching batches that evaluate to a zero cost. One such example would be driver generated queries like “SET ANSI_NULLS ON” that are issued every time we make a connection to the server. Often times the driver generated queries are single statement batches that evaluate to a zero cost. We mark such statements cache even though zero cost. Also cursor select, api cursor fetch, queries (DML + select) statements are marked cache even though zero cost.

Now it is interesting to see how these statements (marked cache even on zero cost) when combined with DDL statements without or without sensitive information (marked not cacheable) affect the cache-ability of the batch. If the batch has one statement marked not cacheable due to sensitive information, then the batch is not cached. If the batch has DDL statements without sensitive that are marked not cacheable, and statements marked cache even on zero cost, then the batch is cached.

In SQL Server 2005 RTM and Sp1, a set options statement or transaction statements would be marked as cache even though zero cost, and therefore almost always causing the entire batch to be cached. In SQL Server 2005 Sp2 however, unless the batch was entirely composed of set statements or transaction statements or if statements the batch is not cached if it evaluates to a zero cost.

Shell queries corresponding to parameterized (simple and forced) are marked cache-able even if they are zero cost. These queries are inserted into the cache with a zero cost, and on first re-use their cost is set to the original cost. Applications that re-execute the same query (including parameter value) will benefit from caching of shell queries. For other applications that do not re-use the same query, since shell queries are inserted with a zero cost these will be the first candidates for removal from the caches when memory pressure conditions are reached. Note that insert shell queries are never cached. This is because the probability of re-use of the exact same insert query is low.

All other queries are evaluated for cache-ability based on their cost. If a batches needs to use blobs or if the batch has a procedure marked with the recompile option then the batch is marked not cache-able.