Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 119

compilation/recompilation, then forced parameterization is probably not appropriate.
Using forced in the absence of these symptoms may result in degraded performance and/or
throughput because SQL Server takes more time to parameterize a lot of queries that are not
later reused. It can also lead to parameter snifi ng, causing inappropriate plan use.
Forced parameterization can also be more i nely controlled through the use of plan guides.
You will learn about plan guides in more detail later in this chapter.
Looking into the Plan Cache
The plan cache is built on top of the caching infrastructure provided by the SQL OS. This provides
objects called cache stores , which can be used to cache all kinds of objects. The plan cache contains
several different cache stores used for different types of objects.
To see the contents of a few of the cache stores most relevant to this conversation, run the following
T-SQL:
select name, entries_count, pages_kb
from sys.dm_os_memory_cache_counters
where [name] in (
'object plans'
, 'sql plans'
, 'extended stored procedures'
)
Example output when I ran the preceding on my laptop is as follows:
name entries_count pages_kbObject Plans
54 12312SQL Plans 48 2904Extended
Stored Procedures 4 48
Each cache store contains a hash table that is used to provide efi cient storage for the many plans
that may reside in the plan cache at any time. The hash used is based on the plan handle. The hash
provides buckets to store plans, and many plans can reside in any one bucket. SQL Server limits
both the number of plans in any bucket and the total number of hash buckets. This is done to avoid
issues with long lookup times when the cache has to store a large number of plans, which can easily
happen on a busy server handling many different queries.
To i nd performance issues caused by long lookup times, you can look into the contents of the DMV
sys . dm _ os _ memory _ cache _ hash _ tables , as shown in the following example. It is recommended
that no bucket should contain more than 20 objects; and buckets exceeding 100 objects should be
addressed.
select *
from sys.dm_os_memory_cache_hash_tables
where type in (
'cachestore_objcp'
, 'cachestore_sqlcp'
, 'cacchestore_phdr'
, 'cachestore_xproc'
)