上QQ阅读APP看书，第一时间看更新

Ad hoc plan caching

An ad hoc query is a T-SQL query that is sent to the server as a block of text with no parameter markers or other constructs. They are typically built on the fly, such as a query that is typed into a query window in SQL Server Management Studio (SSMS) and executed, or one that is sent to the server using the EXECUTE command as in the following code example which can be executed in the AdventureWorks sample database:

EXECUTE (N'SELECT LastName, FirstName, MiddleName FROM Person.Person WHERE PersonType = N''EM'';')

The letter N preceding a string in a T-SQL script indicates that the string should be interpreted as Unicode with UTF-16 encoding. In order to avoid implicit data-type conversions, be sure to specify N for all Unicode string literals when writing T-SQL scripts that involve the NCHAR and NVARCHAR data types.

The process of parsing and optimizing an ad hoc query is like that of a stored procedure, and will be just as costly, so it is worth it for SQL Server to store the resulting plan in the cache in case the same query is ever executed again. The problem with ad hoc caching is that it is extremely difficult to ensure that the resulting plan is reused.

For SQL Server to reuse an ad hoc plan, the incoming query must match the cached query exactly. Every character must be the same, including spaces, line breaks, and capitalization. This even includes comments since they are part of the statement. This is because SQL Server uses a hash function across the entire string to match the T-SQL statement. If even one character is off, the hash values will not match, and SQL Server will again compile, optimize, and cache the incoming ad hoc statement. For this reason, ad hoc caching cannot be relied upon as an effective caching mechanism.

Even if the database is configured to use a case-insensitive collation, the ad hoc plan matching is still case-sensitive because of the algorithm being used to generate the hash value for the query string.

If there are many ad hoc queries being sent to a SQL Server, the plan cache can become bloated with single-use plans. This can cause performance issues on the system as the plan cache will be unnecessarily large, taking up memory that could be better used elsewhere in the system. In this case, turning on the optimize for ad hoc workloads server configuration option is recommended. When this option is turned on, SQL Server will cache a small plan stub object the first time an ad hoc query is executed. This object takes up much less space than a full plan object and will minimize the size of the ad hoc cache. If the query is ever executed a second time, the full plan will be cached.

See Chapter 8 , Building Diagnostic Queries Using DMVs and DMFs, for a query that will help identify single-use plans in the cache.