Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 65

Caches
SQL Server uses three types of caching mechanism: object store, cache store, and user store.

Object stores are used to cache homogeneous types of stateless data, but it's the cache and user
stores that you'll come across most often. They are very similar in that they're both caches — the
main difference between them is that user stores must be created with their own storage semantics
using the development framework, whereas a cache store implements support for the memory
objects mentioned previously to provide a smaller granularity of memory allocation.

Essentially, the user stores are mainly used by different development teams within Microsoft to
implement their own specii c caches for SQL Server features, so you can treat cache stores and user
stores the same way.

To view the different caches implemented on your SQL Server, use the sys.dm_os_memory_cache_
counters DMV. For example, running the following query will show you all the caches available,
ordered by the total amount of space they consume:

SELECT [name],
[type],
pages_kb,
entries_count
FROM sys.dm_os_memory_cache_counters
ORDER BY pages_kb DESC;

Sample output showing the top three
caches by size is shown in Figure 3-9.

Here, the caches you see are all related
to query processing (discussed further
in Chapter 5). These specii c caches are
used for the following:

FIGURE 3-9

➤

CACHESTORE_OBJCP — Compiled plans for objects such as stored procedures, functions, and
triggers

➤

CACHESTORE_SQLCP — Cached plans for SQL statements or batches that aren't in stored
procedures. If your application doesn't use stored procedures, then the plans are cached
here. However, they are much less likely to be reused than stored procedure plans,
which can lead to a bloated cache taking a lot of memory (see the “Optimize for Ad-Hoc
Workloads” section later in the chapter).

➤

CACHESTORE_PHDR — Algebrizer trees for views, constraints, and defaults. An algebrizer
tree is the parsed SQL text that resolves table and column names.